Data Engineering·4 min read·June 12, 2025

The Postgres patterns we keep coming back to

Across years of long-term client projects, the same handful of Postgres habits keep proving their worth. None of them are clever. All of them save you from a bad afternoon later.

For long-term client projects, our database is Postgres, and after enough of those projects you stop making fresh decisions every time and start carrying a set of habits. None of these are clever. Clever is not what you want from a database. What you want is to not be surprised, and these are the patterns that have most reliably kept us un-surprised.

Get the schema right early, because it is the expensive part

Application code is cheap to change. A schema with two years of data in it is not. So at the start of a long-term project, the schema gets real attention — proper types, real constraints, foreign keys that actually exist, NOT NULL where the data is genuinely not null.

This feels slow on day one and it is the single biggest favour you can do your future self. A constraint in the database is a rule that cannot be violated no matter which code path, which migration, or which manual fix runs against it. A rule that only lives in application code is a rule that holds until the day it does not.

Migrations are code, and they get reviewed like code

Every schema change is a migration, in version control, reviewed, applied the same way in every environment. No changes made by hand in a console, ever. The console change that "just" fixes one thing in production is how environments drift apart, and drifted environments are how you get a bug that only happens in production and that nobody can reproduce.

For long-lived projects we also care that migrations are reversible and that they are safe to run against a live database — a migration that takes a heavy lock on a large, busy table is a migration that causes an outage, and that is a property you want to know about before you run it, not after.

Index deliberately, from real query patterns

Indexes are not free — they cost write performance and storage — so they get added in response to actual query patterns, not sprinkled around hopefully. We look at what the application actually asks the database, we look at the query plans, and we index for those. Then, periodically, we look for indexes nothing uses and remove them, because an unused index is pure cost.

Let Postgres do more than you remember it can

The recurring mistake we see — and have made — is pulling data out of Postgres to do in application code something Postgres would have done better and faster itself. It does JSON well. It does full-text search well enough that many projects never need a separate search service. It has window functions, rich date handling, materialised views. A surprising amount of "we need another piece of infrastructure for this" is actually "we did not check whether Postgres already does this". Usually it does.

Plan for data growth on the tables that will have it

Most tables stay small forever and need no special thought. But on a long-term project there are usually one or two tables — events, logs, time-series, history — that grow without bound, and those few get a plan: partitioning, a retention policy, an archival path. You do not need to solve it on day one. You need to know which tables it applies to, so that when they get big it is a planned step and not an emergency.

The pitfalls

  • Putting off the schema. "We'll tighten it later" becomes "we have two years of data that does not satisfy the constraint we now want". Later is more expensive than it sounds.

(If you have ever wondered when we add ClickHouse to a Postgres setup, the trigger is contention, not raw slowness — that is a separate conversation.)

  • The quick manual production fix. It is never quick, in the sense that matters. It is a permanent, invisible difference between production and everywhere else.
  • Indexing by vibes. Both directions hurt — too few and reads crawl, too many and writes crawl and the disk fills. Index from evidence.
  • Reaching for new infrastructure reflexively. Check whether Postgres already does it. The answer is yes more often than people expect.

What we would tell you

There is no secret here. Postgres rewards boring discipline: a schema you took seriously, migrations treated as code, indexes added from evidence, and a willingness to actually use the database you already have. Do those things and Postgres becomes the part of the stack you think about least — which, for the component holding all of your data, is precisely the goal.

PostgreSQLDatabasesArchitectureBest Practices

Working on something similar?

We have probably hit the exact scaling or architectural bottlenecks you are navigating. Book a call and we'll walk through the technical trade-offs.