PostgreSQL

PostgreSQL is the relational database in every project here that needs one. The reason is not a single decisive feature — MySQL is also mature and fast — but an accumulation of things that matter when a system lives for years.

What actually decides it

Extensions. PostGIS is the one that settles the question for my work. It makes Postgres a spatial database with geometry types, spatial indexes, and coordinate transformations, and there is no comparable alternative in any other open-source database. For environmental and climate data this is not a preference.

Correctness at the edges. Postgres is conservative about accepting data it cannot represent faithfully. Constraint violations error rather than being coerced into something plausible, and transactional DDL means a failed migration rolls back rather than leaving the schema half-changed. That last point matters more than it sounds when Alembic migrations fail partway.

Rich types. jsonb with indexing, arrays, ranges, and proper enums mean the schema can express what the data actually is instead of encoding everything as text. jsonb in particular is a genuine escape hatch for data whose shape is not fully known — and one worth using sparingly, since a schemaless column inside a relational database gives up the guarantees the database was chosen for.

What to know operationally

Connection cost is real. Each connection is a process, so connection count matters much more than in databases using threads. An application opening connections freely will exhaust the server well before the hardware is stressed. A pooler — PgBouncer, or the application’s own pool — is not optional at any scale, and this is the most common way people are surprised by Postgres performance.

Vacuum is not optional either. MVCC means updates and deletes leave dead rows that autovacuum reclaims. On a heavily updated table with default settings, autovacuum can fall behind, tables bloat, and performance degrades gradually with no obvious cause. Knowing this exists is most of the fix.

Its defaults are conservative. Out-of-the-box settings assume a small machine, and shared_buffers and work_mem in particular usually need raising. A default-configured Postgres on a large server uses a fraction of what is available.

See also: PostGIS, SQLModel and Alembic for the application layer, MySQL and SQLite for comparison, and ORMs.