This post is a quick summary of Why Uber moved from PostGres to MySQL.

PostGres Rows and CTID

• PostGres provides transactions. Transactions need multiple versions of data. So PG is Multi Versioned DB.
• PG considers each row immutable. Any change to a row creates a new row version.
• A row is represented as an offset in disk, called ctid
• Every row will have a unique ctid because they occupy a different space. However, multiple rows (ctid) can share same disk offset (e.g. multiple verisons of a row)

------------------
| Row 0 (ctid 1) |
------------------
                       ----------> Disk block offset x
------------------
| Row 1 (ctid 2) |
------------------

PostGres Index Management

• Each index has key and values as CTID
• Any change in a row’s data creates a new CTID. That would need changing all indexes.
• This is expensive because
  • PG uses WAL technique so each write is at least twice
  • PG replicates WAL to secondary nodes.
  • The WAL has CTID and disk offset level information.
  • Replication across geographies is expensive since data volume is high.

PostGres Replication

• At a secondary, while a transaction is in progress on a row, the WAL copy will wait.
• If transaction runs for a long time, PG will terminate the transaction after a timeout.
• So there are two problems:
  • Transactions can terminate unexpectedly
  • Replicas may be lagging the master more than exptected

Written with StackEdit.