26 ms·

We're currently transitioning from a multi-tenant 2TB postgres DB hosted on AWS RDS to using sqlite instead, a separate database for each client. We're doing t

We're currently transitioning from a multi-tenant 2TB postgres DB hosted on AWS RDS to using sqlite instead, a separate database for each client. We're doing this for multiple reasons: a) As our DB grew the service became very expensive, one of the biggest items in our AWS invoice; b) Keeping the PG servers up to date is a pain, we simply don't have time for this; c) We wanted to be able to migrate to other clouds and even be able to offer a self-hosted version of our platform.

simonw 5y ago
I'd be fascinated to hear more about this. Where do the SQLite files live? Are you using some kind of NFS or EFS or similar for them? Sharding them across many machines? How are you handling backups and high availability? Are you using Litestream? How many SQLite files do your application servers have open at any one time?
- mosselman 5y ago
  Interested in more info as well. Also, how do you handle migrations, both schema as well as data?
  - deleted 5y ago
    [deleted]
- ngrilly 5y ago
  Also super interested in hearing more.
- tyingq 5y ago
  SQLite mentions NFS as problematic because SQLite makes a lot of use of advisory locking. Which is supposed to work on NFS, but apparently is often buggy.
  - mceachen 5y ago
    Writing to a SQLite file on a remote filesystem only works with a heap of caveats: don't use WAL, and disable SHM. SQLite is crazy fast in WAL mode, but it's because they use a memory-mapped file to coordinate consistency across multiple processes/threads. You can't mmap a remote file. (Source: hacking in a DB replica workaround to PhotoStructure so people can store their library on arbitrary filesystems... BTW, I'd love to be wrong here: if someone's figured out how to do this, please share!)
    - exikyut 5y ago
      I'm idly curious why you need to use replicas in a workaround context, and why arbitrary filesystems are a problem.
      - mceachen 5y ago
        The local-replica approach is only applied when your library database is on a remote filesystem, which SQLite can't read/write to (due to the absence of shm and flock).
        exikyut 5y ago
        I think I get it now: you want people to be able to point PhotoStructure at a NAS and have it Just Work. That was the context I was missing. I was wondering if maybe the cute hack that was posted recentlyish (https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630) to enable SQLite3 to access large remote databases over HTTP might be relevant, but that approach requires a small server to be sitting at the remote, and only implements read-only access in any case. Now I'm wondering what an NFS/SMB translation of the above idea (which uses SQLite3's VFS hooks) might look like: ramp internal buffering to the max, use separate transient files (with eg date-based unique names) for each in-flight request, etc. Append-only journal files also come to mind... It's crazy how flaky network filesystems are at the lower levels. Like, I use NFS at home every day, without Kerberos (which lets me enable encryption, which IIUC provides AEAD), all while knowing my data integrity entirely depends on the IP checksum computed by my 15 year old ISP router. :D (I already know I need to upgrade. Shhh.)
- kurko 5y ago
  Also super interested, specially handling migrations and structure/schema syncs.
cutthegrass2 5y ago
This sounds super interesting, are you able to share any further detail? I'm currently wrestling with a 35TB Aurora DB and considering options for future growth. The system is multi-tenant and one of the options on the table is breaking out the tenants and hosting them on either their own system or alongside smaller subset of tenants.
- gregwebs 5y ago
  The issue with this approach (other than isolation) is that the largest tenant may be orders of magnitude bigger than others and still need more sharding to perform well while their smallest still needs to share a db. Have you looked at TiDB (MySQL) or CRDB (PG)?
  - cutthegrass2 5y ago
    Absolutely. Thanks for the suggestion of TiDB, haven't previously considered this but it looks interesting, will take a closer look.
- aynyc 5y ago
  Done that before, where my largest clients are exponentially bigger than my smallest client. The key is to realize one-size-fits-all doesn't work, not in the long term. I'm talking about feature development, storage, etc.. In the end, we break out large clients onto their own instances of RDS, where smaller clients stay multi-tenants. They share the same schemas, etc.. We wrote a lot of utility scripts to migrate clients from small to big, and vice versa. Smallest clients get automatic minor and major upgrades to latest features almost immediate. Larger clients generally only get major upgrades.
- exikyut 5y ago
  I must admit I'm very curious as to what real-world thing(s) translate to 35TB of stored data (that presumably isn't large blobs, like video or ML models). (And oh wow I just realized the billing must be amazing...)
  - 5y ago
codewithcheese 5y ago
Slack used to partition their databases per workspace (customer) and then moved away from it. The "Disadvantages" section lists reasons not to do that. https://slack.engineering/scaling-datastores-at-slack-with-vitess/ https://slack.engineering/scaling-datastores-at-slack-with-v... Two disadvantages that stand out for me are: 1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision. 2. You are stuck with customers being isolated and makes it much harder to develop features that cut across a group of customers.
- OJFord 5y ago
  2 can be a feature too!
- andix 5y ago
  2. can be a huge security feature. If your web application only has access to the database of this one customer, you practically can't leak data from other customers by accident.
  - renewiltord 5y ago
    Slack has cross-workspace connectivity and communication as a product feature. It’s a really cool feature.
- panta82 5y ago
  My experience mirrors that. If you have a dynamic set of databases or even tables, be ready to invest into tooling to recreate features you take for granted in a normal database.
- littlestymaar 5y ago
  > 1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision. IIRC, Slack had a lot of scaling issues when they won a deal with IBM. Jumping from medium-size customers to such a behemoth will obviously be a challenge, but a lot of the difficulties they faced was non-technical and more business-related (“What do you mean, you want our product but not our monthly plan charged per seat?”). Anyway, that's a nice problem to have for a start-up ;).
escovabr 5y ago
How do you deal with common sqlite lock file when using it for multiple users?
- ilrwbwrkhv 5y ago
  Not a problem up to 100,000 customers imp
  - Jnr 5y ago
    I used it 10 years ago and initially locks were a slight problem. Not sure if this has been solved somehow in sqlite itself. I solved the problem by handling all the DB operations in a separate thread that handled write and read queues. It certainly was not as easy as using mysql/postgresql where server does everything for you. Nowadays there are probably plenty of libraries that handle that for you.
    - Moto7451 5y ago
      This pattern is what I did when I used SQLite in production. I used a lightweight actor model microframework to make it easier to route write requests from multiple areas of the code to the single writer thread and offer a continuation (or async/await) perform any follow up actions. Performance easily went beyond the requirements for the app.
      - zo1 5y ago
        I'm not sure how to express my reaction to this kind of design/architecture without being rude. Especially with performant open source databases being available. Was this some sort embedded app or toy project?
    - benbjohnson 5y ago
      WAL mode has been added in the last 10 years which handles read concurrency really well IMHO. You’re still constrained to a single writer but that’s not usually a problem as long as you keep your write transactions short.
Labo333 5y ago
I'm very curious, how do you handle concurrency? Or is this why you have one db per client?
jrochkind1 5y ago
> Keeping the PG servers up to date is a pain, we simply don't have time for this I've never used AWS RDS (or similar), but I had been thinking that part of the advantage of RDS was you didn't need to "keep the PG server up to date". But apparently I was wrong! What does this look like on RDS?
- tehbeard 5y ago
  (note: we use MySQL on RDS, but the docs for PG seem to be similiar enough) Minor versions you can tell it a day and rough timeslot (few hours) for it to apply them. Major version upgrades always require you to start them, and the docs lay out prep work to do before hand (check for incompatibilities, setup any custom config before hand, take a backup/snapshot, do dry run) One of the things I love with RDS is that last bit, doing a dry run with a clone of the database is fairly easy (can't say what it's like at the TB level of data, probably not as easy/quick) to test.
- Slartie 5y ago
  You mostly need to do that if you have a security division that's looking for reasons to justify their existence. One reason involving little actual work on their own side is to constantly nag other teams to install updates "for security reasons", regardless of how much this actually contributes to security. Which usually isn't that much in case of RDS not exposed to the public network at all.
liveoneggs 5y ago
https://www.actordb.com/docs-howitworks.html#overview https://www.actordb.com/docs-howitworks.html#overview
txprog 5y ago
Very interested to hear your story and details!
softwarefounder 5y ago
Sounds like a nightmare if you ever have to go under SOC2.
mattmanser 5y ago
You'll probably come to regret that. A DB per customer is a maintenance nightmare. I remember a discussion here a few years ago about it, and there were so many voices chiming up to say 'we did this, terrible idea'.
- ngrilly 5y ago
  Why?

simonw 5y ago

I'd be fascinated to hear more about this. Where do the SQLite files live? Are you using some kind of NFS or EFS or similar for them? Sharding them across many machines? How are you handling backups and high availability? Are you using Litestream? How many SQLite files do your application servers have open at any one time?

mosselman 5y ago
Interested in more info as well. Also, how do you handle migrations, both schema as well as data?
- deleted 5y ago
  [deleted]
ngrilly 5y ago
Also super interested in hearing more.
tyingq 5y ago
SQLite mentions NFS as problematic because SQLite makes a lot of use of advisory locking. Which is supposed to work on NFS, but apparently is often buggy.
- mceachen 5y ago
  Writing to a SQLite file on a remote filesystem only works with a heap of caveats: don't use WAL, and disable SHM. SQLite is crazy fast in WAL mode, but it's because they use a memory-mapped file to coordinate consistency across multiple processes/threads. You can't mmap a remote file. (Source: hacking in a DB replica workaround to PhotoStructure so people can store their library on arbitrary filesystems... BTW, I'd love to be wrong here: if someone's figured out how to do this, please share!)
  - exikyut 5y ago
    I'm idly curious why you need to use replicas in a workaround context, and why arbitrary filesystems are a problem.
    - mceachen 5y ago
      The local-replica approach is only applied when your library database is on a remote filesystem, which SQLite can't read/write to (due to the absence of shm and flock).
      - exikyut 5y ago
        I think I get it now: you want people to be able to point PhotoStructure at a NAS and have it Just Work. That was the context I was missing. I was wondering if maybe the cute hack that was posted recentlyish (https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630) to enable SQLite3 to access large remote databases over HTTP might be relevant, but that approach requires a small server to be sitting at the remote, and only implements read-only access in any case. Now I'm wondering what an NFS/SMB translation of the above idea (which uses SQLite3's VFS hooks) might look like: ramp internal buffering to the max, use separate transient files (with eg date-based unique names) for each in-flight request, etc. Append-only journal files also come to mind... It's crazy how flaky network filesystems are at the lower levels. Like, I use NFS at home every day, without Kerberos (which lets me enable encryption, which IIUC provides AEAD), all while knowing my data integrity entirely depends on the IP checksum computed by my 15 year old ISP router. :D (I already know I need to upgrade. Shhh.)
kurko 5y ago
Also super interested, specially handling migrations and structure/schema syncs.

cutthegrass2 5y ago

This sounds super interesting, are you able to share any further detail? I'm currently wrestling with a 35TB Aurora DB and considering options for future growth. The system is multi-tenant and one of the options on the table is breaking out the tenants and hosting them on either their own system or alongside smaller subset of tenants.

gregwebs 5y ago
The issue with this approach (other than isolation) is that the largest tenant may be orders of magnitude bigger than others and still need more sharding to perform well while their smallest still needs to share a db. Have you looked at TiDB (MySQL) or CRDB (PG)?
- cutthegrass2 5y ago
  Absolutely. Thanks for the suggestion of TiDB, haven't previously considered this but it looks interesting, will take a closer look.
aynyc 5y ago
Done that before, where my largest clients are exponentially bigger than my smallest client. The key is to realize one-size-fits-all doesn't work, not in the long term. I'm talking about feature development, storage, etc.. In the end, we break out large clients onto their own instances of RDS, where smaller clients stay multi-tenants. They share the same schemas, etc.. We wrote a lot of utility scripts to migrate clients from small to big, and vice versa. Smallest clients get automatic minor and major upgrades to latest features almost immediate. Larger clients generally only get major upgrades.
exikyut 5y ago
I must admit I'm very curious as to what real-world thing(s) translate to 35TB of stored data (that presumably isn't large blobs, like video or ML models). (And oh wow I just realized the billing must be amazing...)
- 5y ago

codewithcheese 5y ago

Slack used to partition their databases per workspace (customer) and then moved away from it. The "Disadvantages" section lists reasons not to do that. https://slack.engineering/scaling-datastores-at-slack-with-vitess/ https://slack.engineering/scaling-datastores-at-slack-with-v... Two disadvantages that stand out for me are: 1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision. 2. You are stuck with customers being isolated and makes it much harder to develop features that cut across a group of customers.

OJFord 5y ago
2 can be a feature too!
andix 5y ago
2. can be a huge security feature. If your web application only has access to the database of this one customer, you practically can't leak data from other customers by accident.
- renewiltord 5y ago
  Slack has cross-workspace connectivity and communication as a product feature. It’s a really cool feature.
panta82 5y ago
My experience mirrors that. If you have a dynamic set of databases or even tables, be ready to invest into tooling to recreate features you take for granted in a normal database.
littlestymaar 5y ago
> 1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision. IIRC, Slack had a lot of scaling issues when they won a deal with IBM. Jumping from medium-size customers to such a behemoth will obviously be a challenge, but a lot of the difficulties they faced was non-technical and more business-related (“What do you mean, you want our product but not our monthly plan charged per seat?”). Anyway, that's a nice problem to have for a start-up ;).

escovabr 5y ago

How do you deal with common sqlite lock file when using it for multiple users?

ilrwbwrkhv 5y ago
Not a problem up to 100,000 customers imp
- Jnr 5y ago
  I used it 10 years ago and initially locks were a slight problem. Not sure if this has been solved somehow in sqlite itself. I solved the problem by handling all the DB operations in a separate thread that handled write and read queues. It certainly was not as easy as using mysql/postgresql where server does everything for you. Nowadays there are probably plenty of libraries that handle that for you.
  - Moto7451 5y ago
    This pattern is what I did when I used SQLite in production. I used a lightweight actor model microframework to make it easier to route write requests from multiple areas of the code to the single writer thread and offer a continuation (or async/await) perform any follow up actions. Performance easily went beyond the requirements for the app.
    - zo1 5y ago
      I'm not sure how to express my reaction to this kind of design/architecture without being rude. Especially with performant open source databases being available. Was this some sort embedded app or toy project?
  - benbjohnson 5y ago
    WAL mode has been added in the last 10 years which handles read concurrency really well IMHO. You’re still constrained to a single writer but that’s not usually a problem as long as you keep your write transactions short.

Labo333 5y ago

I'm very curious, how do you handle concurrency? Or is this why you have one db per client?

jrochkind1 5y ago

> Keeping the PG servers up to date is a pain, we simply don't have time for this I've never used AWS RDS (or similar), but I had been thinking that part of the advantage of RDS was you didn't need to "keep the PG server up to date". But apparently I was wrong! What does this look like on RDS?

tehbeard 5y ago
(note: we use MySQL on RDS, but the docs for PG seem to be similiar enough) Minor versions you can tell it a day and rough timeslot (few hours) for it to apply them. Major version upgrades always require you to start them, and the docs lay out prep work to do before hand (check for incompatibilities, setup any custom config before hand, take a backup/snapshot, do dry run) One of the things I love with RDS is that last bit, doing a dry run with a clone of the database is fairly easy (can't say what it's like at the TB level of data, probably not as easy/quick) to test.
Slartie 5y ago
You mostly need to do that if you have a security division that's looking for reasons to justify their existence. One reason involving little actual work on their own side is to constantly nag other teams to install updates "for security reasons", regardless of how much this actually contributes to security. Which usually isn't that much in case of RDS not exposed to the public network at all.

liveoneggs 5y ago

https://www.actordb.com/docs-howitworks.html#overview https://www.actordb.com/docs-howitworks.html#overview

txprog 5y ago

Very interested to hear your story and details!

softwarefounder 5y ago

Sounds like a nightmare if you ever have to go under SOC2.

mattmanser 5y ago

You'll probably come to regret that. A DB per customer is a maintenance nightmare. I remember a discussion here a few years ago about it, and there were so many voices chiming up to say 'we did this, terrible idea'.

ngrilly 5y ago
Why?