PostgreSQL
PostgreSQL review: our default database, and where it stops
It's the safe default for a production database: strong SQL, extensions like pgvector and no licence cost. The trade-offs you plan for are connection pooling and no built-in sharding.
The short answer
Postgres is the right default database for almost any product, and the exceptions are narrow enough that you should be able to name yours before reaching for something else.
Choose differently in two situations. A serverless deployment that opens a connection per request needs a pooler in front of Postgres, or it runs out of connections under real traffic. And a queue does not belong in a relational table just because the database is already there. We have built one system on MySQL instead of Postgres, for reasons specific to that build rather than a general preference.
What decides this
4 things that decide this
- 01Postgres is relational and open source, and it follows the database rules that keep data correct. That makes it a safe pick you rarely regret.
- 02Connection limits are the sharpest edge in serverless setups. Each function call can open its own connection, and the database runs out before the app does.
- 03Row-level security and add-ons like pgvector let one Postgres database cover access control and vector search. You do not need a second database for that.
- 04A queue is not a table. Using Postgres to hold job state works at small scale. At real volume, it turns into a locking problem.
A relational database that has been the safe default for three decades
PostgreSQL is an open-source relational database. Data lives in tables that link to each other, and you get it back out with SQL, a query language most engineers already know. None of that is new. That is the point.
What keeps it current is the range of add-ons. pgvector adds vector search for AI retrieval, with no separate vector database needed. PostGIS adds location queries. Row-level security sits inside the database itself, so access rules live next to the data they protect, not scattered through app code. One database now covers ground that used to need three.
PostgreSQL pros and cons
Pros
- No licence cost and a permissive open-source licence, so the database you start on is the one you can keep at any size.
- Standard SQL and a huge hiring pool. Nobody joins a team and has to learn a new query language first.
- Add-ons cover ground that would otherwise need a second system: pgvector for AI retrieval, PostGIS for location data, row-level security to keep tenants apart.
- It scales further than most teams expect, and read replicas cover most of the growth before harder work like sharding is ever needed.
- No vendor lock-in. A Postgres database moves between Supabase, Neon, RDS and a self-hosted server with a dump and restore, not a rewrite.
Cons
- Connection limits bite hard in serverless setups. Without a pooler in front of it, a burst of function calls uses up every connection and requests start failing.
- It is not a queue. We have seen job tables that worked fine at low volume start locking up once background jobs ran into the thousands per hour. We moved that work onto Redis instead.
- There's no built-in sharding. Read replicas carry most growth, but splitting writes across servers means an extension such as Citus or work in your own code, which a document store puts off longer.
- Migrations on a large table can lock it. A schema change that looks small can hold a table longer than a team expects, if nobody plans it around live traffic.
- Function invokedEach cold start opens a new connection
- No poolerConnections go straight to Postgres
- Traffic burstConcurrent invocations multiply connections
- Limit hitPostgres refuses new connections
- Requests failNot a Postgres bug, a missing pooler
This is the single most common way a serverless team blames the database for a problem in their own connection handling. A pooler in front of Postgres, run by the platform or self-managed, is what prevents it.
What we actually run, and the one time we did not
ZhoopZhoop runs Postgres directly under a FastAPI backend, holding shop operations and parts procurement for a multi-branch auto repair business. WAIQ runs it the same way for multi-site business operations and execution tracking. Both connect from a long-running server process, so the serverless connection problem never applies.
ExtraaJe runs Postgres through Supabase, using row-level security to keep each employee's data apart on a European workforce platform. TrialTriage runs it through Neon's serverless tier, matching oncology patients to clinical trials. There, we had to plan connection pooling from the start, not add it after a traffic spike caused failures.
Our own product runs on it too. SalesCrew, the sales CRM we built and run, gives every client a separate database. Each instance has its own Supabase stack with its own Postgres, storage and functions. That's the cleanest answer we know to the question a security reviewer asks about shared tables. It also means your client can take a full Postgres dump of their own data on request.
We built Elevent, a live multiplayer trivia platform for corporate events, on MySQL instead. That choice fit the way that system handles live sessions and leaderboards. It was not a verdict against Postgres as a whole. Boring is a feature, but it is a default, not a rule with no exceptions.
- 01A long-running server process (not serverless) removes the connection-pooling question entirely.
- 02Supabase and Neon both add pooling for you, but their defaults differ and it is worth reading which one you are on.
- 03Row-level security is worth the setup cost once more than one tenant shares a table.
Systems running on this database
What teams ask before committing
01What are the pros and cons of PostgreSQL?+
On the plus side, you get standard SQL with a deep hiring pool and no licence cost. Extensions like pgvector and PostGIS replace a second database, and row-level security lives inside the database. There's no lock-in either: a dump moves between Supabase, Neon, RDS and your own server. On the minus side, connections run out fast in serverless setups without a pooler, and there's no built-in sharding. A job queue in a table starts locking at volume, and a careless migration can lock a large table. For most products the pros win, and the cons are things you plan for rather than reasons to pick something else. If you're weighing a document store instead, our Postgres vs MongoDB comparison covers that split.
02Postgres or MySQL for a new startup?+
Postgres, by default, for most products. It sticks closer to the SQL standard and has more add-ons, including pgvector for AI retrieval, which most new products end up wanting. MySQL still earns a place in some cases, such as read-heavy apps built around a simpler setup for copying data across servers.
03Is Postgres enough for a real production system, or do we need something else alongside it?+
For most products, yes, on its own. Two jobs sit better outside it. Background job processing at real volume runs better on a dedicated queue such as Redis. Pure key-value caching also fits a table poorly, since a table was never designed for it.
04What breaks first as a Postgres-backed product grows?+
Connection handling, almost always. This shows up most in serverless setups, where each call can open its own connection. A pooler in front of the database, planned before launch rather than bolted on after an outage, is what stops it.
05Do we need a separate vector database for AI features?+
Not at moderate scale. The pgvector add-on puts similarity search into a table you already have, so retrieval sits next to the rest of your data. A dedicated vector database earns its place once search runs at a volume that starts to strain Postgres itself.
