Quickstart: pg_sift in five minutes

You need only Docker; everything below is removed at the end. The image is built from this repository on the official postgres image (CONTAINERS.md) and takes a few minutes the first time.

1. Start PostgreSQL with pg_sift

packaging/docker/build.sh 18        # pg_sift:pg18 (17 works the same way)
docker run -d --name sift-try -e POSTGRES_PASSWORD=example pg_sift:pg18
docker exec -it sift-try psql -U postgres

CREATE EXTENSION pg_sift has already run for the postgres database and template1, so \dx lists it and every new database has it.

2. Load a small data set

100,000 orders built in SQL, with the two indexes pg_sift adds: sift_col for aggregates over the columns it lists, sift for BM25 search on the title.

CREATE TABLE orders AS
SELECT g AS id,
       1 + (g * 7919) % 5000 AS customer_id,
       (ARRAY['DE','FR','US','BR','JP'])[1 + g % 5] AS country,
       (ARRAY['cat_a','cat_b','cat_c','cat_d'])[1 + (g / 7) % 4] AS category,
       (ARRAY['paid','shipped','delivered','cancelled'])[1 + (g / 3) % 4] AS status,
       (5 + (g * 7919) % 29500)::numeric / 100 AS amount,
       timestamp '2025-01-01' + (g * 311 % 31536000) * interval '1 second' AS created_at,
       (ARRAY['wireless','portable','compact','premium','rugged','slim','smart','classic'])[1 + g % 8] || ' ' ||
       (ARRAY['charger','speaker','headphones','keyboard','lamp','monitor','camera'])[1 + (g / 8) % 7] ||
       CASE WHEN g % 3 = 0 THEN ' with case' ELSE '' END AS title
FROM generate_series(1, 100000) AS g;
ALTER TABLE orders ADD PRIMARY KEY (id);
CREATE INDEX orders_col ON orders USING sift_col (customer_id, country, category, status, amount, created_at);
CREATE INDEX orders_title ON orders USING sift (title);
VACUUM ANALYZE orders;

3. BM25 search

SELECT id, title, round(sift.score(ctid)::numeric, 3) AS score FROM orders
WHERE title @@> 'wireless headphones' ORDER BY sift.score(ctid) DESC, id LIMIT 3;
--  id  |        title        | score
-- -----+---------------------+-------
--   16 | wireless headphones | 4.484
--  128 | wireless headphones | 4.484
--  184 | wireless headphones | 4.484

SELECT count(*) FROM orders WHERE title @@> 'wireless headphones';      -- 1786, from the index

@@> matches all the words; OR, NOT, "phrases", batt*, /regex/ and word~1 are in USAGE.md. The score is exact BM25: a longer title with the same words (“wireless headphones with case”) scores lower.

4. An aggregate and its plan

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT category, count(*), sum(amount) FROM orders
WHERE country = 'DE' AND created_at >= '2025-06-01' GROUP BY category;
--  Custom Scan (SiftAgg) (actual rows=4.00 loops=1)
--    Index: orders_col
--    Group Keys: category
--    Pushed Aggregates: count(*), sum(orders.amount)
--    Units: 4 read, 1 pruned
--    Rows: 77918 scanned, 11611 aggregated, 4 groups
--    Buffers: shared hit=75

The aggregate ran inside the column index (Custom Scan (SiftAgg)); 1 pruned is a unit skipped unread. With the pushdown off the same query takes the plain PostgreSQL plan and returns the same answer:

SET sift.enable_agg = off;
-- the same EXPLAIN:   HashAggregate -> Seq Scan on orders   (Rows Removed by Filter: 88389, Buffers: shared hit=1216)
RESET sift.enable_agg;

(EXPLAIN lines are shortened; figures are from PostgreSQL 18.6 with default shared_buffers.) When a query keeps the normal plan, SET sift.explain_refusals = on says why (USAGE.md).

5. The switches worth knowing

The quickstart used only defaults, and pg_sift is meant to be used with them (TUNING.md is the page for the few options worth touching). Two short lists: what is off until you turn it on, and what is on and has the switch that gives it up (SHOW in a session that has used the extension prints the value; USAGE.md has the full list):

Off by default What it does when you turn it on
sift.parallel_scan let the planner add parallel BM25 scans and counts
sift.table_async_promote write-path variant of sift_table (no gain measured on a 4-client insert loop)
sift.explain_refusals report why a query kept the normal plan
Setting Default What it does
sift.enable_col_scan on plan a sift_col index built with the sift.col_*_ops classes as a bitmap index scan for queries that return rows (COL-SCAN.md)
sift.col_auto_merge on a background worker vacuums the table after churn, after a build or a bulk load that leaves its pages out of the visibility map, and, after the VACUUM, merges a sift_col index that churn has scattered (within 5% of the WAL the server writes; off stops it)
sift.table_bulk_groups on COPY, CREATE TABLE AS and REFRESH MATERIALIZED VIEW write column groups straight away
sift.agg_lookup_shared_kb -1 (the smaller of the cache of a backend and 5% of shared_buffers) shared memory that lets backends reuse the lookup tables a SiftAgg join loaded
sift.col_format_minor 15 the column format the segments are written in: lower it (6 for 3.2, 4 for 3.1, 3 for 3.0) before writing what an older release must read; a 3.2 library cannot read the sift_table tables or the indexes with the newer options at 15 (SUPPORT.md)

ALTER SYSTEM or ALTER DATABASE ... SET make a setting stick; sift.enable_agg = off is the way back to plain PostgreSQL at any time.

Which options are worth knowing, in one page: TUNING.md.

Check the numbers on your machine

bench/bench kit                      # docker only; builds the image if needed
bench/bench kit --rows 200000 --cpus 2 --runs 3

It starts a throwaway container, loads the orders data of the benchmark (100,000 orders by default), runs the ten questions with and without the pushdown, compares every answer, and prints a table (exit status 3 when an answer differs). It took about 25 seconds once the image existed (100,000 orders, the only size run); a laptop with fewer, slower cores differs in times and ratios. Citus, Elasticsearch and ClickHouse (about an hour).

Clean up

docker rm -f -v sift-try