AI Ping-Pong with Polly (and Omnigent)

Sometimes the most entertaining way to test a new orchestration tool isn't to run a standard benchmark, but to lock two state-of-the-art AI models in a virtual room and make them debate historical facts. This week, I got to do exactly that using an interesting new tool called polly within the omniagent ecosystem.

The idea was simple: set up a relay where Claude and Gemini (specifically the Antigravity agent) pass a task back and forth, validating and revalidating each other's work.

However, getting there wasn't entirely smooth sailing. I see recent polly changes that seem to be working on Antigravity support but I presume that it is just heavily in beta and under active development - Simply because I couldn't get it to work cleanly on my local test setup out of the box. In a somewhat ironic twist of AI-assisted development, I ended up having Claude help me debug my environment and write the custom harness around Antigravity to get the toolchain working.

Once the harness was solid, the fun began. I decided to test their interaction on a simple prompt asking for a list of India's Prime Ministers (based on their first stint). I then pass that list back-n-forth between the two AI engines for cross-validation and updation. To avoid an endless loop, I limited the number of iterations to exactly two.

Here is the exact prompt and how it kicked off:

 ❯ ask antigravity to generate the list of prime ministers of india in the
 chronological order (of their first stint) and then pass the list to
 claude for validation and an updated list of the same. and pass that onto
 to antigravity for revalidation with the same request. Do this round trip
 only 2 times and display the resultant list.

   · thinking…
◆ I'll run this as a relay: antigravity generates → 
    Claude validates/updates → antigravity revalidates, repeated for 
    2 round trips. Starting with antigravity's initial list.

◆ Antigravity produced the initial list (15 entries). Round trip 1 —
   passing to Claude for validation + an updated list:

◆ Claude is validating now. Waiting for its result.

.
.
.

Watching the two models interact was fascinating. Antigravity would confidently output the initial sequence, Claude would meticulously review it (often catching nuance around interim Prime Ministers like Gulzarilal Nanda), and then Antigravity would process Claude's corrections. It functioned like a highly pedantic game of ping-pong.

While this specific test was just good fun, the real motivation behind integrating polly is much more practical. I've been working on a complex PostgreSQL B-tree index compaction patch to solve the index bloat problem that VACUUM leaves behind. Developing database internals is notoriously tricky, and I've been relying on Claude (and now Antigravity) to help write and re-write and review the code heavily.

Having two AI agents look at the developing patch from different angles has been invaluable. They continually find subtle bugs, naming inconsistencies, edge-case production gotchas, and deviations from C best practices. Instead of manually passing code snippets back and forth between different chat windows, the idea here is to see if polly (or a similar tool) can allow me to seamlessly automate this cross-validation part without my intervention.

Now that the Antigravity harness is patched up and working, I've proven that this dual-engine review process can work, and be an automated part of my Postgres hacking workflow. That said, because Google's API billing operates separately from the standard Google AI Pro account, this specific Claude-and-Gemini pairing was mostly a test run. For the near future, I may just end up cross-validating between Claude and Codex (or a similar alternative) to keep the pipeline running smoothly and cost-effectively.

For the curious, here is the Relay trace of that conversation, and the final result.

   Relay summary

    Hop               Agent                 Result
    ─────────────────────────────────────────────────────
    Generate          antigravity (Gemini)  15-entry list
    RT1 → validate    Claude                two changes
    RT1 → revalidate  antigravity           no changes
    RT2 → validate    Claude                no changes
    RT2 → revalidate  antigravity           no changes


   Resultant list — Prime Ministers of India (chronological by start of first term)


     1 Jawaharlal Nehru — 1947
     2 Gulzarilal Nanda (Acting) — 1964
     3 Lal Bahadur Shastri — 1964
     4 Indira Gandhi — 1966
     5 Morarji Desai — 1977
     6 Charan Singh — 1979
     7 Rajiv Gandhi — 1984
     8 Vishwanath Pratap Singh — 1989
     9 Chandra Shekhar — 1990
    10 P. V. Narasimha Rao — 1991
    11 Atal Bihari Vajpayee — 1996
    12 H. D. Deve Gowda — 1996
    13 Inder Kumar Gujral — 1997
    14 Manmohan Singh — 2004
    15 Narendra Modi — 2014

   One note worth flagging (the cross-checks agreed, but for completeness): this
   list counts Gulzarilal Nanda once at his first acting stint (1964). He also
   served a second acting term in 1966 after Shastri's death — both models
   consistently treated 'first stint' as the ordering key, so he
   appears a single time at 1964, which matches your request.
 

Keep having fun !!

Index Bloat - The Problem VACUUM Leaves Behind

This is an opening post (of a series) about my attempt at automatic B-tree index compaction integrated into autovacuum.

---

Index bloat is one of the most common performance problems in production PostgreSQL deployments — and one of the most misunderstood. People assume that a fully vacuumed table has a healthy index - which isn't true. There is a significant gap between an index that has been cleaned and one that has been compacted, and although Postgres is closing that gap (v13 enabled index deduplication & v14 brought bottom-up index deletion), there is a lot more to do here.

This post explains what index bloat actually is, why standard VACUUM cannot fix it, and why the existing manual tools — while useful — are not a substitute for continuous automated maintenance.

---

What Index Bloat Is (and Isn't)

After a large bulk delete — say, removing 80% of the rows in a table — autovacuum will dutifully remove all 80% of the dead index entries. The index will look clean. But the leaf pages are still there, mostly empty. A table that went from 1 million to 200,000 rows may still have an index file that occupies roughly the same space as before (a leaf page that held 100 index entries, and now holds 20, still occupies 8 kB on disk). Only the entries are gone; the pages remain.

The practical consequence is stark. A fully vacuumed B-tree index can have hundreds of leaf pages each filled to just 20% capacity. A range scan covering 10% of the keyspace now could end up reading five times as many pages as a compact index would require. Every buffer-cache miss costs the same whether the page carries 10 tuples or 100.

---

Why VACUUM Can't Merge Pages

PostgreSQL's autovacuum already visits every B-tree index after vacuuming a table. It calls btbulkdelete to remove dead index entries, then btvacuumcleanup to perform index-level tidying. What it cannot do is merge two partially-filled leaf pages into one and free the other.

Merging requires moving index entries from one page (the source) to another (the destination), then marking the source as deleted and unlinking it from the sibling chain. The B-tree code already knows how to do this: _bt_pagedel handles the case where a leaf page becomes completely empty after all its entries are removed. But VACUUM never merges pages that are merely sparse. A page at 20% fill is considered live and is left alone indefinitely.

Why can't VACUUM just merge sparse pages the same way it handles empty ones?

The obstacle is locking. B-tree index scans descend to a leaf page, pin it in memory, and then walk right by following btpo_next links to adjacent sibling pages. If a merge moves entries from a right page to a left page while a concurrent scan has already pinned the right page but not yet finished reading from it, the scan will miss the moved entries. That is a correctness bug.

Page-level locks — shared for reading, exclusive for writing — are not enough to prevent this. A concurrent scan can pin the right page before a merge begins, but after the check that would have prevented the merge.

So the option is either (a) Carefully coordinating page-level locks across all concurrent access-paths, OR, (b) Taking a relation-level lock, held across the entire merge operation, which can then guarantee that no scan is mid-flight on the page being moved. This series of blog-posts tries to revisit this locking dilemma (that makes index compaction difficult today) and see what else is possible to do to help the average application workload.

---

The Existing Solutions and Their Limitations

The standard tool for recovering bloated index space is REINDEX — or its online variant, REINDEX CONCURRENTLY. A full REINDEX rebuilds the index from scratch and produces a perfectly packed result, but it holds an AccessExclusiveLock on the relation for the entire duration of the build. All reads and writes are blocked. On a large production table that can mean minutes of downtime.

REINDEX CONCURRENTLY avoids that lock but still requires building a complete shadow copy of the index: a second, full-sized index is written while the original stays online. This is expensive in I/O and CPU, can run for hours on large indexes, may leave an invalid index (on cancellation) and requires 2x disk-space.

VACUUM FULL and extensions like pg_repack have the same fundamental character. pg_repack is more surgical than VACUUM FULL — it rebuilds the table and its indexes while the original stays accessible, and swaps them in atomically — but it still builds a complete copy before anything is reclaimed.

PostgreSQL 19 (barring last minute rollbacks) introduces a built-in REPACK command that performs online table and index repacking without requiring an external extension. This is genuine progress: it eliminates the pg_repack dependency, integrates with standard tooling, and is maintained by the core team. But the fundamental character of the operation is unchanged. REPACK rebuilds from scratch, and it is a scheduled one-shot command.

The common thread across all of these tools: a DBA has to notice the bloat — typically through a monitoring query or a performance regression — schedule a maintenance window, and issue the command. In practice, indexes frequently stay bloated for months between maintenance events. A table that sees steady churn (a history log, an events queue, a session store) will re-accumulate bloat between scheduled maintenance runs, sometimes faster than it can be addressed manually.

What production systems actually need is continuous, incremental compaction that runs in the background — the way autovacuum continuously reclaims heap space — without ever taking a lock that blocks application traffic.

---

What This Series Covers

The remaining posts in this series measure what continuous automated compaction delivers across 19 benchmark scenarios: static bulk deletes, steady-state churn, HOT and non-HOT update workloads, UUID-keyed indexes, concurrent readers under load, sawtooth growth patterns, TPS degradation under accumulating bloat, and streaming replication. The scenarios start simple and progressively test harder conditions.

The final post in this series (coming soon) covers the algorithm itself: the multiple failed design attempts, the core insight, the locking design that keeps application traffic unblocked, the implementation details, and operational considerations.

---

This is personal research, done on my own time and not connected to or endorsed by my employer. This is a relatively complex topic and I am sharing my findings as I explore this space.

Why Postgres Doesn't Have synchronous_commit=remote_receive?

In distributed database environments, balancing durability and performance is a constant tug-of-war. PostgreSQL’s synchronous_commit parameter sits at the heart of this, giving administrators a dial to choose exactly when a COMMIT returns success to the client.

The idea of remote_receive was born from a simple question: does skipping the standby's disk write yield a measurable, real-world performance benefit? By waiting only for WAL bytes to reach the standby's memory, could we get a meaningful boost over remote_write? I set out to implement and benchmark this feature to find out.

What followed was a journey through network latency, OS page caches, CPU scheduler thrashing, and benchmarking noise. Here is the breakdown of the implementation, the tests, the initial anomalies, and the final results.

---

1. The Feature: What is remote_receive?

Before this branch, PostgreSQL offered four primary synchronous commit modes:

• off: Fully asynchronous. (Fastest, least safe)
• local: Waits for local disk flush on the primary.
• remote_write: Waits for the standby to write the WAL to its OS buffer cache (pwrite).
• remote_apply: Waits for the standby to fully replay the WAL. (Slowest, most safe)

remote_receive sits directly between local and remote_write. In this mode, the primary guarantees that the WAL bytes have physically arrived at the standby's walreceiver process buffer. It does not wait for the standby to call pwrite().

The Hypothesis: By completely bypassing the standby's disk I/O, remote_receive should deliver lower latency and higher throughput than remote_write, especially on replica hardware with slow disks.

Implementation Details

To build this, I had to modify both the standby and the primary:

1. Standby Status Update: I modified the 34-byte wire message that the standby sends to the primary, adding a new 8-byte receivePtr (creating a 42-byte message, backward compatible).
2. Early Replies: I modified walreceiver.c to send a reply message immediately upon receiving a WAL chunk in memory, before the XLogWalRcvWrite() call executes the pg_pwrite.
3. Primary Wait Logic: I updated syncrep.c and walsender.c to track the SYNC_REP_WAIT_RECEIVE wait queue, releasing waiting backends as soon as the standby's receivePtr advanced.

---

2. Scenario 1: The SSD Baseline (Fast Primary, Fast Replica)

To validate the code, I first set up a baseline test using two fast machines on a gigabit LAN.

The Servers:

• Primary: lenovo (Intel Core i7-12700 12C/20T, 48GB RAM, NVMe SSD)
• Replica: camry (Intel Core i7-4770 4C/8T, 24GB RAM, SATA SSD)

I ran pgbench (Scale 10, 4 clients) for 30 seconds across the different modes.

The Result:

• remote_write: 3,944 TPS (Median)
• remote_receive: 3,946 TPS (Median)

The performance was virtually identical (a 0.06% difference). Why didn't remote_receive pull ahead?

The Reality of pwrite(): On modern operating systems with free RAM, a standard pwrite() to a buffered file does not write to physical disk immediately. It copies the data into the OS page cache (essentially a memory copy taking mere microseconds), leaving the kernel to flush dirty pages asynchronously.

With network round-trip time (RTT) on a gigabit LAN between 0.2ms and 1.0ms, the 5 microseconds saved by bypassing pwrite() is completely dwarfed by network latency. This makes remote_write and remote_receive perform nearly identically in typical conditions.

This fundamental reality explains why core PostgreSQL developers historically questioned the return on investment (RoI) of a memory-only receive mode. Because a standard pwrite() to the OS page cache is already a RAM-speed operation (taking mere microseconds), remote_write is practically as fast as any network-receive-only mode under normal conditions, but with a major durability advantage: it survives a PostgreSQL process crash on the standby (as long as the OS remains running). Bypassing it would reduce durability without providing any real-world performance benefit in typical conditions. In pgsql-hackers discussions—such as the thread on Measuring Replay Lag where distinct write_lag and flush_lag tracking was introduced—it is clear that the network round-trip time dominates the replication pipeline. To find a measurable benefit for a receive-only mode, I needed a replica where disk I/O was a severe enough bottleneck to cause page cache pressure and slow down the pwrite() call itself.

---

3. Scenario 2: The HDD Challenge (Asymmetric Hardware)

For the next test, I replaced the fast camry replica with a much weaker machine.

The Servers:

• Primary: lenovo (i7-12700, SSD)
• Replica: mac (Intel Core i5-2520M 2C/4T, 16GB RAM, 5400RPM HDD)

With a mechanical hard drive and a slow dual-core processor, I expected remote_receive to outpace remote_write. I ran three 30-second runs per mode, but the initial results were unexpected:

The First (Anomalous) Result:

• remote_write: 203.6 TPS (Median)
• remote_receive: 179.0 TPS (Median)

remote_write was ~20 TPS faster than remote_receive. Tracing the walreceiver and walsender loops ruled out code bugs; instead, the bottleneck came down to four factors:

1. The OS Cache Illusion: Even on a slow HDD, pwrite() still writes to RAM. The mechanical disk's extreme latency only hits during fsync or when the page cache fills up, meaning the receive-only advantage remained small.
2. CPU/Scheduler Thrashing: By sending an "early reply" before writing to disk, remote_receive generates twice as many TCP reply packets (one for receive, one later for flush). On the replica's older dual-core CPU, processing this packet flood alongside WAL replay caused high context-switching overhead.
3. Flow Control: remote_write acted as a natural flow-control mechanism. By waiting to write before replying, it throttled the primary slightly, keeping the replica's CPU out of a thrashing state.
4. Statistical Noise: The remote_write runs ranged from 177 to 211 TPS (a 19% spread). A 3-run, 30-second test was simply too noisy to yield a reliable median.

Aside: The Raspberry Pi 4 Attempt Before settling on the Mac Mini HDD, I attempted to use a Raspberry Pi 4 (pi4 — Cortex-A72 4C/4T, 4GB RAM, SD card / USB storage) as the slow replica. However, the Pi 4 was a poor fit for this benchmark. The main issue wasn't simply that the CPU maxed out, but that the low-power ARM CPU could not keep pace with the primary's faster rate of WAL generation. This lag cascaded into secondary issues—such as rapidly mounting replication lag, TCP buffer queues, and process starvation—which completely dominated the environment and masked any storage-level performance differences.

---

4. The Final Test: Eliminating the Noise

In the initial runs, the slow mechanical disk combined with standard kernel buffering created a massive source of noise: the sustained background I/O writes from a just-finished test were still flushing to disk when the next test began. Although an LSN cross-check was already in place (waiting for the replica's replay_lsn to catch up to the primary's current WAL LSN), this only verified database-level catch-up, not physical disk queue clearance. The residual write queue in the OS cache severely penalized the subsequent run, creating artificial variance. To isolate the actual replication performance and eliminate this noise, I overhauled the benchmarking methodology:

1. Interleaved Runs: I interleaved executions (Write, Receive, Write, Receive...) to average out temporal background OS tasks and thermal states.
2. Longer Runs and More Iterations: I ran 10 iterations per mode at 60 seconds per run (20 runs total).
3. Aggressive Cache Flushing: I ran sync on both the primary and replica between runs, followed by a 30-second sleep, to flush the OS page cache to physical disk platters and guarantee clean disk queues.

The Final Results:

Mode	Median TPS	Mean TPS	Median Latency
remote_write	201.58	200.74	20.663 ms
remote_receive	211.44	206.85	19.434 ms

The Results

With the noise eliminated and disk queues flushed, the true behavior emerged: remote_receive outperformed remote_write by ~10 TPS (~4.9% gain) at the median and ~6 TPS (~3.0% gain) at the mean. The small gap is expected: bypassing a RAM-buffered pwrite() on the replica yields microsecond-level gains, while network round-trip time remains the dominant factor.

Conclusion

The remote_receive implementation successfully introduces a granular durability option that guarantees WAL has crossed the network into the standby's memory before committing.

This exercise highlighted a key rule of database benchmarking: the OS page cache masks physical disk latency until it fills up. To accurately benchmark high-variance storage, one must use interleaved runs, higher iteration counts, and rigorous OS-level cache flushes between tests. Otherwise, you are measuring cache behavior and statistical noise rather than raw database throughput.

---

A Note on the Development Process & Resources: While I dabble in C and C++ from time to time, development at this level within PostgreSQL internals would not have been possible without AI assistance. Additionally, all resources for this project—including the Claude subscription, development machines, laptop, and time—were entirely personal and completely independent of my employer.

Taming the Token Burn

Cloud AI is incredibly convenient, but the costs scale rapidly once you start leaning on long-context workflows. I've spent the last year relying on Gemini Pro for everything from Postgres automation to complex scripting, but the honeymoon phase is officially over (for me). As my projects grew in scope, so did the token consumption, eventually hitting a brick wall that forced a rethink of my entire stack.

For most, this is a niche problem. For anyone building high-context agentic workflows, it’s the only problem that matters.

Last year, the ride was smooth. My "AI usage" was minimal and Gemini Pro handled my daily tasks with ease. However, over the last three months, I've started pushing it with "decent-sized" projects—tasks that require keeping dozens of context balls in the air simultaneously. The result? It guzzles through tokens like a V8 engine in a traffic jam.

The breaking point came recently when, during a deep-dive development session, I fed the model a substantial task involving a complex codebase. Within two hours, I burned through 100% of my monthly allocation!!

Three (3) months in a row!

Yeah, that's not going to fly.

Finding the Right Tool for the AI Job

It’s time for a pivot, but not necessarily a total exit from the cloud. I’ve recently taken a Claude subscription as well, and it has been proving to be remarkably good at tasks where Gemini used to stutter. It’s basically been a lesson in finding "the right tool for the job" rather than looking for a single silver bullet.

That said, reducing my reliance on expensive cloud tokens is a priority. The recent NVIDIA RTX 5060 Ti purchase was a steep investment, but nonetheless a strategic move towards local autonomy. The goal is to migrate my heavy-lift agentic AI workflows to my local server farm, reserving premium cloud services for the few tasks where they are truly indispensable—specifically, massive context shifts that can't be easily decomposed. Some key examples being planning complex projects, or tasks that require reasoning over large codebases.

I have a few machines at home (hosting IronClaw, Postgres fuzzing, Jellyfin, Git servers, Obsidian repositories, and a Grafana dashboard to keep an eye on it all), and the goal is to keep costs down while making these tools smarter. The only way I see that happening is if I reduce Cloud API dependence and switch existing tooling to local LLMs.

Another key driver is that I expect some of my newer projects, still in stealth mode, to burn through tokens like nobody's business; they wouldn't see the light of day if I didn't change the status quo.

Stay tuned.

The "Skip Scan" You Already Had Before v18

PostgreSQL 18 introduces native "Skip Scan" for multicolumn B-tree indexes, a major optimizer enhancement. However, a common misconception is that pre-v18 versions purely resort to sequential scans when the leading column isn't filtered. In reality, the cost-based planner has long been capable of leveraging such indexes when efficient.

How it works under the hood:

In pre-v18 versions, if an index is significantly smaller than the table (the heap), scanning the entire index to find matching rows—even without utilizing the tree structure to "skip"—can be cheaper than a sequential table scan. This is sometimes also referred as a Full-Index Scan. While this lacks v18’s ability to hop between distinct leading keys, it effectively still speeds up queries on non-leading columns for many common workloads.

Notably, we would see why this v18 improvement is not a game-changer for all workloads, and why you shouldn't assume speed-up for all kinds of datasets.

Benchmarks: v17 vs v18

To understand when the new v18 Skip Scan helps, we tested two distinct scenarios.

Scenario A: Low Cardinality (The "Success" Case)

The Setup: We created an index on (bid, abalance) and ran the following query:

SELECT COUNT(*) FROM pgbench_accounts WHERE abalance = -2187;

Note: We did not run a read-write workload, so abalance is 0 for all rows. The query returns 0 rows.

Data Statistics:

• Table Rows: 1,000,000
• Leading Column (bid): ~10 distinct values (Low Cardinality).
• Filter Column (abalance): 1 distinct value (Uniform).

Results:

Version	Strategy	TPS	Avg Latency	Buffers Hit
v17	Index Only Scan (Full)	~2,511	0.40 ms	845
v18	Index Only Scan (Skip Scan)	~14,382	0.07 ms	39

Why the massive 5.7x gain?

Pre-v18 Postgres sees that bid is not constrained. It decides its only option is to read the entire index (all leaf pages) to find rows where abalance = -2187. It scans 1,000,000 items (845 pages).

Postgres v18 uses Skip Scan. It knows bid comes first. instead of scanning sequentially, it:

1. Seeks to the first bid (e.g., 1). Checks for abalance = -2187.
2. "Skips" to the next unique bid (e.g., 2). Checks for abalance = -2187.
3. Repeats for all 10 branches.

It effectively turns one giant scan into 10 small seeks. The EXPLAIN output confirms this with Index Searches: 11 (10 branches + 1 stop condition) and only 39 buffer hits.

v18 Skip Scan Plan (Scenario A):

 Index Only Scan using pgbench_accounts_bid_abalance_idx on public.pgbench_accounts ...
   Index Cond: (pgbench_accounts.abalance = '-2187'::integer)
   Heap Fetches: 0
   Index Searches: 11
   Buffers: shared hit=39

Scenario B: High Cardinality (The "Failure" Case)

The Setup: We used the same query, but against an index on (aid, abalance).

Data Statistics:

• Leading Column (aid): 1,000,000 distinct values (100% Unique / High Cardinality).

Results:

Version	Strategy	TPS	Avg Latency	Buffers Hit
v17	Index Only Scan (Full)	~55.8	17.92 ms	2735
v18	Index Only Scan (Full)	~51.2	19.52 ms	2741

Why no improvement? If v18 attempted to "Skip Scan" here, it would have to skip 1,000,000 times (once for every unique aid). Performing 1 million seeks is significantly heavier than simply reading the 1 million index entries linearly (which benefits from sequential I/O and page pre-fetching). The planner correctly estimated this cost and fell back to the standard "Index Only Scan" used in v17.

v18 Plan (Identical to v17):

 Index Only Scan using pgbench_accounts_aid_abalance_idx on public.pgbench_accounts ...
   Index Cond: (pgbench_accounts.abalance = '-2187'::integer)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=2741

How to Identify a Skip Scan

You might notice that the node type in the EXPLAIN output remains Index Scan or Index Only Scan in both cases. PostgreSQL 18 does not introduce a special "Skip Scan" node.

Instead, you must look at the Index Searches line (visible with EXPLAIN (ANALYZE)):

• v17 (and older): The Index Searches row does not appear.
• v18 (Standard Scan): Index Searches: 1. The scan started at one point and read sequentially (as seen in Scenario B).
• v18 (Skip Scan): Index Searches: > 1. The engine performed multiple seeks (as seen in Scenario A which had 11).

In our Scenario A (Success), Index Searches: 11 tells us it performed ~11 hops, confirming the feature was used.

How to reproduce:

Note: We use PostgreSQL 13 in this section to meaningfully demonstrate that even older versions (long before v18) could efficiently utilize multicolumn indexes for these types of queries, to clarify that older Postgres versions were capable of avoiding a Full Table Scan (and instead fallback to Full Index Scan) in such scenarios.

• PostgreSQL 13 (a running cluster)
• A pgbench-initialized database (run pgbench -i to create pgbench_accounts, pgbench_branches, pgbench_tellers, and pgbench_history)
• Reasonable data loaded (default pgbench -i -s 10 or similar)

Steps to reproduce the example:

Follow these steps in your terminal to set up the test environment. This assumes you have PostgreSQL 13 (or a similar version) installed and its command-line tools are in your PATH.

Step 1: Create and Initialize the Database

createdb testdb                # Create a new database
pgbench -i -s 10 testdb         # Initialize it with 1 million rows

Step 2: Run a Quick Read-Write Test

To simulate some database activity, run a standard 10-second read-write benchmark.

pgbench -T 10 testdb

Step 3: Connect and Run the Test Query

Now, connect to the database with psql to run the SQL commands that demonstrate the index scan behavior.

SQL steps (run in psql):

psql testdb
-- create a multicolumn index (first column aid, second column abalance)
testdb=# CREATE INDEX CONCURRENTLY IF NOT EXISTS pgbench_accounts_aid_abalance_idx
  ON pgbench_accounts(aid, abalance);

testdb=# select abalance, count(*) from pgbench_accounts group by abalance order by count(*) desc limit 5;
 abalance | count
----------+--------
        0 | 995151
    -2187 |      4
    -4621 |      4
    -4030 |      4
    -2953 |      4
(5 rows)

-- run an explain on a selection that filters only on the second column (abalance)
testdb=# EXPLAIN (ANALYSE, VERBOSE, COSTS)
SELECT COUNT(*) FROM pgbench_accounts WHERE abalance = -2187;

Aggregate  (cost=18480.77..18480.78 rows=1 width=8) (actual time=18.672..18.674 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using pgbench_accounts_aid_abalance_idx on public.pgbench_accounts  (cost=0.42..18480.69 rows=33 width=0) (actual time=8.583..18.659 rows=4 loops=1)
         Output: aid, abalance
         Index Cond: (pgbench_accounts.abalance = '-2187'::integer)
         Heap Fetches: 0
 Planning Time: 0.328 ms
 Execution Time: 18.732 ms
(8 rows)

Notes on the output above:

• The important line is Index Only Scan using pgbench_accounts_aid_abalance_idx along with Index Cond: (pgbench_accounts.abalance = '-2187'::integer) — this demonstrates the planner chose an index scan that probes the multicolumn btree for entries where the second column matches the predicate.

Why this is not the v18 "skip-scan" feature in full:

• The v18 skip-scan adds optimizer logic to efficiently iterate distinct values of leading columns and probe the remainder of the index; it's a targeted algorithmic addition. What we show above is the planner choosing an index scan over a multicolumn index and applying the Index Cond to the second column. That can be effective for many queries and data layouts, but it lacks the specialized skip-scan internals that v18 adds for certain other cases.

Production Tips

• If you need queries on a non-leading column to be fast on older Postgres versions, create the right index and keep statistics current (ANALYZE). The planner may prefer an index scan over a seq scan when selectivity and costs align.
• Consider partial or expression indexes when appropriate; they let you make an index that directly serves the filter you need.
• When portability across versions is important, test on the earliest Postgres version you need to support; planner behavior can vary by release, statistics, and data distribution.

Conclusion

Postgres v18's documented skip-scan addition for B-tree indexes is a welcome and useful optimizer enhancement, specifically for low-cardinality leading columns. However, for high-cardinality leading columns (like our first example), the standard Full Index Scan remains optimal, and pre-v18 versions handle them just fine.

References

• PostgreSQL 18 release notes (skip-scan, indexes): https://www.postgresql.org/docs/18/release-18.html
• nbtree skip-scan optimization: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=92fe23d93
• Further optimize nbtree search scan key comparisons: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8a510275d

Turbocharging LISTEN/NOTIFY with 40x Boost

Unless you've built a massive real-time notification system with thousands of distinct channels, it is easy to miss the quadratic performance bottleneck that Postgres used to have in its notification queue. A recent commit fixes that with a spectacular throughput improvement.

The commit in question is 282b1cde9d, which landed recently and targets a future release (likely Postgres 19, though as with all master branch commits, there's a standard caveat that it could be rolled back before release).

The Problem

Previously, when a backend sent a notification (via NOTIFY), Postgres had to determine which other backends were listening to that channel. The implementation involved essentially a linear search or walk through a list for each notification, which became costly when the number of unique channels grew large.

If you had a scenario with thousands of unique channels (e.g., a "table change" notification system where each entity has its own channel), the cost of dispatching notifications would scale quadratically. For a transaction sending N notifications to N different channels, the effort was roughly O(N^2).

The Fix

The optimization introduces a shared hash table to track listeners. Instead of iterating through a list to find interested backends, the notifying process can now look up the channel in the hash table to instantly find who (if anyone) is listening. This implementation uses Partitioned Hash Locking, allowing concurrent LISTEN/UNLISTEN commands without global lock contention.

Additionally, the patch includes an optimization to "direct advance" the queue pointer for listeners that are not interested in the current batch of messages. This is coupled with a Wake-Only-Tail strategy that signals only the backend at the tail of the queue, avoiding "thundering herd" wake-ups and significantly reducing CPU context switches.

Finally, the patch helps observability by adding specific Wait Events, making it easier to spot contention in pg_stat_activity.

Benchmarking Methodology

Tests were conducted on an Intel Core i7-4770 server with 16GB RAM.

Two Postgres instances were set up:

1. Before: Commit 23b25586d (the parent of the fix), running on port 5432.
2. After: Commit 282b1cde9 (the fix), running on port 5433.

The "Sender's Commit Time" (in milliseconds) was measured across three different scenarios to test scalability limits.

Results

Test 1: Channel Scalability

Metric: Time to send 1 NOTIFY to N unique channels.

The "Before" code exhibits clear O(N^2) or similar quadratic degradation as the number of channels increases. The "After" code remains effectively flat.

Channels	Before (ms)	After (ms)	Speedup
1	0.095	0.094	1x
10	0.059	0.054	1.1x
100	0.080	0.089	0.9x
1,000	2.032	0.485	4.2x
2,000	6.195	0.791	7.8x
3,000	13.314	1.019	13x
4,000	24.256	1.656	15x
5,000	35.711	1.693	21x
6,000	54.520	2.456	22x
7,000	70.088	3.274	21x
8,000	93.798	3.450	27x
9,000	128.252	4.483	29x
10,000	140.061	3.598	39x

Test 2: Listener Scalability (at 1,000 channels)

Metric: Impact of multiple backends listening to the same channels.

Listeners	Before (ms)	After (ms)	Speedup
1	2.468	0.452	5.5x
10	5.085	0.536	9.5x
50	2.728	0.522	5.2x
100	4.342	0.620	7.0x
200	5.485	0.783	7.0x

Test 3: Idle User Overhead (at 1,000 channels)

Metric: Impact of connected but non-listening backends.

Idle Users	Before (ms)	After (ms)	Speedup
0	3.065	0.425	7.2x
50	3.123	0.474	6.6x
100	4.219	0.353	12x
200	3.262	0.437	7.5x
300	2.102	0.381	5.5x
400	2.500	0.384	6.5x
500	2.091	0.362	5.8x

Conclusion

This optimization eliminates a significant scalability cliff in Postgres's messaging system. While "don't create 100,000 channels" is still good advice, it's great to see Postgres becoming more robust against these high-load scenarios.

For those interested in the technical nitty-gritty, reading the discussion thread is highly recommended to see how the solution evolved from a simple list switch to a shared hash map implementation over thirty-five (35!) iterations before finally landing in the codebase.

Credits: The commit was authored by Joel Jacobson and reviewed extensively by Tom Lane (who also committed it) as well as Chao Li.

3x Faster TID Range Scans - Postgres 19

If you've ever had to scrub significantly large tables—whether updating older records or deleting expired ones—you know the pain of trying to do it efficiently without locking the entire table. One common trick is to iterate over the ctid (the physical location of the tuple) to process the table in chunks. Until now, this was strictly a single-threaded affair. But with the latest commit to Postgres 19, TID Range Scans can now be parallelized, allowing you to harness multiple cores to rip through these maintenance tasks significantly faster.

In my tests, upto 3x Faster!!

The Problem with Linear Scanning

When you perform a batched operation using ctid (e.g., WHERE ctid >= '(0,0)' AND ctid < '(1000,0)'), Postgres uses a Tid Range Scan. Previously, this scan type didn't support parallelism. If you had a 1 TB table and wanted to process ranges of it, a single CPU core would have to do all the heavy lifting for that range, fetching blocks one by one. Even if you had 64 cores gathering dust, they couldn't help with that specific scan node.

The Solution: Parallel TID Range Scan

Commit 0ca3b169, developed by Cary Huang with an extensive review (and committed) by David Rowley, introduces infrastructure to allow Tid Range Scan to participate in parallel query plans. The logic effectively splits the block range among the available parallel workers. Instead of one process sweeping from block 0 to N, workers can grab chunks of blocks concurrently.

NOTE

This feature is currently in the master branch (Postgres 19devel). As with all unreleased features, it could theoretically be rolled back or modified before the final release.

Benchmark: Before vs. After

To see this in action, I spun up two Postgres instances: one built just before the commit (Postgres 18) and one after (Postgres 19). I created a table bench_tid_range with 10 million rows and ran a count(*) query over the first 50% of the table using a ctid range condition.

Test Setup:

• Table Size: 10 Million rows
• Query: SELECT count(*) FROM bench_tid_range WHERE ctid >= '(0,0)' AND ctid < '(41667,0)'

Environment	Workers	Execution Time (Median)	Speedup
Before (Pg 18)	0	448 ms	1.00x
After (Pg 19)	0	435 ms	1.03x
After (Pg 19)	1	238 ms	1.88x
After (Pg 19)	2	174 ms	2.58x
After (Pg 19)	3	151 ms	2.97x
After (Pg 19)	4	150 ms	2.98x
After (Pg 19)	5	147 ms	3.05x
After (Pg 19)	6	143 ms	3.14x
After (Pg 19)	7	147 ms	3.04x
After (Pg 19)	8	147 ms	3.04x

(Each execution time is a Median of 9 runs)

The chart above illustrates the performance scaling. Note that Postgres 19 with 0 workers (i.e. serial) is almost identical in speed to Postgres 18; the efficiency gains are visible only when parallelism is enabled.

We see a massive drop in execution time just by enabling 1 worker (which effectively gives us 2 processes scanning: the leader + 1 worker). The gains continue robustly up to 3 workers, where execution time settles around 150ms. Beyond that, we hit diminishing returns as the overhead of managing parallel workers and aggregating results begins to dominate the raw scanning speed. The "sweet spot" for this specific workload appears to be around 2-3 workers.

Under the Hood: Deep Dive

Let's look at the actual EXPLAIN (ANALYZE, BUFFERS) output to see *why* it's faster.

Before (Postgres 18)

The legacy behavior forces a serial scan. The single process reads all 41,667 pages.

 Aggregate  (cost=104171.87..104171.88 rows=1 width=8) (actual time=427.723..427.724 rows=1.00 loops=1)
   Buffers: shared read=41667
   ->  Tid Range Scan on bench_tid_range  (cost=0.01..91671.70 rows=5000069 width=0) (actual time=0.130..243.502 rows=5000040.00 loops=1)
         TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(41667,0)'::tid))
         Buffers: shared read=41667

After (Postgres 19)

The new Parallel Tid Range Scan node appears. Notice the Gather node launching 2 workers.

 Finalize Aggregate  (cost=68713.25..68713.26 rows=1 width=8) (actual time=166.873..169.987 rows=1.00 loops=1)
   Buffers: shared read=41667
   ->  Gather  (cost=68713.03..68713.24 rows=2 width=8) (actual time=166.238..169.978 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared read=41667
         ->  Partial Aggregate  (cost=67713.03..67713.04 rows=1 width=8) (actual time=164.518..164.519 rows=1.00 loops=3)
               Buffers: shared read=41667
               ->  Parallel Tid Range Scan on bench_tid_range  (cost=0.01..62504.63 rows=2083362 width=0)
                     (actual time=0.095..97.492 rows=1666680.00 loops=3)
                     TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(41667,0)'::tid))
                     Buffers: shared read=41667

Key Improvements:

1. Distributed Cost: The cost estimate dropped from 104k to 68k, indicating the planner correctly anticipates the benefit of parallelism.

2. Shared Load: While the total buffers read (41667) is the same, the work is shared across 3 processes (1 leader + 2 workers), reducing the wall-clock time for the scan itself from ~243ms to ~97ms per worker.

Counter-Intuitive Alternative: Forced Parallel Seq Scan?

You might ask: "Why didn't Postgres v18 just choose a Parallel Sequential Scan? Wouldn't scanning the whole table with 4 workers be faster than scanning half the table with 1?"

I tested exactly that on the v18 instance by forcing enable_tidscan = off with 4 workers.

• Execution Time: ~230 ms.

 Finalize Aggregate  (cost=124959.76..124959.77 rows=1 width=8) (actual time=227.917..229.476 rows=1.00 loops=1)
   Buffers: shared hit=15791 read=67543
   ->  Gather  (cost=124959.34..124959.75 rows=4 width=8) (actual time=227.660..229.469 rows=5.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         Buffers: shared hit=15791 read=67543
         ->  Partial Aggregate  (cost=123959.34..123959.35 rows=1 width=8) (actual time=224.154..224.155 rows=1.00 loops=5)
               Buffers: shared hit=15791 read=67543
               ->  Parallel Seq Scan on bench_tid_range  (cost=0.00..120834.30 rows=1250017 width=0) 
                     (actual time=0.150..169.772 rows=1000008.00 loops=5)
                     Filter: ((ctid >= '(0,0)'::tid) AND (ctid < '(41667,0)'::tid))
                     Rows Removed by Filter: 999992
                     Buffers: shared hit=15791 read=67543
 Planning:
   Buffers: shared hit=25
 Planning Time: 0.217 ms
 Execution Time: 229.584 ms

It was faster than the serial TID scan (~448 ms)! However, it had to visit all ~83k pages (15,791 hit + 67,543 read) instead of just the ~41k pages we cared about.

The new Parallel TID Range Scan (~150 ms) is still 35% faster than the brute-force/forced Parallel Seq Scan *and* it generates half the I/O load. It is the best of both worlds: fast execution time (Parallelism) and efficient resource usage (Index-like scoping).

Use Case: The Hotel Analogy

To understand why this matters, let's step away from databases and imagine a massive hotel with 1 million rooms. You need to verify all guests whose names start with a letter before 'S'.

The Logical Approach (Offset Problem)

You tell the front desk: "Give me a list of the first 100 guests with names A-R." matches 100 people named 'Aaron', 'Abby', etc.

Then you ask: "Give me the next 100 guests."

To answer this, the clerk has to skip through the 'A's again just to know where to start counting the 'B's. As you get deeper into the alphabet, the clerk spends more and more time skipping known names just to find the new ones. By the time you are asking for names starting with 'R', they are re-reading 90% of the guest list just to find your batch.

The Physical Approach (TID Scan)

This is the TID Range Scan. You stop caring about the names (the logical value) and organize by Room Number (the physical location).

• Worker 1: "Go check Rooms 1 to 100."
• Worker 2: "Go check Rooms 101 to 200."

Worker 2 doesn't care who is in Room 1. They go straight to Room 101. If the person inside is named "Zach", they skip them. If it's "Alice", they verify them.

• Zero Rescanning: No one re-reads the list of 'A's.
• Parallelism: Because the rooms are physically distinct, you can send 8 workers to different floors simultaneously without them needing to coordinate or skip over each other's results.

This approach transforms an O(N^2) maintainence nightmare into a linear, parallelizable task.

Reproduce this Test

Want to try it yourself on Postgres 19devel? Here is the SQL to set up the test table and run the benchmark.

-- 1. Create the table
DROP TABLE IF EXISTS bench_tid_range;
CREATE TABLE bench_tid_range (id int, payload text);

-- 2. Insert 10M rows to generate ~41k pages
INSERT INTO bench_tid_range 
SELECT x, 'payload_' || x 
FROM generate_series(1, 10000000) x;

-- 3. Vacuum to set visibility map and freeze (important for stable benchmarks)
VACUUM (ANALYZE, FREEZE) bench_tid_range;

-- 4. Enable Parallelism for the Session
SET max_parallel_workers_per_gather = 4; -- Try 2, 4, 8
SET min_parallel_table_scan_size = 0;    -- Force parallel scan even for smaller tables

-- 5. Run the Query
EXPLAIN (ANALYZE, BUFFERS) 
SELECT count(*) 
FROM bench_tid_range 
WHERE ctid >= '(0,0)' AND ctid < '(41667,0)';

Conclusion

This is a welcome "plumbing" improvement. It might not change your daily ad-hoc queries, but for DBAs and developers building custom data maintenance scripts, the ability to parallelize TID based scans is a powerful new tool in the optimization toolkit.

References:

• Commit 0ca3b169
• Discussion Thread

Speed up JOIN Planning - upto 16x Faster!

The hidden cost of knowing too much. That's one way to describe what happens when your data is skewed, Postgres statistics targets are set high, and the planner tries to estimate a join.

For over 20 years, Postgres used a simple O(N^2) loop to compare (equi-join) Most Common Values (MCVs) during join estimation. It worked fine when statistics targets are small (default_statistics_target defaults to 100). But in the modern era - we often see Postgres best-practices recommend cranking that up. Customers are known to be using higher values (1000 and sometimes even higher) to handle complex data distributions + throw a 10 JOIN query to the mix - and this "dumb loop" can easily become a silent performance killer during planning. 

That changes in Postgres 19.

The Problem: It's Quadratic!

When you join two tables, the planner needs to estimate how many rows will match. If both columns have MCV lists (lists of the most frequent values), eqjoinsel() tries to match them up to get a precise estimate.

Historically, it did this by comparing every item in list A with every item in list B.

• If you have 100 MCVs, that's 10,000 comparisons. Fast.
• If you have 10,000 MCVs (max stats target), that's 100,000,000 comparisons. Not so fast.

This meant that simply *planning* a query could take significantly longer than executing it, especially for simple OLTP queries.

The Solution: Hash It Out

The fix is elegant and effective.

Instead of a nested loop, the planner now:

1. Checks if the total number of MCVs is greater than 200 (100 each side).

2. If so, it builds a Hash Table of the MCVs from one (smaller) side.

3. It then probes this hash table with the MCVs from the other side.

The threshold of 200 was chosen because hashing has a startup cost (allocating memory, computing hashes). For smaller lists, the simple loop is actually faster. But once you cross that threshold, the hash table wins.

This transforms the complexity from O(N^2) to O(N), making the estimation step virtually instantaneous even with the largest statistics targets.

Let's Benchmark

I wanted to verify this myself, so I set up a worst-case scenario: two tables with 100,000 rows, but only 10,000 distinct values, and I cranked the statistics target to the maximum (10,000) to force a massive MCV list.

Setup

CREATE TABLE t1 (x int);
CREATE TABLE t2 (x int);

-- Scenario A: 3M rows, Stats 10000
INSERT INTO t1 SELECT x % 10000 FROM generate_series(1, 3000000) x;
INSERT INTO t2 SELECT x % 10000 FROM generate_series(1, 3000000) x;

-- Maximize statistics target
ALTER TABLE t1 ALTER COLUMN x SET STATISTICS 10000;
ALTER TABLE t2 ALTER COLUMN x SET STATISTICS 10000;

ANALYZE t1;
ANALYZE t2;

Results

I ran the following query 10 times on both versions, interleaved to ensure fairness and used median for the results:

EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM t1 JOIN t2 ON t1.x = t2.x;

Scenario A: High Stats (10k MCVs)

• Rows: 3 Million
• Statistics Target: 10,000
• MCV Count: 10,000

• Before (Postgres 18): ~27.8 ms
• After (Postgres 19): ~1.75 ms
• Speedup: ~16x

Scenario B: Medium Stats (1k MCVs)

• Rows: 300,000
• Statistics Target: 1,000
• MCV Count: 1,000

• Before (Postgres 18): ~0.85 ms
• After (Postgres 19): ~0.60 ms
• Speedup: ~1.4x (40% speed-up)

Scenario C: Default Stats (100 MCVs)

• Rows: 30,000
• Statistics Target: 100 (Default)
• MCV Count: 100

• Before (Postgres 18): ~0.40 ms
• After (Postgres 19): ~0.43 ms
• Speedup: None (Optimization correctly skipped)

Since the total MCV count in Scenario C (100 + 100 = 200) did not exceed the 200 threshold, Postgres 19 correctly chose the simpler loop, avoiding the overhead of building a hash table. This confirms that the patch is smart enough to only kick in when it matters.

The "Quadratic Curve" in Action

To visualize the O(N^2) vs O(N) difference, I ran a benchmark across a wide range of statistics targets (from 10 to 10,000). In each test, the number of rows was set to 300x the statistics target to ensure the MCV list was fully populated and relevant.

Stats Target	PG 18 (ms)	PG 19 (ms)	Speedup
10	0.40	0.38	-
100	0.40	0.45	-
200	0.45	0.43	-
500	0.53	0.52	-
1000	0.85	0.63	1.3x
2000	1.73	0.68	2.5x
5000	7.54	1.14	6.6x
8000	17.97	1.57	11.4x
10000	27.56	1.92	14.3x

As you can see, up to a target of 500, the difference is negligible (and the optimization might not even kick in). But as the target grows, the quadratic cost of the old method explodes, while the new hash-based method scales linearly and remains extremely fast.

This patch is a classic example of modernizing legacy assumptions. The code written 20 years ago assumed MCV lists would be short (in all fairness the default was 100 for a long time). Today's hardware and data requirements have pushed those boundaries, and Postgres is evolving to meet them.

Thanks to Ilia Evdokimov for the patch, David Geier for co-authoring, and Tom Lane for the review.

Note: This feature is currently in the master branch for Postgres 19. As with all development features, it is subject to change before the final release.

References

• Commit: Speed up eqjoinsel() with lots of MCV entries
• Discussion: Mailing List Thread

Teaching Query Planner to See Inside C Functions

The Postgres planner just got a new superpower: X-ray vision for your black-box functions.

For years, Postgres has been able to "inline" simple SQL functions. If you wrote CREATE FUNCTION ... LANGUAGE SQL AS 'SELECT ...', the planner would often rip out the function call and paste the raw SQL directly into the main query. This was magic. It meant that WHERE clauses could be pushed down, indexes could be used, and performance was excellent.

But if you stepped outside the safety of simple SQL functions—say, into the world of C extensions or complex dynamic SQL—you were often stuck with a "black box." The planner saw a function, executed it blindly, and only *then* applied your filters.

That changes in Postgres 19.

The "Black Box" Problem

Imagine you have a function that returns a large dataset, but you only want a few rows:

SELECT * FROM my_complex_function() WHERE id = 42;

Before Postgres 19, if my_complex_function wasn't a simple SQL function, the database would:

1. Run my_complex_function() to completion (generating, say, 1 million rows).

2. Filter the result to find id = 42.

This is painfully inefficient. You wanted an Index Scan, but you got a full execution followed by a filter.

The Solution: SupportRequestInlineInFrom

With this feature, Postgres now allows function authors (specifically in C extensions) to provide a "support function" that tells the planner: *"Hey, if anyone calls me in a FROM clause, here is the raw SQL query tree that represents what I'm doing."*

The planner can then take that query tree and inline it, just like it does for standard SQL functions.

The Impact

This is a game-changer for:

• Extensions: Many extensions provide table-like interfaces (e.g., foreign data wrappers, set-returning functions). They can now expose their internal logic to the planner.
• Dynamic SQL: Functions that generate SQL strings and execute them can now potentially participate in optimization.

Before vs. After

Scenario: A PL/pgSQL function foo_from_bar that executes a dynamic query on text_tbl (containing 1 million rows).

CREATE TABLE text_tbl (f1 text);
INSERT INTO text_tbl SELECT 'row_' || generate_series(1, 1000000);
INSERT INTO text_tbl SELECT 'common_row' FROM generate_series(1, 100);
CREATE INDEX text_tbl_idx ON text_tbl(f1);
ANALYZE text_tbl;

Here is the PL/pgSQL function I used for testing (designed to return 100 rows). Note that it uses dynamic SQL, which is normally opaque to the planner, and then linked it to the C support function test_inline_in_from_support_func using ALTER FUNCTION.

CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
RETURNS SETOF TEXT
LANGUAGE plpgsql
AS $function$
DECLARE
  sql TEXT;
BEGIN
  sql := format('SELECT %I::text FROM %I', colname, tablename);
  IF filter IS NOT NULL THEN
    sql := CONCAT(sql, format(' WHERE %I::text = $1', colname));
  END IF;
  RETURN QUERY EXECUTE sql USING filter;
END;
$function$ STABLE;

ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT)
  SUPPORT test_inline_in_from_support_func;

And here is the query I ran:

SELECT * FROM foo_from_bar('f1', 'text_tbl', 'common_row');

Before (PG 18 and earlier)

->  Function Scan on public.foo_from_bar  (cost=0.25..10.25 rows=1000 width=32)
      Output: foo_from_bar
      Function Call: foo_from_bar('f1'::text, 'text_tbl'::text, 'common_row'::text)
      Buffers: shared hit=32 read=4
      Execution Time: 0.353 ms

The function is treated as a black box. The planner estimates 1000 rows (default) and scans the function output.

After (PG 19)

->  Index Only Scan using text_tbl_idx on public.text_tbl  (cost=0.42..8.44 rows=1 width=10)
      Output: text_tbl.f1
      Index Cond: (text_tbl.f1 = 'common_row'::text)
      Buffers: shared hit=1 read=3
      Execution Time: 0.064 ms

The planner "sees through" the function, realizes it's just a query on text_tbl, and uses the index directly. The execution time drops significantly (5.5x faster in this case).

I also ran a pgbench test (1000 iterations) to measure the end-to-end latency improvement:

• Before: 0.107 ms average latency
• After: 0.087 ms average latency
• Improvement: ~19% faster end-to-end (including network overhead)

Bonus: Outer WHERE Pushdown - Orders of magnitude faster!

Next, I ran a test where NULL was passed to the function (returning all 1 million rows) and applied an external WHERE clause:

SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) WHERE foo_from_bar = 'common_row';

Before (PG 18):

->  Function Scan on public.foo_from_bar  (cost=0.25..12.75 rows=5 width=32)
      Output: foo_from_bar
      Function Call: foo_from_bar('f1'::text, 'text_tbl'::text, NULL::text)
      Filter: (foo_from_bar.foo_from_bar = 'common_row'::text)
      Rows Removed by Filter: 1000000
      Execution Time: 136.267 ms

The database scanned all 1 million rows from the function, then filtered them down to 100.

After (PG 19):

->  Index Only Scan using text_tbl_idx on public.text_tbl  (cost=0.42..8.44 rows=1 width=10)
      Output: text_tbl.f1
      Index Cond: (text_tbl.f1 = 'common_row'::text)
      Execution Time: 0.055 ms

The planner pushed the WHERE clause *inside* the function logic, triggering an index lookup (~2400x speedup).

Similarly, I tested adding a LIMIT 5 clause to the query and saw a ~20x speedup!!

• Before: The database scanned the function output until it got 5 rows (~1.091 ms).
• After: The planner pushed the limit down into the index scan, stopping immediately after finding 5 rows (~0.051 ms).

Testing Methodology

The benchmarks above were conducted using pgbench with the following parameters:

• Iterations: 1000 transactions per client.
• Concurrency: Single client (1 thread).
• Data Volume: The target table text_tbl contained 1,000,100 rows.
• Return Size: The query returned 100 rows per execution.
• Metric: Average latency reported by pgbench (excluding connection overhead).

• Commits tested: c0bc9af (before) and b140c8d (after).

The C support function (test_inline_in_from_support_func) used in this example is defined in src/test/regress/regress.c that is a part of the regression tests. To know more about support functions, see PostgreSQL Documentation on Function Optimization Information.

Discussion & Credits

Coincidentally a Linkedin thread mentioned that Set-Returning Functions (SRF) began their journey in Postgres 7.3 - that was 17 years ago! With this patch, the planner should be able to see through such a function and develop much better execution plans around them! Thanks to Paul A. Jungwirth (for initating the idea with a working patch) and Tom Lane (for in-depth review).

Note: This feature is currently in the master branch for Postgres 19. As with all development features, it is subject to change before the final release.

Settling COUNT(*) vs COUNT(1) debate in Postgres 19

A recent commit to the PostgreSQL master branch brings a nice quality-of-life optimization for a very common SQL pattern - improving performance by up to 64% for SELECT COUNT(h) where h is a NOT NULL column.

If you've ever wondered whether you should use COUNT(*) or COUNT(1), or if you've been dutifully using COUNT(id) on a non-null column, this change is for you.

Note: This feature is currently on the PostgreSQL master branch (committed in November 2025). As with any commit on master, it is subject to change or even rollback before the final release, although this is relatively rare for committed features. If all goes well, this change will be part of the PostgreSQL 19 major version release.

The Optimization

Historically in PostgreSQL, COUNT(*) has been faster than COUNT(1) or COUNT(column).

• COUNT(*): The planner knows it just needs to count rows. It doesn't need to check if any specific column is null.
• COUNT(column): The executor has to fetch the value of the column for each row and check if it is NULL before counting it. This involves "deforming" the tuple (extracting the data), which adds overhead.
• COUNT(1): Often used as a "faster" alternative by users, but treated similarly to COUNT(column) where the "column" is the constant 1.

The new commit, Have the planner replace COUNT(ANY) with COUNT(*), when possible, changes this behavior.

The planner can now detect when you are counting a generic expression (COUNT(ANY)) that:

1. Cannot be NULL: For example, a constant like 1 or a column defined as NOT NULL.

2. Has no extra clauses: No DISTINCT or ORDER BY inside the aggregate.

In these cases, the planner will automatically transform the query to use COUNT(*).

Performance Impact

This optimization eliminates the overhead of tuple deforming and null checking for these cases.

I ran a benchmark on a local PostgreSQL instance to verify the impact, comparing COUNT(h) (not null column), COUNT(1) (constant), and COUNT(*) (rows) on a table with 10 million rows.

Before Patch:

• SELECT count(h) FROM t: ~195 ms
• SELECT count(1) FROM t: ~126 ms
• SELECT count(*) FROM t: ~119 ms
• *Observation:* COUNT(h) is significantly slower (~64% overhead vs count(*)). COUNT(1) is faster than COUNT(h) but still lags behind COUNT(*).

After Patch:

• SELECT count(h) FROM t: ~117 ms
• SELECT count(1) FROM t: ~116 ms
• SELECT count(*) FROM t: ~114 ms
• *Observation:* COUNT(h) sees the major win, improving dramatically to match COUNT(*). COUNT(1) sees a minor speedup, also converging to the optimal COUNT(*) performance.

This means users who prefer COUNT(1) for stylistic reasons, or who count specific non-null columns, will now get the optimal performance of COUNT(*) automatically.

(The patch author, David Rowley, demonstrated similar speedups in the initial discussion).

What about Nullable Columns?

I also tested COUNT(nullable_col) to see if the optimization applies. As expected, it does not.

• Before Patch: COUNT(nullable_col): ~162 ms
• After Patch: COUNT(nullable_col): ~158 ms (No significant change)

Since the planner cannot guarantee the column is free of NULLs, it must still scan and check every value, so the performance remains unchanged. The optimization strictly targets columns where the planner *knows* the value cannot be NULL.

What about DISTINCT or ORDER BY?

I also tested COUNT(DISTINCT h) and COUNT(h ORDER BY h) to see if the optimization applies. As expected, it does not.

• Before Patch: COUNT(DISTINCT h): ~2663 ms (Median of 10 runs)
• After Patch: COUNT(DISTINCT h): ~2708 ms (Median of 10 runs)

Similarly for ORDER BY, the executor still needs to process the values to sort them, so no optimization is possible.

Test Methodology

• Versions:

* Before: Tested on commit dbdc717ac6743074c3a55fc5c380638c91d24afd.

* After: Tested on commit 42473b3b31238b15cc3c030b4416b2ee79508d8c.

• Data: Table t with 10 million rows, populated via INSERT INTO t (h) SELECT 1 FROM generate_Series(1,10000000);.
• Procedure: Each query was executed 10 times in an interleaved pattern (A, B, C, A, B, C...) to ensure fairness and mitigate caching bias. The reported time is the median of those 10 runs.
• System: Intel Core i7-4770 CPU @ 3.40GHz (4 cores/8 threads), 16GB RAM.

Detailed Analysis

To understand *why* the performance improved, we can look at the EXPLAIN plans and table statistics.

• Table Size: The table t (10 million rows) is approximately 346 MB.
• Disk vs. Memory: The benchmark results (median of 10 runs) indicate mostly in-memory / hot-cache performance (~3 GB/s throughput). However, even with some disk I/O involved (as seen in single-run EXPLAIN (ANALYZE, BUFFERS) tests showing ~28k buffer reads), the CPU overhead of deforming the tuple was the dominant differentiator.

The Key Difference:

In the Before scenario, COUNT(h) required the scanner to fetch the column data (width=4), whereas COUNT(*) did not (width=0).

-- Before Patch: COUNT(h)
->  Parallel Seq Scan on t  (... rows=4166657 width=4) ...

In the After scenario, the planner transforms COUNT(h) into COUNT(*), allowing it to skip fetching the column data entirely (width=0).

-- After Patch: COUNT(h)
->  Parallel Seq Scan on t  (... rows=4166657 width=0) ...

This reduction in "width" means the executor doesn't need to extract the integer value from the row, saving significant CPU cycles.

What about COUNT(1)?

While COUNT(1) also uses width=0 (even before the patch), it historically carried a small overhead because the executor had to evaluate the constant expression 1 for every row.

The patch allows the planner to detect that any constant non-null value within a count aggregate can be replaced by COUNT(*). By rewriting it internally, PostgreSQL skips the expression evaluation and parameter passing logic entirely, resulting in the slight performance edge seen in the benchmarks.

We can see this in the EXPLAIN (VERBOSE) output where the aggregate node itself changes:

-- Before Patch: Output contains the constant expression
->  Partial Aggregate
      Output: PARTIAL count(1)

-- After Patch: Optimized to parameterless count(*)
->  Partial Aggregate
      Output: PARTIAL count(*)

Community Discussion & Trivia

Thanks to David Rowley for working on the patch and Corey Huinker / Matheus Alcantara for reviewing this. The discussions debated on trade-offs between what is fastest and which of these is an "anti-pattern", but it is great to see that now all three are now equivalently optimized.

Future Possibilities:

The infrastructure added (SupportRequestSimplifyAggref) opens the door for further optimizations. For instance, COUNT(NULL) could theoretically be optimized to instantly return 0 without scanning any rows - although that specific change wasn't included in this commit to keep the scope focused.

References

• Commit Message: Have the planner replace COUNT(ANY) with COUNT(*), when possible
• Discussion Thread: Have the planner convert COUNT(1) / COUNT(not_null_col) to COUNT(*)

FOR KEY SHARE optimization and the SLRU Trap

 

FOR KEY SHARE optimization and the SLRU Trap

Optimizing database concurrency is a constant balancing act. We often tweak locking strategies in PostgreSQL, aiming to allow more simultaneous operations without compromising data integrity. A common scenario involves shifting from stricter row-level locks to more lenient ones. But sometimes, what seems like a straightforward optimization can lead to unexpected performance bottlenecks in less obvious parts of the system.

This post explores one such scenario: moving from SELECT FOR NO KEY UPDATE to SELECT FOR KEY SHARE, the potential for subsequent MultiXactOffsetSLRU wait events, and how PostgreSQL 17 offers a direct solution.

The Locking Strategy Shift: Aiming for Higher Concurrency

Let's start with the locks themselves. Within a transaction, you might initially use:

• SELECT FOR NO KEY UPDATE: This acquires a moderately strong row lock. It prevents others from deleting the row, updating key columns, or acquiring FOR UPDATE / FOR NO KEY UPDATE locks on it. However, it does allow concurrent non-key updates and weaker FOR SHARE / FOR KEY SHARE locks. Importantly (and we’ll see why later), only one transaction can hold this lock (or FOR UPDATE) on a given row at a time.

To potentially increase concurrency, especially if you only need to prevent key changes or deletions (like ensuring a foreign key reference remains valid), you might switch to:

• SELECT FOR KEY SHARE: This is a weaker, shared lock. It blocks deletions and key updates but allows concurrent non-key updates and even other concurrent SELECT FOR KEY SHARE (or FOR SHARE) locks on the exact same row.

The intended outcome of switching to FOR KEY SHARE is often to reduce blocking and allow more transactions to proceed in parallel, particularly if the main concern is referential integrity rather than preventing all concurrent modifications.

The Unforeseen Bottleneck: Enter MultiXacts and SLRU Caches

While the switch does allow higher concurrency at the row-lock level, it can create pressure elsewhere. Here’s the chain reaction:

1. Increased Shared Lock Concurrency: Your application now has more situations where multiple transactions hold a shared lock (FOR KEY SHARE) on the same row simultaneously.

2. The MultiXact System: How does PostgreSQL track that multiple transactions (potentially dozens or hundreds) have a shared interest in a single row? It uses a mechanism called MultiXact IDs (Multi-Transaction IDs). Instead of just one transaction ID locking the row, PostgreSQL assigns a special MultiXact ID that represents the group of transactions currently sharing a lock on it.

3. SLRU Caches: Managing this MultiXact metadata efficiently requires quick access. PostgreSQL uses specialized SLRU (Simple Least Recently Used) caches in shared memory for this. These caches store the mappings (offsets) from rows to their MultiXact member lists (MultiXactOffsetSLRU) and the member lists themselves (MultiXactMemberSLRU).

4. The Bottleneck (PG 16 and older): Before PostgreSQL 17, these SLRU caches had relatively small, fixed sizes determined at compile time. When the workload switch dramatically increased the demand for MultiXact tracking (due to more concurrent shared locks), these small caches could easily become overwhelmed.

5. The Symptom (MultiXactOffsetSLRU Waits): An overloaded SLRU cache leads to performance degradation manifesting as specific Wait Events. You might see high MultiXactOffsetSLRU waits, indicating processes are frequently:

• Waiting for disk I/O because the required MultiXact offset data wasn't found in the small cache (cache miss).

• Waiting to acquire low-level locks needed to access or update the cache's shared memory buffers, because many processes are trying to use the limited cache concurrently (lock contention).

• Many backends appear hung - as can be seen in this recent community thread.

So trying to increase concurrency at the row-level, created a bottleneck in the underlying mechanism to manage that very concurrency!

PostgreSQL 17 to the Rescue: Configurable SLRU Buffers

Recognizing this potential bottleneck, the PostgreSQL developers introduced direct solutions in PostgreSQL 17 (released September 2024). These come in the form of new configurable parameters:

• multixact_offset_buffers:

• This parameter controls the size (in buffer pages) of the MultiXactOffset SLRU cache in shared memory. The default value is very small (16) and this allows administrators to allocate more RAM to cache the crucial row-to-member-list mappings. This significantly increases the cache hit rate, reduces disk I/O for MultiXact offsets, and directly alleviates the pressure causing MultiXactOffsetSLRU waits.

• multixact_member_buffers:

• This parameter controls the size of the MultiXactMember SLRU cache, which stores the actual lists of transaction IDs. This is possibly less directly tied to the Offset wait event, ensuring in-cache member lists improves the overall performance and throughput of the entire MultiXact lookup process, which is essential when handling high shared-lock concurrency.

You can learn more about these new parameters in this fantastic discussion - https://pganalyze.com/blog/5mins-postgres-17-configurable-slru-cache

These parameters allow DBAs to tune the memory allocated to these critical caches based on their specific workload, moving away from the one-size-fits-all limitation of previous versions.

Conclusion

Switching locking strategies in PostgreSQL requires careful consideration not just of the direct blocking rules but also of the potential impact on underlying mechanisms. Moving from SELECT FOR NO KEY UPDATE to the more concurrency-friendly SELECT FOR KEY SHARE can be beneficial, but it increases the load on the MultiXact system. In versions before PostgreSQL 17, this could lead to performance bottlenecks manifesting as MultiXactOffsetSLRU wait events due to contention on small, fixed-size caches.

Thankfully, PostgreSQL 17 provides the tools needed to manage this directly with the multixact_offset_buffers and multixact_member_buffers GUCs. If you encounter these specific wait events after increasing shared lock usage, upgrading to PostgreSQL 17+ and tuning these parameters should be a key part of your resolution strategy. As always, monitor your system's wait events and performance metrics closely when making changes to locking or configuration.