The TopN extension: Maintaining Top 10 lists at scale | PGConf EU 2019 | Furkan Sahin

Whether it's the biggest, oldest, fastest, slowest one of everyone's favorite questions about data is: What is the top 10? SQL offers easy solutions like "SELECT item, count(*) FROM data GROUP BY item ORDER BY 2 DESC LIMIT 10", but if you have a lot of data and a dashboard that can generate many ever-changing top 10 lists, then such queries don't scale at all. What we need is to be able to incrementally update top N lists as new data comes in. PostgreSQL did not have an easy way to do that yet, so we added it via the postgresql-topn extension.

In this talk, I will focus on the PostgreSQL extension postgresql-topn (new as of March 2018). TopN can provide approximate answers to these kinds of aggregation queries within reasonable and configurable error bounds. TopN is not only fast and memory-efficient but also has very interesting properties which especially shine in a distributed environment. In this talk, I will talk about the internals of TopN and how it estimates top items. At the end of this session, you will have fallen in love with the capabilities of TopN and you will be adding it to your analytics tool belt.

Transcript

1. The TopN extension: Maintaining top 10 lists at scale • Furkan Şahin • Software Engineer • Citus Data @Microsoft • Author of postgresql-topn extension @sahinffurkan @furkansahin
2. Presentation Flow • TopN lists and their usages • How to calculate TopN lists in PostgreSQL? • Why do we need an extension? • How does postgresql-topn work? • How do we use postgresql-topn? • Demo • Summary @sahinffurkan @furkansahin
3. TopN Lists and Their Usages How to calculate TopN lists in PostgreSQL? Why do we need an extension? How does postgresql-topn work? How do we use postgresql-topn? Demo Summary
4. TopN Lists and Their Usages Spotify: Your Top Songs 2018 IMDB: Top Rated Movies E-commerce: Most selling items @sahinffurkan @furkansahin
5. How to calculate TopN lists in PostgreSQL SELECT item, count(*) FROM table GROUP BY 1 ORDER BY 2 DESC LIMIT 10; SELECT topn(topn_add_agg(item),10) FROM table; @sahinffurkan @furkansahin
6. Why do we need an extension? How does postgresql-topn work? How do we use postgresql-topn? Demo Summary TopN Lists and Their Usages How to calculate TopN lists in PostgreSQL
7. Why do we need an extension? Continuous calculations Memory usage Scanning CPU usage Sorting @sahinffurkan @furkansahin
8. Why needed a TopN extension? High load times @sahinffurkan @furkansahin
9. Solution? An extension that uses count-min sketch Low memory footprint Low CPU usage Facilitate pre- processing @sahinffurkan @furkansahin
10. How does postgresql-topn work? How do we use postgresql-topn? Demo Summary TopN Lists and Their Usages How to calculate TopN lists in PostgreSQL Why do we need an extension?
11. How does postgresql-topn work? Count-min sketch Limited number of counters @sahinffurkan @furkansahin
12. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Heavy fuel 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
13. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Heavy fuel 1 Back in Black 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
14. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Back in Black 2 Heavy fuel 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
15. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Back in Black 3 Heavy fuel 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
16. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Back in Black 3 Heavy fuel 1 Englishman in New York 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
17. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Back in Black 3 Heavy fuel 2 Englishman in New York 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
18. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 Item Freq Back in Black 3 Heavy fuel 2 Englishman in New York 1 Breakfast in America 1 Wipe the bottom half SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin
19. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 No more data SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin Item Freq Back in Black 3 Heavy fuel 2
20. Example: Calculating the Top 1 song Date Music 10-10-2019 Heavy Fuel 11-10-2019 Back in Black 12-10-2019 Back in Black 13-10-2019 Back in Black 13-10-2019 Englishman in New York 14-10-2019 Heavy Fuel 14-10-2019 Breakfast in America topn.number_of_counters = 1 SELECT topn(topn_add_agg(Music),1) FROM my_music_history; @sahinffurkan @furkansahin Item Freq Back in Black 3
21. How does postgresql-topn work? topn(column::JSONB, n::int) => returns Tuple Set(item, frequency) topn_add_agg(column::text) => returns JSONB topn_union_agg(column::JSONB) => returns JSONB topn_add(column::JSONB, data::text) => returns JSONB topn_union(column_1::JSONB, column_2::JSONB) => returns JSONB @sahinffurkan @furkansahin
22. Why JSONB? Human readable Portable Easy to process @sahinffurkan @furkansahin
23. How do we use postgresql-topn? Demo Summary TopN Lists and Their Usages How to calculate TopN lists in PostgreSQL Why do we need an extension? How does postgresql-topn work?
24. How do we use postgresql-topn? Pre-aggregation Materialization of TopNs
25. Demo Summary TopN Lists and Their Usages How to calculate TopN lists in PostgreSQL Why do we need an extension? How does postgresql-topn work? How do we use postgresql-topn?
26. Summary Requires pre-processing for performance gain Low memory footprint Approximates the results Open source Production ready
27. Resources • https://github.com/citusdata/postgresql-topn • TopN for your Postgres database https://www.citusdata.com/blog/2018/03/27/topn-for-your-postgres-database/ @sahinffurkan @furkansahin
28. Thank you @sahinffurkan @furkansahin