SSiddiq.in
The database you'll still love in 10 years

Most "database problems" are two missing indexes and one query that hasn't been read in three years.

Postgres is the most under-appreciated superpower in a modern stack. This page is what we actually do when we're brought in to make one fast — read EXPLAIN, add the right index, pick the right extension, and leave it running for years.

Postgres audit / tuning
EXPLAIN, in plain English

A 30-ms query has three properties. Learn to spot them.

The slow query
Seq Scan on orders  (cost=0.00..48291.00 rows=2)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 998,342
  Buffers: shared read=4123
  Planning Time: 0.2 ms
  Execution Time: 812 ms
  • × Seq Scan on a million-row table
  • × 998k rows read, 2 returned
  • × 4123 buffers pulled from disk
After: CREATE INDEX ON orders(customer_id)
Index Scan using orders_customer_id_idx
  Index Cond: (customer_id = 42)
  Buffers: shared hit=6
  Planning Time: 0.1 ms
  Execution Time: 1.2 ms
  • ✓ Index Scan, direct hit
  • ✓ 6 buffers from cache
  • ✓ 812ms → 1.2ms (~680× faster)
The index taxonomy

Six index types. Most codebases use one.

B-tree

The default. Equality, range, ORDER BY.

Hash

Equality only, smaller than B-tree, rarely worth it.

GIN

JSONB, arrays, full-text search. The workhorse of tsvector.

GiST

Geometric data, ranges, tsvector. PostGIS lives here.

BRIN

Massive append-only tables with natural ordering (time-series).

Partial

`WHERE deleted_at IS NULL` — index only live rows. Smaller, faster.

Extension picks that earn their keep

Four extensions we install by default. Two we hold back.

Install day one
  • pg_stat_statements · answers "what's slow" instantly
  • pgcrypto · UUIDs, hashes, encryption without app-layer libs
  • unaccent · search that treats "café" and "cafe" as the same
  • pg_trgm · fuzzy match without a separate search index
Hold back until you need them
  • pgvector — only when semantic search is a real product feature
  • PostGIS — big install; only when geospatial matters
  • TimescaleDB — for time-series only; changes upgrade paths

PostgreSQL FAQ

Two queries answer 90% of it: `pg_stat_user_indexes` (which indexes are used) and `pg_stat_user_tables` (which tables are seq-scanning). If a table has millions of rows and `seq_scan > idx_scan`, you have a problem.

Index reference

Six index types. Picking wrong is the most common Postgres bill spike.

B-tree (default)
Equality + range on scalar columns. 95% of indexes. Multi-column: leftmost-prefix rule applies — order matters.
Hash
Equality only, single column. Faster than B-tree for exact match, no range support. Rarely worth it since PG 10 made B-tree very good.
GIN
Inverted index for arrays, jsonb, tsvector (full-text). Slow to update, fast to query. Use for search, tags, `@>` containment.
GiST
Geometric, range types, exclusion constraints. `EXCLUDE USING gist (room WITH =, during WITH &&)` for no-double-booking.
BRIN
Block Range INdex. Tiny footprint for huge append-only tables sorted by insert time (logs, events). Bad on random-order data.
Unique + partial
`CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL` — enforce soft-delete uniqueness cleanly. Underused; changes lots of schemas.

Book a Postgres tuning session.

Get an audit
Right tech, honest advice

Not sure if this is the right stack for you?

Tell us your goals — we'll recommend the right technology honestly, even if it isn't this one.

  • Personal reply from Siddiq within 24 hours
  • Honest advice — no pushy sales, no fake urgency
  • Clear next steps, timeline, and estimate

We only use your details to reply to this enquiry. Privacy.