Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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.

More Decks by Azure Database for PostgreSQL

Other Decks in Technology

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 full-size slide

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

    View full-size 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 full-size slide

  4. Sharding Models

    View full-size slide

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

    View full-size 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 full-size 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 full-size slide

  8. What is Postgres?

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

  13. Data modelling
    How to efficiently shard your schema?

    View full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size slide

  20. Monitoring
    What is the pulse of your system?

    View full-size 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 full-size slide

  22. Noisy neighbors

    View full-size 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 full-size slide

  24. 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 full-size slide