The State of Serene, October 2026

Welcome back to The State of Serene, our monthly note on what shipped and where SereneDB is heading.

October brought logical replication from PostgreSQL, online backups that pgBackRest can take, scheduled jobs and the system catalogs of PostgreSQL 18, which make the catalog queries of drivers and tools 11x faster. Search got faster again: a new term dictionary speeds up cold queries and a query that keeps a few rows reads the rest of their columns only for those rows. A refresh after a DELETE on a search table takes milliseconds instead of seconds. Vector search filters inside the HNSW walk and tunes itself. Phrases no longer need positions in the index, and inverted indexes take INET and CIDR columns. Ingest parses on several threads and deeply nested queries plan in linear time.

v26.11.2 is the latest release.

Star UsContact Us

What happened in October

Logical replication from PostgreSQL

SereneDB subscribes to a PostgreSQL publication the way a PostgreSQL server does. CREATE SUBSCRIPTION copies the published tables and then applies every committed INSERT, UPDATE, DELETE and TRUNCATE exactly once, since the publisher's position commits together with the rows:

SQL

CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=pg.internal dbname=shop user=replicator'
  PUBLICATION shop_pub;

The connection string works as in libpq, with several hosts, target_session_attrs, .pgpass, service files and sslmode up to verify-full. The initial copy runs several tables at a time, partitioned tables and published generated columns included, and a large transaction that the publisher streams is applied while it arrives. Conflicts, triggers, replication origins, disable_on_error, SKIP and the statistics views behave as in PostgreSQL. Any table can be the target, a search table or a table without a primary key too.

Against a PostgreSQL 18 subscriber on the same machine, from the same PostgreSQL 18 publisher:

Tables	1M-row copy	2M-row transaction	200k single-row inserts
No keys	1.1x faster	1.4x faster	1.35x faster
Primary keys	1.9x faster	1.6x faster	1.3x faster
Primary keys, a unique column and a foreign key	2.0x faster	2.2x faster	1.5x faster

Two changes made for the subscriber speed up every table. An UPDATE that leaves the columns of an index as they are updates the row in place, as PostgreSQL's HOT updates do, and so do MERGE and INSERT ... ON CONFLICT DO UPDATE. Fetching rows by row id no longer decodes the same block again for every row: 2,000 primary key lookups run 2.6x to 8.5x faster, depending on the column's compression.

Docs: CREATE SUBSCRIPTION and replication origins.

Backups

SereneDB takes online backups with PostgreSQL's pg_backup_start and pg_backup_stop. Between the two calls, copy the data directory with any tool while queries and writes go on:

SQL

SELECT pg_backup_start('nightly') AS start_lsn;

SQL

SELECT lsn, labelfile FROM pg_backup_stop();

The backup holds every transaction that committed before pg_backup_start returned. To restore it, put the copy into an empty data directory with the label as backup_label and start the server. pgBackRest takes full backups and restores them. On storage that takes crash-consistent snapshots, such as EBS, Google Cloud and Azure disks, LVM, ZFS or btrfs, a snapshot of the running server's volume is a backup too.

Docs: backup and restore.

Scheduled jobs

A job runs SQL on a schedule inside the server, as its owner and with the owner's privileges:

SQL

CREATE JOB doc_rollup EVERY 1 DAY OFFSET 2 HOURS AS
    INSERT INTO doc_daily SELECT kind, count(*) FROM doc_events GROUP BY kind;

EVERY runs on a calendar grid in UTC: EVERY 15 MINUTES at :00, :15, :30 and :45, EVERY 1 DAY OFFSET 2 HOURS at 02:00 and EVERY 1 MONTH on the first of the month. AFTER 30 MINUTES counts from the end of the previous run, RANDOMIZE FOR spreads the jobs that share a grid, CONCURRENT lets runs overlap and EXECUTE JOB runs a job right away. A body can be a string of several statements, so a job that drops itself runs once:

SQL

CREATE JOB doc_once AFTER 1 SECOND AS $$
    INSERT INTO doc_notes VALUES ('ran once');
    DROP JOB doc_once;
$$;

sdb_jobs() shows each job's next run and last result, and sdb_job_runs() the recent runs. The reindex_interval of an index over a view is now a job the index owns, so its refreshes show up there as well.

Docs: CREATE JOB.

Star UsContact Us

System catalogs that match PostgreSQL 18

pg_catalog and information_schema are now built from PostgreSQL 18's own catalog definitions: the same tables and columns, the same built-in types, functions and system views, with PostgreSQL's OIDs. A lookup by name, key or prefix reads only the matching objects, so the catalog queries of drivers, ORMs and tools don't grow with the number of tables. With 10,000 tables and 2,000 views:

	Change
144 catalog queries, geometric mean	14.1x faster
Queries of drivers and clients	11.1x faster
psql's \d of a table	23x to 63x faster
dbt reading information_schema.columns of a table	443x faster
Catalog queries from 32 clients	30x the throughput

New types came with the catalogs, money, xml, macaddr, the range types and the geometric types among them, and so did functions such as json_each_text, json_populate_record and casefold.

Docs: system tables.

Vector search

Vector search was reworked around HNSW for real workloads. A WHERE next to a nearest-neighbor ORDER BY is answered inside the search: for each segment the planner estimates how many rows the predicate admits and either scores exactly those rows, which is faster and exact for a selective filter, or walks the graph and admits only the rows that match. The predicate can mix indexed and INCLUDEd columns, IN lists, OR and full-text matches.

The search settings now pick themselves. sdb_hnsw_ef_search and sdb_ivf_search_nprobe follow the index and the LIMIT, so an untuned HNSW search for the nearest 1,000 rows finds 99.8% of them instead of about half, and an untuned IVF search 95.5% to 97.5% instead of 56% to 80%. sdb_ann_rescore_width re-reads at full precision only the candidates whose estimated error leaves them a chance at the top k, so HNSW answers up to 2.1x the queries at the same recall. usq8 and usq4 quantize a whole vector on one range, and sdb_ann_force_exact answers by brute force.

Large tables got the most attention. With a quarter of a billion vectors a server starts in 5 seconds instead of 53 and loads its HNSW graphs at disk speed. At 32 clients an unfiltered search serves 3x to 3.5x the queries and a filtered one 5x to 12.6x, and a prepared search keeps its plan for 1.8x the throughput of the simple protocol.

vectorbench, our vector benchmark, is public now.

Docs: filtered vector search and re-scoring.

Smaller, faster search indexes

A new postings format stores a term's documents in blocks of 256 instead of 128, with a block index that skipping reads without touching a data block, short terms kept inside the term dictionary and norms packed by row group. Every part of the postings got smaller:

Part of the index	Change
Documents and frequencies	20% smaller
Positions	15% smaller
Norms	more than 50% smaller

Offsets, the start and length of every token, take less space too.

Smaller files read faster from a cold cache, and wider blocks score faster:

	Change
SearchBench on a cold cache, 100 million and a billion logs	1.4x faster
search-benchmark-game, counts	1.2x faster
search-benchmark-game, top 100 by relevance	1.3x faster

Merging segments works a block at a time instead of a document at a time: compacting the benchmark index takes 2.1x less time and building it end to end 1.2x less, with byte-identical files.

Phrases with gaps of four or more terms now start from their rarest term instead of an end term. A five-term interval phrase with a rare word in the middle runs 1.4x faster. A phrase with a prefix, pattern or fuzzy part also starts from its rarest part, and a ranked top-k prunes it unless the part is fuzzy.

Deleted rows cost less. A segment keeps its deleted rows in a compressed bitmap and runs a plan made for its shape: with rows deleted all over a segment, the median count and top-100 query is 1.5x to 1.6x faster, and rows deleted in runs take a few kilobytes instead of hundreds.

Star UsContact Us

A term dictionary read in place

The term dictionary now lives in blocks of 4 KiB, found through a small root in memory, read where they lie and prefetched by the query that needs them. Every query gets faster on a cold cache, and the more of the dictionary it can skip, the more it gains: fuzzy search and many regular expressions run up to 70x faster, while a pattern that starts with a wildcard, such as LIKE '%tion', still reads most of the dictionary and gains little. A search table now also finds keys faster than the primary key of a regular table: an IN list of keys 60x to 130x faster on a cold cache.

Phrases without positions

A phrase query no longer needs position in the dictionary when the index keeps the column's text: every column of a search table, a column in INCLUDE or an expression stored with included(). It returns the same rows and the same BM25 scores as with positions, for every phrase form:

SQL

CREATE TEXT SEARCH DICTIONARY notes_en AS
    split_text(case := 'lower') WITH (frequency);

CREATE INDEX notes_idx ON notes
    USING inverted (id, body notes_en) INCLUDE (body);

SELECT id, BM25(notes_idx.tableoid) > 0 AS scored
FROM notes_idx
WHERE body @@ ts_phrase('quick brown fox')
ORDER BY id;

The index finds the rows that hold every word, and the text of those rows is checked after the query's other conditions, so a phrase next to a selective condition costs little: a phrase and a common word run 5x to 20x faster than when every candidate is checked. A ranked top-k query prunes such a phrase as it prunes a single word. Positions still pay off for a phrase of common words over long texts.

An indexed expression listed again with included() keeps its value in the index, and a query that returns, filters, groups or sorts on the expression reads that value instead of computing it from the row.

Docs: phrases without positions.

Network addresses in search indexes

INET and CIDR work as in PostgreSQL, ordering, operators and the binary wire format included, and an inverted index answers every operator on them, subnet containment too:

SQL

CREATE INDEX hosts_idx ON hosts USING inverted (id, ip);

SELECT id, ip FROM hosts_idx WHERE ip <<= '10.0.0.0/8' ORDER BY id;

A range or a subnet takes 70 to 90 microseconds whatever it matches: a /8 that holds 902,000 of 2 million addresses takes 92 µs, where PostgreSQL scans the table for 25 ms. >>= finds the stored networks that contain an address, which suits routing and geolocation tables, and the network operators run 4x to 13x faster than Lucene and 17x to 50x faster than PostgreSQL's GiST index.

Docs: INET and CIDR.

K of N across columns

"At least two of these four conditions" had no direct form when the conditions are on different columns: you wrote out every combination of two as an AND, six of them for two of four and 252 for five of ten. ::min_match(K) puts the threshold on the OR itself, and the index runs it as one node:

SQL

SELECT id
FROM docs_idx
WHERE (body @@ 'quick' OR category = 'drama' OR id >= 2)::min_match(2)
ORDER BY id;

A branch can be any predicate the index answers: an @@ match, a comparison, a range, IN or IS NULL. The branches count as written, so two identical ones count twice, and a K above their number is an error. On a TSQUERY value the threshold counts the words of a string or the operands of ||:

SQL

SELECT id
FROM docs_idx
WHERE body @@ 'quick red grey'::min_match(2)
ORDER BY id;

Docs: matching K of N predicates.

Star UsContact Us

Late materialization

A query that keeps a few rows, through ORDER BY ... LIMIT, a plain LIMIT or USING SAMPLE, now reads the columns it only returns for the rows it keeps, instead of for every row it looks at. This works over tables, Parquet files, search tables and inverted indexes, those over views of files, PostgreSQL, ClickHouse and object storage included:

	Change
SELECT * from a wide table, ORDER BY id LIMIT 10	3.3x faster
A phrase and a tenant filter, top 10 by score	5x faster
The same query on a cold cache	4.7x faster, 9x less read from disk
An index over a PostgreSQL table, top 10 by BM25	20x faster

EXPLAIN shows the read as a LOOKUP_JOIN above the TOP_N or LIMIT. A row deleted from the source of a view's index since the index was built is left out, where it came back as a row of NULLs.

Docs: late materialization and LOOKUP_JOIN in plans.

Faster DELETE and UPDATE on search tables

A DELETE or UPDATE on a search table now removes rows by their position in the snapshot it read. The table keeps no index of row ids and no hidden key column, which makes the ClickBench table 5% smaller, and a refresh applies the removals without looking each row up. On that table of 100 million rows:

	Change
Refresh after a DELETE of 1% of the rows	18x faster
Refresh after a DELETE of 10% of the rows	135x faster
The DELETE of 10% of the rows	1.8x faster
Refresh after an UPDATE of 1% of the rows	9.7x faster
A restart that replays a DELETE of a million rows	3.9x faster

UPDATE and DELETE on a search table run on every thread, and an UPDATE of all 10 million rows of a search table runs 7.5x faster. A search table's rowid is the row's position, as ctid is in PostgreSQL, so use it within the statement that read it.

Inverted indexes on tables and views remove rows the same way and store no term per row, so they are smaller and refresh faster.

Docs: row IDs and write memory.

Faster ingest

COPY FROM, the Elasticsearch _bulk API and OpenTelemetry exports parse a large input on several threads:

	Change
COPY of 205 MB from stdin into a search table	8.5x faster
COPY of 205 MB from stdin into a table	2.9x faster
_bulk of 19 MB over one connection	1.75x faster
OpenTelemetry logs into a search table, one client	2.1x to 2.4x faster

Single-row inserts into a search table from many sessions now share their fsync: twice the inserts per second at 4 sessions, 8x at 16 and 15x at 64.

OpenTelemetry metrics arrive as running totals and histogram buckets, and otel_rate, otel_increase, otel_irate and otel_histogram_quantile turn them into rates and percentiles. Retention is a scheduled job that deletes old rows. At startup the server creates the OpenTelemetry tables it lacks with their default indexes and leaves the others alone, so an index of your own replaces the shipped one for good. With create_schema=false it creates nothing, and an export to a missing or mismatched table gets a 503, which OTLP clients retry, instead of a 500 that loses the batch.

Docs: COPY, OpenTelemetry metrics, your own OpenTelemetry schema and counter and histogram aggregates.

Star UsContact Us

Queries of any shape

Generated and deeply nested SQL binds, plans and runs in time that grows linearly with its size wherever such an algorithm exists. We fed SereneDB ray tracers, Mandelbrot renderers and other programs written as a single query:

Query	Before	Now
A UNION ALL of 800 nested branches	15.7 s	10.5 ms
800 nested INTERSECTs	54 s	0.15 s
300 levels of [[...]][1]	21.8 s	0.25 s
A SELECT adding up 3,200 aggregates	2.25 s	0.15 s
A WHERE of 50,000 OR terms	310 s	6 s
A ray tracer's scene, planning	over 24 GB	0.015 to 0.12 s
Three levels of nested vector macros, first call	22.8 s	3 to 5 ms
20 nested SQL functions	39 s	2 ms

statement_timeout now stops binding and planning too, and the functions that build one huge value: at a 200 ms timeout, lpad('x', 1e9, 'y') ran for 46 s and now stops at 0.22 s.

The SQL fuzzer found a query of nested LATERAL and EXISTS subqueries that the planner didn't finish in ten minutes. It plans in 0.3 seconds now, and a chain of twelve correlated LATERAL subqueries plans 36x faster.

Every session has its own share of the scheduler: its queries take turns with those of other sessions, so a short query no longer waits behind a long one.

Faster queries

	Change
sum(a * b + c) over 100 million BIGINTs that can overflow	2.1x faster
ILIKE over ASCII text	1.7x to 3.7x fewer instructions
lower(x) = 'value', now run as ILIKE	1.9x to 2.5x faster
LIKE ... ESCAPE and GLOB over sorted data	6x to 7x fewer instructions
Regular expressions with capture groups	4x to 7.6x faster
count(*), min and max of a search table without WHERE	5.7x to 10x faster
A GROUP BY under a LIMIT without ORDER BY	4x to 5.8x faster
range() summing 50 million rows	15x faster
HUGEINT division and modulo	9x to 10x faster
Nested columns of an attached ClickHouse table	5x to 22x faster
An Elasticsearch _search of 10,000 hits	3.6 s to 34 ms

range(), generate_series() and the part of a plan above a recursive query run on all threads.

Attach a copy of a database

A database directory copied from another SereneDB server, such as a snapshot, attaches read-only and answers queries, searches through its indexes included:

SQL

CREATE SERVER archive FOREIGN DATA WRAPPER serenedb_fdw
  OPTIONS (path '/mnt/snapshots/shop/engine_v1/16390');

SELECT id FROM archive.public.docs WHERE body @@ 'invoice';

Nothing is written on either side. Any number of servers can read one directory, and a server and the readers of its directory exclude each other.

duckdb_fdw attaches a DuckDB database file as a server, which comes back after a restart and needs USAGE like any other server:

SQL

CREATE SERVER sales FOREIGN DATA WRAPPER duckdb_fdw
  OPTIONS (path '/data/sales.duckdb', read_only 'true');

Docs: read-only SereneDB databases and DuckDB files.

Star UsContact Us

EXPORT and IMPORT that round-trip

EXPORT DATABASE followed by IMPORT DATABASE gives back the same database: the same objects, the same data and, for sequences, the same next values. Text search dictionaries, inverted indexes and jobs come along with their definitions, a SERIAL or OWNED BY sequence comes back owned by its column, user types keep their schemas and system tables stay out.

SQL

EXPORT DATABASE 'target_directory' (FORMAT parquet);
IMPORT DATABASE 'source_directory';

CSV, text, binary and Parquet carry every value exactly, and Parquet is the one we recommend. Grants stay behind, since roles belong to the server.

Docs: EXPORT and IMPORT DATABASE.

More SQL and a faster parser

A primary key or unique constraint can wait for the commit: with DEFERRED, a transaction may hold duplicate keys in between, and the commit fails if any are left.

SQL

CREATE TABLE accounts (id INTEGER PRIMARY KEY DEFERRED, name VARCHAR);

The parser is 2x to 3.5x faster again than in v26.10.1 across our parser benchmarks. The grammar got stricter where it used to guess: GENERATED BY DEFAULT AS (expr) is refused instead of becoming GENERATED ALWAYS, and a dotted column name in CREATE TABLE is a syntax error instead of losing its qualifier.

A view or macro that reads another database depends on what it reads, and so does a table whose default calls another database's sequence. DROP refuses while such a dependent exists and CASCADE drops the view or macro or removes the default. DROP DATABASE ... CASCADE | RESTRICT follows the same rules.

SET integer_overflow = 'wrap' makes integer arithmetic wrap around instead of failing, for hash functions written in SQL. clamp(x, min, max), hash_city64(value, ...) and range_st, a range that produces its rows in order on one thread, are new.

filter_reorder sets how far the conditions of an AND may move around the ones that can fail, such as a cast that overflows: safe keeps those in place, fast (the default) lets cheap conditions run before them and never keeps the written order.

A partial index whose WHERE is a constant is folded when it's created, as a query's WHERE would evaluate it: WHERE 'true' makes a plain index.

ST_PointOnSurface and ST_Normalize return what PostGIS returns, and geometry casts to and from WKB blobs.

serened shell and serened psql now answer SQL as the server does, with the same integer division, identifier case and PostgreSQL types.

Docs: deferred constraints, dependencies across databases, numeric functions and filters that can fail.

CREATE OR REPLACE INDEX

CREATE OR REPLACE INDEX builds the new index and drops the old one in one transaction, and other sessions use the old index until the commit. If the build fails or the transaction rolls back, the old index stays as it was:

SQL

CREATE OR REPLACE INDEX genre_idx ON films (genre, year);

The new index can be of another kind and on another table, so an HNSW index can take the place of an IVF one, and an index of your own the place of the shipped OpenTelemetry index. Both indexes exist until the commit, so a replace needs memory and disk space for both.

Docs: CREATE OR REPLACE INDEX.

Closer to Postgres

• Drivers bind what they send. A parameter a driver declares numeric, numeric[] or jsonb, as psycopg does for Decimal and Jsonb, used to fail every statement; it binds now, and a numeric is rounded only where the statement stores it. psycopg 3 inserts a NULL before a binary parameter, which SQLAlchemy's batched inserts do, pgjdbc's batches keep a VARCHAR parameter a VARCHAR and asyncpg sends vector parameters.
• Prepared statements work as in PostgreSQL. PREPARE name(type, ...), DEALLOCATE ALL of a driver's statements, DISCARD ALL and pg_prepared_statements behave as PostgreSQL's, and a prepared TRUNCATE of several tables or a prepared COPY runs again on every execution.
• SQL functions and macros work as in PostgreSQL. An argument is a value of the call, so a volatile one such as random() has one value per row however often the body reads it. A subquery of the body runs for each call, STRICT returns NULL for a NULL argument and RETURNS void runs its body.
• COPY works as more tools expect. COPY TO STDOUT sends one message per row, as psycopg's Copy.rows() expects, and a text COPY FROM STDIN commits only when the client finishes its input.
• Serial columns. pg_get_serial_sequence returns the column's sequence, which Django's sequence reset and Rails rely on, and ALTER TABLE ... ADD COLUMN id serial numbers the existing rows.
• Views have columns. pg_attribute and information_schema.columns list a view's columns, so tools that introspect them, such as dbt, see them.
• Command tags are PostgreSQL's, and COMMIT of a failed transaction answers ROLLBACK.
• An error raised at commit keeps its SQLSTATE. A value an inverted index refuses at commit fails with 42804, not with 40001, which tells a client to retry a statement that can't succeed.
• Floats print as in PostgreSQL, with the same switch to exponent notation and a -0 that keeps its sign.

Docs: prepared statements and SQL functions.

Star UsContact Us

Privileges and secrets

SET GLOBAL, RESET GLOBAL, DROP SECRET and changes to max_expression_depth need a superuser. An S3 secret hides its proxy password when it's listed, and pg_cancel_backend and pg_terminate_backend check privileges as PostgreSQL does. Behind a proxy that speaks the PROXY protocol, client authentication judges the client the proxy names, not the proxy.

Docs: SET scopes.

Faster on newer CPUs

The binary still runs on any x86 CPU since Haswell and any ARM CPU with the CRC32 and crypto extensions, and now uses more of a newer one. Vector search picks AVX-512 kernels on x86 and SVE kernels on ARM at run time, and text analysis picks SVE and SVE2 on ARM and AVX-512 on x86.

On a CPU below that baseline, or in a virtual machine that hides part of it, as Docker Desktop on an Intel Mac can, every binary now names the extensions it lacks and exits instead of crashing at startup: serened cannot run on this CPU: it lacks AVX2 BMI1 BMI2.

Docs: CPU requirements.

On the blog

Sparse n-grams for substring search puts GitHub's code-search trick inside an inverted index: the grams a line indexes, the few a substring query needs and the stack that picks them. Phrases with gaps, intervals and slop shows how one positional index answers exact phrases, gaps of a fixed or ranged length and proximity in or out of order.

In numbers

October's 158 pull requests closed 262 issues and hold about 250 fixes and nearly 300 optimizations. More than 100 of the fixes make SereneDB answer as PostgreSQL does. The release notes list every change.

Upgrading

v26.11.2 can't read inverted indexes and search tables of earlier releases: EXPORT DATABASE with the old release, then IMPORT DATABASE with the new one.

What to expect in November

In the first half of November search tables get new codecs for their stored columns, a merge rewritten around them and ORDER BY, which keeps a search table sorted by an expression, such as a column or a Hilbert or Z-order curve. Search index files can then live on S3 and Azure and SereneDB servers talk to each other over the quack protocol.

Kudos

OrlovEvgeny made ts_between analyze its bounds like the one-sided range functions (#1530), validated the numbers in Elasticsearch queries (#1645), kept the default database name intact at startup (#1646) and removed a deadlock from index refresh (#1736).

AlmazAlmaz made tuples work everywhere structs do (#1387), made a CREATE INDEX that can't commit fail with its cause (#1388), folded constant partial-index predicates (#1421), fixed date ranges on included columns (#1722) and three build problems on the way (#1382, #1383, #1390).

deymon-d made interval phrases of four or more terms start from their rarest term (#704).

LostImagin4tion fixed the macOS build and tests (#1640).

Our student zhemalb made phrases with a prefix, pattern or fuzzy part start from their rarest part and let ranked top-k prune them (#1879), and turned a Lucene query with a slop on a phrase with a range gap into an error instead of a crash (#1846).

In review and in progress: numeric tuples and geometry on multidimensional curves from AlmazAlmaz (#1317, #1320), a YAML reader from OrlovEvgeny (#1357), substring search on an FM-index from aboffa (#1980), PDF text readers from varvarkins (#1644), .zip archives from Dmitry909 (#1637) and IResearch as a standalone C++ library from mosya415 (#1389).

Want to be in the next one? We tag beginner-friendly work with good first issue, so grab one, ask questions in the issue and we'll get you going.

---

v26.11.2 is the current release. Grab it and point it at something. If you'd rather look before you install, the code search demo is live, ⌘K on the docs gets you SereneDB searching SereneDB's documentation and .docs in serened shell does the same offline. Every raw benchmark result is at playground.serenedb.com/searchbench.

If you like what you see, ⭐ star us on GitHub. Hit a rough edge? Open an issue, or send the fix like everyone in Kudos did.

See you in the next State of Serene.