Multi-tenant SaaS apps made simple on Azure Cosmos DB for PostgreSQL | SQL Bits 2023 | Adam Wolk & Alicja Kucharcyzk

Cloud computing has given rise to a massive new market of SaaS applications built by ISV's and enterprises, however architecting these apps to optimize for underlying infrastructiure cost whilst still providing guarantee's of isolation and performance is hard. In this session, Adam and Alicja will showcase a number of unique feature in Azure Cosmos DB for PostgreSQL that enable developers to build and scale multi-tenant SaaS apps easily with a variety of tenant management features built natively into the database.

Transcript

1. 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
2. Software as a Service Squeezing margins out of your hardware
3. Multi-tenant SaaS • Same software • Same infrastructure • Multiple businesses/customers Customer value: Ease of operation Operator margin: Efficient use of hardware DNS
4. Sharding Models
5. Row based • 100k tenants per 16vCPU node • Adjust schema • Modify application queries Best hardware utilization
6. 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
7. 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
8. What is Postgres?
9. 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
10. Azure Why PostgreSQL? Postgres-as-a-Service on Azure Postgres at any scale: Azure Cosmos DB for PostgreSQL
11. Azure Cosmos DB for PostgreSQL Build Multi-tenant Scale-out ready
12. 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.
13. Data modelling How to efficiently shard your schema?
14. 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
15. Identify types of tables • Distributed table • Reference table • Local table
16. 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);
17. 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);
18. 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
19. Modify your application • Add distribution column to PK/FK • Add distribution key to queries
20. Monitoring What is the pulse of your system?
21. 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
22. Noisy neighbors
23. 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);
24. Scaling out
25. 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();
26. Thank You