Maintenance & Introspection

An inverted index is eventually consistent. Writes to the base table are buffered and become searchable only after the index is refreshed; segments are compacted in the background to keep queries fast; and per-term statistics used by scorers can be recomputed. This page covers that lifecycle and how to inspect an index.

Diagramflowchart LR

Visibility and the refresh model

A newly written row is not searchable until a refresh publishes it to readers. There are two ways a refresh happens:

• Automatically -- a background thread refreshes each index on an interval (refresh_interval, default 1000 ms).
• Explicitly -- run VACUUM (REFRESH_TABLE) to publish pending writes immediately. This is what you want right after a bulk load, before querying:

SQL

VACUUM (REFRESH_TABLE) my_logs;
SELECT id FROM logs_idx WHERE msg @@ 'disk' ORDER BY id;

 id
----
  1

Background intervals

Three WITH options control the background lifecycle of an inverted index (set at CREATE INDEX) and of a search table (set at CREATE TABLE ... WITH (storage = 'search', ...)); 0 disables each:

Option	Default	Controls
refresh_interval	1000	Milliseconds between automatic refreshes
compaction_interval	1000	Milliseconds between automatic segment compactions
cleanup_interval_step	1	Automatic refreshes between cleanup passes, which delete the files no segment uses anymore

View-backed indexes have a fourth interval, reindex_interval, which re-scans the view's source for new, changed and removed data -- see Refreshing the index.

An inverted index on a search table has no storage or background tasks of its own: the table's options run refresh and compaction for every index on it. Set them on the table, in CREATE TABLE ... WITH or with ALTER TABLE ... SET, whether or not it has indexes. CREATE INDEX ... WITH and ALTER INDEX ... SET reject them for an index on a search table. They also reject the options that only CREATE TABLE ... WITH sets on a search table: row_group_size, segment_memory_max and optimize_top_k. segment_docs_max doesn't apply to such an index and is rejected too.

SQL

CREATE INDEX my_events_idx ON my_events USING inverted (msg) WITH (refresh_interval = 0);
CREATE INDEX my_events_idx ON my_events USING inverted (msg) WITH (row_group_size = 4096);

db error: ERROR: refresh_interval is a table option on a search-backed table
HINT: Change it with ALTER TABLE ... SET (refresh_interval = ...).

db error: ERROR: row_group_size is a table option on a search-backed table
HINT: Set it in CREATE TABLE ... WITH (storage = 'search', row_group_size = ...).

Background compaction

Compaction is on by default, for inverted indexes and search tables alike: every compaction_interval milliseconds a background task merges segments of similar size into larger ones, so a table loaded in many small batches does not keep one segment per batch. Three more WITH options shape each merge:

Option	Default	Controls
compaction_max_segments	10	Segments merged by one background compaction
compaction_max_segments_bytes	5368709120 (5 GB)	Size budget of one background compaction
compaction_floor_segment_bytes	2097152 (2 MB)	Segments smaller than this count as equal-sized when choosing what to merge

The session settings of the same names hold the defaults a new index or search table takes.

• Disable background compaction with compaction_interval = 0, in WITH at creation or later with ALTER INDEX ... SET for an index and ALTER TABLE ... SET for a search table. Segments are then merged only when you ask for it.
• Enable it again, or change how often it runs, the same way with a non-zero interval. RESET (compaction_interval) returns to the session default. The change applies to the running background task at once.
• Compact manually at any time, whether background compaction is enabled or not, with VACUUM (COMPACT_INDEX) or VACUUM (COMPACT_TABLE) -- see below.

SQL

ALTER INDEX logs_idx SET (compaction_interval = 0);
ALTER TABLE my_events SET (compaction_interval = 0);
VACUUM (COMPACT_TABLE) my_events;
ALTER TABLE my_events RESET (compaction_interval);
ALTER INDEX logs_idx RESET (compaction_interval);

Manual maintenance with VACUUM

Each operation comes in a family scoped to a single index, a table, a schema, a database, or instance-wide (*_ALL, which takes no argument). At most one maintenance option may appear per VACUUM statement.

Operation	Forms	Effect
Refresh	REFRESH_INDEX · REFRESH_TABLE · REFRESH_SCHEMA · REFRESH_DATABASE · REFRESH_ALL	Publish pending writes to readers
Compact	COMPACT_INDEX · COMPACT_TABLE · COMPACT_SCHEMA · COMPACT_DATABASE · COMPACT_ALL	Merge segments to reclaim space and speed queries
Recompute statistics	RECOMPUTE_STATS_COLUMN · RECOMPUTE_STATS_TABLE · RECOMPUTE_STATS_SCHEMA · RECOMPUTE_STATS_DATABASE · RECOMPUTE_STATS_ALL	Rebuild the term statistics used by relevance scoring and planning

SQL

VACUUM (COMPACT_TABLE) my_logs;

SQL

VACUUM (RECOMPUTE_STATS_TABLE) my_logs;

Recompute statistics after large changes in data distribution so that relevance scores and planning stay accurate.

Refreshing view-backed indexes

A view- or external-data-backed index holds a snapshot of its source and does not track later changes on its own. REINDEX INDEX <name> refreshes it in one pass, incrementally for Iceberg tables and file globs, and the reindex_interval index option repeats that pass in the background. See Refreshing the index.

Schema changes on an indexed table

An inverted index pins every column it reads, including columns reached only through an indexed expression -- for example a struct sub-field such as (s['id']::INTEGER) pins the whole s column. On a regular table:

• RENAME column, table or index -- allowed; the index follows the rename.
• ADD COLUMN -- allowed; the index is unaffected.
• ALTER COLUMN ... TYPE on a pinned column -- rejected with Cannot change the type of this column: an index depends on it!; the index stores values of the old type. Drop the index first, change the type, then recreate it.
• ADD / DROP / RENAME of a struct field on a pinned column -- rejected for the same reason, even when the indexed expression targets a different sub-field (the whole column is pinned). Drop the index first.
• DROP COLUMN on a pinned column -- allowed; it drops every index that covers the column.
• DROP COLUMN on any other column -- allowed, including one that comes before a pinned column.

A search table's columns are fixed, whether or not it has indexes: ADD COLUMN, DROP COLUMN and ALTER COLUMN ... TYPE are rejected, while renaming the table or a column is allowed. See Search tables in the ALTER TABLE reference.

Performance

Tuning is mostly about the background cadence and segment layout; use only the options that exist:

• Refresh vs. compaction cadence -- lower refresh_interval for fresher results, raise it (or 0) to reduce overhead on write-heavy tables; compaction_interval governs how aggressively segments merge, and cleanup_interval_step how soon the files they replace are deleted.
• Row-group size -- row_group_size controls the columnstore batch size for stored (INCLUDEd) columns and for norm columns alike; norms share this one setting. It must be a multiple of the vector size (2048), and it is also the unit a scan hands to one worker: an index whose segments hold few row groups cannot spread a scan over more threads than it has row groups, so lower it when rows are expensive to materialise and the index is small. It is fixed at CREATE INDEX and applies to segments written afterwards.
• Build then index -- for a bulk load, create the table, load the data, then create the index; this produces a more compact index than loading into an already-indexed table.
• Whole-relation aggregates -- count(*) over a search table or an index relation (FROM my_index) without a WHERE clause is answered from the document counts, without reading any document. min and max of numeric and temporal INCLUDEd columns and of keyword columns are answered the same way while the relation holds no deleted documents; deleted documents are dropped by compaction, and until then min and max read the documents. EXPLAIN shows no index scan for an aggregate answered this way.
• Top-K -- set optimize_top_k to accelerate ORDER BY <scorer> ... LIMIT k.
• Write memory -- segment_memory_max sets the segment size a write produces and, together with checkpoint_threshold for a serial write, what the write holds in memory.

Write memory

Memory held while writing to a search table depends on segment_memory_max, set in the table's CREATE TABLE ... WITH (segment_memory_max = ...) clause and defaulting to 256 MB, and for a serial write also on checkpoint_threshold, 16 MB by default: a serial write buffers up to that much of its rows before it writes them to the index. Estimate the peak for one writing transaction as:

Write	Peak memory
Serial INSERT / UPDATE	≈ segment_memory_max + checkpoint_threshold
Parallel INSERT / UPDATE	≈ segment_memory_max x threads

Lower segment_memory_max to cap the segment part of that peak, raise it for larger segments and less compaction work. checkpoint_threshold is set for the whole server, and also decides how often the database checkpoints.

sql

CREATE TABLE docs (id BIGINT, body TEXT)
  WITH (storage = 'search', segment_memory_max = 134217728);  -- 128 MB

Tip

DELETE sits outside this bound, but costs little: until the removal is applied, a removed row takes at most a few bytes and a run of consecutive rows almost none, so even a million-row DELETE stays within a few megabytes.

Durability

A commit to a search table is durable once it is in the write-ahead log of the table's database; a refresh then writes the changes into the table's own index files. A checkpoint leaves search tables alone: it keeps the write-ahead log it retires, as data.db.wal.retained.<n> beside the database file, while a search table still has changes in it that its own files do not durably hold, and a later checkpoint deletes the kept logs no table needs anymore. A table's changes are durable in its files once a refresh has written them and a cleanup pass has run after it: by default every automatic refresh runs one (cleanup_interval_step = 1), and so do VACUUM (REFRESH_TABLE) and VACUUM (COMPACT_TABLE). After a crash, the server replays the kept logs and the current one into the tables that need them.

Kept logs pile up while automatic refresh (refresh_interval = 0) or periodic cleanup (cleanup_interval_step = 0) is disabled, until VACUUM (REFRESH_TABLE) or VACUUM (COMPACT_TABLE) saves the table or the server restarts; with only cleanup disabled, a few background compactions save it too.

Session settings

Beyond the per-index WITH options, a few sdb_-prefixed session settings tune search at query time. Set them per connection with SET (and restore with RESET); they affect only the current session:

Setting	Default	Effect
sdb_disable_top_k_optimization	false	When true, the optimizer does not pull ORDER BY <scorer> DESC LIMIT k into the index scan, so WAND top-K pruning never engages. Useful to A/B the optimization or work around a plan regression.
sdb_levenshtein_max_terms	50	Maximum number of dictionary terms a fuzzy predicate (ts_levenshtein) expands to, per index segment. The terms closest to the query survive; the rest neither match nor contribute to scoring. Raise it for wide expansions, or set 0 to match every term within the edit distance. A predicate on a column that a ts_dict_* query enumerates is exempt, since there the terms are the result; other predicates in the same query keep the cap.
sdb_ivf_search_nprobe	-1	Number of IVF cluster lists each segment scans per vector kNN query (ORDER BY <dist> LIMIT k). Higher = better recall, slower queries. -1 lets the engine choose per segment, 1.3 · log10(max(LIMIT, 10)) · √lists with lists the segment's rows over the index's posting size, which keeps recall near 0.95 at any LIMIT and segment size. Does not affect range (WHERE <dist> < r) queries.
sdb_ivf_max_search_fanout	-1	Upper bound on the beam of the descent through a multi-level IVF centroid tree (large segments, or a small sdb_ivf_posting_size), where each level keeps the beam's worth of its nearest centroids and scores only their children. The beam follows sdb_ivf_search_nprobe as the query resolves it; a narrower one scores fewer centroids at some recall cost. Applied after sdb_ivf_min_search_fanout, so setting both to one value fixes the beam. -1 leaves it unbounded. Does not affect range queries.
sdb_ivf_min_search_fanout	-1	Lower bound on the same beam. A wider one reaches clusters a narrow descent misses, and one as wide as the segment's list count makes the descent exact. -1 leaves it unbounded. Does not affect range queries.
sdb_hnsw_ef_search	-1	Beam width of each segment's search for an HNSW kNN query. Higher = better recall, slower queries. A value is used as written, floored only at the share of the query's LIMIT one segment is expected to hold. -1 lets the engine choose: the index's ef_construction or the LIMIT, whichever is larger, which keeps recall high at any LIMIT without tuning.
sdb_hnsw_filter_mode	auto	How an HNSW index answers a kNN query with a WHERE: auto picks by the predicate's estimated selectivity and, where codes are long or the rows near the query are mostly rejected, walks as twohop, scan scores every admitted row, and walk, prune, twohop and bridge force a walk shape. For measurement -- auto is the one to leave alone.
sdb_hnsw_column_filter	auto	How an HNSW walk answers a predicate the columnstore can evaluate: fold evaluates one on columns the index does not index (INCLUDEd ones) over the whole segment before the walk, and answers one on indexed columns from the index; read reads the columns of each row the walk reaches, for indexed columns of a search table too; auto reads while the walk is expected to reach few enough rows for that to cost less than the fold, or than the index for an indexed column, pricing a read by the columns' encodings. For measurement -- auto is the one to leave alone.
sdb_ann_rescore_width	-1	For a quantized (quant other than none) vector index, how far a candidate's code score may be off and still have it re-read at full precision: candidates from every segment are re-scored while their code score plus this many times the code's estimated error could still beat the k-th best exact score. 0 answers from the codes alone; -1 uses a width per quantizer, listed under Re-scoring. Ignored for unquantized indexes.
sdb_ann_force_exact	false	Skip the ANN walk and return the exact answer, for a nearest-k query and a distance range alike. Every row with a vector is considered; an hnsw index's 8-bit codes (sq8, usq8) rule out most rows before their stored vectors are read. Useful to measure recall. false leaves the choice to the engine, which may still answer a selective filter with an exact scan.
sdb_scan_split	auto	How an index scan shares a segment between worker threads. Each worker scans the row groups of a segment in order. tail gives every worker a segment of its own; only when no segment is left unclaimed does an idle worker join the segment with the most row groups left, so a segment is split across workers only at the end of the scan, when there are fewer segments than workers, or when one segment holds most of the data. always puts every worker on the same segment until all of its row groups are claimed; never scans each segment whole on one worker. auto is always when the query has an ORDER BY <column> LIMIT scan order and tail otherwise. Meant for benchmarking and tests.
sdb_scan_order	auto	The order an index scan claims its segments in. largest_first and smallest_first order them by live document count; order is best-first by the ORDER BY column's row-group statistics when the query has a scan order, so the TOP_N bound tightens early. auto is order under a scan order and largest_first otherwise.
sdb_scan_no_split_row_groups	1	A segment with at most this many row groups is always one unit of an index scan and is never split across workers.
sdb_compact_target_segments	1	How many segments VACUUM (COMPACT_*) leaves in a search table. 0 or 1 merges every segment into one; a larger N splits the segments into N disjoint groups and merges each into one segment, so no single merge holds the whole table. A table with N segments or fewer is left as it is.

sql

SET sdb_ivf_search_nprobe = 32;  -- scan more IVF clusters for this session
SET sdb_hnsw_ef_search = 256;    -- widen the HNSW beam
SET sdb_ann_rescore_width = 3;   -- re-score more candidates of a quantized index
SET sdb_levenshtein_max_terms = 256; -- widen fuzzy expansion
-- ... run queries ...
RESET sdb_ivf_search_nprobe;     -- back to the default

Introspection

List the inverted indexes with the sdb_indexes() table function:

SQL

SELECT index_name, table_nameFROM sdb_indexes()WHERE database_name = current_database() AND table_name = 'my_logs';

 index_name | table_name
------------+------------
 logs_idx   | my_logs

The standard pg_indexes view also lists them (alongside the primary-key index), with the CREATE INDEX statement in its indexdef column:

SQL

SELECT indexnameFROM pg_indexesWHERE tablename = 'my_logs'ORDER BY indexname;

 indexname
--------------
 logs_idx
 my_logs_pkey

Info

pg_relation_size() of an inverted index returns its size on disk, counting the refreshed rows only, and pg_indexes_size() of a regular table adds the inverted indexes on it to its other indexes. An index on a search table has no storage of its own, so both count it as 0; its data is part of pg_relation_size() of the table. pg_indexes_size() of a view returns 0.

Statistics in sdb_metrics

The sdb_metrics system table exposes live runtime metrics. Most rows are process-wide gauges (connection counts, active/pending maintenance tasks) and carry a NULL relation_id. In addition, each search table and each inverted index on a regular table or a view contributes a row per statistic, tagged with its oid in the relation_id column -- describing its physical state and the health of its background maintenance. An inverted index on a search table has no storage of its own and contributes no rows: the search table's rows cover its indexes.

Column	Description
metric	Metric name
value	Current value (UBIGINT)
description	Human-readable description
relation_id	Oid of the search table or inverted index the row describes (a relation in pg_class), or NULL for a process-wide gauge

The metrics of each search table and inverted index:

Metric	Meaning
num_docs	Documents in committed segments, including deleted-but-not-yet-compacted
num_live_docs	Live (non-deleted) documents; num_docs - num_live_docs is the deleted backlog awaiting compaction
num_buffered_docs	Documents written but not yet committed -- still in memory, not searchable (see the refresh model)
num_segments	On-disk segments
num_files	Files backing the index
index_size	On-disk index size, in bytes
num_failed_commits	Commit (refresh) operations that failed
num_failed_cleanups	Cleanup operations that failed
num_failed_consolidations	Consolidation (compaction) operations that failed
avg_commit_time_ms	Average duration of the last few commits, in ms
avg_cleanup_time_ms	Average duration of the last few cleanups, in ms
avg_consolidation_time_ms	Average duration of the last few consolidations, in ms

The avg_* values are a rolling average over a small window of the most recent successful operations. Rows cover the search tables and inverted indexes in the current database. Join pg_class to resolve the name. To group the rows by table, join pg_index as well: an index's rows reach its table through indrelid, while a search table's rows carry the table's own oid. Here my_logs is a regular table with the inverted index logs_idx, and my_events a search table:

SQL

SELECT c.relname AS relation, m.metric, m.valueFROM sdb_metrics mJOIN pg_class c ON c.oid = m.relation_idWHERE m.metric IN ('num_docs', 'num_live_docs')ORDER BY c.relname, m.metric;
SELECT t.relname AS table_name, c.relname AS relation, m.value AS live_docsFROM sdb_metrics mJOIN pg_class c ON c.oid = m.relation_idLEFT JOIN pg_index i ON i.indexrelid = c.oidJOIN pg_class t ON t.oid = coalesce(i.indrelid, c.oid)WHERE m.metric = 'num_live_docs'ORDER BY t.relname, c.relname;

 relation  | metric        | value
-----------+---------------+-------
 logs_idx  | num_docs      |     2
 logs_idx  | num_live_docs |     2
 my_events | num_docs      |     3
 my_events | num_live_docs |     3

 table_name | relation  | live_docs
------------+-----------+-----------
 my_events  | my_events |         3
 my_logs    | logs_idx  |         2

A persistently high num_buffered_docs, a growing num_failed_*, or a climbing avg_commit_time_ms are signs that maintenance is falling behind the write rate -- see Background intervals and Performance for the knobs.

See also

• Inverted Index · Ranking
• VACUUM -- full statement reference
• CREATE INDEX ... USING inverted -- index WITH options