DEV Community

Cover image for pgcoalesce: a Postgres proxy that merges concurrent point reads
kobima6813
kobima6813

Posted on

pgcoalesce: a Postgres proxy that merges concurrent point reads

pgcoalesce is a Postgres wire-protocol proxy that holds concurrent primary-key reads of the same shape for a couple of milliseconds, runs them as one query, and fails closed when it cannot prove that is safe. It is not a cache: every answer is read from Postgres at that moment. The only application change is the connection string.

Status: 0.3.0, experimental — benchmarked on one machine, no production users yet. License: Apache-2.0. Repository: github.com/kobima6813/pgcoalesce.

This write-up covers how the proxy works, the numbers we measured against PgBouncer, the safety model, and how it sits next to other tools in this space. We have not published a benchmark against any product except PgBouncer. Competitor features and prices are from their public docs, checked on 15 November 2026.

The problem

Serverless, edge and API backends often fire many tiny reads at the same moment:

SELECT * FROM users WHERE id = $1
Enter fullscreen mode Exit fullscreen mode

Each statement is cheap on its own. What adds up is Postgres's per-query overhead: bind, plan or lock a cached plan, snapshot, execute, and return a result, once per client. A pooler such as PgBouncer multiplexes connections, but it still runs every statement as its own query. Under a few thousand identical-shaped point reads per second, that per-query work saturates a small instance, not the data pages themselves.

Application batchers such as DataLoader solve this inside one request, and only if you change the code. A read cache can absorb the load, but then the answer may be stale. The gap we wanted: merge concurrent point reads across clients, keep every answer live from Postgres, and leave the application unchanged.

How it works

pgcoalesce sits in front of Postgres like a pooler. When several clients, in the same result-affecting context, issue the same shape of point read with different keys, it waits for an adaptive window (about 2 ms by default; roughly 0 when there is no partner) and then runs one statement:

SELECT k.ord, x.*
FROM unnest($1::int8[]) WITH ORDINALITY k(v, ord)
CROSS JOIN LATERAL (
  <your exact query, with $1 replaced by k.v>
) x
Enter fullscreen mode Exit fullscreen mode

Each client receives exactly its own rows, in the format it asked for (text or binary, simple or extended protocol). Writes, unrecognised SQL, and anything that is not a catalog-verified point read pass through unchanged.

diagram

At low load the proxy almost never waits; a request with no partner is forwarded immediately. At higher load the window opens just enough to find partners, so the latency cost tracks the configured cap: about +1.8 ms at p50 with a 2 ms window, about +3.4 ms with 5 ms.

A second path recognises the PostgREST / Supabase transaction template (BEGIN; set_config(...); one point SELECT; COMMIT) and merges whole transactions only when the fingerprint — role, claims, headers, path and watched GUCs — is identical.

Benchmarks

All figures in this section are from one box, on 15 November 2026. Unless noted, they are medians of three runs, and a single run moves around by roughly ±20%.

Rig. Postgres 17.12 in a cgroup limited to 1 CPU and 256 MB (shared_buffers=64MB), 1 million row users table (172 MB, against 64 MB of shared_buffers and the 256 MB limit). 500 client connections, open-loop load, pgx v5. Baseline: PgBouncer 1.24.1 in transaction mode with pool_size=4 (pool_size=20 was worse). Database CPU is measured from the cgroup. The 5,000 req/s table and the lower-rate table are medians of three runs. Runs are short: 10,000 requests in about 2 seconds at 5,000 req/s, and 4 seconds at the lower rates. Raw data is in results/.

The 5,000 req/s prepared / uniform / 2 ms cell was measured again in two later sessions, one of them on the code that includes the pre-launch fixes: 18.4 and 18.3 µs per request, against 60.2 and 59.2 µs for PgBouncer.

Database CPU per request at 5,000 reads/s

Database CPU per request at 5,000 reads/s, pgcoalesce versus PgBouncer

Driver mode Keys PgBouncer pgcoalesce 2 ms pgcoalesce 5 ms p50 / p99 (ms): PgBouncer → 2 ms → 5 ms
Named prepared, binary Uniform 57.8 µs 17.5 µs (−70%) 12.3 µs (−79%) 0.59 / 1.7 → 2.41 / 4.2 → 3.98 / 7.3
Named prepared, binary Zipf 1.1 54.8 µs 11.7 µs (−79%) 6.9 µs (−87%) 0.58 / 1.6 → 2.33 / 4.0 → 3.96 / 7.3
Unnamed (exec) Uniform 88.3 µs 17.1 µs (−81%) 14.0 µs (−84%) 0.59 / 1.6 → 2.46 / 4.1 → 4.06 / 8.4
Unnamed (exec) Zipf 1.1 82.9 µs 12.3 µs (−85%) 7.3 µs (−91%) 0.60 / 1.5 → 2.33 / 4.0 → 3.94 / 7.3

Larger windows save more CPU and cost more latency. The 2 ms window is the default. Uniform keys save less than skewed ones: −70% to −84% in this table, against −79% to −91% for Zipf. Page access costs the same whether a key is fetched alone or in a batch, and uniform keys touch more pages.

Latency at 5,000 reads/s, pgcoalesce versus PgBouncer

Lower rates (uniform keys, 2 ms window)

Rate PgBouncer pgcoalesce Database CPU p50
1,000 req/s 92.9 µs 39.4 µs −58% 0.79 → 2.32 ms
250 req/s 169.5 µs 95.8 µs −43% 1.46 → 1.21 ms

The 1,000 req/s row is where batching starts to pay. At 250 req/s the window rarely finds a partner; the remaining saving comes from pgcoalesce's long-lived prepared templates, not from merging keys. Below about 1,000 point reads per second of a single query shape, requests go straight through with no added wait.

Proxy CPU versus PgBouncer

pgcoalesce itself is more expensive than PgBouncer. At 5,000 req/s, median proxy CPU was 41–49 µs per request, versus 26–28 µs for PgBouncer with pool_size=4 (the README's 27–30 µs range also covers pool 20). Work moves off the database, which scales vertically, onto a stateless tier. Total system CPU was still lower in these runs, because the database saving is larger than the extra proxy cost.

A single-run sweep at 20,000 req/s showed the two proxies at roughly the same CPU per request. Sweep rates are one run each and noisier than the 5,000 req/s table; that is not a firm result.

PostgREST / Supabase

PostgREST 16.5, an RLS-protected profiles table, GET /x?id=eq.N. Database CPU per request, proxied versus direct. These are single runs, not a three-run median.

Scenario 1,000 req/s 3,000 req/s
S1: anonymous catalog reads (identical claims) 149 vs 199 µs (−25%) 70 vs 165 µs (−58%)
S2: 2,000 logged-in users (Zipf), stable token per user 228 vs 216 µs (parity) 186 vs 176 µs (7% coalesced, parity)
S3: unique claims per request (iat / jti) 198 vs 224 µs (0% coalesced) 181 vs 175 µs (0% coalesced)

It helps anonymous and public reads. It does almost nothing for per-user traffic, because each user's claims and headers differ. We do not normalise claims such as iat or exp: policies and functions can read any claim, so merging them is not provably safe. Cold fingerprints are passed straight through, so a unique user does not pay for a shared transaction of one.

Caveats

  • You pay latency. p50 rises by about the window. Do not use this for latency-critical single reads.
  • One machine, synthetic schema, short runs. 10,000 requests is about two seconds. Run-to-run variance is about ±20%.
  • Overload is “not worse”, not “better”. A same-session comparison of the pre-fix and post-fix binaries under overload was noisy (PgBouncer moved just as much). The post-fix binary was not worse. We are not citing an older unreproduced overload figure (later runs: about 30–37k req/s, p99 229–1,010 ms).
  • The Dockerfile in the repository has not been built on the test box.

Correctness and safety

A read is coalesced only when every check passes. Anything unrecognised is relayed unchanged.

  1. Exact shape. SELECT <plain columns|*> FROM <table> WHERE <unique col> = $1. No functions, casts, ORDER BY or LIMIT, no extra predicates, no FOR UPDATE / SHARE.
  2. Catalog-verified. Ordinary or partitioned, non-temporary table; a single-column valid unique index; RLS off for plain coalescing; no views. Output columns are real columns of that table. Key and output types are built-in. = must be a pg_catalog operator backed by an immutable function.
  3. Identical result-affecting context. Clients share a batch only when database, current_user, session_user, and the session settings listed in pg_settings match — including search_path, TimeZone, DateStyle, client_encoding, standard_conforming_strings, and the rest — plus temp-schema state. Custom settings such as app.tenant_id are not in this fingerprint, because pg_settings does not list them (checked on Postgres 16 and 17.12). That cannot change a plain-coalesced answer: only RLS policies, views and functions can read a custom setting, and all three are excluded from the plain path. In the transaction path, the custom settings that policies and functions read are compared explicitly.
  4. Transactions. Plain coalescing happens only when the client is idle. The transaction path accepts only the exact PostgREST shape. Writes, savepoints, a second statement, errors, or an RLS policy that calls a volatile function all force a replay on the client's own session.
  5. Errors stay per-client. If a batch fails (for example one client sent 'abc' for an int key), it is re-run key by key, so each client gets the SQLSTATE and message it would have seen directly.

Adversarial suites compare every response, byte for byte, with direct Postgres — two tenants, different GRANTs, RLS, SET ROLE, same-named tables behind different search_path values, TimeZone/DateStyle differences, and PostgREST personas. After a pre-launch review we reran every suite twice: once with default settings, and once with batching forced (min_expected=0, window=15ms), because under default settings the test clients rarely overlap.

In total that is 1,600,522 operations, 0 mismatches, 0 data races, 0 crashes. All of these runs used a build with Go's race detector on. The forced-batching runs alone produced 63,969 multi-key (or multi-transaction) batches — the only place a mix-up between clients could happen — and still 0 mismatches. Across both modes there were 131,245 such merged batches. A volatile-function RLS policy was correctly refused (39 transactions, 0 coalesced).

Bugs found before release, and fixed

The suites first caught a PostgREST path bug: a statement prepared inside a coalescible transaction, then a deviation from the template, produced 42P05. That was fixed before review.

A code review done with an AI model (Claude) then found four more, all now fixed and covered by regression tests:

  1. Wrong results for non-UTF-8 text keys. A client_encoding=LATIN1 client looking up a non-ASCII text key could get a miss through the proxy when the key was part of a multi-key batch, while direct Postgres returned the row. Batches are now built byte for byte. Client-only encodings in which a multi-byte character can contain an ASCII byte such as a backslash (SJIS, BIG5, GBK and similar) run key by key.
  2. Process-wide crash from one malformed extended-protocol group. A Close followed by a Bind/Execute of the same name in the same message group panicked the whole process and dropped every client. The proxy now returns the same error Postgres would, and an internal error on one connection cannot take the others down.
  3. Unbounded server connections. Each distinct session fingerprint held server connections forever. A client that changed search_path on every request (a schema-per-tenant pattern) could exhaust the server. Engines are now capped (max_engines, default 32) and closed after engine_idle_timeout (default 60s).
  4. String literals with standard_conforming_strings=off. A simple-protocol literal containing a backslash was parsed as if the GUC were on. Those literals are now passed through.

The regression tests from that review failed 10 of 11 checks on the old binary, and killed the process. On the fixed binary they failed 0. We reproduced that on the original test box, then reran the full suites and the 5,000 req/s benchmark; the CPU numbers were unchanged within run-to-run noise.

Residual risks

These are the main open items. Most are also listed in the README:

  • C-language functions that read custom GUCs are invisible to the SQL/PL source scan.
  • A STABLE function that calls a volatile one is not checked recursively.
  • A transaction-mode pooler as upstream_addr makes per-client session-state verification meaningless. Use Postgres directly, or a session-mode pooler, if clients use SET, temp tables or set_config.
  • ALTER ROLE ... SET takes effect for coalescing at the next connect or engine refresh.
  • The metrics endpoint (/metrics, /stats, POST /admin/reload) is unauthenticated; bind it to loopback or a private interface.
  • Server-side TLS defaults to prefer, which does not verify the server certificate. Use verify-full for anything that is not on localhost.
  • Query templates are cached for template_ttl (5 minutes by default). Behaviour right after a schema change on a coalesced table, for example ADD COLUMN under SELECT *, has not been tested.
  • One machine, synthetic schemas, and no third-party security review. Treat this as experimental.

How it compares

Two tables follow. The first is the only one we measured. The second is a feature and status comparison from public documentation, checked on 15 November 2026. We did not benchmark Gold Lapel, ReadySet, Hyperdrive, ProxySQL, or any other product in that table. Cells about freshness or merge behaviour describe documented design, not a measurement of ours.

Measured: pgcoalesce versus PgBouncer

Same rig as above, 5,000 req/s, median of three runs, 2 ms window.

Workload PgBouncer DB CPU pgcoalesce DB CPU Saved p50 (ms) Proxy CPU, pgcoalesce Proxy CPU, PgBouncer
Prepared, uniform 57.8 µs 17.5 µs −70% 0.59 → 2.41 44 µs 27 µs
Prepared, Zipf 1.1 54.8 µs 11.7 µs −79% 0.58 → 2.33 42 µs 28 µs
Exec, uniform 88.3 µs 17.1 µs −81% 0.59 → 2.46 49 µs 27 µs
Exec, Zipf 1.1 82.9 µs 12.3 µs −85% 0.60 → 2.33 46 µs 26 µs

pgcoalesce does not replace a pooler. It can sit in front of Postgres, or use a pooler in session mode as upstream_addr.

Public feature comparison (not a benchmark)

“Merges across clients” here means merging different keys of the same point-read shape into one live SQL statement. Adjacent behaviour — singleflight of identical in-flight queries, or a cache — is noted in the cell rather than scored as the same thing.

Product Approach Merges different keys across clients? Always fresh? App code changes? Postgres? Status / pricing (15 Nov 2026)
pgcoalesce Coalescer Yes (live unnest + LATERAL) Yes — no cache No Yes Experimental, Apache-2.0, free
Gold Lapel Pooler + cache + matviews + coalescing Partial — see note 1 No — caches and matviews No Yes Public preview: Esquire $49/DB/mo, Noble $199/DB/mo; 14-day trial. Self-serve listed as coming soon
ReadySet SQL cache No No — see note 2 No Yes (also MySQL) Community free (BSL). Cloud: see note 3. Private is custom
Cloudflare Hyperdrive Pooler + query cache No No — 60s max_age + 15s stale; writes do not invalidate No Yes (via Workers) Included in Workers Free (100k queries/day) and Paid (unlimited)
ProxySQL Pooler + TTL cache No No — writes do not invalidate No Yes, and MySQL (native PG in current releases) Open source (GPLv3)
PgBouncer Pooler No Yes (pass-through) No Yes Maintained (1.26.1 as of Oct 2026; we measured 1.24.1). ISC, free
PgCat Pooler No Yes (pass-through) No Yes Its original author says he no longer develops it and now works on PgDog; a user-filed issue dates the last activity to about May 2025 (MIT)
Supavisor Pooler No Yes (pass-through) No Yes Maintained (v2.10.1, 14 Nov 2026). Apache-2.0, free
PgDog Pooler, balancing, sharding No Yes (pass-through) No Yes AGPL-3.0 core, free. Enterprise closed source; price on request
DataLoader In-process batcher No — one request only Per-request memoisation, not a TTL cache Yes N/A (library) MIT, free
Vitess MySQL shard proxy No — singleflight of byte-identical queries only; can lose read-after-write consistency No TTL, but a shared in-flight result can be stale vs a concurrent write No No — MySQL only Apache-2.0, free
PolyScale Database cache No No (it was a cache) No Yes Hosted service shut down 1 June 2024. A self-hosted product was announced; we could not verify it shipped
Prisma Accelerate Pooler + query cache No No (it was a cache) Yes (cacheStrategy) Yes (and others) Retires 1 December 2026. Prisma says the cache has no drop-in replacement

Notes on the table:

  1. Gold Lapel. Its docs describe opt-in singleflight of identical in-flight queries across connections, and a separate N+1 detector that can prefetch across connections and then serve later hits from a batch cache (example TTL 300s). That is not the same as merging different keys into one live SQL statement.
  2. ReadySet. Official docs say cached results are kept in sync from the replication stream. That is still a derived copy; replication lag is possible.
  3. ReadySet Cloud pricing. The official pricing page did not expose a numeric starting price in our fetch. A third-party archive (PriceTrack) lists Cloud from $199/month. AWS Marketplace lists Small (10 GB) at $1,000/month and Medium (25 GB) at $2,500/month.

Sources for every status and price claim are listed at the end.

When to use it, and when not to

Worth trying when many clients issue the same shape of unique-column read at around 1,000 requests per second or more; when the working set is hot or skewed, or statements are unnamed/exec (we saw 80–90% less database CPU there); when you cannot change application code and cannot accept a cache; or when anonymous PostgREST reads share claims.

Not a good fit when load is well below 1,000 point reads per second of one shape (at 250 req/s any remaining saving is from prepared templates, not batching); when the workload is write-heavy, or the reads are not point lookups (joins, aggregates, ORDER BY / LIMIT, views, functions all pass through); when you need the lowest possible p50 on single reads; when traffic is mostly per-user PostgREST with distinct claims (expect parity, not a gain); or when you need a third-party security review before putting a proxy on the path to the database. There is not one yet.

Try it

git clone https://github.com/kobima6813/pgcoalesce.git
cd pgcoalesce/src
CGO_ENABLED=0 go build -o pgcoalesce .
Enter fullscreen mode Exit fullscreen mode

Copy pgcoalesce.example.ini, set server_addr (where coalesced batches go) and upstream_addr (everything else: Postgres itself, or a pooler in session mode), and point auth_file at a PgBouncer-format user list — or use auth_query with a SECURITY DEFINER function, as in PgBouncer.

./pgcoalesce -config pgcoalesce.ini
psql "postgres://app@localhost:6432/app?sslmode=require"
Enter fullscreen mode Exit fullscreen mode

Metrics are on 127.0.0.1:9187 in the example config (/metrics, /stats). A longer walkthrough is in docs/QUICKSTART.md. Bind metrics_addr to loopback or a private network; those endpoints are unauthenticated.

If you try this on a real workload, we want to hear about it — including cases where it does not help, or where a result does not match Postgres. Please open an issue. The project is experimental, and reports from outside this one test box are the most useful thing it can get.

Sources

Checked 15 November 2026.

  1. Gold Lapel pricing: https://goldlapel.com/pricing
  2. Gold Lapel architecture (identical-query coalescing, caches, N+1 batch cache): https://goldlapel.com/docs/architecture
  3. Gold Lapel configuration (coalescing is opt-in; N+1 cross-connection toggle; example batch_cache_ttl_secs = 300): https://goldlapel.com/docs/configuration
  4. ReadySet pricing page (Community / Private / Cloud; numeric Cloud price not visible in our fetch): https://readyset.io/pricing
  5. ReadySet Cloud starting price as archived by PriceTrack: https://pricetrack.dev/products/readyset
  6. ReadySet Cloud Small, AWS Marketplace, $1,000/month: https://aws.amazon.com/marketplace/pp/prodview-lwqyxlrdseo5o
  7. ReadySet Cloud Medium, AWS Marketplace, $2,500/month: https://aws.amazon.com/marketplace/pp/prodview-a6dde42iu3qm6
  8. Cloudflare Hyperdrive query caching: https://developers.cloudflare.com/hyperdrive/concepts/query-caching/
  9. Cloudflare Hyperdrive pricing: https://developers.cloudflare.com/hyperdrive/platform/pricing/
  10. ProxySQL query cache (PostgreSQL support; writes do not invalidate): https://proxysql.com/documentation/query-cache/
  11. ProxySQL PostgreSQL configuration: https://proxysql.com/documentation/proxysql-configuration-postgresql/
  12. PgBouncer: https://www.pgbouncer.org/ — 1.26.1 in the changelog; ISC license
  13. PgCat status (author's comment in #942; user-filed status issue #948): https://github.com/postgresml/pgcat/issues/942, https://github.com/postgresml/pgcat/issues/948
  14. Supavisor v2.10.1 (14 Nov 2026), Apache-2.0: https://github.com/supabase/supavisor/releases/tag/v2.10.1
  15. PgDog Enterprise (pricing on request): https://docs.pgdog.dev/enterprise_edition/
  16. DataLoader: https://github.com/graphql/dataloader
  17. Vitess query consolidation (identical queries only; MySQL; read-after-write caveat): https://vitess.io/docs/25.0/user-guides/configuration-advanced/query-consolidation/
  18. PolyScale hosted shutdown, 1 June 2024: https://www.polyscale.ai/future/
  19. Prisma Accelerate retires 1 December 2026: https://www.prisma.io/docs/accelerate
  20. PgDog license (AGPL-3.0): https://github.com/pgdogdev/pgdog/blob/main/LICENSE

Top comments (0)