Slow count

Nikolay and Michael discuss why counting can be slow in Postgres, and what the options are for counting things quickly at scale.

Here are some links to things they mentioned:
• Aggregate functions (docs) postgresql.org/docs/current/functions-aggregat…
• PostgREST github.com/PostgREST/postgrest
• Get rid of count by default in PostgREST github.com/PostgREST/postgrest/issues/273
• Faster PostgreSQL Counting (by Joe Nelson on the Citus blog) citusdata.com/blog/2016/10/12/count-performance
• Our episode on Index-Only Scans postgres.fm/episodes/index-only-scans
• Postgres HyperLogLog github.com/citusdata/postgresql-hll
• Our episode on Row estimates postgres.fm/episodes/row-estimates
• Our episode about dangers of NULLs postgres.fm/episodes/nulls-the-good-the-bad-th…
• Aggregate expressions, including FILTER postgresql.org/docs/current/sql-expressions.ht…
• Spread writes for counter cache (tip from Tobias Petry) x.com/tobias_petry/status/1475870220422107137
• pg_ivm extension (Incremental View Maintenance) github.com/sraoss/pg_ivm
• pg_duckdb announcement motherduck.com/blog/pg_duckdb-postgresql-exten…
• Our episode on Queues in Postgres postgres.fm/episodes/queues-in-postgres
• Our episode on Real-time analytics postgres.fm/episodes/real-time-analytics
• ClickHouse acquired PeerDB clickhouse.com/blog/clickhouse-acquires-peerdb…
• Timescale Continuous Aggregates timescale.com/blog/materialized-views-the-time…
• Timescale editions docs.timescale.com/about/latest/timescaledb-ed…
• Loose indexscan wiki.postgresql.org/wiki/Loose_indexscan

~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, (postgres.fm/people) or by commenting on our Google doc (docs.google.com/document/d/1PNGSn_d0A7gTR4C0p6…)!

~~~
Postgres FM is produced by:
• Michael Christofides, founder of pgMustard (pgmustard.com)
• Nikolay Samokhvalov, founder of Postgres.ai (postgres.ai)

With special thanks to:
• Jessie Draws for the elephant artwork