Designing a SaaS Database for Scale with Postgres
citusdata.com
citusdata.com
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.
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.
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.
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.
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.
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.
In this case, how is the business logic (codewise) handled across different states of databases?
We don't support very old versions, mind you, except for security fixes.
Almost all the points above are the precise reason we went database-per-tenant. We also chose this method because our system was self-hosted originally (Single DB, non-multi-tenant) for 4 years or so. Adding columns to each table to separate was not ideal and we didn't want to run the risk of 'forgetting a where clause' at any point.
For those wondering, all of our databases have the same schema. We use Visual Studio Database Projects to handle updates to keep it all in sync.
"Captain Obvious" out!
I had a couple of colleges working for the banking financial sector and they where pretty high strung most of the time. They where a bit more direct in regards to putting money on the table to get shit done.
It was very much not a tech company, although there were a few good people in the org. There was of course a big focus on buying supported packages, and off-shoring as much as possible. I understand buying "non core competency commodities", but the off shoring was dubious.
The custom stuff in my unit was very much neglected, except for integration projects when changing vendors, which of course have to happen when you move some of your dependencies.
Anyway, uptime was VERY important, which also implies a fair amount of risk management/reduction. In fairness, buying external services does make costs much more predictable. However, if you are on the vendor side of this, you need to make sure you build in pricing to cover what will be a very demanding customer.
(they of course did some other ignorant things which made this an issue, as well)
Exactly. Unless you're ACV is $100k+ this is strategy isn't really sustainable and might materially impact your gross margins.
EDIT: Further, read the more technical limitations of this approach: https://news.ycombinator.com/item?id=12652021
Why? We have customers with ACVs under $10k (even after adjusting for cost of living/general prices level) with this model. We simply automated the whole process, so the clients can register for our service and get a new database (created from a template) without any manual intervention.
Still, we'll never hit anything close to 1000 customers per server, we distribute them over more servers instead. In fact, that's an advantage of this model over the single-database one: since every tenant is isolated, the model is perfectly horizontally scalable. You can simply fill up servers without worrying about massively distributed databases or datacenter-wide load balancing. Hell, we just migrated across hosting providers with barely breaking a sweat: we simply moved machine by machine without ever worrying about latency between DCs and all that.
I frankly prefer this approach, since it completely bypasses the problems that come with sharding and distributed deployments, since every customer is completely isolated. I just wish there was more tooling available that supported our architecture.
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.
How do you know that?
"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.
https://www.youtube.com/watch?v=jrKA3cJmoms
(Salesforce Multitenant Architecture: How We Do the Magic We Do)
Are there serious issues with this approach I am not aware of?
We are a b2b product and we've picked up customers around the world. Now it seems like it might make sense to shard geographically. Is that common?
You run into corner cases when a customer wants to operate into two distinct geographical areas, so basically you may have to maintain a central repository of tenants and, ultimately, under the hood, your tenant primary keys are not handled via local sequences.
We had to write a custom backup script to handle individual backups, taking advantage of the knowledge of our own database architecture (basically: we don't have to read the schema list and figure out relationships between them because we already know that).
If you read pgdump's source code, when doing the actual backup it uses postgres COPY command, so it was easy enough to write our custom exporter.
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.
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.
You see I originally thought I was building a SaaS service, but it's actually turned out that my customers needed a two-sided network. A per-tenant schema would've been an painful impediment to making that paradigm shift.
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.
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.)
And use Postgres:-)
Seems a nice article but I kind of assume that to be basic knowledge amount DBAs ?
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 !
Or do you keep the actual PK on the adgroup table the traditional key (ie "adgroupid") then you just also throw customerid on adgroup (even though you will never join adgroup directly to customer) so you can shard each table by customerid?
What is the advantage of using a composite PK instead of the traditional PK so long as all ancestral ids are also stored on the table (so sharding is collocated correctly).
The motivation for that is enforcing uniqueness. When the primary key (PK) contains the sharding key, the database can easily push down the PK constraint to the related shard and enforce the PK's uniqueness.