There was a Postgres User Group meetup at Pivotal Labs today. The talk was about MVCC (Multi version concurrency control) in Postgres and why COUNT queries are slow. It seemed interesting enough, so I made my way towards King and Yonge. There were about 10 Postgres enthusiasts in attendance. I did learn a few interesting things though, and I also brushed up my knowledge after the meetup (Source 1, Source 2, Source 3). I’ve compiled some of my learnings below.

• There are 2 ways to achieve concurrency in relational ACID DBs :
  • 2 phase locking (2PL).
  • Multi Version Concurrency Control (MVCC).
• 2PL :
  • A shared lock blocks Writers, but it allows other Readers to acquire the same shared lock.
  • A exclusive lock blocks both Readers and Writers competing for the same lock.
  • Not scalable because too much contention.
• MVCC :
  • More scalable than 2PL because MVCC reduces (but doesn’t completely eliminate) contention due to locking.
  • Allows Readers to not block Writers and Writers to not block Readers. How?
    • Readers don’t need to acquire shared locks.
    • Writers require a relaxed version of exclusive locks. These locks are not as strict as 2PL exclusive locks because they don’t prevent Readers from reading the row (they only prevent concurrent Writers from updating the row). This means concurrent Writers can still block Writers since, once modified, a row is always locked until the transaction that modified this record either commits or rolls back.
  • There is no standard MVCC approach. Oracle and Postgres have different MVCC approaches.
• MVCC in Postgres :
  • Postgres has multiple versions of the same record i.e., rows are immutable (in 99.9% of the cases).
  • Updates or deletes just insert additional rows into the table, and newer rows have pointers to older rows via two hidden columns called xmin (transaction id of insert) and xmax (transaction id of update/deletion).
  • There’s a function called is_visible that determines which version of a row is visible to transactions.
  • Periodically, a VACUUM command is applied to the table in order to perform garbage collection on older rows. You can also apply VACUUM manually.
  • VACUUM can truncate the space used by the table, while VACUUM FULL still retains the entire space of the table.
• SELECT COUNT is slow because of how MVCC is implemented in Postgres. Because different transactions see different versions of the data means that COUNT can’t summarize accurately the number of rows. Imagine thousands of transactions and the resulting rows! Hence, the entire table (or index) has to be scanned sequentially. COUNT on indexes is faster because it limits number of records to be counted (since indexes are B Trees).
• To speed up count, query the stats collector. Another mechanism is to do an EXPLAIN and query the returned result (since EXPLAIN uses the stats colector).
• Other things I learnt unrelated to MVCC :
  • Postgres 11 introduced stored procedures. It didn’t have this before. In comparison, Oracle has had stored procedures for many many years now.
  • Sharding is also called horizontal partitioning.