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
   

   View Slide

2. Software as a Service
   Squeezing margins out of your hardware
   

   View Slide

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

   View Slide

4. Sharding Models
   

   View Slide

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

   View Slide

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
   

   View Slide

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
   

   View Slide

8. What is Postgres?
   

   View Slide

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
   

   View Slide

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

    View Slide

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

    View Slide

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.
    

    View Slide

13. Data modelling
    How to efficiently shard your schema?
    

    View Slide

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
    

    View Slide

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

    View Slide

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);
    

    View Slide

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);
    

    View Slide

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
    

    View Slide

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

    View Slide

20. Monitoring
    What is the pulse of your system?
    

    View Slide

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
    

    View Slide

22. Noisy neighbors
    

    View Slide

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);
    

    View Slide

24. Scaling out
    

    View Slide

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();
    

    View Slide

26. Thank You
    

    View Slide