PostgreSQL

What "relational" buys you

The database knows the shape of your data and refuses to break it. A column typed as a date cannot hold the string "soon". A foreign key means an order cannot reference a customer that does not exist. A unique constraint means two accounts cannot share an email address, no matter how many requests arrive at once.

Transactions extend the same guarantee across several statements: either all of them take effect or none do. Charging a card and creating an order either both happen or neither does, which is exactly what you want when a request fails halfway through.

That is the trade against document databases. Schemaless storage is faster to start with, and slower to live with, because every inconsistency your application ever wrote is still in there and every reader has to defend against it.

Not just tables

Postgres has absorbed a lot of what people used to run separate systems for. JSONB columns store and index unstructured documents, so you can be relational where it matters and flexible where it does not. Full-text search is built in. Extensions add capability without forking: PostGIS for geospatial queries, and pgvector for embedding similarity, which is what makes Postgres a common vector store for retrieval-augmented generation.

Row Level Security lets access rules live in the database itself rather than only in application code — the mechanism Supabase builds its permission model on.

What to expect in practice

Postgres is not the reason most applications are slow; missing indexes are. A query that scans a whole table stays fast in development with a thousand rows and collapses at a million.

Connections are the other common surprise. Each one costs memory, so a serverless environment that opens a connection per invocation will exhaust the limit quickly. A pooler such as PgBouncer, or the pooled connection string a managed provider gives you, is the standard answer.

Because it is open source and widely hosted, moving a Postgres database between providers is a dump and a restore rather than a rewrite — a meaningful hedge against vendor lock-in.