Postgres, MVCC, and you or, Why COUNT(*) is slow (David Wolever)

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.
Presentation page -- 2017.pycon.ca/schedule/25/

[@MuhammadDaif]

I think my life has slightly changed to the better after watching this :D Thank you !

[@SteveSand]

Great presentation David, very understandable.

[@dengan699]

Introduction level talk..

[@nicholaslipanovich827]

This was really informative, thanks David!

[@scotteza]

This is a great talk, thanks for posting it!

[@kobi683]

This is a great and informative lecture!

[@sarvarnishonboyev4233]

Thank you for awesome presentations, it was really useful and informative.

[@CosmeJunior]

Amazing video, must watch again

[@cschandragiri]

Amazing explaination

[@rpbhagato]

excellent presentation.

[@Soham_Badjate]

great session!!

[@kacy6014]

Great breakdown

[@faraazali9589]

Dude great explanation! 👍

[@Patilsfact786]

Great informative

[@alexandershchegretsov9615]

I love it, thanx man, it was interesting

[@bobslave7063]

best explanation ever!

[@calvinchankf]

david thanks for your presentation.....however if i really need to count the total number of rows of a table which has over 10 million records, what should i do?

[@alexanderroussakov2263]

maintain it as single value in another table ;)

[@Adaetro]

If you semi recently performed "analyze" you probably have count number saved somewhere in statistics of that table

[@ai__76]

This was really informative... The sound and picture were not synced!

[@jaskaranbindra9939]

cool

[@been2082]

From the video blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html