VACUUM

VACUUM serves two roles in SereneDB. On standard tables it is a PostgreSQL-compatible no-op, accepted so that existing tooling keeps working. It is also extended with options that maintain SereneDB's inverted indexes.

Standard VACUUM and ANALYZE

On a standard table VACUUM does no work — it is accepted purely for PostgreSQL compatibility.

The bare form:

Query

VACUUM;

ANALYZE is accepted as well:

Query

VACUUM ANALYZE;

Targeting a specific table, optionally with a column list, is also accepted and is likewise a no-op:

Query

VACUUM my_table(name);

Query

VACUUM ANALYZE my_table(name);

VACUUM FULL is not supported:

Query

VACUUM FULL;

Result

error db error: ERROR: FULL is not yet implemented

Inverted Index Maintenance

SereneDB extends VACUUM with options that maintain inverted indexes. A statement takes at most one of these options, and an extension option cannot be combined with standard options such as ANALYZE. The options fall into three families.

Refreshing — REFRESH_*

Inverted indexes are eventually consistent: rows you INSERT, UPDATE or DELETE may not be visible to queries until the index is refreshed. REFRESH_* publishes pending writes to readers. After writing to an indexed table, refresh it so the new rows become searchable:

Query

INSERT INTO articles VALUES (1, 'the quick brown fox');
VACUUM (REFRESH_TABLE) articles;
SELECT count(*) FROM articles_idx WHERE body @@ ts_phrase('fox');

Result

 count-------     1

Compacting — COMPACT_*

COMPACT_* merges index segments to reclaim space and keep queries fast. It does not change query results:

Query

VACUUM (COMPACT_TABLE) articles;

Recomputing statistics — RECOMPUTE_STATS_*

RECOMPUTE_STATS_* recomputes the index statistics used for relevance scoring and planning:

Query

VACUUM (RECOMPUTE_STATS_TABLE) articles;

Scopes

Each option applies at the scope named by its suffix and takes the matching object name as its argument:

Scope	REFRESH	COMPACT	RECOMPUTE_STATS	Argument
Index	REFRESH_INDEX	COMPACT_INDEX	—	index name
Table	REFRESH_TABLE	COMPACT_TABLE	RECOMPUTE_STATS_TABLE	table name
Schema	REFRESH_SCHEMA	COMPACT_SCHEMA	RECOMPUTE_STATS_SCHEMA	[database.]schema
Database	REFRESH_DATABASE	COMPACT_DATABASE	RECOMPUTE_STATS_DATABASE	database name
Everything	REFRESH_ALL	COMPACT_ALL	RECOMPUTE_STATS_ALL	(none)

RECOMPUTE_STATS additionally accepts a column scope, RECOMPUTE_STATS_COLUMN, whose argument is [schema.]table.column.

Object names may be qualified, for example VACUUM (REFRESH_TABLE) mydb.public.articles. The *_ALL scopes are instance-wide and take no argument.

Combining options

A statement accepts at most one maintenance option, and a maintenance option cannot be mixed with standard VACUUM options such as ANALYZE:

Query

VACUUM (REFRESH_TABLE, ANALYZE) my_table;

Result

db error: ERROR: VACUUM SereneDB option 'refresh_table' cannot be combined with standard VACUUM options

The vacuum_rebuild_indexes setting that governs index rebuilds is fixed at startup and cannot be changed while the database is running:

Query

SET vacuum_rebuild_indexes = 1000000;

Result

error db error: ERROR: Cannot change vacuum_rebuild_indexes setting while database is running

Reclaiming Space

The VACUUM statement does not reclaim space. To reclaim space, use the CHECKPOINT statement or compact the database by creating a fresh copy with the COPY FROM DATABASE statement.

Syntax

See the scope table for the valid ( … ) maintenance options and their arguments.