Using Postgres and Citus for Lightning Fast Analytics, also ft. Rollups | Live Demo | Sai Srirampur

Watch Sai Srirampur, Solutions Engineer at Citus Data (now part of the Microsoft family), give a live demo of how you can use Postgres and the Citus extension to Postgres to manage real-time analytics workloads.

View if you & your application need:

>> A relational database that scales for customer-facing analytics dashboards, with real-time data ingest and a large volume of queries

>> A way to scale out Postgres horizontally, to address the performance hiccups you’re experiencing as you run into the resource limits of single-node Postgres

>> A way to roll-up and pre-aggregate data to build fast data pipelines and enable sub-second response times.

>> A way to consolidate your database platforms, to avoid having separate stores for your transactional and analytics workloads

Using a 4-node Citus database cluster in the cloud, Sai will show you how Citus shards Postgres to give you lightning fast performance, at scale. Also featuring rollups.

Transcript

1. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 WITH FROM Thu 27 February at 10am PT Using Postgres & Citus for Lightning Fast Analytics PRESENTED BY: SAI SRIRAMPUR LIVE DEMO
2. Sai Srirampur | PyConCA 2018 • Sai Srirampur a.k.a Sai • Engineer at Citus Data (now part of the Microsoft family!!) • Joined Citus to make it so developers never have to worry about scaling their database • Recently got married! • Follow me @saisrirampur @citusdata
3. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Does this sound like you? Hitting resource limits with single-node Postgres, but don’t want to give up Postgres 1
4. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Want to reduce dev costs by consolidating—& avoid separate stores for analytics (OLAP) & transactional workloads (OLTP) Does this sound like you? 2
5. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Does this sound like you? Pre-aggregation or rollup pipeline lags and never catches up 3
6. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
7. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Why Postgres? TLDR; Open source Constraints Extensions PostGIS / Geospatial HLL, TopN, Citus Foreign data wrappers Rich SQL CTEs Window functions Full text search Datatypes JSONB
8. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Q: Why Citus? A: Citus transforms Postgres into a distributed database
9. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 TLDR; on scaling out Postgres w/Citus 1. Distributes data across multiple nodes 2. More memory, cpu, disk + parallelization 3. Extension to Postgres (not a fork) DATABASE AS A SERVICE ENTERPRISE SOFTWARE OPEN SOURCE
10. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 10
11. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
12. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 What is a rollup? Period Customer Country Site Hit Count SELECT… Pre-computed aggregates for a period and set of (group by) dimensions. Can be further filtered and aggregated to generate charts.
13. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 What are the benefits of rollups? • Fast (indexed) lookups of aggregates • Avoid expensive repeated computation • Rollups can be further aggregated • Rollups are compact, can be kept over longer periods
14. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 What is hll? • Approximation algorithm (“sketch” algorithm) • Estimates COUNT(DISTINCT)/cardinality of given data • Extension on postgres: hll • hll data type to store distinct values
15. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Already provisioned a 4-node Citus database cluster for today’s demo / & regular Postgres on single node
16. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Load data Create indexes (btree and GIN) Query patterns in analytics workload Rollup queries What I will show you in today’s analytics demo? Citus 4-node cluster Single node Postgres TOP BOTTOM Load data Create indexes (btree and GIN) Query patterns in analytics workload Rollup queries
17. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Sai Srirampur | PyConCA 2018
18. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 1/ Parallelism 2/ Linear scale 3/ Freshness (real-time, concurrency) 4/ Both transactional && analytics
19. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 1/ Faster rollups - parallelism - no lag 2/ Increase granularity of period 3/ Store and query longer periods of data 4/ Extensions - hll, topn, pg_cron, pg_partman Enhance your rollup pipeline with
20. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 Sai Srirampur | PyConCA 2018 citusdata.com/customers/heap citusdata.com/customers/heap
21. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 21 citusdata.com/customers/algolia
22. Scaling Postgres for Time Series Data with Citus | Nov 15 2018 | Marco Slot | Claire Giordano Using Postgres and Citus for Lightning Fast Analytics | February 2019 Min Wei of Microsoft
23. Scaling Postgres for Time Series Data with Citus | Nov 15 2018 | Marco Slot | Claire Giordano Using Postgres and Citus for Lightning Fast Analytics | February 2019
24. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
25. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019 25 Any Questions?? (And, resources) DOWNLOAD CITUS OPEN SOURCE: https://www.citusdata.com/download CREATE CITUS CLOUD DEV ACCOUNT: https://www.citusdata.com/product/cloud JOIN OUR PUBLIC SLACK: https://slack.citusdata.com/
26. Live Demo of Citus Cloud | July 2018 www.citusdata.com @citusdata © 2019 Citus Data. All rights reserved. Sai Srirampur | @saisrirampur Thank you for your time