PostgreSQL

PostgreSQL is my relational database of choice. It delivers what serious applications need out of the box: strict ACID transactions, referential integrity, window functions, full-text search, and a concurrency model that holds up under real load.

The extension ecosystem is what keeps it relevant: I use JSONB for flexible schema when relational strictness would get in the way, PostGIS for geospatial, and pgvector for embedding-backed similarity search alongside standard relational data.

Operationally it is dependable and well understood — logical replication, PITR backups, careful index tuning, and EXPLAIN-driven query optimisation keep it fast and recoverable in production.

The transactional model is trustworthy by default. ACID and referential integrity give me a safety net I do not have to re-invent, so the database enforces the invariants while the application stays focused on the domain.

The SQL feature set is genuinely rich. Window functions, CTEs, and partial indexes let me express complex queries and efficient indexes in the language of the database instead of contorting an application layer to fake the same outcomes.

JSONB is what makes Postgres flexible when it must be. When a shape is genuinely unstructured or evolving, I store it as JSONB and still query it relationally — getting document-style leniency without giving up a mature database underneath.

The extension ecosystem keeps it strangely current. PostGIS for geospatial, pgvector for embeddings, and more mean the same engine I trust for money also handles location and similarity search without a stack of separate systems.

Operations are deliberately boring. Logical replication for scaling and failover, point-in-time backups for recovery, and careful index tuning keep it fast and restorable — the quiet reliability a production system depends on.

Optimisation is evidence-based. Whenever a query is slow I start with `EXPLAIN`, then index or restructure according to what the plan and the data actually show, rather than guessing at syntax

I start from `EXPLAIN` before touching a query. Reading the plan and the statistics tells me the real bottleneck, and changes follow evidence rather than guesswork.

Index design follows the access patterns. I build composite and covering indexes around the actual queries the workload runs, and let the plan confirm they are used.

The transactional model is the safety net. ACID and referential integrity enforce the invariants, so the application does not have to re-invent correctness.

JSONB is used where a shape is genuinely flexible. I store it, query it relationally, and keep the document-style leniency without giving up a mature engine.

The extension ecosystem keeps it current. PostGIS for geospatial and pgvector for embeddings mean the same trusted engine also handles location and similarity search.

Operations are deliberately boring. Logical replication for scale and failover, point-in-time backups for recovery, and careful tuning keep it fast and restorable.

Migrations are versioned and forward-safe. Schema changes apply predictably across environments, so the schema evolves without drift.

Caching sits above the database to protect the hot path, keeping the expensive queries rare and the common ones fast.

When Postgres and a document or search engine each fit a slice better, I use both. The relational engine carries the transactional core; the others serve their strengths.