Three Postgres Hacks for 2026

Most PostgreSQL advice on the internet is still 2019 with a new year in the title. Cache-hit ratio as a north star. A B-tree on every foreign key. A connection for every serverless function. That list is how production databases get slower while dashboards stay green.

These three moves are the ones we still see skipped on Australian product databases in 2026, and they are specific to the Postgres 18 line: asynchronous I/O you can actually see, pooling that survives ORMs, and locality that starts at the primary key. They sit next to the representation argument in quantum mathematics for performance: unstructured search is expensive, so stop representing the data as unstructured.

The three moves at a glance

Hack 1: Instrument I/O, stop worshipping cache-hit ratio

Buffer cache hit ratio answers "did this page live in shared buffers?" It does not tell you whether the query was sequential, random, a vacuum, or a backend stuck on writeback. Postgres 16 added pg_stat_io. Postgres 18 added an asynchronous I/O subsystem, pg_aios, and per-backend I/O stats. The 2026 habit is to read those views weekly, not to chase a percentage you already won.

Enable pg_stat_statements in shared_preload_libraries, restart once, and keep it on. Rank by total time, then by mean time, then by shared-block reads. For any query in the top ten, run EXPLAIN (ANALYZE, BUFFERS, WAL) on a replica or in a maintenance window. Guessing the plan is malpractice.

On Postgres 18, the new I/O path is controlled by io_method. The official 18 release notes describe worker, io_uring, and sync. Tomas Vondra, who built much of AIO, is blunt: keep io_method = worker unless you can prove io_uring wins on your storage. The default io_workers of 3 is a laptop number. On a real server, start near 25% of physical cores (minimum 4) and watch pg_aios plus pg_stat_io before you go higher. Sequential scans, bitmap heap scans, and vacuum are where AIO shows up. Latency-sensitive OLTP with a hot cache may barely move, which is information, not a failed upgrade.

Do not copy a blog that says "use io_uring for maximum efficiency". Vondra's sequential-scan numbers had worker ahead of io_uring. Your NVMe array may differ. Measure.

Hack 2: Pool like it is 2026

Postgres still forks a backend process per connection. That is fine at 50 clients. It is how you melt a 4 GB VM at 500 idle serverless functions. The 2026 default for web apps is a pooler in front: PgBouncer, or the pooler your host already runs (Supabase, Neon, RDS Proxy, Cloud SQL Auth Proxy with a real pool behind it).

Use transaction mode for almost every OLTP app. Session mode wastes backends. Statement mode breaks transactions. Transaction mode's cost is session state: LISTEN/NOTIFY, session-level SET, temporary tables, SQL-level PREPARE, and session advisory locks do not survive a backend hand-off. SET LOCAL is fine. Anything that truly needs a session goes on a direct, unpooled connection (migrations, a notify worker, a report that builds a temp table).

Prepared statements were the recurring trap. PgBouncer 1.21 added protocol-level prepared statements in transaction mode via max_prepared_statements (typically 100 to 200). That is not SQL PREPARE. Prisma's old pgbouncer=true flag fights this; current guidance is a pooled URL for the app and a direct URL for migrations. Rails still needs an explicit choice: protocol-level support, or prepared_statements: false.

If you are on a managed host, you still have this problem. Neon and Supabase give you a pooler endpoint and a direct endpoint for a reason. Point the ORM at the pooler. Point migrate and LISTEN at direct. The names differ; the topology does not.

Hack 3: Locality is the index

Random UUIDv4 primary keys scatter inserts across the B-tree. Pages split, the working set bloats, and sequential scans of "recent" rows become random I/O. Postgres 18 ships native uuidv7() (RFC 9562, no uuid-ossp). Time-ordered UUIDs insert near the right-hand edge of the index, the way a bigint identity does, while staying globally unique for distributed writers.

On new high-insert tables:

id uuid PRIMARY KEY DEFAULT uuidv7()

On 17 and older, generate v7 in the application or an extension; do not pretend gen_random_uuid() is the same thing. v7 leaks an approximate creation time. If that is a threat model problem, use identity columns plus a separate opaque public id.

Then stop indexing as if skip scan did not exist. Postgres 18 can use a multicolumn B-tree when the leading column is missing, by enumerating distinct leading values, if that leading column has low cardinality (status flags, a small tenant set). That is a reason to keep a well-ordered composite index instead of creating a second index for every query shape. Confirm with EXPLAIN: you want an index scan with extra index searches, not a sequential scan you "fixed" by adding another 2 GB index.

Pick the access method for the query you run:

• Covering INCLUDE so hot lookups never heap-fetch.
• GIN for jsonb containment, arrays, and full-text. B-tree on a jsonb column is usually the wrong tool.
• BRIN for large, naturally ordered time-series. Tiny, and excellent when block min/max matches reality.
• Partial indexes for the hot subset (WHERE status = 'open'), not a full index you filter in the query.

Drop unused indexes. pg_stat_user_indexes with zero scans over a business cycle is a write tax you chose. On write-heavy tables, fillfactor and HOT updates are the companion, not a fourth hack: leave room on the page so updates do not scatter.

Partitioning is not a personality. Range-partition time-series when vacuum, indexes, or drop-old-data hurt. Do not partition a 2 million row table because a talk said 10 million. The planner and your ORMs will make you pay for the extra relations.

How this shows up on real products

We see the same three failures on app and custom software builds that grew out of a prototype: every request opens a connection, every table has a random UUID, and nobody has looked at pg_stat_statements since staging. The buy-versus-build question in our custom versus off-the-shelf guide does not save you if the system you keep (or the one you build) treats Postgres as a black box.

If the slow path is a product, not a missing index, request a Discovery Session. Bring pg_stat_statements output if you have it. If you do not, that is the first hour.

PostgreSQL hacks FAQs

Do I need to upgrade to Postgres 18 this year?

If you are on 14, community EOL arrives in November 2026, so yes, plan a landing zone. 18.4 or later is the practical 18 target after the early minor-version noise; verify extensions (pgvector, trigram, anything you compiled) on a replica first. Estates already on 16 or 17 can wait for a quiet window, but AIO, skip scan, statistics-preserving pg_upgrade, and native uuidv7() are the reasons to schedule it this cycle rather than the next.

Do Neon, Supabase, or RDS change the pooling story?

They rename the endpoints. You still want a pooled URL for request traffic and a direct URL for migrations, LISTEN, and anything session-scoped. Check whether the host's pooler is transaction mode and whether it supports protocol-level prepared statements before you enable ORM prepares.

When is partitioning the wrong move?

When the table is not huge, when every query would have to touch every partition, or when your ORM cannot do partition-pruning predicates. Partition to make vacuum, drop, and cold-storage cheap, not because the row count felt impressive.

Can you review our Postgres before we scale the app?

Yes. Request a Discovery Session. We will say whether the next dollar belongs in pooling, indexes, an upgrade, or a simpler data model.