Indexing is not just adding CREATE INDEX. In 2026, it is an art form.

With PostgreSQL 18’s new Async I/O engine and the explosion of JSONB usage, the rules of optimization have changed. If you are still relying on default B-tree indexes and simple pg_dump backups, you are leaving 50% of your performance on the table.

This guide covers the “Senior Engineer” patterns for scaling Postgres to petabytes.

The Indexing Cheat Sheet (2026)

Defaulting to B-Tree for everything is a junior mistake.

JSONB Performance: The “Hybrid” Approach

“Postgres is slower than Mongo.” False. But only if you index correctly.

The Specific Index: jsonb_path_ops

By default, GIN indexes every key and value (jsonb_ops). This is huge. If you only query containment (@>), use jsonb_path_ops.

-- ❌ Standard GIN (Flexible but Big)
CREATE INDEX idx_data ON events USING GIN (payload);

-- ✅ Specialized GIN (Faster, Smaller)
CREATE INDEX idx_data_path ON events USING GIN (payload jsonb_path_ops);

The Hybrid Strategy

Don’t dump everything into JSONB.

• Columns : User ID, Created At, Status, Foreign Keys.
• JSONB : User Settings, Feature Flags, Metadata.

Connection Pooling: Supavisor vs. PgBouncer

In 2026, the debate is over.

• Supavisor : The cloud-native winner. Built in Elixir, it handles millions of connections and supports query load balancing. Essential for Serverless (Next.js/Edge).
• PgBouncer : The low-resource champion. Use it if you are running on a $5 VPS or strict embedded environments.

▲ 引用：XO Ruby - Scaling PostgreSQL

Vacuuming & Bloat

Postgres uses MVCC (Multi-Version Concurrency Control). Only updates create “dead tuples” (ghost rows). If autovacuum doesn”t run, your table bloats, and queries scan empty space.

Best Practice : Don’t run manual VACUUM FULL (it locks everything). Tune autovacuum_vacuum_scale_factor to run more frequently on high-churn tables.

Postgres 18 Features You Should Use

1. Async I/O : Postgres 18 finally pre-fetches data before the query executor asks for it. This is a game-changer for analytical queries on SSDs.
2. Generated Columns : Stop using triggers to update search_vector. Use GENERATED ALWAYS AS ... STORED.

Conclusion

Postgres scales to petabytes, but it asks for respect. Define your indexes with intent, pool your connections suitable for your architecture (Supavisor), and watch your dead tuples.

Treat your database like a state engine, not a bucket.