Databases

Summary

Relational database engines: two mature single-primary RDBMS (PostgreSQL and MySQL) and one distributed SQL database (CockroachDB). The topic pages cover replication and HA topologies, release and support lifecycles, licensing, and newer capabilities such as PostgreSQL 18 asynchronous I/O, vector search and multi-region SQL. As of 2026-09-25: PostgreSQL 18.6 is current (19 GA targeted for October 2026, 14 EOL on 2026-11-12), MySQL 8.0 is end of life (LTS lines 8.4 and 9.7, Innovation 26.x), and CockroachDB has been proprietary since v24.3.

← Knowledge Base

Domain Map

The map shows how the three engines relate: CockroachDB speaks the PostgreSQL wire protocol and is the usual migration target when PostgreSQL outgrows one primary, MySQL estates usually reach CockroachDB through PostgreSQL, and all three can stream change data into Kafka.

flowchart LR
    PG["PostgreSQL 18<br/>(single primary, WAL streaming)"]
    MY["MySQL 9.7 LTS / 8.4 LTS<br/>(single primary, Group Replication)"]
    CRDB["CockroachDB v26.2<br/>(distributed SQL, Raft per range)"]
    KAFKA["Kafka<br/>(CDC sink)"]

    CRDB -.->|"PostgreSQL wire protocol<br/>(drivers, most ORMs)"| PG
    MY -->|"pgLoader / AWS DMS"| PG
    PG -->|"MOLT Fetch + Replicator"| CRDB
    PG -->|"logical replication / CDC"| KAFKA
    MY -->|"binlog (Debezium)"| KAFKA
    CRDB -->|"changefeeds"| KAFKA

Topics

Database	Description	Latest version (2026-09-25)	License
CockroachDB	Distributed SQL with serializable ACID transactions over Raft-replicated ranges, PostgreSQL wire protocol, automatic sharding and multi-region survivability.	v26.2.6 Regular (2026-08-21). v26.3 Innovation (2026-08)	CockroachDB Software License (proprietary, source-available) since v24.3
MySQL	Oracle's widely deployed RDBMS with two LTS lines plus calendar-versioned Innovation releases, InnoDB Cluster for built-in HA, and commercial HeatWave analytics and vector search.	26.7.0 Innovation (2026-07-28). LTS 9.7.2 and 8.4.11 (2026-07). 8.0 EOL 2026-04-30	GPLv2 (Community). Commercial (Enterprise)
PostgreSQL	Community-governed RDBMS with strict ACID semantics, rich SQL (JSONB, temporal constraints) and extensions (PostGIS, pgvector, TimescaleDB, Citus). v18 adds asynchronous I/O for reads.	18.6 (2026-08-13). 19 Beta 4 (2026-09-24). 14 EOL 2026-11-12	PostgreSQL License (OSI-approved, BSD/MIT-style)

Comparisons

Comparison	Scope
Database Comparison	CockroachDB vs MySQL vs PostgreSQL: licenses, consistency and isolation, replication, migration paths, managed services and cost trade-offs, with a decision flowchart

When to Use Which

This summary follows the decision flowchart in the comparison.

Situation	Pick	Why
New general-purpose OLTP backend	PostgreSQL	Richest SQL and extensions, permissive license, every cloud offers it managed
Existing MySQL estate or PHP stack (WordPress, Laravel, Magento)	MySQL 9.7 LTS or 8.4 LTS	Tooling and skills already in place. InnoDB Cluster gives built-in failover. Move off 8.0 (EOL) via 8.4
Writes must survive a zone or region loss automatically, or outgrow one primary	CockroachDB	Raft per range, automatic failover and rebalancing, REGIONAL BY ROW data placement
Same needs, but an OSI-approved license is required	YugabyteDB or TiDB (not covered here)	Apache-2.0 distributed SQL alternatives named on the CockroachDB page
Vector search next to relational data	PostgreSQL (pgvector) or CockroachDB (vector index GA v25.4)	MySQL Community has the VECTOR type but DISTANCE() only in HeatWave / MySQL AI

Landscape

The database landscape is stratified between mature single-node RDBMS engines (PostgreSQL, MySQL) and distributed SQL databases (CockroachDB, TiDB, YugabyteDB). Distributed SQL offers horizontal scaling while keeping SQL semantics and ACID guarantees. Cloud providers blurred the lines further with proprietary services such as Aurora, AlloyDB and PolarDB. They decouple storage from compute for elastic scaling while keeping PostgreSQL or MySQL wire compatibility. Amazon Aurora DSQL (GA 2025-05) adds a serverless, PostgreSQL-compatible, multi-Region active-active option.

The "PostgreSQL everywhere" trend continues as PostgreSQL becomes the default wire protocol even for non-relational workloads: TimescaleDB for time series, pgvector for embeddings, and Citus for scale-out and analytics. Connection management is a critical operational concern at scale. PgBouncer, PgCat and Supavisor compete to solve connection pooling for serverless and multi-tenant architectures.

Licensing and governance also diverged. PostgreSQL has never been relicensed. CockroachDB moved from BSL 1.1 core plus CCL to the proprietary CockroachDB Software License in 2024-11 and retired its free Core edition. MySQL stays GPLv2, but after Oracle's 2025 team cuts the community launched the OurSQL Foundation (2026-05) and Oracle published an advisory governance model (2026-06).

Embedded Analytics Shift

Embedded analytical databases like DuckDB enable a new pattern: OLAP queries run in-process alongside application code. This challenges the traditional separation of transactional and analytical workloads. DuckDB can query Parquet files directly from S3, so many scenarios can run analytical queries without a dedicated data warehouse.

The AI/ML wave is also pulling databases into vector search. PostgreSQL has pgvector, CockroachDB has a pgvector-compatible VECTOR type with a built-in vector index (GA since v25.4), and MySQL Community has the VECTOR type (since 9.0) while the DISTANCE() function stays in HeatWave and MySQL AI. All aim to serve RAG and similarity search without a separate vector database.

Key Concepts

ACID vs BASE

ACID (Atomicity, Consistency, Isolation, Durability) guarantees that every transaction is all-or-nothing, moves the database between valid states, isolates concurrent transactions, and persists committed data. BASE (Basically Available, Soft state, Eventually consistent) is the relaxed model used by many NoSQL and AP-leaning distributed systems, trading strict consistency for availability and partition tolerance.

Distributed SQL databases like CockroachDB provide ACID across nodes using consensus (Raft), which adds latency proportional to inter-node round trips. In practice, every CockroachDB write needs a Raft quorum, so it pays at least one round trip to the nearest quorum of voters. Within one availability zone Cockroach Labs quotes about 2 ms for single-row writes. With SURVIVE REGION FAILURE each write pays a cross-region round trip (see CockroachDB Explanation).

CAP Theorem in Practice

Brewer's CAP theorem states that a distributed system cannot guarantee Consistency, Availability and Partition tolerance all at once; during a network partition it must give up either consistency or availability. In practice, the choice is between CP systems (CockroachDB, Spanner: consistent, but ranges without a quorum reject requests during partitions) and AP systems (Cassandra, DynamoDB: available, but can serve stale reads).

Modern databases often allow per-query tuning of the consistency-availability trade-off:

• CockroachDB supports AS OF SYSTEM TIME and follower reads for bounded-staleness reads served from the nearest replica
• Cassandra allows per-query consistency levels (ONE, QUORUM, ALL)
• DynamoDB offers both eventually consistent and strongly consistent read options

Replication Topologies

Common Topologies

• Single-leader (primary-replica): All writes go to one node. Replicas serve reads. Simple, but creates a write bottleneck. Used by PostgreSQL streaming replication and MySQL Group Replication in single-primary mode.
• Multi-leader: Multiple nodes accept writes for the same data, with conflict detection or resolution required. Used by MySQL Group Replication in multi-primary mode and CockroachDB logical data replication (LDR) between clusters.
• Consensus per shard (multi-Raft): Each shard has its own leader, so writes spread across all nodes while every shard stays single-leader. Used by CockroachDB ranges, where the leaseholder is the Raft leader since v25.2.
• Leaderless (quorum): Any node can serve reads/writes with quorum agreement. Used by Cassandra and DynamoDB.

Connection Pooling

A middleware layer that maintains a pool of persistent database connections, multiplexing many short-lived application connections onto fewer backend connections. Essential for Kubernetes workloads where pod churn creates connection storms that can overwhelm the database's max_connections limit.

PgBouncer operates in three modes:

• Session mode: Holds a backend connection for the entire client session (safest, least efficient)
• Transaction mode: Releases the backend connection after each transaction (best balance for most workloads)
• Statement mode: Releases after each statement (most efficient but breaks multi-statement transactions)

Newer poolers like PgCat add query-level load balancing across read replicas, and Supavisor (Elixir-based) supports multi-tenant pooling with per-tenant connection limits.

MVCC (Multi-Version Concurrency Control)

A concurrency control method where the database maintains multiple versions of each row, allowing readers to see a consistent snapshot without blocking writers. PostgreSQL implements MVCC by storing old row versions in the heap (requiring VACUUM to reclaim dead tuples). MySQL/InnoDB instead uses an undo log with rollback segments, cleaned by a purge thread.

CockroachDB extends MVCC across distributed ranges with hybrid-logical clock (HLC) timestamps for global ordering. A read at timestamp T then sees a consistent snapshot across all nodes, even when data is spread across multiple regions.

Related

• Kafka: CDC sink for all three engines (Debezium, CockroachDB changefeeds)
• Kubernetes: deployment platform (CloudNativePG for PostgreSQL, the CockroachDB Kubernetes operator)
• HashiCorp Vault: dynamic PostgreSQL and MySQL credentials
• LLM Fundamentals: pgvector as a RAG vector store
• Longhorn and Ceph: persistent volumes for Kubernetes-hosted databases

Sources

• PostgreSQL and PostgreSQL Versioning Policy
• MySQL, MySQL documentation and MySQL EOL notices
• CockroachDB, CockroachDB documentation and Licensing FAQs
• Amazon Aurora DSQL is now generally available (AWS, 2025-05)

Open Questions

• As pgvector and CockroachDB's vector index mature, do they remove the need for dedicated vector databases (Pinecone, Weaviate, Qdrant) for most RAG workloads? No controlled benchmark is recorded in this vault yet.
• What are the real-world latency and consistency trade-offs when running CockroachDB across multiple cloud regions with survival goals set to "region" versus "zone"? Cockroach Labs publishes no fixed cross-region numbers.
• How will the convergence of OLTP and OLAP in single engines (HTAP), as attempted by TiDB's TiFlash and AlloyDB's columnar engine, affect the traditional ETL pipeline to a data warehouse?
• Will the OurSQL Foundation and Oracle's MySQL steering committee converge, or will a community fork emerge?