PostgreSQL

PostgreSQL 18 (September 25, 2025) introduces asynchronous I/O that delivers up to 3× sequential read performance on NVMe storage — a fundamental architectural improvement that applies automatically without application changes. Other key additions: `uuidv7()` for time-ordered UUIDs, virtual generated columns (now the default), OAuth authentication, temporal constraints on PRIMARY KEY/UNIQUE/FOREIGN KEY, and the ability to reference OLD/NEW in RETURNING clauses. The async I/O improvement alone justifies upgrading production systems on modern storage — pg_upgrade with retained optimizer statistics (a new 18 feature) makes the migration smoother than previous versions.

PostgreSQL leads MySQL on every adoption metric that matters. Stack Overflow 2025: PostgreSQL at 55.6% (#1), MySQL at 40.5% (#2) — a 15-point gap that has widened year-over-year. PostgreSQL is #1 most admired (65%) and #1 most desired (46%). Feature-wise, PostgreSQL has stronger ACID compliance, better JSONB support, a far richer extension ecosystem (pgvector, PostGIS, TimescaleDB), and more advanced SQL compliance. MySQL's advantage is ecosystem integration with PHP/WordPress and broader shared hosting availability. For new projects, PostgreSQL is the clear choice unless you have specific MySQL ecosystem requirements.

Yes, with pgvector. pgvector (21K+ GitHub stars) adds native vector similarity search to PostgreSQL — store embeddings alongside relational data, query them with HNSW or IVFFlat approximate nearest-neighbor search. A single query can combine semantic similarity (vector search), relational filters (WHERE in_stock = true), and JOINs (JOIN categories ON ...) — something impossible with a standalone vector database. pgvectorscale benchmarked 471 QPS at 99% recall on 50M vectors. For teams whose primary data is relational with AI search as a secondary feature, pgvector is the pragmatic choice over adding a separate vector database.

PostgreSQL uses one OS process per connection (~5-10MB each). An application with 500 persistent connections consumes 2.5-5GB of RAM just for connection overhead before a single query runs. The standard solution is PgBouncer in transaction-mode pooling: it sits between your application and PostgreSQL, multiplexing thousands of application connections over 10-50 database connections. In Kubernetes or serverless environments, we use PgBouncer as a sidecar or dedicated deployment. Supabase and managed PostgreSQL providers include connection pooling in their managed tier — a significant reason to prefer managed over self-hosted.

Row-level security is a PostgreSQL feature that adds database-level access control policies. Policies are defined as SQL predicates — `USING (tenant_id = current_setting('app.tenant_id'))` — and PostgreSQL automatically appends them to every query for non-superuser roles. This means tenant isolation is enforced by the database engine, not application code. Missing a WHERE clause in application code doesn't expose another tenant's data when RLS is properly configured. It's the right pattern for any SaaS application storing multiple tenants in the same schema. The operational overhead is minimal; the security upside is significant.

At 100M+ rows, three techniques matter most: (1) Partitioning — declarative range or hash partitioning with partition pruning so queries only scan relevant partitions; PostgreSQL 18's parallel partition operations improve maintenance. (2) Proper indexes — a sequential scan on 100M rows is catastrophic; composite indexes covering query predicates, GIN for JSONB, BRIN for time-ordered data. (3) Table bloat management — high UPDATE/DELETE tables accumulate dead tuples; autovacuum must be tuned with `autovacuum_vacuum_scale_factor = 0.01` or lower for large tables. We've optimized PostgreSQL tables up to 2TB with sub-100ms P99 query times using these techniques.

Supabase is an open-source Firebase alternative built entirely on PostgreSQL. It adds a real-time API layer (via logical replication), auto-generated REST and GraphQL APIs, authentication, storage, and Edge Functions — all backed by a managed PostgreSQL instance. Supabase raised $100M at a $5B valuation in October 2025 and reached $70M ARR (250% YoY growth) — all on the thesis that PostgreSQL is the right foundation for the modern application stack. We use Supabase for projects that benefit from its managed tier and real-time capabilities; we use bare PostgreSQL for projects needing fine-grained operational control.

Adding a column (PostgreSQL 11+): adding a column with a non-volatile default is now instantaneous (metadata change only). Adding a non-null column with a volatile default (like NOW()) still requires a table rewrite. Changing a column type: still requires ACCESS EXCLUSIVE lock and full table rewrite. The strategy: (1) add the new column, (2) backfill in batches with a low-priority UPDATE, (3) add a constraint NOT VALID and then VALIDATE CONSTRAINT separately, (4) drop the old column. For complex rewrites on tables over 10GB, we use pg_repack which rebuilds the table in the background and swaps atomically. We always test migration duration on production-representative data before executing.

For time-series workloads at moderate scale (sub-1B rows, sub-100K inserts/second), PostgreSQL with TimescaleDB (a native PostgreSQL extension) is the pragmatic choice — one database, standard SQL, no additional operational complexity. TimescaleDB's hypertables automatically partition by time, and its continuous aggregates maintain materialized rollups incrementally. At extreme scale (trillions of rows, hundreds of thousands of writes/second), InfluxDB or ClickHouse win on raw throughput. We default to TimescaleDB on PostgreSQL unless scale projections clearly exceed what it can handle.

PostgreSQL itself is open source and free. Infrastructure cost depends on managed vs self-hosted, database size, and traffic — Supabase Pro starts at $25/month, AWS RDS PostgreSQL at ~$50/month for a small production instance, self-hosted on a VPS at ~$20-40/month with more operational work. Development cost depends on schema complexity, migration requirements, performance tuning scope, and HA configuration. We scope specifically rather than estimate generically — describe your project requirements and we'll provide a detailed breakdown with clear rationale.