gin_fuzzy_search_limit

Definition PG 18 manual

Soft upper limit of the size of the set returned by GIN index scans. For more information see Section 65.4.5.

How it works

gin_fuzzy_search_limit sets the maximum allowed result for exact search by GIN. 0 means no limit. A positive limit makes broad GIN searches return a randomly chosen subset of matches, trading completeness for bounded work; zero preserves exact results.

gin_fuzzy_search_limit is a USER-context setting. An authorized role can change it for a session, while ALTER ROLE or ALTER DATABASE can establish a default for future sessions.

GIN behavior also depends on per-index storage parameters, work or maintenance memory, autovacuum, and the shape of indexed values and predicates.

Operational considerations

Changing gin_fuzzy_search_limit in one session and assuming role defaults, database defaults, or other pooled sessions changed with it.

Applying a cluster-wide GIN value to fix one exceptional index instead of using its storage parameters.

Ignoring cleanup latency, I/O bursts, autovacuum interaction, or result completeness while tuning.

Enabling a positive limit without telling callers that exact searches may return an incomplete random subset.

Workload guidance

OLAP: For batch loads or broad searches, test gin_fuzzy_search_limit against ingestion latency, cleanup spikes, result completeness, and maintenance windows.

OLTP: Change gin_fuzzy_search_limit only after measuring the affected GIN maintenance or search path. Prefer an index-level override when one index, rather than the whole cluster, is exceptional.

SMALL: Do not raise gin_fuzzy_search_limit merely because the default is reached; bound memory and I/O bursts, and keep exact-query semantics unless approximation is explicitly acceptable.