$30 off During Our Annual Pro Sale. View Details »

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

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.

Citus Data

March 19, 2019
Tweet

More Decks by Citus Data

Other Decks in Technology

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