Scaling your SaaS Database with Postgres

If you're building a SaaS application, you probably already have the notion of tenancy built into your data model. Typically, most information relates to tenants / customers / accounts, and your database tables capture this natural relation.

With smaller amounts of data (10s of GB), it's easy to throw more hardware at the problem and scale up your database. As these tables grow however, you need to think about ways to scale our multi-tenant database across dozens or hundreds of machines. In this webinar, we cover when you should start thinking about scaling out, common architectural patterns for scaling multi-tenant databases, and how you can migrate from an existing PostgreSQL instance to one that linearly scales out.

[LeLario]

In case of Sharding, how is primary key created? is it a clustered composite (tenant_id, campain_id) or a primary key on campain_id and index on (tenant_id, campain_id)? Thanks.

[simoncharette]

I unfortunately couldn't attend the webinar but I would have a question concerning the case where tenant can have different schemas. We've tried going the JSONB path but as we need to allow running queries against these fields, and not simply storing them, our query were significantly slower than concrete columns (10x). We then experimented by partially indexing (WHERE json_column ? 'field') these JSONB column fields by their actual data type ((jsonb_column -> 'field')::bool)) for integrity and and speed purpose. This managed to speed up the queries but it introduced an expected slow down on writes and a large storage overhead. Do you have any solution to propose for this issue?