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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

10. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
    10
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

17. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
    Sai Srirampur | PyConCA 2018
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

21. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
    21
    citusdata.com/customers/algolia
    

    View Slide

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
    

    View Slide

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
    

    View Slide

24. Live Demo of Using Postgres and Citus for Lightning Fast Analytics, also featuring Rollups | March 2019
    

    View Slide

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/
    

    View Slide

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
    

    View Slide