PostgreSQL and data modelling
When an application is slow, the database is the first place to look and usually the last place anyone looks. Most of what gets called performance optimisation is really a missing index and a query written inside a loop.
Discuss your projectWhen we reach for it
- Essentially every project — this is the default database and the burden of proof is on any alternative
- Anything with relational integrity requirements, which is more systems than claim to have them
- Workloads that would otherwise reach for a separate search engine, cache or queue before they need one
- Systems where correctness under concurrency actually matters — money, stock, bookings
When we don’t
- Genuinely enormous append-only telemetry, where a purpose-built time-series store earns its complexity
- Estates already standardised on SQL Server with real dependencies on it
- Caching layers, where Redis is the right tool alongside rather than instead
This half is the useful one. A firm that has never declined to use a technology has never chosen one.
What we have learned about the parts of PostgreSQL that bite — written from projects rather than from documentation.
Constraints belong in the schema
A rule enforced only in application code is a rule that will be violated — by a migration script, an admin fixing something by hand, a second service, or a bug in one of four code paths. Foreign keys, unique constraints, check constraints and not-null are not bureaucracy, they are the last line of defence, and they are enforced regardless of what wrote the row.
The most common form of data corruption we find in inherited systems is not dramatic. It is a nullable column that should never have been null, filled in by a code path that no longer exists, now poisoning every report that touches it.
Where the time actually goes
Queries inside loops — the pattern where fetching a list then fetching each item’s relations produces a hundred queries where one would do. It is invisible at development scale and dominant in production.
Missing indexes on the columns actually filtered and sorted on, which is often not the columns someone guessed at. And indexes that exist but are unusable because the query wraps the column in a function. Reading the query plan answers all of these in minutes, and it is a skill that is oddly rare.
Postgres does more than people use it for
Full-text search that is entirely adequate up to a substantial scale, removing a search engine from your architecture. JSONB where part of the model is genuinely schemaless, without abandoning relational integrity for the parts that are not. Row-level security for multi-tenancy enforced by the database rather than by every query. Listen/notify for lightweight eventing. Range types for booking systems, with exclusion constraints that make double-booking impossible at the storage layer.
Each of these removes a component from your architecture, and every component removed is one fewer thing to operate, monitor and pay for.
PostgreSQL and data modelling, applied
The shapes of system this technology is actually good at, rather than the ones it is capable of.
Usually alongside
- PostgreSQL
- Prisma
- Drizzle
- pgvector
- PostGIS
- TimescaleDB
- pgBouncer
- Redis
- Data models designed against the real domain rather than the first draft of it
- Migration strategies for zero-downtime schema changes
- Query and index performance work, driven by query plans
- Multi-tenancy with row-level security enforced in the database
- Reporting structures and materialised views that do not contend with production load
PostgreSQL, answered
PostgreSQL or MySQL?
PostgreSQL, unless there is a specific reason otherwise. Stricter default behaviour that catches errors rather than silently coercing them, materially better handling of complex queries, and a set of capabilities — JSONB, full-text search, row-level security, range types with exclusion constraints — that each remove a separate component from your architecture. MySQL is a fine database and is the right answer where your team or your hosting is already committed to it, but on a green field the burden of proof sits with the alternative.
Can you make our slow application faster?
Usually, and it usually is the database rather than the application. We profile first rather than optimising on instinct, and the findings are consistent across projects: queries executed inside loops, indexes missing on the columns actually filtered on, and indexes present but unusable because a function wraps the column. You get a written list ranked by impact against effort. Some items you can fix yourselves in an afternoon, and we will say which those are.
Do we need a separate search engine?
Probably not, at least not yet. PostgreSQL full-text search handles a substantial catalogue perfectly well and removes an entire component from your architecture — one fewer thing to run, monitor, keep in sync and pay for. A dedicated search engine earns its place when you need typo tolerance, sophisticated relevance tuning, faceted aggregation at scale, or sub-fifty-millisecond response across millions of documents. Start with Postgres and move when a measurement says to, not when an architecture diagram suggests it.
How do you handle database migrations without downtime?
By making every change backwards-compatible for one release. Add a column before anything writes to it; deploy code that writes to both old and new; backfill; deploy code that reads the new one; only then drop the old. It is more steps than a single destructive migration and it means a deploy never requires a maintenance window, and never leaves the system broken in the interval where old and new code are both running. We also test migrations against a copy of production data, because the timing on a large table is where the surprises are.
Where this shows up
- BuildCustom Software DevelopmentCustom platforms, marketplaces and SaaS. We write the parts that are specific to your business and buy the parts that aren’t.Read more
- RunCloud & DevOpsDeploys that are boring on purpose. Infrastructure as code, real environments, and a bill you can read line by line.Read more
- BuildWeb Application DevelopmentDashboards, portals and internal tools that hold up under real data volumes — not a prototype that falls over at ten thousand rows.Read more
Building with PostgreSQL?
Whether it is a new build, a takeover or a second opinion on an architecture someone else chose — the first conversation is free and we will tell you if the stack is wrong for the job.