Postgres vs MySQL

Postgres and MySQL are the two most widely deployed open-source relational databases. Most teams picking between them are choosing between two good options for their OLTP workload, and the right answer depends on factors that aren't always obvious: SQL compliance, ecosystem maturity, extension support, and what happens when your analytics needs outgrow both of them.

This post covers where each database genuinely excels, where each has real limitations, and when you need something different for your analytical workload.

What they have in common

Both are mature, well-tested relational databases with strong ecosystems. Both support:

• ACID transactions with row-level locking
• Standard SQL (with vendor extensions)
• Replication for high availability
• Broad ORMs and framework support (Rails, Django, Laravel, etc.)
• Cloud-managed options (RDS, Cloud SQL, Aurora, Supabase, PlanetScale)
• Active open-source communities

For a straightforward web application with standard CRUD operations, both will serve you well. The differences matter most at the edges: complex queries, JSON workloads, full-text search, and scale.

Where Postgres excels

SQL compliance and advanced queries

Postgres has more complete SQL standard compliance than MySQL. Features that are standard SQL but work reliably only in Postgres:

• Window functions: full support including RANGE BETWEEN, GROUPS, and custom frames
• CTEs (Common Table Expressions): full recursive CTE support and materialization control
• Lateral joins: LATERAL JOIN for correlated subqueries with set-returning functions
• FILTER clause: count(*) FILTER (WHERE condition) for conditional aggregation

MySQL added window functions and CTEs relatively recently (8.0+) and has some edge cases in behavior that differ from the standard.

JSONB for flexible schemas

Postgres's JSONB type stores JSON as a binary format with full indexing support. You can index arbitrary JSON keys and run fast queries against nested data:

-- GIN index on JSONB for fast key lookups
CREATE INDEX ON events USING GIN (properties);

-- Query nested JSON with index support
SELECT *
FROM events
WHERE properties @> '{"event_type": "purchase", "plan": "pro"}';

-- Extract and aggregate JSONB fields
SELECT
    properties ->> 'country'   AS country,
    count()                    AS events,
    avg((properties ->> 'amount')::float) AS avg_amount
FROM events
WHERE properties ? 'amount'
GROUP BY country
ORDER BY events DESC;

MySQL has JSON support but without the same indexing depth. For applications where data shape varies across records, Postgres's JSONB is significantly more capable.

Extensions ecosystem

Postgres's extension system is its biggest differentiator. Production-ready extensions that change what Postgres can do:

• PostGIS: full geospatial queries (geometry types, spatial indexes, distance functions)
• TimescaleDB: time-series optimizations, continuous aggregates, data retention policies
• pgvector: vector similarity search for AI/embedding workloads
• pg_trgm: trigram-based fuzzy text search
• Citus: horizontal sharding for distributed Postgres

MySQL has a plugin architecture but the extension ecosystem is narrower and less commonly used in production.

Concurrent writes

Postgres uses MVCC (Multi-Version Concurrency Control) with genuine row-level locking. Multiple writers can update different rows in the same table simultaneously without blocking each other. MySQL's InnoDB also uses MVCC, but Postgres's implementation handles high-concurrency write workloads with less contention in many benchmarks.

Where MySQL excels

Replication and high availability

MySQL's replication system is battle-tested and widely understood. MySQL Group Replication and tools like Orchestrator for automated failover have been in production at enormous scale (Facebook, Twitter, GitHub) for years. The operational knowledge for running MySQL HA at scale is more widely distributed than for Postgres.

Postgres's replication has improved significantly (logical replication in Postgres 10+, Patroni for HA), but the operational tooling ecosystem is less mature and requires more manual configuration.

Speed for simple queries

For high-volume, simple query workloads (primary key lookups, single-table selects with no joins, high-throughput inserts), MySQL is often faster than Postgres. The Postgres query planner is more sophisticated, which helps for complex queries but adds overhead for trivial ones.

If your application runs thousands of requests per second, each doing simple indexed lookups, MySQL has historically shown lower latency per query in this profile.

Ease of operations

MySQL has a longer track record as a managed service. RDS MySQL, Cloud SQL, and Aurora MySQL are more widely deployed and have more documented operational patterns than their Postgres equivalents. For teams without deep database expertise, MySQL tends to have fewer surprises in production.

Full-text search

MySQL's built-in full-text search (MATCH ... AGAINST) is more mature and better integrated than Postgres's tsvector/tsquery system for simple use cases. For basic keyword search over text content, MySQL's full-text indexes are simpler to set up.

Where both hit limits: analytics at scale

Neither Postgres nor MySQL is designed for analytics workloads on Postgres. Both are row-oriented OLTP databases. Their shared limitations at analytical scale:

• Full table scans for aggregations: SELECT count(*), sum(revenue) FROM orders WHERE month = 'May' reads every column in every matching row, not just the two you need
• No columnar compression: row storage means 5-10x more data reads than a columnar database for typical analytics queries
• Concurrent query degradation: OLTP databases optimize for many short-lived transactions, not a few long analytical scans
• Memory pressure: large GROUP BY operations and window functions can exhaust memory on production OLTP instances

When you're running complex analytical queries against tables over 100 million rows, or need sub-second response on dashboards with multiple concurrent users, evaluating the right OLAP solution is the right next step. The standard pattern is: keep Postgres or MySQL for writes and OLTP reads, and replicate the data you need for analytics into a columnar store.

Choosing between them

The "add ClickHouse" row is where most growing applications eventually land. ClickHouse is what best-in-class OLAP databases look like: columnar, vectorized, and purpose-built for aggregation over large datasets. For real-time analytics, the architecture that scales is Postgres or MySQL for OLTP, with CDC or event streaming feeding ClickHouse for the analytical layer.

Where each database falls short: specific numbers

The performance gap between OLTP and OLAP databases is not subtle. Tinybird's own engineering team documented that simple aggregation queries on standard relational databases take 20-30 seconds, while the same data in ClickHouse with 5-minute pre-aggregated rollups returns in 250ms on a 500M-row dataset. Without pre-aggregation, raw ClickHouse on 500M rows runs in approximately 2,900ms on a 4-core machine, still far faster than a row-oriented store where the engine reads every column in every matching row rather than only the two needed for a count/sum query.

MySQL has the same limitation. InnoDB stores rows sequentially on disk. An aggregation query reads every column in every matching row even if only two are needed in the SELECT clause. At 100 million rows, this becomes the performance wall that causes teams to put analytics reporting on replicas, to time-limit queries, or to cache results aggressively.

The index coverage strategy that works for OLTP queries (add an index on the WHERE clause column) does not help for analytics aggregations. An index helps you find rows. It doesn't help you aggregate them faster once found.

For high-cardinality GROUP BY queries (aggregate by user_id across 50 million users), Postgres and MySQL both require large temporary tables that frequently exceed the sort buffer and spill to disk. ClickHouse handles these in memory using hash aggregation with a fixed memory budget, falling back to disk-based aggregation that is still significantly faster than the OLTP equivalent.

Adding analytics to Postgres or MySQL

The typical pattern for adding real-time analytics alongside an existing OLTP database:

1. CDC from Postgres/MySQL to ClickHouse: capture every transaction as a change event and stream it into a ClickHouse table designed for analytical queries
2. Event streaming: emit application events (user actions, state changes) directly to ClickHouse via an ingestion API, bypassing the OLTP database for analytics writes
3. Batch sync: for workloads where data freshness of minutes is acceptable, periodic exports to ClickHouse are simpler to set up

Postgres to ClickHouse comparisons consistently show that the combination of a row-oriented OLTP database for writes and a columnar OLAP database for reads is both faster and cheaper than trying to make one system do both jobs.

For CDC specifically, Postgres uses logical replication slots and MySQL uses binlog replication. Both are mature mechanisms for capturing every committed change. The challenge is operating a change data capture pipeline reliably at production scale: handling schema changes, managing replication lag, and ensuring no events are dropped. Streaming ingestion to ClickHouse covers the architecture patterns that work in practice: consumer groups, at-least-once delivery with deduplication in ClickHouse using ReplacingMergeTree, and schema migration handling that doesn't break the pipeline.

Tinybird for Postgres and MySQL teams

Tinybird is managed ClickHouse that connects to both Postgres and MySQL. CDC connectors capture changes from your existing OLTP database and keep ClickHouse in sync in real time. SQL Pipes expose your analytics queries as HTTP endpoints.

The contrast with building this yourself: standing up a ClickHouse cluster, configuring a Debezium connector for CDC, managing Kafka topics, writing consumer code that handles schema evolution, setting up the ClickHouse sink, and then building an API layer on top is a multi-week project that produces infrastructure requiring ongoing maintenance. Resend, an email platform, built their complete Marketing Analytics feature in two weeks using Tinybird rather than building that infrastructure themselves.

On the querying side, every analytics query you write against Tinybird is a parameterized HTTP endpoint. Your dashboards call it. Your product calls it. Your data team calls it. Resend measured 62ms p90 query latency in production, processing 100TB per month, with no caching layer. Each endpoint handles authentication, rate limiting, and caching at the Tinybird layer. Your application treats the analytics layer as an API, which is what it should be.

The pipeline from database change to queryable API endpoint takes hours to set up rather than weeks. Postgres and MySQL stay exactly where they are, handling writes and OLTP reads. Tinybird handles the analytical layer, with no changes to your existing database configuration.