
Designing a SaaS Database for Scale with Postgres - mixmastamyk
https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/
======
e1g
We serve enterprise customers, and use the 'separate database' strategy for
these reasons -

1\. Stronger data isolation. Technically schemas provide the same level of
isolation, but they are much harder to explain or defend during compliance
audits.

2\. More secure backups. Client data can be encrypted with their own key and
stored as per their needs.

3\. More useful backups. The frequency and retention of backups can vary to
meet the SLA requirements (and costs). And we if something goes wrong, we can
recover that particular customer's data in isolation without impacting the
rest of the application/customers or worry about merging it in.

4\. Secure data deletion. Many European customers demand that _all_ their data
is securely removed upon termination. This creates a massive problem with
purging backup information if everything is in the same dump.

5\. Independent load & performance. If one customer is particularly high-load
or chatty, we can move them onto a separate server where they don't impact
well behaved folks.

6\. Easier horizontal scalability. Just move heavy loads to their own servers
+ read replicas.

7\. Direct access to data. Specifically, we can use BI tools that do not
understand schemas and even give direct access to clients' analysts.

8.Independent migration paths. Sometimes the customer's timetable does not
allow them to upgrade to the newest version right now (e.g. they are in the
middle of using the app for a key process this month). We can leave their
account routed to the previous version in both the codebase and the data
store.

Out of those, the key 3 reasons are: stronger data isolation, better backup
strategy, and more predictable compliance story. But that's enterprise: even
if we're widely successful, we'll have "thousands" of customers - never
millions. And we can manage thousands of databases, so this architecture path
is preferable to us within those boundaries.

~~~
noxee
How do you handle look ups that would require cross tenant queries? e.g. each
customer has multiple devices connected devices to our system and we want to
know the health (is it online, is it responding, etc).

The one way I've thought of current is either having an aggregate table in a
"central" database that would be used to collect these kind of statistics. It
would be "real-time" but it would be near real-time depending on the frequency
of updates. The downside is you have the over head of maintaining a separate
data source.

The other option was to just have the software set up to query each tenant at
a time and take the performance/time hit. That's not really the best
experience and probably violates the idea of data isolation.

~~~
xemdetia
I'm having a trouble understanding what you are saying by cross-tenant queries
in context. What you are describing is application monitoring which would be
completely independent of the DB holding customer data, which is what they are
describing in your parent's post and the OP. Application runtime/health status
could trivially be handled in a separate stream.

~~~
noxee
It's not really the application runtime/health status, it's the status of
individual assets for each tenant. So a scenario would be as technical support
person I would like to look at what customer assets are currently off-line
across all tenants.

One way to do that would be to query each individual tenant database and then
combine the information. But this would void the idea of security/isolation.
The other downside is that it's slow.

But I think I've been looking at this from the wrong point of view, there
should be a central health check service for these assets that collects the
status of the assets and then that would be queried by the support person.

~~~
xemdetia
Generally I see it the other direction as a technical support person- each
tenant's host is responsible for reporting uptime to a central service
(because it's SaaS) and the customer is aware of this (even if it's basic
syslog forwarding). Because we are not reaching in and they are sending out
their customer data is still isolated.

Any time you are reaching into the customer datasets that is where you have
the possibility of breach via that access and would have to make sure all of
those events are audited and that audit records are secure. If it is sending
monitoring data to an aggregation service that can be well-defined in contents
and much easier to demonstrate that no sensitive tenant information is shared.

It's much easier and saner to setup a server to receive and configure each
tenant's host to send exactly what is needed and then analyze who didn't send
their stuff at regular intervals for a more direct investigation and everyone
can agree on what needs to be done based on that.

~~~
noxee
Yeah I agree with what you're saying, as I said I was looking at the problem
somewhat naively.

When you talk about having each tenant's host do you mean a dedicated
application server for each host? The current setup we have is that we have
multiple tenant databases but only two application servers providing
functionality to them, so there's a switching cost on the application servers
because they can't maintain a connection pool to the databases (they also have
to know the credentials for the databases.

It seems excessive if you have a 100 tenants to have your infrastructure for
serving the application replicated 100 times but maybe we're just haven't
quite built our system correctly yet.

~~~
xemdetia
In the model I am describing every tenant component would have an agent that
was reporting the state of that tenant. If there was a shared component/pool
that wouldn't be reporting separately, I'm really only describing the tenant
databases/tenant-specific files. So if you had a tenant database, there should
be an agent that just is reporting on that tenant database.

As for separation of the application frontend it really is a risk assessment
and culture thing. If the team understands that making sure you never cross
streams of customer data is a critical requirement which is one of the first
viability questions in the design then sharing really isn't too bad. If your
team struggles with this or has recurring issues to this end separate
application servers make more sense. Generally if you have tenants with data
exposing another customer's data is a quick way to have trust in a product
evaporate, I know I have had this happen as a customer where I was able to
show a bug where I was getting another customer's accounting information in a
cloud app which caused me to terminate the contract.

If you guys are minding the soup of the application servers well enough
there's nothing say you can't, there are many people that do it.

------
memracom
Salesforce has successfully deployed multitenancy SaaS at a huge scale, and
they have all tenants sharing the same tables. Every row in every table has an
Organization-ID column and all queries include the Org Id to keep the data
separated. They built their SaaS on top of Oracle RDBMS but are now using
PostgreSQL in some not yet publicized way.

Google for Salesforce multitenant architecture and you will find several
presentations/videos where they explain much of this architecture. IMHO this
is required reading for anyone designing any sort of multitenant SaaS because
you are less likely to make disastrous mistakes when you can compare your
solution to a known successful solution, and explain where your solution
differs and why.

~~~
ngrilly
> They built their SaaS on top of Oracle RDBMS but are now using PostgreSQL in
> some not yet publicized way.

How do you know that?

~~~
dragonwriter
There have been published reports that they are trying to move to PostgreSQL
specifically, and off of Oracle more generally. This is an example (with links
to some others): [http://fortune.com/2016/05/11/salesforce-two-software-
worlds...](http://fortune.com/2016/05/11/salesforce-two-software-worlds/)

~~~
collyw
Interesting, but I am surprised by the quote:

"A source with knowledge of the effort said a database change could give
Salesforce flexible technology that could be more easily used across many data
centers."

I though Oracle did pretty much everything, but cost a fortune.

------
davidroetzel
The articles mentions the approach to have one schema per tenant but sadly
does not mention this again afterwards.

Are there serious issues with this approach I am not aware of?

~~~
craigkerstiens
Craig from Citus here. There are a number of issues with one schema per
tenant, the biggest one is that at a larger scale Postgres will mostly just
not work anymore. Things like pg_dump start to fall over and while this has
been improved some, there is still an upper limit somewhere between 1,000 and
10,000 tenants. Further having to then run schema migrations against all of
them can be quite painful. There are tools that help on the schema migration
front, but what we've seen is that again at scale things start to break. If
you'll only ever have 100 customers then by schema can work for you.

~~~
marktangotango
Performance, management, SLA are some big issues with any "SQL as a service"
project. How can you garuntee 1) disk utilization per tenant 2) CPU
utilization per tenant and 3) transaction volume per tenant? Most simply, I
don't believe you can, and we concluded it's simply not worth going down this
path of shared, multi-tenant database (postgresql not withstanding, any RDBMs
would have the same issues).

Our conclusion was that the only way to get the required level of management
per tenant, and to support truly massive number of tenants, was to use an
inprocess database over https ie SQLite and Apache. But, SQLite has an image
problem, it's everywhere, and nowhere. It's built with some fundamentally
different decisions than other databases, and isn't traditionally used for web
applications.

So that's the course we took, links in my profile for more info.

~~~
ngrilly
> use an inprocess database over https ie SQLite and Apache

Why not, but you introduced a whole bunch of new issues. SQLite only supports
a single writer at a time. This is a problem if you have a lot of users on the
same tenant. This is also a problem when you need to create an index, for
example, which is not a background operation.

~~~
marktangotango
Indeed there are trade offs, and high write applications are a weak spot of
this approach. But for low to medium write applications (ie most
applications?), SQLite WAL [1] option performs really well. We implement
application level caching via a X-Query-Cache header[2], in that case, you're
serving directly from redis. This set up can scale really, really well.

[1] [https://www.sqlite.org/wal.html](https://www.sqlite.org/wal.html)

[2] [https://www.lite-engine.com/docs.html#caching](https://www.lite-
engine.com/docs.html#caching)

------
prabhatjha
Multi-tenancy at scale with RDBMS is just hard to do. Once you get to certain
volume, you just have to have different data stores for different needs. Only
a subset of business require ACID properties so you would use RDBMS for those
and for the rest choose a NoSQL stack that has the least resistance to ramp up
WRT team skill set.

You can get away without using NoSQL but you probably would end up spending
time and money maintaining several DB clusters, migrations, handling
replication lags etc.

~~~
dragonwriter
> Only a subset of business require ACID properties so you would use RDBMS for
> those and for the rest choose a NoSQL stack

While ACID is an important and nice set of properties of RDBMS's, its not the
only reason to choose them; its a lot more adaptable to changing query
patterns and needs than less-structured document-oriented datastores. There
are sometimes performance and other reasons to choose some non-relational
datastore for a particular load, but "don't need ACID guarantees" is, itself,
an insufficient reason for abandoning the RDBMS model for NoSQL (don't need
ACID guarantees, of course, is a _necessary_ condition to selecting a non-ACID
store, but not sufficient to choose one.)

~~~
prabhatjha
ACID was just an example. Thanks for clarifying it though.

------
gkop
What are best practices for gracefully allowing end users to use a single
login for multiple of your SaaS tenants when the tenants are in separate
databases?

~~~
icebraining
I'd say OAuth, like StackExchange does. Then you can run your own
authentication and/or accept others like Google. In my experience (we run a
db-per-tenant service, with OAuth for internal support users), it works fine.

------
lifeisstillgood
to;dr denormalise a bit so that primary keys represent a full ancestor path to
the "usual" primary key, making shardingnand collocation of customers much
simpler

And use Postgres:-)

Seems a nice article but I kind of assume that to be basic knowledge amount
DBAs ?

~~~
lucaspiller
How does this compare to having a separate Postgres schema per tenant? You'd
need to make your app a bit more complicated (to set the right schema before
every query), but you won't need to denormalise.

~~~
lifeisstillgood
This is really a judgement call. If you are expecting volumes of tenants in
the thousands, a schema per tenant is slightly crazy. I would personally only
think of a schema or database per tenant if I had either customisation issues
(If most of the sale is in professional services) or worse if we have data
regulation issues (an Indian tenant, an EU tenant and a US tenenat would be
challenging to manage the complex data transfer laws around, and it might be
easier to have geographically located servers)

But unless you have few small expensive clients then I cannot see much value
in multi-tenanting via schema or database. It's hard enough keeping that in
mind when using Dev/UAT/prod schemas !

------
harel
I use uuid as primary key when I plan for scale and future sharding. It's
negligible performance hit of at all and I can move entire datasets from one
db to another without worrying about references.

