SQL Database Selection

Last updated:

The decision

Once you've decided to use a relational database, a second decision awaits: which one? PostgreSQL, MySQL, Microsoft SQL Server, Amazon Aurora, CockroachDB, and others all implement the SQL standard but differ significantly in features, operational characteristics, ecosystem support, and cost. The choice has long-term implications for what capabilities are available, what the migration path looks like as requirements evolve, and how much operational expertise your team needs to develop.

PostgreSQL and MySQL have dominated open-source relational databases for two decades, with PostgreSQL growing rapidly as the preferred choice for new projects due to its superior feature set. Cloud-managed variants (Amazon RDS, Cloud SQL, Azure Database for PostgreSQL) reduce operational burden significantly. Distributed SQL databases like CockroachDB and Google Spanner extend the relational model to horizontal scaling across multiple nodes and regions—solving the single-node scaling limit that traditionally required NoSQL for write-heavy global workloads.

The emergence of managed cloud databases as a service has shifted the calculus. Running PostgreSQL on Amazon RDS or Aurora is fundamentally different from self-hosting: you trade some control for dramatically reduced operational overhead. Automated failover, point-in-time recovery, automated backups, and read replicas that take minutes to provision rather than days change what a small team can operate. Understanding the managed vs. self-hosted dimension is as important as the database engine selection itself.

Why it matters

Database migrations are among the highest-risk, highest-cost operations in software. Migrating from MySQL to PostgreSQL after years of production data requires running parallel databases, migrating application code to handle dialect differences, validating data integrity, and executing a carefully orchestrated cutover with minimal downtime. Teams that discover their database choice cannot support their requirements face this cost. Getting the selection right prevents it.

Feature differences matter more than they appear on the surface. PostgreSQL's JSONB type, partial indexes, and powerful extension ecosystem (PostGIS for geographic data, pg_vector for AI embeddings, TimescaleDB for time-series) mean that PostgreSQL can often replace several specialized databases in your stack. MySQL's replication model and wide CDN/hosting support made it the default for web applications for a decade. SQL Server's deep Windows and Active Directory integration makes it the natural choice in Microsoft-first enterprises. These ecosystem considerations often outweigh pure capability comparisons.

Cost at scale diverges substantially. Oracle and SQL Server enterprise licensing costs can reach six figures annually before you've written a line of application code. PostgreSQL's open-source license eliminates that cost entirely. Cloud-managed Aurora charges a premium over standard RDS but provides capabilities (Aurora Serverless, Global Database, Aurora DSQL) that can eliminate expensive infrastructure work. For startups and growing companies, licensing costs deserve as much weight as technical capabilities in the selection process.

Choose PostgreSQL when

  • You want the most feature-complete open-source relational database: advanced JSON support, window functions, CTEs, full-text search, geometric types, foreign data wrappers
  • The application requires geospatial data (use PostGIS extension—the industry standard for spatial queries)
  • You want a single database that can store both structured relational data and semi-structured JSON documents (JSONB)
  • AI/ML features require vector similarity search (pgvector extension integrates with embedding workflows)
  • Strong ACID compliance and complex query capabilities are required
  • You want the most active extension ecosystem with specialized capabilities for time-series, analytics, and more

Choose other databases when

  • Deep Microsoft ecosystem integration (Active Directory, SSRS, SSIS, Power BI native) makes SQL Server the least-friction choice
  • You need horizontal write scaling across regions with strong consistency—CockroachDB or Google Spanner
  • Existing team expertise and tooling investment in MySQL makes migration costly without compelling benefit
  • AWS-native fully managed with auto-scaling and serverless capacity is prioritized—Aurora Serverless v2
  • An existing Oracle estate has procedures, functions, and tools that would require expensive migration to PostgreSQL

Comparison


FEATURE COMPARISON MATRIX
══════════════════════════════════════════════════════════════════════════

Feature              PostgreSQL   MySQL    SQL Server   Aurora      CockroachDB
────────────────────────────────────────────────────────────────────────────────
License              Open Source  GPL/Comm  Commercial  AWS Service  BSL/CCL
JSON/Document        JSONB ✓✓✓   JSON ✓    JSON ✓       JSONB ✓✓✓   JSONB ✓✓
Full-text search     Built-in     Limited   Built-in     Built-in     Limited
Geospatial           PostGIS ✓✓✓ Limited   Built-in     PostGIS ✓✓✓  Limited
Window functions     ✓            ✓         ✓            ✓            ✓
CTEs / Recursive     ✓            ✓         ✓            ✓            ✓
Horizontal scale     Sharding     Sharding  Manual       Aurora DSQL  Native ✓✓✓
Global multi-region  Manual       Manual    AG / FCI     Global DB    Native ✓✓✓
ACID transactions    ✓            ✓         ✓            ✓            ✓
Replication          Streaming    Binlog    AG           Multi-AZ     Raft
Managed cloud        RDS/Aurora   RDS/Cloud RDS/MI       AWS native   Cloud
Extension ecosystem  Rich ✓✓✓    Limited   CLR          Rich          Limited
Connection pooling   PgBouncer    ProxySQL  Built-in     RDS Proxy     Built-in
Max DB size          Unlimited    ~65 TB    ~524 PB      128 TB        Distributed
Typical use case     General SaaS Web apps  Enterprise   AWS apps     Global OLTP

WHEN TO USE EACH:
────────────────────────────────────────────────────────────────────────────────
PostgreSQL   → New projects, SaaS, startups, feature-rich applications
MySQL        → Existing MySQL codebase, web hosting, WordPress/Drupal ecosystems
SQL Server   → Microsoft enterprise, .NET apps, Power BI, Active Directory auth
Aurora       → AWS-native, need managed operations with PostgreSQL/MySQL compat
CockroachDB  → Global multi-region, need SQL semantics with NoSQL-style h-scale

Trade-offs

PostgreSQL strengths

  • Most feature-complete open-source relational database: JSONB, full-text search, window functions, rich extensions
  • Excellent standards compliance and complex query support—rarely hits "not supported" walls
  • Rich extension ecosystem enables specialized use cases without adding new database infrastructure
  • Zero licensing cost with strong community support, extensive documentation, and broad cloud managed availability

PostgreSQL weaknesses

  • Single-primary write scaling—horizontal write scale requires application-level sharding or distribution middleware
  • Connection overhead: each connection spawns a new process; PgBouncer connection pooling is typically required for applications with many short-lived connections
  • VACUUM process for MVCC cleanup can cause performance surprises under heavy write loads if not configured carefully
  • Less ubiquitous than MySQL in shared web hosting environments and some legacy application ecosystems

Implementation considerations

For PostgreSQL deployments, connection pooling is not optional for web applications—it is mandatory. Each PostgreSQL connection forks a process (~5-10MB memory), meaning a 300-connection limit on a modest instance is realistic. PgBouncer in transaction mode multiplexes hundreds of application connections over a small pool of actual database connections, dramatically improving throughput. For cloud deployments, Amazon RDS Proxy, Azure Flexible Server's built-in pooler, and Cloud SQL Proxy provide managed pooling. Configure max_connections and shared_buffers appropriately for your instance size and usage pattern.

For Amazon Aurora, understand the distinction between Aurora Provisioned and Aurora Serverless v2. Provisioned instances have fixed capacity that you manage; Serverless v2 scales from 0.5 to 128 ACUs automatically within seconds, ideal for workloads with variable traffic. Aurora Global Database replicates from one primary region to up to five secondary regions with typically under 1 second replication lag, enabling low-latency reads globally and disaster recovery with sub-1-minute RPO. Aurora's storage layer automatically grows in 10GB increments, eliminating manual storage management.

For CockroachDB, the distributed architecture requires rethinking several PostgreSQL patterns. Transactions that span multiple ranges (data shards) are more expensive than single-range transactions. Design schemas to co-locate related data using computed interleaving (though explicit interleaving was deprecated; use hash-sharded indexes for write-heavy workloads). CockroachDB uses serializable isolation by default rather than read committed, which eliminates many common race conditions but requires adapting applications that relied on read committed semantics. The SQL dialect is highly compatible with PostgreSQL but differences exist in system catalogs, procedural language support, and some data types.

Common mistakes

  • No connection pooler on PostgreSQL: Connecting directly from application servers without PgBouncer or equivalent quickly exhausts the connection limit under load, causing connection refused errors during high-traffic periods.
  • LIKE '%search%' queries at scale: Prefix-less LIKE queries cannot use standard B-tree indexes and perform full table scans; use PostgreSQL's full-text search (tsvector/tsquery) or pg_trgm extension for efficient substring matching.
  • Default autovacuum settings on high-write tables: PostgreSQL's MVCC model requires VACUUM to reclaim space from dead tuples; tables with high update/delete rates need aggressive autovacuum settings to prevent table bloat.
  • Choosing SQL Server purely for cost familiarity: Underestimating the dramatic difference in total licensing cost compared to PostgreSQL for multi-server deployments; SQL Server Developer edition is free but production licenses are substantial.
  • Aurora without Read Replicas: A single Aurora instance has no high availability; Aurora requires at least one replica in a different availability zone for automatic failover within 30-60 seconds.

Decision checklist

  • Is this a greenfield project or an existing system? Migration cost from an existing database is often the deciding factor.
  • What cloud platform are you targeting? Aurora for AWS, Cloud SQL for GCP, and Azure Database for Azure reduce management overhead significantly.
  • Do you need horizontal write scaling across multiple regions? If yes, CockroachDB or Spanner; if no, single-primary options suffice.
  • Are there geospatial, time-series, or vector search requirements that benefit from PostgreSQL extensions?
  • What are the licensing cost constraints? Does your organization have existing SQL Server or Oracle licenses?
  • What is your team's DBA expertise and operational capacity for managing the database at scale?
  • Will you use a managed cloud service or self-host? Managed services reduce operational risk for smaller teams significantly.

Real-world examples

  • Instagram: Uses PostgreSQL as a primary operational database, demonstrating that a single well-tuned PostgreSQL cluster with careful sharding can handle enormous scale. Instagram's engineering team has written extensively about their PostgreSQL infrastructure, including custom connection pooling and shard management.
  • Cockroach Labs (self): CockroachDB powers banking and fintech workloads like DoorDash's payment infrastructure, where strong ACID transactions, global data distribution, and high availability without single points of failure are non-negotiable requirements that traditional single-region SQL cannot satisfy.
  • Enterprise ERP systems: SAP, Oracle ERP, and Microsoft Dynamics are built on Oracle or SQL Server respectively. Enterprise organizations running these systems choose the underlying database by following the vendor's recommendation—attempting to run SAP on PostgreSQL creates unsupported configurations and voids support agreements.
  • SQL vs NoSQL — the prior decision: whether to use a relational database at all
  • Cloud Provider Selection — cloud provider choice influences managed database options
  • Data Architecture — database selection in the context of broader data platform design
  • Scalability — read replica patterns, sharding, and connection pooling strategies

Further reading