Contents

• pg_sift
  • In five minutes
  • At a glance
    • The benchmark cases
    • Tables that change: track_changes
    • Limitations and trade-offs
  • Quick start
    • Install
    • Try it: analytics
    • Try it: full-text search
  • Features
  • Compared with
  • SQLite and MariaDB
    • SQLite
    • MariaDB
  • Operations and limits
  • SQL reference
  • Options
  • Vulkan (experimental)
  • Docs
  • Security and license

pg_sift

pg_sift makes analytics on PostgreSQL fast: you keep your queries and add an index. A sift_col index, or a sift_table, answers counts, sums, distinct counts, group-bys and top-N from compressed columns; the planner picks it, and a query it cannot prove equal keeps PostgreSQL’s own plan. It also adds ranked BM25 full-text search (boolean, phrase, prefix, regex, fuzzy, facets) with a sift index, which has its own query syntax. One CREATE EXTENSION pg_sift: no separate analytics database, no search server, no shared_preload_libraries. It is for applications that keep their data in PostgreSQL and serve dashboards, reports and search from the same rows. It supports PostgreSQL 17, 18 and 19¹; the search engine also runs as a SQLite extension and a MariaDB library.

In five minutes · At a glance · Quick start · Features · Compared with · SQLite and MariaDB · Operations and limits · SQL reference · Docs

In five minutes

Install from source (PostgreSQL 17, 18 or 19 with server headers and a C compiler; packages and Docker are under Install):

make USE_PGXS=1 && sudo make USE_PGXS=1 install
psql -d mydb -c 'CREATE EXTENSION pg_sift'      # as a superuser

Then, in psql -d mydb, a generated table of 1,000,000 orders, nothing to download:

CREATE TABLE orders AS
SELECT g AS id,
       1 + (random() * 50000)::int AS customer_id,
       (ARRAY['US','DE','BR','JP','IN','FR'])[1 + (random() * 5)::int] AS country,
       (ARRAY['books','toys','garden','music'])[1 + (random() * 3)::int] AS category,
       round((random() * 300)::numeric, 2)::numeric(10,2) AS amount,
       timestamp '2025-01-01' + random() * interval '365 days' AS created_at
FROM generate_series(1, 1000000) g;
ALTER TABLE orders ADD PRIMARY KEY (id);
CREATE INDEX orders_col ON orders USING sift_col (customer_id, country, category, amount, created_at);
VACUUM ANALYZE orders;

EXPLAIN (COSTS OFF) SELECT category, count(*), sum(amount), avg(amount) FROM orders GROUP BY 1;   -- Custom Scan (SiftAgg) on orders_col
SET sift.enable_agg = off;                                                                           -- the plan PostgreSQL has without pg_sift

Medians of six runs, warm, PostgreSQL 18.6 on a shared x86-64 host (2026-10-24), the same table and answers with sift.enable_agg on and off:

system	count, sum, avg by category	distinct customers where country = ‘DE’	top 5 customers by sum	a month of orders by country, category
pg_sift	0.14 ms	2.5 ms	12 ms	20 ms
PostgreSQL	54 ms	68 ms	177 ms	30 ms

The last column is a month of random dates, which no unit can skip: the gain is the smallest there. Declare amount as numeric(10,2): with a bare numeric the scan is chosen and still wins, but 4 to 19 times less than with the precision (known gap). Full-text search is below.

Use it when the data stays in PostgreSQL and you want fast dashboards and reports from the same rows, with counts, sums, distinct counts, group-bys and top-N that do not scan the table (your queries stay as they are; you add an index), and ranked search as a second kind of index. Do not use it for a primary-key lookup (the btree is as fast), for update-heavy tables through sift_table (a write costs more than a heap write), or for warehouse-size tables that need sharding; every measured loss is in Limitations and trade-offs. Versions: PostgreSQL 17, 18 and 19 on Linux, 17 and 18 on macOS (docs/SUPPORT.md). Upgrades: ALTER EXTENSION pg_sift UPDATE, and a rebuild only when the on-disk format changes (docs/UPGRADING.md). License: the PostgreSQL License.

At a glance

Use it when the question reads many rows and the answer is small (your queries stay as they are; you add an index), or when ranked search should sit next to the data (it has its own query syntax):

• Aggregates over a filtered table: counts, sums, distinct counts, group-bys, time buckets, top-N and window reports over hundreds of thousands to millions of rows, answered from pre-aggregates and compressed columns; the time grows slower than the rows.
• Ranked search next to the data: BM25 with filters, counts and facets in the same transaction as the rows and no second system to keep in step; updates, deletes, VACUUM and replicas are PostgreSQL’s.

Do not use it (every measured loss is in Limitations and trade-offs) for:

• a lookup by key where a btree exists: the btree is as fast or faster, and the planner keeps it;
• a report whose whole query takes a few milliseconds over a small result: flat PostgreSQL tables are as fast or faster than the scan;
• tables that need a warehouse: it is one PostgreSQL node, without sharding or distributed execution; ClickHouse loads faster and stores the same table in less space, and ClickHouse and Elasticsearch are ahead on some filtered averages and range counts at 10,000,000 rows;
• text analysis beyond English stemming and one key per index, and the search classes where Elasticsearch is ahead;
• writes, spilling distinct counts and queries that keep a few rows: slower than PostgreSQL’s own plan on some shapes;
• a shape the planner cannot prove equal: it keeps the normal plan.

The benchmark cases

The 26 cases of bench/cases.tsv marked headline, measured on release 4.0 (tip 3c5e625d, track_changes on) with bench/bench cases --rows 300000 (run 3c5e625dfb80-20261103T202442Z-8ca1): PostgreSQL 18.6, one x86-64 host, the data in memory, one backend, warm medians of 5 runs. Each cell is the time of the statement (a read: the execution time of EXPLAIN ANALYZE; a load: the wall time of its statements), the millions of instructions of the backend and its workers, and the buffers it touched (shared-buffer hits and reads of every relation, 8 kB each: with the data in memory, the IO of the plan in blocks). The other column is the faster of PostgreSQL’s plan with the index left out (a sequential scan) and, where the table has one, the plan with its b-tree; the ratio is its time divided by pg_sift’s, so a value under 1 is a statement the other plan answers faster. One case per row, as the registry lists them; reproduce one with bench/bench cases --cases ID --rows 300000.

case	against	pg_sift: ms · M instr · blocks	the other: ms · M instr · blocks	other ÷ pg_sift
topn_page_totals: A page of rows with the total of its set	heap plan	0.61 ms · 7.69 M · 473	29 ms · 374 M · 4,107	48x
topn_coalesce: ORDER BY coalesce(column, constant) LIMIT n	heap plan	0.4 ms · 5.58 M · 98	32.1 ms · 299 M · 4,105	80x
key_presence_distinct: count(DISTINCT key) of a whole table from the key lists and bitmaps of its units	heap plan	0.14 ms · 2.09 M · 26	56.7 ms · 410 M · 4,105	405x
bulk_copy_wal: COPY into a sift_table, written as column groups, against a heap COPY	heap plan	422 ms · 5,210 M · 802	229 ms · 3,164 M · 12,061	0.5x
topn_sorted_runs: ORDER BY an unsorted integer column LIMIT n from the sorted run of each unit (order_columns)	heap plan	0.115 ms · 0.873 M · 281	22.4 ms · 150 M · 4,105	195x
lateral_first_row: a LIMIT 1 child with an ORDER BY, read as a first-row lookup when a unique index holds the key and the order columns	heap plan	4.17 ms · 65.6 M · 2,973	1,449 ms · 25,536 M · 455,241	347x
cte_two_readers: a common table expression that two aggregates read is planned inline when the scans are the cheaper part	heap plan	2.48 ms · 25.8 M · 500	68.4 ms · 542 M · 13,639	28x
list_elements: the elements of a jsonb array leaf counted with a filter on them (the decoded list lengths are kept for the backend)	heap plan	3.36 ms · 35.9 M · 623	113 ms · 781 M · 13,639	34x
cte_series_arms: three UNION ALL arms over the elements of a jsonb array, counted DISTINCT above a spine: the shared common table …	heap plan	8.76 ms · 133 M · 768	858 ms · 8,283 M · 13,731	98x
grouping_sets_report: a report with four levels in one statement: GROUPING SETS over two dimensions, a date filter and three aggregates	heap plan	2.85 ms · 38.9 M · 198	65.9 ms · 744 M · 4,107	23x
ranked_top_per_group: the three best categories of every country by revenue: a window function over the groups of an aggregate, filtered by …	heap plan	2.82 ms · 33.1 M · 248	63.2 ms · 645 M · 4,105	22x
window_json_report: a ranked report as one document per category: the rank of the category, its share of the total, and the top 3 countries …	heap plan	4.53 ms · 61.8 M · 298	71.5 ms · 724 M · 4,105	16x
two_level_spend: the distribution of spend per customer: an aggregate of an aggregate, 200,000 customers at 1,000,000 orders	heap plan	24.4 ms · 245 M · 338	153 ms · 748 M · 4,105	6.3x
group_by_id_top: one group per row: the 10 largest orders by value, as a GROUP BY of the primary key (1,000,000 groups at 1,000,000 …	heap plan	13 ms · 103 M · 253	162 ms · 1,936 M · 4,105	12x
count_distinct_unique: count(DISTINCT) of a key that is unique: the set holds one entry per row	heap plan	1.13 ms · 14.7 M · 126	238 ms · 2,962 M · 4,105	211x
semi_exists_self: a semi join on the same table: the orders of the customers that have a cancelled order	heap plan	2.77 ms · 19.3 M · 4,381	57.2 ms · 468 M · 8,212	21x
anti_not_in_self: an anti join as NOT IN (SELECT ..) on the same table: the orders of the customers that never cancelled	heap plan	10.1 ms · 76.6 M · 4,464	56.1 ms · 577 M · 8,212	5.6x
keyset_page: keyset pagination: the page after a (created_at, id) bar, a row comparison with a two-column order	b-tree	0.104 ms · 0.997 M · 91	0.039 ms · 0.491 M · 56	0.4x
point_agg_fk: a lookup of the rows of one foreign key with no btree on it: the aggregate of one customer	heap plan	0.063 ms · 0.391 M · 95	10.7 ms · 121 M · 4,107	170x
point_agg_id: a lookup by the primary key through the aggregate scan, against the btree of the key	b-tree	0.035 ms · 0.339 M · 25	0.012 ms · 0.173 M · 5	0.3x
keycut_customer_agg: a lookup of the rows of one customer when the heap is in order of arrival and the index cuts its units by the customer: …	heap plan	0.069 ms · 0.719 M · 34	10.3 ms · 121 M · 4,107	150x
jsonb_each_member: one member of a JSON object read through jsonb_each: a LEFT JOIN LATERAL that keeps the rows without it	heap plan	3.35 ms · 35.9 M · 673	117 ms · 916 M · 13,639	35x
softdel_live_groupby: a report over the rows that are not soft-deleted: the units with no deleted row are answered from their pre-aggregates, …	heap plan	0.057 ms · 0.667 M · 32	50.8 ms · 596 M · 4,107	891x
point_in_100: an IN list of 100 ids through the access method: the point directory looks each key up (up to 64 keys it did; a longer …	heap plan	0.076 ms · 1.07 M · 143	14.8 ms · 161 M · 4,107	195x
topn_scattered_time: the n best rows by a time column whose rows are in no order, for an n above the longest head of a run: a superblock …	heap plan	0.188 ms · 1.89 M · 27	24.6 ms · 163 M · 1,911	131x
key_table_point: the rows of three keys of a foreign key column (6,313 values): one record of each segment’s key table, not the units of …	heap plan	0.075 ms · 0.748 M · 28	19.3 ms · 273 M · 4,107	257x

Tables that change: track_changes

track_changes is on by default ('auto': the primary key of the table; WITH (track_changes = 'off') turns it off for an index). It writes a tombstone for each entry an UPDATE, a key change or a DELETE ended and installs two statement triggers (AFTER UPDATE and AFTER DELETE; one NOTICE says so).

What a table that changes ends as. 100,000 rows, a primary key and one sift_col index, 10 rounds of an UPDATE of every row (1,000,000 updates) with no VACUUM, then autovacuum (naptime 1 s) and the worker of sift.col_auto_merge, both on as by default, until the index keeps one state for 30 s; track_changes as it is (the default) and 'off' (bench/churn/heapfree_settle.py, run 2783fcb3884a-20261104T000858Z-444e, 3 repetitions of a grouped count of 10 tenants; the REINDEX column is the index built again over the same rows):

track_changes	fresh	after the updates, before the VACUUM	settled	REINDEX	settled over REINDEX	grouped count before the VACUUM: ms · M instr · heap blocks	settled
default	0.57 MB	47.6 MB (999,038 entries in the log)	0.71 MB, 1 segment	0.63 MB	+13.0%	14.8 ms · 161 M · 1,406	0.029 ms · 0.40 M · 2
'off'	0.26 MB	20.2 MB	0.47 MB, 6 segments	0.28 MB	+67.7%	27.2 ms · 253 M · 10,280	0.043 ms · 0.50 M · 3

So the index does not end within 1% of a rebuilt one: the settled index is 13.0% over its REINDEX with the default and 67.7% with 'off', the log is empty after the vacuum, and a grouped count after it reads 2 heap blocks (3 with 'off'), as on the fresh table. The updates take 4.7 s and write 338 MB of WAL with the default, 2.7 s and 288 MB with 'off'. At 300,000 rows (3,000,000 updates, run 2783fcb3884a-20261104T001112Z-3429) the default index was 55.7 MB 30 s after the VACUUM, its rewrite not yet run (1.8 MB after a REINDEX), and the one with 'off' 1.39 MB (+91.0% over 0.73 MB).

What it costs a write: 100,000 rows, a table with a primary key and one sift_col index, vacuumed, medians of 3 (bench/churn/heapfree_default.py, run fb9b4acd6f58-20261103T182341Z-00ce on the tree of the commit that merged the option); each cell is the time, the millions of instructions, the MB of WAL and the buffers touched:

statement	track_changes = 'off': ms · M instr · WAL · blocks	default (auto): ms · M instr · WAL · blocks	instructions
UPDATE of 1 row	0.223 ms · 0.363 M · 0.04 MB · 39	0.303 ms · 0.415 M · 0.04 MB · 54	+14.2%
UPDATE of 100 rows	0.651 ms · 3.51 M · 0.11 MB · 650	1.11 ms · 4.77 M · 0.12 MB · 1,061	+35.9%
UPDATE of 5% of the rows (a covered column)	18.8 ms · 145 M · 11.06 MB · 32,079	27.5 ms · 208 M · 11.25 MB · 52,256	+43.6%
UPDATE of the key of 1% of the rows	10 ms · 66.4 M · 7.98 MB · 6,936	12.9 ms · 86.9 M · 8.02 MB · 11,224	+30.8%
DELETE of 2% of the rows	8.19 ms · 53.6 M · 7.78 MB · 3,871	9.09 ms · 72.3 M · 7.85 MB · 7,404	+34.9%
INSERT of 5% more rows	9.29 ms · 86.5 M · 1.03 MB · 15,223	10.2 ms · 87 M · 1.05 MB · 15,241	+0.6%
CREATE INDEX	44.8 ms · 672 M · 0.32 MB · 3,763	61.1 ms · 858 M · 0.64 MB · 104,713	+27.8%
REINDEX	48.1 ms · 672 M · 0.26 MB · 3,763	58.9 ms · 858 M · 0.60 MB · 104,713	+27.8%

The triggers of several indexes of one table each look the key up in the unique index: with 2 and 3 indexes an UPDATE of the key of 1% of the rows takes 99.8 and 111.9 M instructions (86.9 with one), a DELETE of 2% 88.5 and 104.9 M (72.3 with one). A table that no read touches between its writes and its VACUUM pays that and gains nothing, which is what 'off' is for.

A table changed and not yet vacuumed (the case the option is for). 300,000 rows, the recent 20% updated (bench/churn/heapfree_churn_reads.py): a grouped count reads 2 heap blocks instead of 1,241 (21.8 to 10.7 M instructions, 1.87 to 0.77 ms) and one tenant’s rows 0.41 MB instead of 10.09 MB read cold. After a VACUUM no read differs, and point reads and index-only scans gain nothing either way.

Limitations and trade-offs

Known gap: the load. A load of 1,000,000 orders (9 columns) takes 1.8 s into a sift_table with its primary key and BM25 index (COPY 1.1 s, the two indexes 0.4 s, VACUUM (FREEZE, ANALYZE) 0.3 s) against ClickHouse 0.7 s (2.6x) and Citus columnar 1.4 s (1.3x). ClickHouse loads without a primary key or a text index, which are 0.4 s of the 1.8 here; against the same COPY alone it is 1.1 s to 0.7 s. The COPY costs 22.7 G instructions of the backend against 12.9 G for a heap table (1.8x, the parse of the CSV is 44% of it for both) because the rows are encoded into column units, dictionaries and pre-aggregates as they come, which a heap table does at query time or never. What is left would need the encoding to leave the backend: the flush of a run is 16.6% of the instructions of the load, so a background worker could save at most that share of the wall time; handing the rows to the batch without per-row allocations saves 2.8% (measured), below what justified a change. Without workers one open segment takes the units as they fill, with no merge afterwards. Against a heap table COPY plus sift.table_maintain of 10,000,000 rows (the bulk_copy_wal case, one CPU) takes 13.8 s against 8.1 s (1.7x slower) and 168 G instructions against 107 G (1.6x), and writes 14 bytes of WAL a row against 84 and a table of 137 MB against 1,023 MB. What is left is the encoding itself (dictionaries, pre-aggregates, zstd), which a heap table does not do at load time; reading the table back is what it pays for. The gap to ClickHouse stays; the measurements. (The figures of the 1,000,000-row comparison with the other engines are not from records files: they were typed from the sessions of the dates in the changelog; those of the 10,000,000-row bulk_copy_wal runs are.)

Known gap: text ordered reads. ORDER BY txt LIMIT n over a text column is served from the index when the build chose the column (the default formula, strings up to 64 bytes) or order_array_columns names it ('off' drops it), under the collation of the column, with one key and no condition: ORDER BY title LIMIT 10 over 500,000 rows takes 0.2 M instructions and 0.10 ms against 473.7 M and 30.0 ms for PostgreSQL’s plan, and a page from a bound, WHERE code >= 'c' ORDER BY code LIMIT 20 over 500,000 32-character strings, 0.4 M instructions and 0.17 ms against 241.2 M and 16.9 ms for the same index without the array (PostgreSQL’s plan 329.9 M and 22.7 ms). What the array costs is the build: CREATE INDEX over 500,000 distinct 26-byte strings takes 6,215 M instructions and 707 ms against 2,248 M and 273 ms for a b-tree on the column (2.8x the instructions), and the index holds 10.8 MB against the b-tree’s 24.9 MB. (Cases topn_text_key, order_array_text_range and text_order_array_size, 500,000 rows, one process, warm medians; the two reads have no b-tree arm, so their distance to a b-tree is not stated.)

Known gap: the dictionary of a large text column. The order array of a text column names its strings by their dictionary codes only while the dictionaries of the index, parsed and added up, take three quarters of the 64 MB cache of parsed dictionaries or less (about 700,000 distinct 32-byte strings: 800,000 hashes take 48.4 MB with the strings kept, 1,000,000 take 60.7 MB, a b-tree 59.1 MB, and 23.6 MB with codes); past that it keeps the strings, because with codes every equality parsed a dictionary again (10 to 25 ms for 0.13). The first backend after a server start parses each dictionary it meets and writes it to the shared ring (sift.col_dict_shared_cache_kb): 26 to 167 ms once per dictionary at 100,000 to 1,000,000 strings, and the other backends find it (1.4 to 2.8 ms for their first statement, a b-tree 2.0 to 2.7); with a ring of 0 or under twice a dictionary’s record each backend parses its own once (23 to 47 ms for an equality over 500,000 to 700,000 hashes, then 0.1 ms). (The figures of this paragraph are not from records files; the cases bigdict_point_probes, dict_shared_first_statement and dict_shared_find hold the same shapes with records.)

Known gap: a numeric without precision. GROUP BY two columns (16 groups) with count, sum and avg of a bare numeric column takes the scan and, since the sum runs in workers, beats PostgreSQL’s plan at every size and worker count measured: 6.8 against 11.5 ms at 100,000 rows (2 workers); 23.3 against 43.4 ms at 1,000,000 (2 workers; 22.7 against 43.4 with 4) and 46.6 against 109.9 ms in one process (693 against 2,110 million instructions); 160 against 389 ms at 10,000,000 (2 workers; 112 against 248 with 4). With numeric(10,2) the same query takes 5.8 ms at 1,000,000 rows and 20.6 ms at 10,000,000 (2 workers), 4 to 19 times faster than with a bare numeric, so declare the precision of money-like columns. What remains: a bare numeric column of almost unique values (2,000,000 rows, 6 groups, round(random() * 100000, 2)) took the scan with the default 2 workers in 79.6 ms against 61.7 ms for PostgreSQL’s plan (29% slower), because every unit holds its own dictionary of values there; the planner now prices the dictionaries that the workers inflate and keeps PostgreSQL’s plan with 2, 4 and 8 workers (79, 62 and 62 ms), and still takes the scan without workers. (Measured 2026-10-25 on the 3.4 tip (the 79, 62 and 62 ms on the 3.5 line), PostgreSQL 18.6, an x86-64 host, work_mem 4GB unless said, medians of 5 runs; sift.enable_agg = off gives PostgreSQL’s plan.) (The figures of this paragraph are not from records files; the cases numeric_sum and numeric_sum_records hold the same shapes with records, at 1,000,000 rows and one process, run d1150b9ef5ae-20261102T231942Z-f7af: a sum of a bare numeric by country takes 392.8 M instructions against 1,840.9 M for PostgreSQL’s plan, and the almost unique values, 6 groups, 622.5 M against 1,925.6 M.)

Quick start

Install

PostgreSQL 17, 18 and 19 on Linux (x86-64 and arm64); 17 and 18 on macOS arm64 (Homebrew has no postgresql@19 yet: build 19 from source). Release packages (x86-64 and arm64/aarch64, any aarch64 Linux: AWS Graviton, Ampere, Azure and Google Arm, Raspberry Pi 4/5) cover PostgreSQL 17, 18 and 19: take the file of your server’s major N (the 19 packages are built against PGDG’s 19~rc1 until it ships 19.0).

Where	How
Debian 12 and 13, Ubuntu 22.04 and 24.04 (PGDG postgresql-N)	download postgresql-N-pg-sift_*_amd64.deb from the latest release, then sudo apt install ./postgresql-N-pg-sift_*_amd64.deb (_arm64.deb on arm64)
RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server)	download pg_sift_N-*.rpm from the latest release, then sudo dnf install ./pg_sift_N-*.rpm (.x86_64.rpm or .aarch64.rpm, the architecture of the server)
Amazon Linux 2023 (the distribution’s postgresqlN-server, N = 17 or 18)	download pg_sift_N-*.amzn2023.x86_64.rpm (or .aarch64.rpm) from the latest release, then sudo dnf install ./pg_sift_N-*.amzn2023.*.rpm; the el9 package does not work there (the distribution keeps its PostgreSQL files in /usr/lib64/pgsql)
Arch Linux	the AUR package pg_sift (recipe in packaging/)
Docker	packaging/docker/build.sh 18 builds the image pg_sift:pg18 on the official postgres image: docs/QUICKSTART.md, docs/CONTAINERS.md (Docker Compose, CloudNativePG)
macOS arm64	the Homebrew formula below
anything else	from source (the first snippet of In five minutes; needs server headers with pg_config on PATH and a C compiler; PG_CONFIG=/path/to/pg_config picks another installation)

Then, as a superuser, CREATE EXTENSION pg_sift in each database (after an upgrade: ALTER EXTENSION pg_sift UPDATE). Details: docs/USAGE.md, docs/UPGRADING.md, docs/SUPPORT.md.

macOS with Homebrew

The formula builds the latest release (brew install --HEAD builds main) for Homebrew PostgreSQL 17 and 18 on macOS arm64:

brew tap brauliobo/pg_sift https://github.com/brauliobo/pg_sift.git
brew install brauliobo/pg_sift/pg_sift          # --HEAD for main
brew test brauliobo/pg_sift/pg_sift
"$(brew --prefix postgresql@18)/bin/psql" -d mydb -c 'CREATE EXTENSION pg_sift;'   # a running server, as a superuser; postgresql@17 for 17

CI builds the stable formula and the HEAD one, runs brew style, brew audit --strict --online and brew test (a BM25 smoke, an extension upgrade and a sift_col aggregate) on macOS arm64 with PostgreSQL 17 and 18, and runs a few regression tests there and the quick tier on Linux arm64; it was also tried by hand on Apple Silicon with 17.11 and 18.6. make check needs bash 4 or newer (brew install bash; the stock bash 3.2 stops with a message). A stage the host cannot run (the Perl TAP tests, which need the server’s src/test/perl; the compatibility tests that build releases older than 3.2.0; the Python 3.12 benchmark generators) is skipped with a message.

Try it: analytics

Your queries stay as they are: add the index, and the planner uses it when it can prove the answer equal; otherwise PostgreSQL’s own plan runs.

CREATE TABLE orders (id bigint PRIMARY KEY, customer_id int, country text, category text, status text, amount numeric(10,2), created_at timestamp);
CREATE INDEX orders_col ON orders USING sift_col (customer_id, country, category, status, amount, created_at);

SELECT category, count(*), sum(amount) FROM orders
WHERE country = 'DE' AND created_at >= '2025-06-01' GROUP BY category;          -- Custom Scan (SiftAgg) on orders_col
CREATE TABLE events (id bigint, kind text, price numeric(10,2), at timestamp) USING sift_table;   -- the table itself in column format

SET sift.enable_agg = off gives the plan PostgreSQL has without sift. Types, options and limits: docs/USAGE.md.

Try it: full-text search

Search has its own query syntax (@@>, sift.score): a search query is written for the sift index.

CREATE TABLE docs (id int PRIMARY KEY, title text, body text, published date, category int);
INSERT INTO docs VALUES
  (1, 'Wireless headphones', 'Battery was great and the support was friendly. The case was squeaky.', '2026-01-10', 1),
  (2, 'Charger', 'Friendly support, no battery, but a sturdy case and clip.', '2026-02-11', 1),
  (3, 'Resume tips', 'A good résumé is short. Cafés near the office help.', '2026-03-12', 2),
  (4, 'Running', 'Runners were running; she runs every day.', '2026-04-13', 2),
  (5, 'Squeaky headphones', 'The headphones were squeaky at night, squeak everywhere. Friendly support though.', '2026-05-14', 1);
CREATE INDEX docs_body ON docs USING sift (body);

SELECT id FROM docs WHERE body @@> 'battery friendly';                     -- all terms: 1, 2
SELECT id, sift.score(ctid) FROM docs WHERE body @@> 'case OR clip' ORDER BY sift.score(ctid) DESC LIMIT 10;   -- BM25 top-10
SELECT count(*) FROM docs WHERE body @@> 'friendly';                        -- 3, from the index

Words are ANDed; OR, NOT, "phrases", batt*, sq?eaky, /regex/, frendly~1 and ^3 boosts combine them: docs/USAGE.md.

Features

• Column engine (USING sift_col, USING sift_table): count, count(DISTINCT ...), sum, avg, min, max with filters and GROUP BY on compressed columns as Custom Scan (SiftAgg); sift_table stores the table itself in column format.
• BM25 search index (USING sift): boolean, phrase, prefix, wildcard, regex and fuzzy queries, exact BM25 ranking, top-k with filters applied inside the scan (the columns of INCLUDE (...) are tested before a document is scored, and a join with a small table adds its keys as one more test: docs/USAGE.md), counts from the index, facets, autocomplete.
• Pre-aggregates, top-N and lookups: units whose rows are all visible answer from the exact count/sum/min/max stored with them (on by default); ORDER BY ... LIMIT n stops after the first rows of the units that can hold them; an equality or IN lookup on a key column reads only the units that hold it (point_columns, point_tid_columns; opt-in per index).
• Reading a heap table’s index without the heap (track_changes, on by default): track_changes (default 'auto': the primary key; WITH (track_changes = 'off') turns it off) keeps a log of the entries an UPDATE, a key change or a DELETE ended and installs two statement triggers on the table (one NOTICE says so), and a grouped count or top-N over rows changed and not yet vacuumed reads 2 heap blocks instead of 1,241 (300,000 rows, the recent 20% updated: 21.8 to 10.7 M instructions, 1.87 to 0.77 ms, 10.09 to 0.41 MB read cold; the top 20 2.3 to 0.8 M). After 1,000,000 updates of 100,000 rows and the vacuums the log is empty and the index 13.0% over a REINDEX (67.7% with 'off'). It costs writes (100,000 rows: an UPDATE of 5% of the rows +44%, a key change +31%, a DELETE of 2% +35%, CREATE INDEX +187 M instructions) and gains nothing on point reads, index-only scans or after a VACUUM, so it pays when a table is read many times for each write while rows are unvacuumed (docs/USAGE.md).
• Faster paths:
  • Top-N by a float key, or by a column that follows the table: a float sort key goes through the same min/max and compare kernels as an integer one, so units whose best value cannot pass the n-th are not read: ORDER BY value DESC LIMIT 10 over 1,000,000 readings 47.5 to 0.80 ms (ts_top_value, 60x); the newest 10 rows of a time column with no btree 80.6 to 0.22 ms (hits_latest_time, 367x).
  • The first statement of a new backend: the choices a statement needs several planner runs for are kept in shared memory (sift.verdict_cache_kb, docs/USAGE.md), so a new backend plans the winning form only: a report of 21 CTEs and 16 lateral sub-selects takes 29.1 ms on the first statement against 35.2 ms with the cache off (verdict_shared_report); 12 lateral aggregates take 45.9 ms first and 41.7 ms second against 3,507 ms on PostgreSQL (lateral_flat_plan, 76x).
• Joins from the child: a sift_col index on a child table connects to the index of the parent it joins (WITH (connect = 'fk -> parent(key)'), made by itself for a validated integer foreign key), so EXISTS, NOT EXISTS and counting joins to the parent are answered from the child, and a LEFT JOIN of two indexed tables groups the child first: 1,000,000 child rows and 100,000 parents, the join count 4.3 ms against 228 ms for the normal plan and NOT EXISTS 2.8 ms against 244 ms (docs/COL-CONNECT.md, docs/INDEX-RECIPES.md).
• Plain PostgreSQL: MVCC, VACUUM, CREATE INDEX CONCURRENTLY, partitions, generic WAL (crash and replica safe), pg_dump; a query the planner cannot prove equal keeps its normal plan.
• Vulkan (experimental, opt-in): make VULKAN=1 and SET sift.gpu = auto|on run grouped and filtered integer counts and sums of a sift_col index on a GPU (below, docs/GPU.md).
• Ports: SQLite and MariaDB run the same search core.

Compared with

Feature facts checked against each project’s own documentation on 2026-10-18; ? is not verified. Analytics (pg_sift against PostgreSQL, Citus columnar, ClickHouse, Elasticsearch, DuckDB and TimescaleDB) and search (against PG FTS, pg_search, pg_textsearch, rum, pgx-bm25, TIN and Elasticsearch) tables, a note per row, sources and the unverified cells: docs/COMPARISON.md.

pg_sift is weaker in text analysis (one key per index; case and accent folding, a keyword analyzer, term* prefixes and the stemmers and stop words of any PostgreSQL text search configuration, stemmer = 'german', are there; n-gram and CJK tokenizers are not) and where its column engine is narrow: joins are accelerated for small tables only, and sift_table scans the superblocks that hold a tombstone or a write buffer row instead of reading their pre-aggregates (where it is weaker).

SQLite and MariaDB

The search engine runs outside PostgreSQL too, with the same core and the same results; the column engine is PostgreSQL-only (docs/COL-PORTS.md).

SQLite

The search engine as a loadable extension, for apps that want search without a server (Linux x86-64, SQLite 3.38 or newer): exact BM25, phrase, prefix, wildcard, regex and fuzzy queries, highlights, snippets, facets; 89x, 242x and 10x the throughput of FTS5 on top-10 'a b', 'a OR b' and '"a b"' (218,058 articles). Install, options and numbers: sqlite/README.md.

.load ./sift
CREATE VIRTUAL TABLE docs USING sift(title, body);
SELECT rowid, rank FROM docs WHERE docs MATCH 'case OR clip' ORDER BY rank LIMIT 10;   -- BM25 top-10, best first

MariaDB

MariaDB 10.5 to 13.x, with results identical to pg_sift: libsift_udf.so and two SQL files, with no storage engine and no server patch; 183x to 794x faster than InnoDB FULLTEXT on top-10 'a b', 'a OR b' and '"a b"' (218,058 articles), with an index 1.7x smaller. Install and guide: mariadb/README.md.

CALL sift_attach('news', 'articles', 'id', "CONCAT_WS(' ', t.title, t.body)", 32);   -- index, queue, triggers
SELECT sift_count('news', 'power AND plant');

Operations and limits

• Options: index options go in WITH (...) (k1, b, analyzer, stemmer, positions, prefix_index, write_buffer_limit, merge_fanin, …); merge_fanin defaults to 4: an index that takes batches keeps 4.6 segments on average instead of the 6.5 of merge_fanin = 8 and reads 4 to 10% fewer instructions and 13% fewer cold MB, for 37% more merge time and 11% more WAL while it ingests (ALTER INDEX .. SET (merge_fanin = 8) merges less often); sift_table is tuned with settings (sift.table_*), not per table.
• Maintenance is on by default: a background worker (sift.async_maintenance) promotes write buffers and merges segments, sift.col_auto_merge vacuums and merges a sift_col index after churn, sift.table_auto_defrag clears the dead rows of a sift_table; VACUUM clears dead rows as usual.
• Large work_mem and unusual planner costs: a seq_page_cost above random_page_cost moves the planner’s choice between the scan and the normal plan most; the results are the same as the normal plan’s either way.
• Inspection: sift.index_info, sift.segments and sift.verify.
• Replication: physical replicas work through generic WAL; publishing a sift_table under logical replication needs shared_preload_libraries.
• One library: the BM25 index and the column engine share one library, about twice the size of the search-only build, at a cost of at most 2% of the instructions of an operation².
• Platforms: PostgreSQL 17, 18 or 19 (x86-64 tested; arm64 tested with 18); SQLite port on Linux x86-64 with SQLite 3.38 or newer; MariaDB port on 10.5 to 13.x.
• Planner: what is not accelerated keeps the normal plan, and a few cases raise an error that names the rewrite.
• Which options to touch (about ten of 150): docs/TUNING.md.- Column engine types, options and limits: docs/USAGE.md; settings, maintenance and replication: docs/USAGE.md; disk, memory and time of a large index: docs/SIZING.md.

SQL reference

Schema sift has 70 functions, 6 views, 2 tables, 2 operator classes, 1 operator. Signatures, results, the release that added each and a line on what it does are in docs/REFERENCE.md, generated from the SQL scripts.

• Search and ranking: @@>, facet_counts, facet_sql, facets, field_query, highlight, index_info, json_build_object, match, match_idx, max_score, parse_query, score, sections, segment_postings, segments, text_ops, tokenize, verify
• Column index: approx_count_distinct, cluster_advice, col_*_ops, col_arena_status, col_clustering, col_connection_fill, col_connection_list, col_connection_state, col_connections, col_extents, col_index_info, col_index_status, col_leaves, col_maintenance_status, col_ops, col_sections, col_segments, col_space, col_stamps, col_track_convert, col_track_old_rows, col_tracking, col_verify, index_advice, index_choices, key_cut_decision, suggest_paths
• Tables: table_find_stale_versions, table_get_option, table_option_values, table_option_values_trg, table_options, table_paths, table_paths_maintained, table_reset_option, table_segments, table_set_option, table_set_paths, table_space
• Maintenance and workers: col_maintain, col_maintenance_due, col_merge, col_promote, col_refresh, col_rollup_counters, col_rollup_queue, col_rollup_refresh, col_rollup_requests, col_rollup_state, merge, promote, table_maintain, worker_state, workers
• Diagnostics and upgrade: col_rollup_misses, explain_why, help, refusal_actions, refusal_slugs, stats, stats_reset, upgrade_commands, version

Settings (sift.*) are in the settings table, fixed limits in docs/LIMITS.md.

Options

178 index options (WITH (...)), sift.* settings and automatic behaviors, each with its default, class (default, opt-in, diagnostic, safety, deprecated), measured gain and cost, and off switch, are in docs/OPTIONS.md; types, ranges and contexts are in the tables of docs/USAGE.md.

Every NOTICE, WARNING and VACUUM VERBOSE line of the library, with the decision it reports, its benefit and trade-off in numbers, and the switch that changes it, is one row of docs/MESSAGES.md. CREATE INDEX and REINDEX of a sift_col index print one short NOTICE; SELECT * FROM sift.index_choices('index') gives the reason, the cost and the command that turns each choice off, and sift.help('slug') returns the row of a message.

Vulkan (experimental)

Opt-in and off by default: build with make VULKAN=1 and SET sift.gpu = auto|on (auto: grouped aggregates on indexes of at least sift.gpu_min_rows, 5,000,000 rows; on: also ungrouped). Grouped and filtered integer and numeric counts and sums of a sift_col index run on a Vulkan device (clean all-visible units on the device, the rest on the CPU in the same query); a missing or failed device falls back to the CPU. The compressed chunks stay on the device, so use persistent connections (each backend pays about 0.2 s to open it). Not eligible: floats, min/max, count(DISTINCT), text filters, more than one key, sift_table. Measurements and limits: docs/GPU.md.

Docs

• docs/USAGE.md: install, queries, ranking, options, settings, maintenance, replication, limits.
• docs/SIZING.md: disk, memory and time of a large index.
• docs/COLUMN-STORAGE.md: what a sift_col index stores besides the columns (point directories, sorted arrays, key tables, dictionaries): what each is for, its default, how to turn it off and one measured figure.
• docs/COMPARISON.md: feature tables against other search and analytics systems, with sources and the date checked.
• docs/CONTAINMENT.md: range and network containment from sift_col, its numbers and what it does not serve.
• docs/FORMATS.md: the on-disk format numbers (metapage minor, segment formats, flags).
• docs/UPGRADING.md: the upgrade preflight (scripts/upgrade-check.sh), ALTER EXTENSION UPDATE, pg_upgrade to a newer PostgreSQL major and going back to an older release.
• docs/INDEX-RECIPES.md: which sift_col indexes to create, what they cost in size and writes, and which btrees to keep.
• docs/COL-CONNECT.md: connected indexes: declaring a connection, the automatic one, what it costs and its limits.
• docs/GPU.md: the experimental, opt-in Vulkan aggregate path.
• docs/COL-PORTS.md: the column engine on SQLite and MariaDB; docs/SAFETY.md: memory safety in the C code.

Security and license

Tests: make check, and make check-full with sanitizers, soak, fuzz and mutants (CONTRIBUTING.md). Report crashes, wrong results and index corruption privately as in SECURITY.md. pg_sift is released under the PostgreSQL License; third-party notices are in NOTICE.