Why Postgres is my default for relational data

Five years ago I switched from MySQL to Postgres. Ten-plus production projects later, the answer is still Postgres. Every time. The question I ask on a new project is not 'why Postgres' but 'what would have to be true for me to pick anything else?'

I came over from MySQL, and never went back

Before Postgres, I always reached for MySQL. Five years ago I switched, and the only MySQL database I still touch now belongs to one legacy client.

The honest version of why I switched: it felt heavier and slower under load than Postgres did on the same hardware. I have not benchmarked that to a paper-grade conclusion, and I am not going to pretend otherwise. But every time I sat the two side by side on a similar VPS, Postgres needed less to do the same job, and the things I wanted to do (logical replication, mixing relational and document data in one row) were either easier or only possible on the Postgres side.

That was enough. Once a tool earns the default, it stops needing to win every comparison.

The built-in types do most of the work

The reason Postgres earns the default is that it stops being just a relational database the moment you need it to. Almost every project I ship leans on the same handful of built-in types.

I use citext for every email column, so [email protected] and [email protected] are the same value to the database without me writing LOWER() in every query. I use jsonb (part of Postgres since 9.4, released December 2014) for the parts of a schema that change shape too often to normalise: payment-transaction metadata, settings blobs, anything where a third-party API decides what comes back. JSONB gives me GIN-indexed key lookups, partial-document updates, and lives in the same row as my relational columns.

I use tsvector for in-app search. None of the projects I have shipped on Postgres have needed a separate search engine on top. Range types come out whenever a row needs a 'valid from / valid to' pair. Arrays earn their keep when a column is genuinely a small set of values rather than a real relation.

  -- a single table, several Postgres types 
  create table events ( 
      id          uuid primary key default gen_random_uuid(), 
      user_id     bigint not null references users(id), 
      type        citext not null, 
      payload     jsonb not null, 
      occurred_at tstzrange not null, 
      tags        text[] not null default '{}' 
  ); 
   
  create index events_payload_gin on events using gin (payload); 
  create index events_tags_gin    on events using gin (tags); 
  create index events_occurred    on events using gist (occurred_at); 

TimescaleDB is the one extension I keep installing

The extension ecosystem is what turns Postgres from a database into a platform, but I only install the extensions I actually use. In practice, that has mostly meant TimescaleDB.

I reach for it when rows arrive constantly and queries want time-bucketed aggregates. Application logs are the obvious case. The less obvious case was an employee-tracking app where timesheet entries pile up fast and the dashboards want yesterday, last week, last month at a glance. Hypertables and continuous aggregates do the work I would otherwise be hand-rolling with materialised views and a cron, and they live inside the same Postgres instance. Backup, replication, and credentials all stay one story.

That last point matters more than the feature itself. Bringing a second database into production has a real cost in operational surface area. An extension does not.

How I actually run it in production

There are two ways a Postgres database ends up in my projects. Either it runs in a Docker container on a VPS the client owns, or it runs on Railway. The choice is mostly the client's preference, and either is fine.

When it is on a VPS, the operational pattern has settled into the same shape on every project. pg_dump runs on cron and ships the dump to an S3 bucket. VACUUM ANALYZE runs on a schedule. pg_stat_statements stays installed so I can see what is actually slow when something feels slow. When a project needs a read replica or a zero-downtime migration, logical replication (which has been built into Postgres since version 10, in 2017) does the work.

That is the whole story. The setup is dull, which is what I want from the data layer.

  -- enable once, query forever 
  create extension if not exists pg_stat_statements; 
   
  -- the ten worst offenders, by total time 
  select 
      substring(query, 1, 80) as query, 
      calls, 
      round(total_exec_time::numeric, 1) as total_ms, 
      round(mean_exec_time::numeric, 2) as mean_ms 
  from pg_stat_statements 
  order by total_exec_time desc 
  limit 10; 

Postgres at the core, Redis at the edges

There is one place I do not stretch Postgres: caching. When I need a cache, I reach for Redis, sitting next to Postgres rather than competing with it. Anything that should not survive a restart, anything that wants sub-millisecond reads, anything I would feel guilty putting in a relational table goes there.

Beyond that, I do not have a practised opinion on the alternatives. Across the ten-plus Postgres projects I have shipped in the last five years, the same combination handles a combined thousand-plus daily users, and on a few of them gigabytes of data. Nothing in that workload has asked for a third database yet.

Postgres at the core, Redis at the edges. Five years in, that is still the whole stack.

Why Postgres is my default for relational data

I came over from MySQL, and never went back

The built-in types do most of the work

TimescaleDB is the one extension I keep installing

How I actually run it in production

Postgres at the core, Redis at the edges

Keep reading.

The Laravel service container, demystified

What Inertia v3 actually fixed