Why this series? What I've learned running PostgreSQL in production

In every project I build today, the data lives in PostgreSQL: in a field management system used by municipalities, in an analytics platform where dozens of background workers write at the same time, and in the blog you are reading. In this series I'll write about the problems I've run into using PostgreSQL in production and how I solved them.

#Why I'm writing this series

PostgreSQL's documentation is excellent. It tells you what a feature does. But problems in production rarely arrive as "what does this feature do?" They arrive as "how do I change this while the system is running?"

My most instructive moments have always looked like that. Adding per-organization data isolation to a schema that was already live. Changing the ORM and the schema approach while the system was in use. Stopping an ORM migration from silently dropping tables I had written by hand for performance. None of this is on the first page of the docs.

That is exactly the kind of topic this series covers, with examples from real systems.

#Where the examples come from

Most examples come from three systems:

• Biocidal: a multi-tenant field management system, live since 2020, that municipalities and pest control companies use to run their operations. Each organization's data is kept separate. PostGIS finds which area of responsibility a point falls in, and pesticide stock is tracked by lot and expiry date.
• KillReport: a real-time killmail tracking and analytics platform for EVE Online. About 45 background workers write to the same database. The daily counters behind the leaderboards are incremented in the same transaction as the record itself, so no separate scheduled job is needed.
• This site: blog posts and the newsletter live in PostgreSQL. Newsletter emails are sent from a job table read by a separate worker. Workers claim jobs with FOR UPDATE SKIP LOCKED, so two workers never send the same email, and a failed send is retried up to five times with a delay.

I'll simplify the examples, but I won't replace them with made-up ones. When an approach worked for me, or didn't, I'll explain why, together with the conditions of the real system.

#How the series is organized

The series has 12 parts in five groups:

Foundations

1. Getting the data model right: where normalization should stop

Query performance

1. Indexes: when to use B-tree, GIN, GiST and partial indexes
2. Learning to read EXPLAIN ANALYZE
3. Finding slow queries: pg_stat_statements
4. The N+1 problem and DataLoader on the GraphQL side

Concurrency and growth

1. Locks, transactions and deadlocks
2. Large tables: partitioning and archiving
3. VACUUM, autovacuum and table bloat

Special topics

1. Spatial queries with PostGIS
2. Multi-tenant architecture: schema, row or separate database?

Operations

1. Backups, PITR and replication
2. Connection pooling: PgBouncer and how it works with ORMs

I write each part in Turkish first; the English version follows. Every part is meant to stand on its own. Still, reading in order makes things easier: EXPLAIN ANALYZE output is hard to read without understanding indexes, and so is a stuck VACUUM without understanding locks.

#Who it's for

This series is for developers who use PostgreSQL behind an application. Basic SQL is enough. If you use an ORM like Prisma or TypeORM and wonder what happens underneath, it's especially for you.

I'm not writing a DBA handbook. The focus is on what someone building applications will actually run into in production. Next to the SQL, the examples include TypeScript and Prisma code where it helps.

#What "doing it right" means

By "right" I don't mean a flawless recipe. Most decisions have a cost. An index speeds up reads and slows down writes. Whatever path you take in a multi-tenant architecture, you give something up. Rather than handing you the right answer to memorize, I'll try to show what I look at when I make that decision.

#Where to start

The first part starts at the foundation of everything: the data model. Where normalization pays off, where it should stop, and why changing a schema later is so expensive: read it here.