It's hard to be a developer today without using a database… but they're often surrounded by an air of reverent mystery.

One of those mysteries is why it's so slow to count all the rows in a table using COUNT(*). After all, it's just a matter of walking a b-tree and counting leaves… and that should be trivial to optimize!

In this talk I'll answer the question of "why COUNT(*) is slow" by taking a deep dive into the the internals of Postgres' MVCC implementation, looking at:

• The question of "why COUNT(*) is slow"
• The on-disk storage layout and why, under the hood, it's not a b-tree
• What Postgres means by MVCC, with examples of the utility of transactions
• Introduce xid, xmin, xmax
• Discuss tuple visibility
• Explain VACUUM and xid wraparound
• Show off transaction isolation levels

This talk is accessible to anyone who's used an SQL database, with enough depth that experienced developers will find some interesting tidbits.

About me: David is a Pythonista from Toronto, Canada. He's co-founder of both PyCon Canada – Canada's regional Python conference – and Akindi.com – a small company that's making multiple-choice bubble sheet tests a little bit less terrible. He's also the author of nose-parameterized, a parameterized testing for every Python testrunner, and pprint++, a Python pretty-printer that's actually pretty. Say hi on Twitter: @wolever!