© Copyright Microsoft Corporation. All rights reserved. Leandro Santana linkedin.com/in/leandroeredia/ @citusdata @AzureDBPostgres Citus Hyperscale Overview

No content

Postgres

PostgreSQL is more popular than ever PostgreSQL at core of many digital transformations Open source Proven resilience & stability Rich feature set enterprise-ready • Zero data loss • Rich indexing and data types, including geospatial • Low TCO. No Lock-in • Innovate faster with broad ecosystem

Single Server Fully-managed, single-node PostgreSQL Hyperscale (Citus) High-performance Postgres for scale out Enterprise-ready, fully managed community PostgreSQL with built-in HA and multi-layered security

Monitor Azure Database for PostgreSQL with Azure Monitor Azure Database for PostgreSQL tracks performance metrics and logs telemetry data Collect, analyze, and take action on telemetry data gathered from your database using Azure Monitor Better understand your apps with deep insights into app and database behavior, view alerts, and build remediation plans Azure Monitor Visualize Analyze Respond Integrate Insights

Migrating to Azure Database for PostgreSQL Opportunity DBAs spending time maintaining their databases, not focused on their apps On premises PostgreSQL VM PostgreSQL Other Managed Services Azure Database Migration Services Azure Database for PostgreSQL Solution Azure Database for PostgreSQL takes care of patching, high availability, and performance management Provides added benefits of comprehensive, intelligent security and compliance, including threat alerts

Migration from Oracle to Azure Database for PostgreSQL Single instance of Oracle Opportunity Oracle license cost is expensive and additional features (such as partitioning and high availability) come at added cost Solution Migrating to Azure Database for PostgreSQL removes need for licenses as the customer can take advantage of open source PostgreSQL Azure Database for PostgreSQL Azure Database Migration Services

Citus extension to Postgres

What is Citus? • Distributed tables • Reference tables • & more, as of Citus 10 Extension to Postgres (not a fork!) • Add nodes • Rebalance Simplicity & flexibility of using PostgreSQL, at scale • Scale transactional workloads • Scale analytical workloads • Mixed workloads too Multi-purpose:

aka.ms/citus

Why

Citus

• Joins • Functions • Constraints • Indexes: B-tree, GIN, BRIN, & GiST • Partial Indexes • Other extensions • PostGIS • Rich datatypes • JSONB • Window functions • CTEs • Atomic update / delete • Partitioning • Interactive transactions • Open source • … Citus

Workloads that benefit from PostgreSQL CITUS WORKLOADS Workloads that benefit from scaling out

Multi-tenant SaaS Time series Analytics dashboards Mixed OLTP/OLAP

Citus Coordinator Hyperscale (Citus) Coordinator Hyperscale (Citus) Workers Hyperscale (Citus) Coordinator Standard Tier in Hyperscale (Citus) Basic Tier

SELECT create_distributed_table( 'table_name', 'distribution_column');

Citus Coordinator Choosing the distribution column for Multi-Tenant scenarios SELECT create_distributed_table('companies', 'id’); SELECT create_distributed_table('campaigns', 'company_id’); SELECT create_distributed_table('ads', 'company_id’); SELECT create_distributed_table('clicks', 'company_id’); SELECT create_distributed_table('impressions', 'company_id');

Citus Coordinator Choosing the distribution column for Real-Time scenarios SELECT create_distributed_table('github_users', 'user_id’); SELECT create_distributed_table('github_events', 'user_id');

Rest of the tables can be reference tables or local table SELECT create_reference_table( 'table_name')

APPLICATION CREATE TABLE campaigns (…); SELECT create_distributed_table( 'campaigns','company_id'); METADATA COORDINATOR NODE WORKER NODES W1 W2 W3 … Wn CREATE TABLE campaigns_102 CREATE TABLE campaigns_105 CREATE TABLE campaigns_101 CREATE TABLE campaigns_104 CREATE TABLE campaigns_103 CREATE TABLE campaigns_106 How Citus distributes tables across the database cluster

APPLICATION SELECT FROM GROUP BY company_id, avg(spend) AS avg_campaign_spend campaigns company_id; METADATA COORDINATOR NODE WORKER NODES W1 W2 W3 … Wn SELECT company_id sum(spend), count(spend) … FROM campaigns_2009 … SELECT company_id sum(spend), count(spend) … FROM campaigns_2001 … SELECT company_id sum(spend), count(spend) … FROM campaigns_2017 … Query across distribution columns.

Transactions routed to a single node BEGIN; UPDATE SET WHERE COMMIT; campaigns start_date = '2018-03-17' company_id = 'Pat Co'; METADATA W1 W2 W3 … Wn BEGIN; UPDATE Campaigns_2012 SET …; COMMIT; APPLICATION COORDINATOR NODE WORKER NODES

Node A Node B Node C 1 2 4 5 3 6

Node A Node B 1 2 4 5 3 6 Node C Node A Node B Node C 1 2 4 5 3 6

Node A Node B 1 2 3 4

1 4 3 2 Node A Node B

10M+ queries per day; 75% https://techcommunity.microsoft.com/t5/Azure-Database-for- PostgreSQL/Architecting-petabyte-scale-analytics-by-scaling-out-Postgres-on/ba- p/969685

Min Wei, Principal Engineer at Microsoft Distributed PostgreSQL is a game changer." aka.ms/blog-petabyte-scale-analytics

Storage efficiency IOPs Query speed Cost Savings Benefits of Citus Columnar

Row-based Storage Columnar storage

CREATE TABLE events_columnar( ts timestamptz, i int, n numeric, s text) USING columnar; It’s as simple as “USING columnar;”

Can change access method from heap à columnar SELECT alter_table_set_access_method( 'events_2021_jan', 'columnar');

Citus Columnar && Range Partitioning in Postgres CREATE TABLE events( ts timestamptz, i int, n numeric, s text) PARTITION BY RANGE (ts); CREATE TABLE events_2021_jan PARTITION OF events FOR VALUES FROM ('2021-01-01') TO ('2021-02-01'); CREATE TABLE events_2021_feb PARTITION OF events FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

events table …

events table …

events table

No content

Multi-tenant (SaaS) tutorial aka.ms/hyperscale-citus-multi-tenant-tutorial

Tutorial: Real-time analytics dashboard aka.ms/hyperscale-citus-real-time-tutorial

© Copyright Microsoft Corporation. All rights reserved. Leandro Santana linkedin.com/in/leandroeredia/ @citusdata @AzureDBPostgres Thank you!