Hacker News new | ask | show | jobs
by craigkerstiens 3547 days ago
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.
1 comments
marktangotango 3547 days ago
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.

link
ngrilly 3547 days ago
> 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.

link
marktangotango 3547 days ago
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

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

link