Postgres for Product Engineers: Indexing, JSONB, RLS, and the 20% That Ships Millions of Users

Ninety percent of Postgres performance problems are one of three shapes: the planner picked a bad plan, the plan is fine but I/O is unnecessary, or the query is holding a lock it shouldn't. Everything in this guide is aimed at making those three failure modes visible early. It pairs with Modern data stack 2026 (where Postgres fits vs the warehouse), dbt in anger (when you want to move analytics off Postgres), and vector databases compared 2026 (why pgvector is the right default for RAG).

The North Star: every product-critical query has a known plan, a measured p95, and an owner. Anything else is a future incident.

Postgres ships more index types than any developer needs to memorize. Here's what actually matters in production.

• B-tree — equality and range on ordered types. The default. If you're unsure, this is the answer.
• Hash — equality only, WAL-logged since PG10. Rarely worth it over B-tree; skip unless you've measured.
• GIN — inverted index for arrays, JSONB, tsvector, trigrams. Slower writes, brilliant reads.
• GiST — ranges, geometry, exclusion constraints. The right pick for tstzrange overlap constraints.
• SP-GiST — non-balanced trees for data that doesn't fit GiST well (prefixes, quad-trees). Niche but powerful.
• BRIN — block-range index for huge, naturally-sorted tables (logs, events, telemetry). Tiny on disk, effective when correlation is high.
• Partial — any index type + WHERE. Half the size, same speed for the hot path.
• Covering — B-tree with INCLUDE (col1, col2). Index-only scans, no heap fetch.
• Expression — index on lower(email) or (data->>'status'). Restores planner sanity for functional predicates.

Reading a plan takes ten minutes to learn and pays for the rest of your career. Always ask for ANALYZE, BUFFERS — without them you're reading fiction.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, FORMAT TEXT)
SELECT o.id, o.total_cents
FROM orders o
WHERE o.tenant_id = $1
  AND o.created_at >= now() - interval '30 days'
ORDER BY o.created_at DESC
LIMIT 50;

Reading order:

1. Start at the innermost node — that's where work begins.
2. Compare rows= (estimate) to actual rows=. A 10× gap means bad stats; ANALYZE the table.
3. Look for Seq Scan on tables > 100k rows — usually a missing or unusable index.
4. Buffers: shared read = cold cache, shared hit = warm. Cold reads at p99 mean your working set doesn't fit RAM.
5. Watch for Sort nodes writing to disk (external merge) — add an index that already returns rows in the right order.

JSONB is fantastic for flexibility and terrible as a substitute for schema. Four rules I enforce in every code review:

1. Extract fields you filter, sort, or join on into real columns with real types and real constraints.
2. Add a GIN index using jsonb_path_ops — smaller and faster than the default operator class for containment.
3. Validate shape at the app boundary (Zod, Pydantic, Valibot). Postgres will happily store garbage.
4. Cap the total JSONB size per row via a CHECK constraint — otherwise one bad producer stores 5MB documents and destroys your cache hit ratio.

ALTER TABLE events
  ADD COLUMN status text GENERATED ALWAYS AS (data->>'status') STORED,
  ADD CONSTRAINT events_payload_size CHECK (octet_length(data::text) < 65536);

CREATE INDEX events_status_created_idx ON events (status, created_at DESC);
CREATE INDEX events_data_gin ON events USING gin (data jsonb_path_ops);

Generated columns give you the ergonomics of nested data with the planner behavior of typed columns. This is the pattern I reach for on every new JSONB column past its first week in production.

RLS is the security boundary and a planner input. The policy runs on every query touching the table — a slow function inside a policy will turn a 5ms query into 5 seconds. Rules:

1. Every column referenced by a policy must be indexed in a way the planner can use.
2. Never call slow or non-STABLE functions inside a policy body.
3. Wrap tenant lookups in a SECURITY DEFINER helper that caches per-transaction.
4. Test policies with SET ROLE in CI — not by trusting your app to send the right session variables.

CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid
  LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
  AS $$ SELECT (auth.jwt() ->> 'tenant_id')::uuid $$;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_tenant_id());

CREATE INDEX documents_tenant_id_idx ON documents (tenant_id);
CREATE INDEX documents_tenant_created_idx ON documents (tenant_id, created_at DESC);

For the RAG side of RLS (chunk-level authorization) see RAG that actually works — same policy engine, different collection.

Every Postgres connection costs ~10MB of RAM and a process. Ten thousand serverless functions with one connection each will exhaust a large instance before any of them run a query. PgBouncer sits between your app and Postgres, multiplexing thousands of client connections onto a small pool of real backends.

In transaction mode you must disable client-side prepared statements or use a driver that opts into the PgBouncer-safe protocol. Otherwise you'll see prepared statement "S_1" already exists under load and spend a weekend debugging it.

The default isolation level is READ COMMITTED. It's fine for most reads and terrible for write-heavy critical paths that need serial-order guarantees.

• READ COMMITTED — default. Lost updates possible. Wrap money moves in explicit row locks.
• REPEATABLE READ — snapshot isolation. Perfect for read-only analytics inside an app.
• SERIALIZABLE — full serializability. Higher retry rate; the right answer for booking, inventory, banking.

-- Prevent double-charge without SERIALIZABLE
BEGIN;
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
UPDATE accounts SET balance = balance - $2 WHERE id = $1;
INSERT INTO ledger(account_id, amount) VALUES ($1, -$2);
COMMIT;

For deadlocks, order matters: every code path that touches multiple rows should acquire locks in the same order (usually by primary key ascending). That eliminates 95% of deadlocks. Log them via log_lock_waits = on so they aren't silent.

Postgres uses MVCC — every UPDATE creates a new row version and marks the old one dead. Autovacuum reclaims that space; when it falls behind, tables bloat, indexes bloat, and even simple queries slow down. The pathological case is a small hot table (a queue) that autovacuum can't keep up with because scale-thresholds are set globally.

ALTER TABLE job_queue SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = 2
);

Monitor:

• pg_stat_user_tables.n_dead_tup / n_live_tup — anything above 20% for a hot table is a red flag.
• pg_stat_progress_vacuum — live progress; if the same table shows up in every snapshot, autovacuum is losing.
• Index bloat via pgstattuple — schedule REINDEX CONCURRENTLY quarterly for the top-5 hottest indexes.

Declarative partitioning by RANGE on a timestamp is the right hammer for time-series data past 50GB in a single table. It gives you cheap DELETE (drop partition), constrained scans (planner prunes irrelevant partitions), and parallel-friendly maintenance.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  data jsonb NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Rules that keep partitioning from becoming its own disaster:

• Automate partition creation (pg_partman or a nightly cron) — humans forget the 1st.
• Include the partition key in every unique/PK constraint. Postgres will refuse to help you break this.
• Do not partition until you actually need to — under 50GB it usually costs more than it saves.

Automated snapshots are not a backup strategy — they're a checkbox. A real backup strategy is: pgBackRest or WAL-G streaming to object storage, encrypted at rest, tested every quarter with a full restore into a staging cluster and a business-verified query.

• RPO (recovery point) — WAL archiving gives you seconds; nightly dumps give you 24 hours. Pick per data class.
• RTO (recovery time) — measure it. If your last restore drill took 6 hours, that's your RTO whether the runbook says so or not.
• Logical vs physical — physical for whole-cluster PITR, logical (pg_dump) for cross-version or partial restores.

-- ❌ suppresses index on email
SELECT * FROM users WHERE lower(email) = $1;
-- ✅ functional index
CREATE INDEX users_lower_email_idx ON users (lower(email));

-- ❌ OR with different columns often defeats indexes
SELECT * FROM orders WHERE user_id = $1 OR email = $2;
-- ✅ UNION ALL of two indexed queries
SELECT * FROM orders WHERE user_id = $1
UNION ALL
SELECT * FROM orders WHERE email = $2 AND user_id <> $1;

-- ❌ OFFSET 100000 — Postgres still scans all skipped rows
SELECT * FROM events ORDER BY created_at DESC OFFSET 100000 LIMIT 50;
-- ✅ keyset pagination
SELECT * FROM events
WHERE created_at < $last_seen
ORDER BY created_at DESC LIMIT 50;

-- ❌ SELECT * in hot paths
SELECT * FROM orders WHERE id = $1;
-- ✅ project only what you need; enables index-only scans

1. pg_stat_statements — top queries by total time, mean time, and calls. Fix the top 10 and you fix 80% of the pain.
2. auto_explain — log plans for anything slower than 500ms. Free flight recorder.
3. pg_stat_user_indexes / pg_stat_user_tables — unused indexes, sequential-scan counts, dead tuples.

Wire these into whatever you already have for AI observability — same principles apply to any high-throughput service.

• pgvector — HNSW vectors; the default for RAG in Postgres. See vector DBs compared.
• pg_stat_statements — non-negotiable observability.
• pg_trgm — fuzzy / substring search with GIN.
• pgcrypto — UUIDv7, hashing, HMAC without leaving the DB.
• pg_partman — automated partition maintenance.
• pgaudit — auditable log stream for compliance.
• timescaledb — if time-series is the whole workload, promote from stock partitioning to Timescale.

Read replicas add lag, not scale. Reach for one when: primary CPU sits above 60% during peaks, your slowest queries are already indexed, and you have a business acceptance for eventual consistency on the affected reads (analytics dashboards yes, read-your-writes flows no).

Before adding a replica, always try: (1) fixing the top-5 pg_stat_statements queries, (2) adding covering indexes so hot reads never touch the heap, (3) moving heavy analytics to a warehouse — see modern data stack 2026 and BigQuery cost control.

• Every DDL takes an ACCESS EXCLUSIVE lock on something. Read the docs before running it in prod.
• Use CREATE INDEX CONCURRENTLY — always, unless you're on an empty table.
• Split ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT into three steps (add nullable, backfill in batches, set NOT NULL).
• Use lock_timeout and statement_timeout in migrations so a stuck lock aborts instead of freezing the app.
• Run migrations via a tool that supports online DDL (Sqitch, Flyway with baseline, or Reshape / pg-osc for large tables).

1. pg_stat_statements enabled and scraped.
2. auto_explain configured (>500ms, sample rate 1.0 in staging, 0.1 in prod).
3. PgBouncer in transaction mode, pool sized to (2 × vCPU).
4. Autovacuum tuned per hot table.
5. PITR via WAL streaming to object storage, tested restore in the last 90 days.
6. Every table has: PK, created_at, updated_at, tenant_id (where relevant), RLS policy, indexes for all policy predicates.
7. Every JSONB column has: GIN index, size CHECK constraint, extracted generated columns for hot fields.
8. Slow-query alerts wired into on-call.
9. Migrations run with lock_timeout and statement_timeout.
10. Monthly review of unused indexes (idx_scan = 0) and bloat.