Multi-tenant SaaS apps made simple on Azure Cosmos DB for PostgreSQL Adam Wołk, Senior Program Manager Alicja Kucharczyk, Senior Program Manager @ Citus Con: An Event for Postgres 2023

Software as a Service Squeezing margins out of your hardware

Multi-tenant SaaS • Same software • Same infrastructure • Multiple businesses/customers Customer value: Ease of operation Operator margin: Efficient use of hardware DNS

Sharding Models

Row based • 100k tenants per 16vCPU node • Adjust schema • Modify application queries Best hardware utilization

Row based vs Schema Based • 100k tenants per 16vCPU node • Adjust schema • Modify application queries Best hardware utilization • 4-5k tenants per 16vCPU node • No schema changes • No query changes Easy lift & shift

Row based vs Schema Based vs Database Sharding • 100k tenants per 16vCPU node • Adjust schema • Modify application queries Best hardware utilization • 4-5k tenants per 16vCPU node • No schema changes • No query changes Easy lift & shift • 100 tenants per 16vCPU node • No schema changes • No query changes Highest operational cost

What is Postgres?

Why We Postgres • Open Source • Constraints • Extensions • PostGIS • Citus • B-tree, GIN, BRIN, & GiST • Available as a database service • Decades of robustness • Millions of happy users • Foreign data wrappers • Window functions • CTEs • ACID • Full text search • JSONB • Rich datatypes • pg_stat_statements • Rollups

Azure Why PostgreSQL? Postgres-as-a-Service on Azure Postgres at any scale: Azure Cosmos DB for PostgreSQL

Azure Cosmos DB for PostgreSQL Build Multi-tenant Scale-out ready

Terminology Coordinator – Stores Metadata. Node which application connects to. Worker / Data nodes – Nodes which store data in form of shards. Sharding – Process of dividing data among nodes. Shards – A partition of the data containing a subset of rows.

Data modelling How to efficiently shard your schema?

Pick a distribution key • Good (high) cardinality • Filters reads to a single node • Fans out writes to multiple nodes Bad vs Good country vs unique_carrier dest or origin airport vs unique_carrier timestamp vs device_id

Identify types of tables • Distributed table • Reference table • Local table

Distributed Tables Definition: • Tables that are sharded. Classification: • Large tables (>10GB) – shard on same key (may require addition of shard key) • All tables are be co-located • Enables localized and fast joins on workers • Ex: transactions, events etc SELECT create_distributed_table(table_name, column_name);

Reference Tables Definition: • Replicated to all the nodes (extra latency) Classification: • Small tables < 10GB • Efficient joins with distributed tables • Cannot have sharding dimension • Ex: countries, categories SELECT create_reference_table(table_name);

Local tables • Plain Postgres tables on the coordinator node. • Admin Tables that don’t interact with main tables • Separate micro-service that doesn’t need sharding

Modify your application • Add distribution column to PK/FK • Add distribution key to queries

Monitoring What is the pulse of your system?

Monitor – upcoming in Citus 11.3 • Query counts per tenant • CPU utilization per tenant 10 80 80 50 50 70 30 1k 2K 100k 1k 99k 0.5k 0.5k

Noisy neighbors

Isolate tenant SELECT isolate_tenant_to_new_shard('table_name', tenant_id); SELECT citus_move_shard_placement(shard_id, source_node_name, source_node_port, target_node_name, target_node_port);

Scaling out

Rebalance { "tasks": [ { "LSN": { "lag": null, "source": "0/731ACC0", "target": null }, "size": { "source": "14 MB", "target": "5440 kB" }, "hosts": { "source": "localhost:9702", "target": "localhost:9700" }, "phase": "Copying Data", "state": "running", "command": "SELECT pg_catalog.citus_move_shard_placement(102020,2,3,'block_writes')", "message": "", "retried": 0, "task_id": 10 } ], "task_state_counts": { "done": 9, "blocked": 1, "running": 1 } } select * from citus_rebalance_start(); select jsonb_pretty(details) from citus_rebalance_status();

Thank You