MySQL vs PostgreSQL: Differences and How Each Handles CDC

MySQL and PostgreSQL are both free, open source relational databases, and either one can run a typical application backend. They differ in licensing, data types, how they handle concurrent reads and writes, and how each exposes its changes once the database starts feeding a warehouse. Most comparisons stop before that last one, and it's where the two behave least alike.

Short answer: choose PostgreSQL if you want richer types (jsonb, arrays, ranges), extensions, or a permissive license. Choose MySQL if your team already runs it, or your workload is mostly primary-key reads and writes. If the database will feed analytics, the change-capture rows in the table below decide which settings you need on day one.

If you searched "psql vs mysql": psql is PostgreSQL's interactive terminal, its command-line client, so this is the same comparison.

MySQL vs PostgreSQL at a glance

Versions checked: MySQL 8.4 LTS and 9.7, PostgreSQL 18.

The first seven rows are what most comparisons cover. The last six are what you'll deal with when you replicate the database into Snowflake, BigQuery, or Databricks.

How MySQL and PostgreSQL differ under the hood

Concurrency and storage

Both databases use multi-version concurrency control, so readers don't block writers. They keep the old versions in different places, and that changes what you tune.

InnoDB, MySQL's default storage engine, stores rows in a clustered index ordered by the primary key and updates them in place. The previous version goes to an undo log, and a purge thread removes it once no open transaction needs it. The MySQL docs warn that a transaction left open stops purge, and the undo tablespace can keep growing.

PostgreSQL writes a new row version for every UPDATE and leaves the old one in the table as a dead row. VACUUM reclaims that space later, and autovacuum runs it for you by default. Update-heavy Postgres tables depend on autovacuum keeping up.

Connections

MySQL hands each client connection to a thread, and reuses threads from a cache. PostgreSQL starts a separate backend process for each connection. Processes are heavier than threads, so apps that open many connections to Postgres commonly put a pooler such as PgBouncer in front of it.

Isolation defaults

InnoDB defaults to REPEATABLE READ: plain reads in a transaction all see the snapshot taken by the first read. PostgreSQL defaults to READ COMMITTED: each statement sees whatever was committed before that statement started. If another transaction commits in between, a transaction that reads the same row twice gets two different answers on Postgres and the same answer on MySQL. Worth knowing before you port code between them.

Types and extensibility

Both have a JSON type. PostgreSQL adds jsonb, arrays, range types, user-defined types, and extensions you install with CREATE EXTENSION (PostGIS for geospatial data is a common example). MySQL's extension point is the storage engine layer: InnoDB is the default, and others plug in beside it.

Licensing

PostgreSQL's license lets anyone use, modify, and distribute it free of charge for any purpose. MySQL is free under the GPL. If the GPL doesn't fit, for example because you embed MySQL in a product you distribute, Oracle sells a commercial license.

How each database exposes changes for CDC

Once an application database feeds a warehouse, something has to read its changes. Change data capture (CDC) tails the database's own log instead of querying tables. MySQL gives you the binlog. PostgreSQL gives you the WAL, decoded into row changes. The two logs differ in five ways, and each one decides a setting or a failure mode.

The log: binlog vs WAL

MySQL's binary log is on by default since MySQL 8.0. For CDC it has to use row-based logging, binlog_format=ROW, which is the default. In MySQL 8.4 the binlog_format variable itself is deprecated, and the docs say only row-based logging should be used for new replication setups. Each row event carries row values: the new row for an insert, the old row for a delete, both for an update.

PostgreSQL's WAL exists for crash recovery first. To read row changes from it, you set wal_level=logical (the default is replica, and changing it needs a restart). A publication then lists the tables to capture, and a logical replication slot decodes the WAL into inserts, updates, and deletes through an output plugin such as pgoutput. We cover the WAL settings in more detail in how Postgres write-ahead logs work.

Position and retention: opposite failure modes

This is the biggest operational difference.

MySQL doesn't keep track of your CDC reader. The reader stores its own position, either as a binlog file and offset or as a GTID set. A GTID has the form source_id:transaction_id and is unique across the whole replication topology, so after a failover the reader can find its place on the new primary. GTIDs are off by default (gtid_mode=OFF). MySQL replication best practices explains why we turn them on.

Binlogs expire on a timer: binlog_expire_logs_seconds defaults to 30 days. On Amazon RDS for MySQL, binlog retention hours defaults to NULL, which means binlogs aren't retained at all, and the maximum is 168 hours. If a stopped reader's position gets purged, the MySQL docs are blunt: it can't resume. The pipeline has to re-snapshot the affected tables. The source database stays healthy. Your copy has a gap until the snapshot finishes.

PostgreSQL keeps track of the reader for you, and that cuts both ways. A replication slot records how far its consumer has read and keeps every WAL segment after that point. The reader can disconnect and resume later with nothing lost. But the slot doesn't care whether anyone is still connected. max_slot_wal_keep_size defaults to -1, which means a slot can retain an unlimited amount of WAL. An abandoned slot also stops VACUUM from removing catalog rows it still needs, and the Postgres docs warn that in extreme cases this can force the database to shut down to prevent transaction ID wraparound.

There's a quieter version of this on RDS: an idle database still generates WAL from RDS's own heartbeat writes, which never show up in the logical stream, so the slot never advances. We wrote up that failure in unbounded WAL growth in idle RDS logical replication.

So the MySQL risk is losing your place, and the Postgres risk is filling the primary's disk. Check the Postgres side with:

SELECT
  slot_name,
  active,
  wal_status,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

PostgreSQL replication slots walks through creating, monitoring, and dropping them safely.

Two newer Postgres releases change the picture. PostgreSQL 16 allows logical decoding on a standby, so CDC doesn't have to read from the primary (not available on Aurora). PostgreSQL 17 adds failover slots, which a standby can keep in sync so CDC survives a promotion. On MySQL, you can read from a replica as long as it writes replicated changes to its own binlog (log_replica_updates=ON).

Deletes and old values

With MySQL's default binlog_row_image=FULL, every update and delete event includes all columns of the old row. You can shrink the binlog with minimal, which logs only the columns that identify the row plus the columns the statement set, or noblob, which drops BLOB and TEXT columns that didn't change and aren't needed to identify the row. Either way, your CDC writer has to handle partial rows.

PostgreSQL's default is the reverse. A table's replica identity decides which old values go into the WAL, and the default records only the primary key. A delete event tells you which row went away, not what it contained. That matters when the destination needs more than the key: for example, replicating a delete into a Snowflake table clustered by created_at needs the deleted row's created_at. Our guide to Postgres replica identity covers when to switch a table to FULL and what that costs in WAL volume.

Two Postgres gotchas follow from this:

• Tables without a primary key break writes, not just CDC. If a table has no primary key and default replica identity, and it's in a publication that replicates updates and deletes, PostgreSQL rejects UPDATE and DELETE on that table with an error on the publisher. Your application sees the error. Fix it with a primary key, REPLICA IDENTITY USING INDEX, or REPLICA IDENTITY FULL.
• Unchanged large values aren't sent. With the default replica identity, values big enough to be TOASTed are left out of an update event when they didn't change, and the protocol marks them as "unchanged" instead of sending them. A CDC writer that doesn't check for that marker overwrites good data with NULL or a placeholder string. See why TOAST columns break Postgres CDC.

Schema changes

MySQL logs DDL such as CREATE TABLE and ALTER TABLE as SQL statements, whatever binlog_format is set to. With the default binlog_row_metadata=MINIMAL, row events don't include column names, so a CDC reader has to parse every DDL statement to keep its own copy of each table's schema in sync. Online migration tools like gh-ost add another layer: they build a ghost table, copy rows into it, and swap it in with a rename, and the reader has to follow the rename. In supercharging our MySQL connector we describe the SQL parser we built for this, which avoids setting binlog_row_metadata=FULL and the extra binlog volume that comes with it.

PostgreSQL doesn't send DDL through logical decoding at all. The docs list "the database schema and DDL commands are not replicated" as a restriction of logical replication. What pgoutput does send is a relation message describing a table's columns before the first change to that table, and again whenever its definition has changed. In practice, a column added to a quiet table only reaches the CDC reader when a row in that table next changes.

Cascading deletes

Before MySQL 9.6, InnoDB carried out ON DELETE CASCADE and ON UPDATE CASCADE itself, and the child-row changes never got their own binlog events. MySQL replicas stay correct because they run the cascade again locally, but a CDC reader isn't a MySQL replica. It sees the parent delete and nothing else, so the child rows stay in the warehouse. That's still the behavior on MySQL 8.4 LTS. MySQL 9.6 moved foreign key handling to the SQL layer by default, and the MySQL manual now says cascades generate row events for the affected child tables, written before the parent event.

PostgreSQL's logical decoding extracts every persistent change to a table, so each child row removed by a cascade arrives as its own delete. Even so, we recommend against cascades in production for other reasons, laid out in MySQL cascading changes and why you shouldn't use them.

How Artie handles these differences

Artie reads both logs and handles these differences for you. On MySQL, it switches to GTID-based replication automatically when GTIDs are on, follows gh-ost migrations, and picks up DDL without binlog_row_metadata=FULL. On PostgreSQL, it checks replication slot size every 15 minutes and alerts past a threshold, can send heartbeats so an idle database doesn't pile up WAL, and doesn't overwrite unchanged TOAST values, without requiring REPLICA IDENTITY FULL.

Setting up each one for CDC

MySQL

On a self-managed server, set these at startup in my.cnf:

[mysqld]
server_id = 1
binlog_format = ROW        # the default; listed to be explicit
binlog_row_image = FULL    # the default
gtid_mode = ON
enforce_gtid_consistency = ON
# 7 days. Keep binlogs longer than your longest pipeline outage.
binlog_expire_logs_seconds = 604800

On RDS or Aurora, set binlog_format = ROW and the GTID settings in the parameter group (a DB cluster parameter group on Aurora) and reboot; RDS sets server_id itself. Aurora MySQL keeps the binlog off until binlog_format is set, and RDS for MySQL only writes binlogs when automated backups are on (a backup retention period above 0). Retention is a stored procedure instead of binlog_expire_logs_seconds:

CALL mysql.rds_set_configuration('binlog retention hours', 168);

Then create a user that can read the binlog and run the initial snapshot:

CREATE USER 'cdc' IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'cdc';

PostgreSQL

Set wal_level = logical in postgresql.conf and restart. On RDS or Aurora, set rds.logical_replication = 1 in the parameter group and reboot. From PostgreSQL 19, the restart goes away: with the default wal_level = replica, creating the first logical slot turns logical decoding on. Then:

-- First, give every table without a primary key a replica identity. Once a table
-- is in a publication that replicates updates and deletes, Postgres rejects its
-- UPDATE and DELETE until it has one.
ALTER TABLE public.events REPLICA IDENTITY FULL;

-- Which tables to capture
CREATE PUBLICATION cdc_pub FOR ALL TABLES;

-- Where the reader's position lives. Most CDC tools create their own slot (Artie
-- does, and expects the publication to be named dbz_publication). Only create one
-- by hand if yours doesn't: a slot nobody reads holds WAL until the disk fills.
SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput');

-- Cap the WAL one stalled slot can hold (PostgreSQL 13+).
-- A slot that falls further behind is invalidated and has to be recreated.
-- ALTER SYSTEM needs superuser: on RDS, Aurora or Cloud SQL, set it in the
-- provider's parameter settings instead.
ALTER SYSTEM SET max_slot_wal_keep_size = '25GB';
SELECT pg_reload_conf();

We've seen 25 GB work for smaller workloads; Postgres replication slot 101 suggests 50–100 GB or more for databases over 1 TB. Size it to your disk. PostgreSQL 18 also adds idle_replication_slot_timeout, off by default, which invalidates slots that sit unused past a set time.

Which should you choose?

For a new application, both are solid choices. Decide on features and your team's experience:

• PostgreSQL if you want jsonb, arrays, ranges, or extensions like PostGIS, or if the permissive license matters because you distribute software.
• MySQL if your team already operates it well, or your access pattern is mostly lookups and writes by primary key, which InnoDB's clustered index serves directly.

If the database will feed analytics, either works, but plan for its change log from the start:

• On MySQL: row format with full images, GTIDs on, binlog retention longer than your longest pipeline outage, and no cascading foreign keys before 9.6.
• On PostgreSQL: wal_level=logical, a replica identity on every table without a primary key, slot monitoring with a max_slot_wal_keep_size cap, and a writer that handles unchanged TOAST values.

If you'd rather not run that layer yourself, Artie replicates MySQL and PostgreSQL, including RDS, Aurora, and Cloud SQL, into Snowflake, BigQuery, Redshift, Databricks, and more. Start for free or talk to the Artie team.

FAQ