← All digests

DBMS Weekly — 2026-10-05 (week of Oct 5–11)

PostgreSQL 19's GA moved from 29 October to 19 November. RC1 stays on 15 October, and an RC2 was added for 12 November, "given the volume of fixes that have been added to the back branches". The week explains why. LLM-assisted bug sweeps kept finding wrong-results bugs, and several are older than 19. bpchar btree indexes have claimed to be safe for deduplication since 13, which can make index-only scans return wrong results, and the commit message asks users to REINDEX them. Hash-index scans that start during a bucket split miss rows on every branch; that fix landed during the RC1 freeze and drew a rebuke. An antijoin returns rows that fail their own WHERE clause on 16 and later. Peter Eisentraut's account of the PG19 reverts lists the same causes: LLM review on code written before it existed, months lost to security fixes, and no "experimental" label to ship behind. On the blog side, a war story traces a throttled queue to SAVEPOINT plus FOR UPDATE creating MultiXacts. An independent test timed 179 index-advisor suggestions and found 18% made the query slower. A Japanese reproduction failed to confirm last week's ClickHouse claim that a WAL-G base backup empties the page cache. The CommitFest grew by a net +20, and commits more than doubled to 17.

PostgreSQL

  • The reverts will continue until morale improves — a committer who worked on several of the reverted features explains why PG19 lost 8–10 significant features after beta began, against about one in a normal cycle. His four reasons: LLM-assisted review found long tails of edge-case bugs (around 40 reports per feature) in code written before such review existed, and he says this was not "vibe coding". Senior developers spent April–August on security fixes, so beta testing started late. Postgres's "everything works with everything" design produces more combinations than anyone can test by hand: domains over composites, dropped and re-added columns, reattached partitions, security-definer triggers. And the tree has no "experimental feature" status to ship behind. He still names REPACK CONCURRENTLY, logical replication of sequences and pg_plan_advice as the 19 headliners. A companion to last week's revert census. (Peter Eisentraut · peter.eisentraut.org) [by committer]
  • How savepoints quietly throttled our Postgres queue — a queue handling about 15M jobs/day on Cloud SQL got slower as workers were added. Each job ran inside a SAVEPOINT, on rows the parent transaction had locked with FOR UPDATE SKIP LOCKED, so every in-flight row's xmax became a MultiXact and LWLock:MultiXact dominated the waits. Query Insights showed only the wait class, which hid the cause. (Rory Malcolm · incident.io) The fix:
    • claim jobs with UPDATE … SET claimed_until = now()+10s … RETURNING, then process each in its own transaction
    • leave claimed_until unindexed and set fillfactor 85, so claims are HOT updates
    • run one dispatcher per pod, cutting concurrent claim queries from workers×pods to pods
    • result: 3.5× peak throughput
  • I timed 179 index recommendations on real data. 18% made the query slower — every index a HypoPG-based advisor suggested for the Join Order Benchmark (PG16, IMDB, 7 GB) was built and timed, with win/loss thresholds fixed beforehand. 128 suggestions helped, 32 hurt, and 7 more than doubled the runtime. The worst case had an estimated −91% cost and went from 0.93 s to 7.3 s, because the new index enabled a nested loop over a badly underestimated join. 34 suggestions could not be built at all ("index row requires 9392 bytes"). Ranking candidates by estimated saving held up (Spearman 0.83); the per-query percentage estimates did not. (Prateek Arora · prateek-arora.github.io)
  • Consolidating databases with Postgres logical replication — part 2 of a series, every step reproducible with docker-compose on postgres:18. (Dimitri Fontaine · tapoueh.org) What it shows:
    • subscriptions cannot remap schema names
    • a row filter on a column outside the replica identity makes the next UPDATE on the publisher fail
    • filters never remove rows that were already copied
    • column lists keep PII off the wire, verified with pg_logical_slot_get_binary_changes
    • with origin=none, a downstream CDC consumer sees none of the applied rows
    • a logical slot on a standby has four prerequisites, and PG19's effective_wal_level removes only one
    • PG19's publication EXCEPT can't be combined with TABLES IN SCHEMA
  • An OLTP perf check on Postgres 19 Beta 3.5 — about 800 hours of runs comparing 18.6 with 19beta3/beta4 on 4–96 vCPU machines (AWS and Hetzner), using pgbench variants and a TPC-C-like script; the scripts are published. Total run time was about 3% lower on 19. pgbench INSERTs ran about 25–30% faster, which he hasn't explained yet; keyed UPDATEs were flat or slightly slower. EC2 jitter was heavy, and he argues for reviving the performance farm. (Kaarel Moppel · kmoppel.github.io)
  • How TABLESAMPLE picks rows — read from system.c/bernoulli.c and tested on 18.6 with 2M rows. SYSTEM hashes the block number; BERNOULLI hashes block plus line pointer (1.2 ms vs 10.4 ms). On time-ordered data, SYSTEM returns runs of about 107 consecutive rows: min() was off by 44 minutes to 5 hours, and averages varied about 10× more than with BERNOULLI. REPEATABLE fixes row addresses, not rows. After an UPDATE of every row, BERNOULLI's repeated sample kept only 109 of its 10,003 rows, because each HOT update gives the row a new line pointer. SYSTEM kept 8,740 of 8,795. (Radim Marek · boringsql.com)
  • pg_vexec: vectorized columnar execution for stock PostgreSQL 19 — follows last week's Cloudberry-on-PG19 port, again built mostly by LLM agents (about 43K lines of C over Oct 5–7). CustomScan vector nodes work on batches of up to 1,024 rows. They compete on cost through the existing path hooks and through ORCA, which now builds on vanilla REL_19_STABLE; no core patches. With the module preloaded but off, 239 regression tests pass with unchanged output. 121 TPC-H/TPC-DS queries match DuckDB's answers, and 651 of 652 hash joins become VecHashJoin. ClickBench at 10M rows: geomean 625 → 506 ms. TPC-H Q1 runs 1.8–2.8× faster from scaled numeric alone. COUNT(DISTINCT) on text got 3.1–3.6× slower, because the cost model picks a serial vector plan over a parallel row plan. No vector nodes under UPDATE/DELETE/MERGE yet. (ru version on Habr) (Igor Suhorukov · igor-suhorukov.github.io) [unverified: single-machine benchmarks]
  • Infinite recursion detected in policy and new row violates row-level security policy — PG17.11, 1M rows. The recursion error is raised at rewrite time, so even WHERE false and a plain EXPLAIN fail, while the table owner (without FORCE) and superusers never see it. Six fixes were measured, from 0.09 ms to 6.6 s. The same SECURITY DEFINER helper took 74.5 ms used as IN (SELECT …), 0.15 ms as = ANY(ARRAY(SELECT …)), and 6,583 ms when left VOLATILE. The "new row violates" message comes in four forms, each meaning something different, and RETURNING and ON CONFLICT also check SELECT policies. (Chris van Eijk · now-next.nl)
  • Amazon Aurora's analytics is gated DuckDB — he ran the same query on the same Parquet file under Aurora's aurora_analytics and under pg_duckdb 1.1.1. The operator trees are identical, and both emit __internal_compress_integral_utinyint(#0, 1920) with the same constant. Aurora exposes only an FDW and a server: DuckDB SQL is rejected, lake tables are read-only, and heap tables can't be accelerated. Read it alongside last week's Qiita measurements of the same feature. (Franck Pachot · dev.to)
  • Loading terabytes into Lakebase Postgres in minutes — a bulk loader built from stock parts. (Yecheng Yang et al. · databricks.com) [vendor blog — substantive] [unverified: 147× and 1 TB in 5 min are vendor figures, heap only]
    • Spark executors run sandboxed Postgres instances in binary-upgrade mode, with the destination's catalog OIDs copied in
    • they write heap pages with COPY FREEZE and checksum every page
    • the PK btree is built from exported (key, ctid) pairs through a custom table AM that hands out shifted ctids, so the standard btree build indexes a heap it never read
    • the primary then commits one small WAL record listing the new files
    • no core patches
  • Rethinking memory management for Azure HorizonDB on disaggregated storage — what changes when there is no kernel page cache under Postgres. shared_buffers goes to 60–70% of RAM, buffer rings are disabled, read_stream's reliance on kernel readahead is reversed, and prefetch responses are written straight into shared buffers. A design note with no measurements. (Anish Shah · Microsoft) [vendor blog — substantive]
  • Scaling Citus beyond one coordinator: query routers — shard-less metadata nodes (shouldhaveshards=false, group IDs from 1024) plan and route queries. Point-SELECT throughput went from about 552k tps on one coordinator to 1.06M with 2 routers and 1.43M with 3 (p99 4 ms). (Álvaro Hernández · stackgres.io) [vendor blog — substantive] [unverified: vendor benchmark]
  • TIN v1.0.6 — PlanetScale's answer to ParadeDB's reply (09-28 issue). It adds stemming in 18 languages (requires an index rebuild) and block-max plans for top-k with tiebreaker columns. Re-running ParadeDB's own scenarios, it claims 3.1–5.0× ParadeDB's throughput with default scoring and 1.3–3.0× with full BM25, and says ParadeDB 0.26.0's index grew 29%. (Patrick Reynolds, Eric Ridge · planetscale.com) [vendor blog] [unverified: vendor-run benchmark]
  • pgvector 0.8.7 — security release. (pgvector · pgsql-announce)
    • fixes a buffer overflow in IVFFlat index builds (CVE-2026-103484, CVSS 8.8) that can lead to code execution
    • any role that can CREATE INDEX on a vector column can reach it, so upgrade
  • pgJDBC 42.7.14 — security release for CVE-2026-107314 and CVE-2026-107315. (pgJDBC · pgsql-announce)
  • pg_ivm 1.16 — incremental view maintenance extension. (pg_ivm · pgsql-announce)
    • PostgreSQL 19 support
    • fixes: segfault when a view is maintained and dropped in the same transaction; failures on columns whose type has no default equality operator; failures once the OID counter passes INT_MAX; spurious lock errors at commit

PostgreSQL mailing lists

  • [hackers] Updated PostgreSQL RC / GA dates — RC1 stays on 15 October. GA moves from 29 October to 19 November, and an RC2 is added for 12 November, one week after the scheduled minor releases. The stated reason is "the volume of fixes that have been added to the back branches", and Katz calls it "an atypical major release cycle". This replaces the dates in last week's issue. (Jonathan S. Katz · pgsql-hackers) [committed]
  • [hackers] [PG19] eager aggregation gives wrong results because of bpchar_ops — bpchar equality ignores trailing spaces, yet bpchar_ops/bpchar_pattern_ops declared equalimage. PG19 eager aggregation trusted that and merged two groups into one. nbtree deduplication trusts it too, and has on every branch since 13: a posting-list split can attach a TID to a key that is equal but not bytewise identical, so index-only scans can return subtly wrong values. Tom Lane found that oidvector_ops has the same flaw. Fixed in a32b8dc986 and back-patched to 14. The commit says: "Users should REINDEX any B-Tree index on a bpchar column." amcheck now flags such indexes ("metapage incorrectly indicates that deduplication is safe"), which also broke the cross-version upgrade tests until a follow-up. (Shihao Zhong; Peter Geoghegan, Tom Lane · pgsql-hackers/-bugs) [committed]
  • [committers] Fix hash index scans that start during a bucket split — _hash_next didn't step from the bucket being populated into the bucket being split, so a scan silently missed every match in the second bucket. This is a wrong-results bug on all stable branches, from 7c75ef57's page-at-a-time hash scans, and the commit credits Claude Code with finding it. Fixed in 173cf85a07 and back-patched to 14 on Saturday; in the thread, Tom Lane pointed out that REL_19_STABLE was frozen for the RC1 wrap. A second hash bug, a buffer-pin leak in _hash_finish_split's failure paths, now has a patch posted. (Peter Geoghegan, Amit Kapila · pgsql-committers) [committed]
  • [hackers] Wrong results from an antijoin and from a parameterized Append — two planner wrong-results bugs in released branches. (Richard Guo, Tom Lane · pgsql-hackers) [patch posted]
    • a NOT EXISTS sublink inside a LEFT JOIN subquery returns rows that "do not even satisfy the WHERE clause", from v16 to master; Guo: "This scares me because it seems very easy to hit". Tom Lane agrees with the approach.
    • a semijoin over UNION ALL with lateral filters returns 3 rows instead of 1, from v17 on. Tom bisected it to 9f13376396 (misused ppi_rows in get_memoize_path()); his v2 still has "a number of loose ends".
  • [hackers] [PG19] Wrong results from NOT NULL-based expression simplification — five queries that work on 18 and break on 19. Examples: an ON CONFLICT arbiter on coalesce(b,'') stops matching its index, and COALESCE swaps in a column with a different collation. Guo pushed three fixes. Fujii Masao then showed one of them breaks another ON CONFLICT case, and that fix is deferred until after RC1. Robert Haas found expr_is_nonnullable() treating IS DISTINCT FROM as never-NULL (fixed in f5635c2786) and warned: "I'm afraid this isn't going to be all the bugs." The same contributor's LLM-assisted sweep of 19 produced more threads this week: pg_verifybackup hanging on -Ft -z backups and COPY TO JSON using the table's column names instead of the query's (both fixed), plus Memoize with a nondeterministic collation (CF #7421) and three CHECK-constraint bugs, still open. (Shihao Zhong; Richard Guo, Fujii Masao, Robert Haas · pgsql-hackers) [committed] in part
  • [hackers] REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates — the release management team raised it at its 7 Oct meeting: the run fails once about 104 million rows have been updated or deleted concurrently. The failing allocation is the comboCids array in GetComboCommandId() (8 B × 209,715,200 entries), not the hash table. Herrera: "IMO this is not a regression", and a PG20 patch removes the mechanism. Outcome: no fix in 19, and the limit is documented in a rewritten REPACK reference page that also adds disk-space and xmin-horizon warnings. (Radim Marek, Álvaro Herrera, Antonin Houska, Nathan Bossart · pgsql-hackers) [rejected as a fix; documented]
  • [bugs] PostgreSQL 18.6/17.11: standby PANIC on restart after VM truncation — a regression in the current minor releases. After a switchover, a standby that replayed a heap/VM truncation PANICs on restart with "page 0 of relation …_vm does not exist" and "WAL contains references to invalid pages". The TAP reproducer fails on 18.6 and 17.11 and passes on 18.4 and 17.10. v4 of the patch is CF #7386, to be back-patched through 17. (Jacky Nguyen; patch by Shihao Zhong · pgsql-bugs) [patch posted]
  • [bugs] BUG #19752: GROUP BY a constant plus a cast to a length-limited array type crashes the server — SELECT ARRAY[]::varchar(500)[] GROUP BY CAST('true' AS boolean) segfaults the PG18 planner; EXPLAIN alone is enough. Bisected to f5050f795ae ("Mark expressions nullable by grouping sets"). Tom Lane: "even non-crash cases might result in seriously wrong answers. We shouldn't be replacing constant subexpressions by group Vars ever." (Mert Kul; Andrey Rachitskiy, Tom Lane · pgsql-bugs) [patch posted]
  • [bugs] GIN jsonpath disagrees with a seq scan — two silent wrong-results bugs in GIN @@ support, from 16 to master. '!(!($.a == 1) && $.b == 20)' returns fewer rows through the index, and in a second report $.a == "2".double() looks up the string "2" instead of the number. Tom Lane posted one patch for both (CF #7433). (Chinmay Kanchi; Tom Lane · pgsql-bugs) [patch posted]
  • [bugs] BUG #19747 → Remove array_nulls GUC — if the target database has array_nulls = off, a plain-format restore errors on int[] or silently turns NULL elements of text[] into the string "NULL". Tom Lane rejected having pg_dump set the GUC ("then we'll never be able to remove it") and deleted the 8.2-era GUC in master (e96efd07c7). Released branches keep the hazard. (Ke; Tom Lane, Laurenz Albe · pgsql-bugs) [committed] (PG20)
  • [hackers] RLS bypass: ON CONFLICT DO UPDATE/SELECT evaluates WHERE before RLS check — the claim: ExecOnConflictUpdate()/ExecOnConflictSelect() run the user's WHERE against the existing row before the RLS conflict check. A user who can't see a row could leak its columns through a leaky function, or use the difference between an error and a silent no-op as an oracle, given a guessable key. The ordering would date from 2015 (168d5805e4). A patch swapping the order is CF #7403; there are no replies yet. (Joao Detomini · pgsql-hackers) [open] [unverified]
  • [hackers] Policy for abandoned extensions — PGXN was told of possible superuser escalations in two abandoned extensions and has no policy for transferring ownership. 122 of 496 distributions show no sign of life in five years (70 of them ship C), but only 3 are still packaged in PGDG. Tom Lane wants abandoned extensions flagged to users; Joe Conway wants any transfer vetted, citing the xz backdoor. (David E. Wheeler; Tom Lane, Joe Conway · pgsql-hackers) [open]

CommitFest (open: PG20-3, #62)

  • Balance (Oct 5–11): 40 new · 20 closed (17 committed / 3 withdrawn) → net +20. (vs +25 last week, about 5 lower. Both weeks are complete counts: new entries from the sequential patch IDs #7394–#7433, closures from each patch's history in #61 and #62.) 6 entries moved to Ready for Committer. Queue at scan: 339 needs review · 38 waiting on author · 92 ready for committer (469 active, up from 433). Two filers produced 18 of the 40 new entries, mostly bug reports.
  • New this week: Expand OR clauses in joins to UNION ALL paths (Ayoub Kazar) — when a WHERE-clause OR spans two or more relations, the planner can only re-run a BitmapOr per outer row or apply the OR as a join filter. The patch plans each arm k separately as Ck AND (C1) IS NOT TRUE AND … AND (Ck-1) IS NOT TRUE, so each row comes out of exactly one branch and no CTID dedup is needed. It combines the branches in an Append that add_path() keeps only if it is cheaper. This revives Tom Lane's 2017 design. The motivation is SQL/PGQ's undirected edges: on LSQB SF0.1, one query rewritten by hand as UNION ALL went from 91.7 s to 1.6 s. The first review found test failures and problems with subplans and volatile functions; v3 is out (thread). Also new: On-demand WAL replay: accept connections before crash recovery (#7427, RFC; pages are replayed the first time they are read), Reduce SyncRepLock contention on the commit path (#7402), Batch TIDBitmap insertions in index scans (#7422), Detoast a column once per row instead of once per reference (#7423), Unlocked path for GetSnapshotDataReuse (#7425), Extensible ReadyForQuery message and hook for poolers and WAIT FOR LSN (#7415), Planner picks an index-only scan for an AM without amcanreturn (#7410).
  • Closed: committed: [PG19] eager aggregation wrong results with bpchar_ops (#7399), WAL segment fd leak on read errors can PANIC (#7328), [PG19] pg_verifybackup hangs on gzip tar (#7398), tuplesort_putdatum() memory accounting (#7311), Don't reprocess PHV copies within an outer-level agg (#7363), Clear FatalError earlier during crash restart (#7345), parallel autovacuum: propagate track_cost_delay_timing (#7346), COPY FROM … WHERE with negated operators (#7395), amcheck: interruptible rightlink walk (#7028), and 8 smaller ones. Withdrawn: Skip a redundant singleton GROUP BY node (#7419) and two PG20-2 leftovers.

Community pulse

  • Should a data engineer rewrite every transformation in Python instead of SQL? — the OP is moving an MSSQL warehouse to Databricks, and a colleague wants the SQL silver layer rewritten as Python notebooks. The top reply described the opposite move ("converting from python to sql silver and everyone is happier"). PySpark and Spark SQL run on the same engine, so speed doesn't decide it; the trade-off is readability against unit tests and parameterisation. Where it landed: SQL is the default for silver, and notebooks nobody else can open are a governance problem, not a language choice. (r/dataengineering · 168 pts, 125 comments)
  • DuckLake — DuckLake's lead developer explained that it is a spec, not a DuckDB feature: a catalog database needs only primary keys, basic types and ACID. DuckDB, SQLite, Postgres and (experimentally) MySQL work as catalogs today, and a DataFusion implementer uses Postgres because Iceberg was too heavy for low-latency work. Users said the spec is 1.0 but the software isn't (broken catalog counts in 1.5.4, a 10× slower parser on the v2 branch). A well-received dissent: lakehouses have no row or column access control, which makes them a poor fit for enterprise BI. (Hacker News · 237 pts, 38 comments)
  • Single-node DuckDB vs Trino at 10 TB — every engine was waiting on S3 with its CPUs about 90% idle, and running 4× as many threads as cores gave about 3× the S3 read rate. On 10 TB TPC-DS, one DuckDB box beat Trino on the same hardware by 1.7–2.1×. Against a 5–13-node Trino cluster it was 2–3× slower, at about half the cost. Replies pointed out that only three queries were tested, with no concurrency or heavy writes; one countered with StarRocks numbers. (r/DuckDB · 94 pts, 14 comments)
  • What is Postgres's most important feature? — the OP argued for logical replication as a way out of a cloud. The votes went instead to the open licence (67) and transactional DDL (29), since MySQL commits DDL implicitly. Pushback on the OP: logical replication carries no DDL or sequences, and an unmonitored slot fills the disk, so "the way out is still a project". (r/PostgreSQL · 26 comments)
  • What does "managed Postgres" actually mean? — the agreed baseline is patching, failover, backups, and clear durability and uptime promises. Autoscaling is rarer, though one commenter reported Aurora Serverless scaling in seconds on a multi-TB database. Autovacuum tuning (watch n_dead_tup, lower per-table scale_factor) and connection pooling remain the customer's job. (r/PostgreSQL · 17 pts, 26 comments)
  • Schema migrations for ClickHouse — there is no open-source winner. dbmate needs every statement wrapped separately, goose struggles with "up to", and Atlas supports ClickHouse only in its Pro plan. Where it landed: write migrations with IF NOT EXISTS (a cluster node may already have applied one) and plan forward-only. (r/ClickHouse · 11 pts, 19 comments)

Wider DBMS & distributed data

  • SlateDB: what if S3 were your only disk? — a week of production numbers from an independent user running a small SlateDB service. (Renato Cron · blog.renatocron.com)
    • 358M keys went into 1.67M WAL objects (about 214 per PUT): about $45/month in requests, against about $7,000 for a PUT per key
    • an in-memory ack took 2.5 ms (median); a durable ack took 216 ms median and 885 ms p99
    • fencing, read from the code: WAL files are created with If-None-Match: * (not the If-Match the launch post describes). A new writer bumps the epoch and claims the next WAL number with a zero-byte object, so the old writer's next flush gets a 412
    • a Kubernetes rolling update left two writers fencing each other up to epoch 33, with no corruption
    • DigitalOcean Spaces rejects quoted ETags, so GC never deleted anything (38 GB stored for 44 MB live); v0.15 added a switch to work around it
  • ClickHouse outcompresses Parquet: surprises from our migration — IoT GPS pings (more than 1T rows/year), sorted by (S2 cell, session, time), with lat/lon stored as int32 ×1e6. Parquet bytes per row: 28.9 with Spark defaults, 59.4 with a bad sort order, 8.2 tuned (DELTA_BINARY_PACKED + ZSTD-11). A 6.1 version with BYTE_STREAM_SPLIT on integers can't be read by DuckDB or Polars. ClickHouse MergeTree with DoubleDelta/T64/Delta and ZSTD(3) reached 5.88 B/row, 28% below tuned Parquet. (Tom Alard · tomalard.github.io)
  • We solved SQLite's single-writer limitation — sqlite-multiwriter is an Apache-2.0 VFS, so SQLite itself is not patched. Each writer gets a private WAL over a snapshot. Commit validates pages against everything published since that snapshot (first committer wins, otherwise SQLITE_BUSY_SNAPSHOT), with a background-compacted group-commit log. An optional rebase replays row-level changes when a conflict is only page-level. The authors describe it as snapshot isolation plus read-page validation, "tested, not proved serializable". 16 threads on disjoint rows: 8,630 → 49,277 tx/s, p99.9 157 → 2.08 ms. All writers on the same 4 rows: 2.3×, with 17 retries per 100 transactions. (Marco Bambini · substack) [unverified: self-reported benchmarks]
  • The ground beneath the database (Atlas Infinite) — why MongoDB couldn't just ship a physical redo log to page servers. WiredTiger is checkpoint-based and lock-free, with speculative eviction, so no single canonical page image exists. Durability is instead the logical oplog on a majority of a Rust log service, and at checkpoint the engine emits a separate physical log of page images and deltas for the page servers. Standby lag is cut to milliseconds by layering a local "ingest table", where the oplog is applied, over a shared checkpoint-consistent "stable table". Pages are encrypted on the compute node before they leave it. A useful contrast with Neon-style Postgres disaggregation. (Abhishek Chauhan · mongodb.com) [vendor blog — substantive] (Murat Demirbas has a summary)
  • SQLite 3.54.0 — (sqlite.org)
    • UPDATE no longer touches an expression index when the indexed value is unchanged
    • better folding of expr OR TRUE/FALSE; DISTINCT is ignored on the right side of IN; faster UNION with LIMIT 1
    • the authorizer is now invoked for functions in CREATE TABLE DEFAULT clauses
    • new SQLITE_LIMIT_SCHEMA and SQLITE_LIMIT_TRIGGER_STEPS; the diskused() extension replaces sqlite3_analyzer
    • Windows XP/CE dropped; about 4% fewer CPU cycles on speedtest1
  • Agent mode in the DuckDB CLI — v2.0's CLI detects a coding agent from environment variables when stdout is not a TTY. It then prints compact tables and caps output at 1,000 rows or 10 KB with an explicit truncation marker. It also adds an order-independent result hash, JSON errors, and cost estimates on stderr. The measured result is modest: on TPC-H SF100 the query output the model read fell 59%, but that was only about 0.5% of total input tokens, and the agent took slightly more turns (237 vs 224). DuckDB 2.0 itself is now dated 21 October. (DuckDB team · duckdb.org)

Commercial engines (SQL Server, Oracle, MySQL, …)

  • How to do pagination right: row comparison, datatypes and index range scans — keyset pagination with (created_on, id) < (:a, :b) across engines. (Franck Pachot · dev.to)
    • Postgres uses the whole row comparison as the index boundary, but if the id is passed as numeric instead of bigint it silently uses only the leading column
    • Hibernate emits a<? OR (a=? AND b<?) on every database, which Postgres turns into a filter instead of a composite seek
    • MySQL 8.4 range-scans only when the row is the leftmost index prefix
    • Oracle has no ordered row comparison, so you add a redundant leading range or use UNION ALL
    • SQL Server turns the OR into several ranges inside one ordered seek; MongoDB merges two index scans
  • Thread pool in Percona Server and MySQL, part 1 — the thread pool now ships in Community MySQL (26.7.0/9.7.2), where it used to be Enterprise-only. Sysbench read-write, 100M rows, 2×20-core Xeon, 40–2,560 connections, single runs. I/O-bound (2 GB buffer pool): 2,304 vs 129 TPS at 2,560 connections, with the best setting at 10 thread groups, fewer than the 40 cores. Fully cached, no thread pool wins on TPS, but p95 at 2,560 connections is 787 ms against 235–297 ms with it. Directly relevant to the Postgres process-per-connection debate. (Bogdan Degtyariov · percona.com)
  • PGO or not PGO: profile-guided builds of mysqld — the gain is under 5% overall: about 12% at low concurrency, under 2% under contention. A build trained only on sysbench-tpcc was slower than a plain build. The reasons: untrained paths get compiled as cold, non-atomic profile counters were corrupted at 128–1,024 threads (-fprofile-update=atomic is required), and instrumentation makes spin loops look hot. (Marco Tusa · percona.com) [results in charts only]
  • MariaDB: in-memory BLOB/JSON temp tables and atomic CREATE OR REPLACE — two long-running features resolved on 11 Oct for 13.2.1. The MEMORY/HEAP engine now stores BLOB/TEXT/JSON/GEOMETRY, with hash keys on blob parts. That lets internal temp tables for GROUP BY, DISTINCT, UNION and CTEs over JSON/TEXT stay in memory instead of going to Aria on disk. MDEV-25292 makes CREATE OR REPLACE TABLE atomic: the old table survives a failed create or a crash. It was filed in 2021 and pulled from the 10.10 RC; its InnoDB review sub-task is still marked stalled. (MariaDB JIRA)
  • TidesDB: an LSM storage engine for stock MySQL 9/26 — a plugin engine with key-value separation, optimistic MVCC (conflicts surface at commit as error 1180) and per-table LSM shape options. With 4 KB values: 100,613 inserts/s with separation, against 32,330 inline and 32,562 for InnoDB, so the gain comes from separation, not from the LSM. Bytes written per operation were 17–47× lower; raw data is published. (Alex Gaetano Padula · tidesdb.com) [vendor blog — substantive] [unverified: self-reported; InnoDB on the default 128 MB buffer pool]

Migration experience

  • Migrating a 500 GB production MariaDB from RDS to EC2, with a rollback path — the trigger was an RDS incident: a broken replica made the master keep its binlogs on the single shared volume, which autoscaled to its 4 TB ceiling and stopped the master. (Pradip Shah · luroconnect.com, republished on mariadb.org Oct 5)
    • the move was a replication chain: mydumper with GTID → EC2 replica → chained EC2 replica → a fresh RDS replica of EC2, kept for weeks as the fallback
    • ProxySQL made the cutover a backend swap of under 10 minutes; the EC2 master has 768 GB of RAM, so all 500 GB sit in the buffer pool
    • gotchas: binlog retention only via rds_set_configuration, no SUPER, and grants missing from a data-only dump
    • cost went from $13,581 to $2,326 a month; no Multi-AZ equivalent, and the performance claim is qualitative

Research & cutting edge

  • Query performance tuning by exploring the optimizer's cost-model parameter space — treats cost units such as seq_page_cost and random_page_cost as per-query knobs, with a deterministic search guaranteed to find every plan reachable by varying them. On PG TPC-H the best plans are 32% faster than those found by random search or Bayesian optimisation; on Q11 the plan is 81% better than the default. The abstract's "orders of magnitude" is not what the body shows. (Wu, Wang, Narasayya, Chaudhuri) [paper]
  • Truly sub-3^n min-sum subset convolution and join ordering — reduces min-sum subset convolution to min-plus matrix product. With the recent subcubic min-plus result, that gives the first exact join-ordering DP under C_out below 3^n: O*(2.9987^n) expected, O*(2.9997^n) deterministic. A theory result, not a practical speed-up. (Stoian) [paper]
  • Mechanizing transactional anomalous patterns for weak isolation levels — a Rocq proof of the anomaly-pattern definitions behind the Plume isolation checker. It found two bugs in the published version: an underspecified history model, and a Read Atomicity definition that misses session-guarantee violations. Both fixes are merged into Plume. (Gu, Liu, Wei) [paper]
  • Should database systems use hardware memory-safety extensions in production? — Arm MTE vs CHERI on DB data structures and on Redis, LevelDB, SQLite, MySQL and DuckDB. MTE costs about 10% and ports easily; CHERI costs 20–60% and needs heavy porting. (Meignan-Masson, Fink, Misono, Stavrakakis, Bhatotia) [paper]
  • FDP: the data-placement promise of modern NVMe SSDs — with Flexible Data Placement, device write amplification stays near 1 instead of about 3, average latency falls 70% and tail latency about 77%. RocksDB needed a 13-line change. Separating MySQL's redo, page writeback and doublewrite streams is the obvious template for WAL, heap and temp files in Postgres. (Lan, …, González, Shah) [paper]
  • RaBitQ-SSD: split codes and pipelined I/O for SSD-resident vector search — at 90% recall, up to 1.74× the throughput of graph indexes with 3.8× fewer SSD page reads. Indexes are 7× smaller and 10× faster to build than DiskANN, and one machine with one SSD serves 10 billion vectors at over 3,000 QPS. (Xu, Gao, …, Douze, Long) [paper]
  • SOLO: certified-recall similarity search with scan-only sampled inverted lists — recall is certified from the index before any query runs, and matches served recall to four decimals at up to 10^9 objects. Deep-1B reaches recall 0.9925 with 96 MB resident, at 1.8× HNSW's throughput. Deletes are exact, which suits an MVCC engine better than HNSW. (Chávez) [paper]
  • Conjunctive queries with negation: beyond signed-acyclicity — a width measure for join queries with anti-joins (NOT EXISTS), with O(|I|^width) preprocessing and an O(|I|^{3/2}) algorithm for cycle queries with one negated edge. (Frisk, Koutris) [paper]
  • CIDR 2027 accepted papers — the list went up this week; only titles so far. For internals people: EXPLAIN Yourself! Finding Query Planner Stalls Across DBMSes (Yu, Marcus, Kraska), The Case for Single-Version Concurrency Control in the Cloud (Leis, Bernstein et al.), Reports of mmap()'s Death Are Greatly Exaggerated, Running Loops around Recursive SQL (Neumann et al.), and Rethinking Vector Search in Databases with Local Indexes (Boncz et al.). (CIDR 2027, Amsterdam, 24–27 Jan)

International (non-English sources)

  • Teaching the planner COALESCE selectivity — it started with a 1C month-close whose join keys used COALESCE(t.col,'\xff') = COALESCE(r.col,'\xff'). With no statistics to use, the planner fell back to defaults and chose a nested loop costed at 1,330 over a hash join at 21,766. Without the COALESCE, the hash join cost 765. The patch, now on -hackers, splits a COALESCE into its arguments, weights each by the null fractions of the ones before it, and sums the per-pair selectivities. If any argument lacks statistics, it keeps the old estimate; <> and hash-bucket estimates get the same treatment. Estimate vs actual rows: 66.6M → 7.77M against 10M actual for a nested-COALESCE join, and 500K → 55.64M against 55.60M for COALESCE(col,0) on both sides. (Egor Savelyev · Tantor Labs on Habr) [ru] (orig: Почему COALESCE ломает план PostgreSQL и как научить планировщик считать его селективность?)
  • A truncated pg_dump that every check called fine — PG 18.4, pg_dump | gzip from cron without pipefail, and a neighbouring test that restarted the server 29 s into the dump. The file was 95 MB instead of 1.12 GB, and gzip -t returned 0. A plain psql restore also returned 0, with all 4,687 tables, none of the 10,362 indexes and about 42% of the rows. Reproduced with SIGKILL at 0.3 s: exit code 0 without pipefail and 137 with it. psql -1 without ON_ERROR_STOP on a file cut mid-row produces an empty database, still with exit code 0. pg_restore -l accepts half a custom-format archive, but pg_restore -f /dev/null catches it. The cheap check is to look for "dump complete" at the end of the file. (zhogov1985 · Habr) [ru] (orig: Копия базы 1С оборвалась, а gzip -t и psql вернули 0)
  • Five PgBouncer transaction-mode traps behind a managed pooler — on a pooled Neon endpoint, statement_timeout=1000 sent at connect time was silently dropped (SHOW returned 0), and a scan of a 10 GB table ran for 43–48 s; the fix was SET LOCAL inside a transaction. (cryptosprof · Habr) [ru] (orig: Таймаут в секунду, запрос на 43 секунды: пять ловушек PgBouncer в режиме транзакций) The other traps:
    • a read-only setting passed in PGOPTIONS was rejected outright
    • temp tables vanished between autocommit statements
    • Prisma's migration advisory lock stuck to a pooled server connection, so later deploys failed with P1002
    • the replacement lease lock had its own bugs: one exit path never released it (the job queue backed up to 1,574 rows), and its expiry check mixed time zones
  • Does WAL-G's direct I/O keep backups out of the page cache? A replication — a scaled-down rerun of ClickHouse's direct-I/O test from last week's issue: i8ge.xlarge with 32 GiB RAM, PG 18.6 with shared_buffers=8GB, a 42.5 GiB database, 3 runs per configuration measured with fincore. A buffered backup used up about 9 GiB of free memory within 22–23 s; with WALG_DIRECT_IO, every table's and index's cached bytes were identical before and after. Direct I/O with the default 128 KiB reads was about 20% slower, and 1 MiB reads with 4-way parallelism matched buffered speed. The headline "evicted all 40 GiB of a table" did not reproduce: an untouched 3.1 GiB table stayed fully cached in every run, MGLRU on or off. (asahide · Qiita) [ja] (orig: WAL-G の Direct I/O でバックアップがページキャッシュに残るか試してみた)
  • 16,000 ORs spend 56 s in the planner, and work_mem can't help — PG 15.19 with defaults, an 800k-row table, and a chain of (warehouse_id=? AND product_id=?) OR … pairs. Planning took 0.20 s / 25 MB at 1,000 ORs, 3.2 s / 48 MB at 4,000 and 56 s / 140 MB at 16,000: memory grows linearly, time about quadratically, with one Bitmap Index Scan per clause. In the server log this shows up as bind time. Neither bind variables nor any work_mem setting changes it, while grouping by warehouse with = ANY(array) takes 3–4 ms. jOOQ 3.20.10 also silently inlines every bind value once a statement passes the protocol's 65,535-parameter limit. Compare CF #7393 (quadratic planning with IN-list BitmapOr). (福土 · Zenn) [ja] (orig: 巨大 SQL を実行したら DB 負荷が高まったエンジニアの備忘録)
  • pgsql-hackers watch, September 2026 — the monthly Japanese digest of -hackers by a Postgres contributor. September's issue covers the 19 reverts (SQL/PGQ's dependency tracking, FOR PORTION OF's lost updates under READ COMMITTED, batched FK checks, online checksums) and PG20 work: 8-byte TOAST value OIDs committed as a per-table option, protocol compression narrowed to server→client row data with ZSTD, and a generic UNDO log starting with orphaned-file cleanup. (Yugo Nagata · SRA OSS) [ja] (orig: pgsql-hackersウォッチ（2026年9月）)
  • A pushed-down parameter that prunes nothing: correlated subqueries vs partition pruning in OceanBase 3.x — a scalar subquery on C.REGION = X.REGION, under an outer X.REGION = 532, scanned all 18 partitions although EXPLAIN showed the parameter pushed down. The subquery stays a SUBPLAN FILTER, so X.REGION is a run-time parameter, while the partition list is fixed at plan time. Predicate deduction works only within one query block, so C.REGION = 532 is never derived. 4.x prunes to one partition by passing constants across query blocks, without unnesting. The same question applies to Postgres run-time pruning under a SubPlan. (士心 · OceanBase community) [zh] (orig: 分区裁剪"明明下推了"，为什么还是全分区扫？（OceanBase 3.x 版本）)
  • A dead NTP server, a 1-second test default, and a TiDB restore that never finishes — on v8.5.4, br restore point always failed after 15 minutes "waiting for schema reload". BR never set a schema lease, so it ran with the 1 s default meant for tests. If PD's timestamp oracle lags BR's clock by more than 0.5 s, the reload loop never exits; here chrony's upstream had been unreachable for about 44 days and root dispersion was about 12 s. The data had already been written; only cleanup failed. Fixed in pingcap/tidb PR #66092 (BR v8.5.8). (克里克里克 · tidb.net) [zh] (orig: br restore point报错waiting for schema info finishes reloading)

Call for papers

Upcoming events

  • PASS Data Community Summit West — Seattle, 9–11 November — mainly a SQL Server conference, now with a substantial Postgres track; workshops on 9 November, main conference 10–11. Internals picks:
    • SQL Server Versioning Explained (Bob Ward) — SQL Server's version store, its recovery, Optimized Locking and snapshot isolation, with measured costs; a reference point for any undo-based storage design in Postgres.
    • Inside Lakebase: Postgres, Separation of Storage and Compute, and LTAP (Savannah Longoria) — WAL quorum-committed to safekeepers, page servers on object storage, copy-on-write branches; a vendor session, but it walks the storage path end to end.
    • PostgreSQL Storage Internals: From MVCC to TOAST and Beyond (Adam Machanic) — visibility rules, hint bits, write amplification and TOAST, presented to SQL Server engineers.

New sources added this week

  • peter.eisentraut.org — a committer's own blog; infrequent, first-hand posts on project process and SQL features. (Peter Eisentraut)
  • kmoppel.github.io — scripted multi-cloud Postgres benchmarks with the runners published. (Kaarel Moppel)
  • prateek-arora.github.io — measurement-first planner and index-advisor experiments, with criteria fixed in advance. (Prateek Arora)
  • blog.renatocron.com — "Sorted Runs": an LSM and object-storage series that checks claims against the code and reports production numbers. (Renato Cron)
  • incident.io/blog — production Postgres engineering from a heavy queue user; reached via HN, not on Planet.
  • Zenn — PostgreSQL topic — Japanese developer platform with dated posts that are easy to window; source of this week's best Japanese item. [ja]

68 items · yield — mailing lists: 1,150 messages in window (758 hackers / 78 bugs / 0 performance / 48 general, plus 266 pgsql-committers) → 74 shortlisted → 14 published · blogs: ~96 posts in window → 95 shortlisted → 30 published · community: ~365 threads viewed → 26 shortlisted → 6 published · research: 52 cs.DB preprints announced in window → 22 shortlisted → 8 published · international: ru 39→9→3, zh 62→5→2, ja 125→7→3, fr 0→0→0, de 0→0→0 (polyglot search unavailable this run; native-language web search, feeds and browser used instead).