10 ms·

> Benefits of SQLite-Per-Partition The obvious caveat here is any situation where you need global tables. I've never worked on a product where 100% of the sche

> Benefits of SQLite-Per-Partition The obvious caveat here is any situation where you need global tables. I've never worked on a product where 100% of the schema could be partitioned like this. I've done a ton of SQLite-per-<entity type>, but there's always been a singleton SQLite database above this tracking the metadata. Looking up a user session via token is not an analytics task. Neither is checking if an email address is already registered. These are very pedestrian queries that demand global visibility, ideally with hard transactional guarantees.

manmal 2y ago
Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? Don’t they need to coordinate themselves outside of transactions?
- bob1029 2y ago
  > Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? If you have hundreds of services hammering the datastore concurrently, then the ability to offer transactional guarantees seems absolutely mandatory for me. > Don’t they need to coordinate themselves outside of transactions? I think we're back at the beginning of our journey here.
  - manmal 2y ago
    Do you mean via distributed transactions? I‘m clueless in that area, and don’t understand how hundreds of self sufficient services would partake in transactions that span different sets of those services, without entangling them badly.
    - refulgentis 2y ago
      I find this topic difficult to reason about because I'm not intimately familiar with DBs at scale. That being said: my understanding is we're always going to have something that needs to maintain its own state that's global, and you're naming that problem as well. For example, let's say we partition users based on the first letter of their email addresses. This works great for most user-specific queries (e.g., fetching a user profile). But what happens when someone registers a new account? At that point, we must ensure the email is globally unique. A purely partitioned approach won't help here—we'll need some kind of global database or service maintaining a single source of truth for email uniqueness checks. (then it gets complicated, because of the simple level at which I can understand and communicate about it. Why not just partition based on the first letter of an email? Well, yes, then we just have to deal with emails changing. Maybe a better example is session tokens, because they don't come with an email. But we could require that, or do some bespoke thing...there's security concerns there but they seem overrated...but to your point, you end up adding a ton of complexity just so you can fit a square peg in a round hole)
      - humodz 2y ago
        Do you mind elaborating why a db partitioned like that is not enough for your registration example? If the partitioning is based on the email address, then you know where the new user's email has to be if exists, you don't need to query all partitions. For example, following your partitioning logic, if the user registers as john.smith@example.com, we'd need to query only partition j.
        juliuskiesian 2y ago
        What if the users are partitioned by ID instead of email? You would have to iterate through all the partitions.
        TylerE 2y ago
        Not much of a partition if it's on what is essentially an opaque unique key.
        refulgentis 2y ago
        You're right, the email address example isn't clearcut -- its not an issue at all at registration. From there, you could never allow an email change. Or you could just add a layer for coordination, ex. we can imagine some global index that's only used for email changes and then somehow coordinates the partition change My broad understanding is that you can always "patch" or "work around" any single objection to partitioning or sharding—like using extra coordination services, adding more layers, or creating special-case code. But each of these patches adds complexity, reduces flexibility, and constrains your ability to cleanly refactor or adapt later. Sure, partitioning email addresses might neatly solve registration checks initially, but then email changes require extra complexity (such as maintaining global indices and coordinating between partitions). In other words, the real issue isn't that partitioning fails in a single obvious way—it usually doesn’t—but rather that global state always emerges somewhere, inevitably. You can try to bury this inevitability with clever workarounds and layers, but eventually you find yourself buried under a mountain of complexity. At some point, the question becomes: are we building complexity to solve genuine problems, or just to preserve the appearance that we're fully partitioned? (My visceral objection to it is, coming from client-side dev virtually my entire career: if you don't need global state, why do you have the server at all? Just give use a .sqlite for my account, and store it for me on S3 for retrieval at will. And if you do need global state...odds are you or a nearby experienced engineer has Seen Some Shit, i.e. the horror that arises in a codebase worked on over years, doubling down on an seemingly small, innocuous, initial decision. and knows it'll never just be one neat design decision or patch)
- NathanFlurry 2y ago
  > Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? If a single request frequently touches multiple partitions, your use cases may not work well. It's the same deal as Cassandra & DynamoDB: use cases like chat threads or social feeds fit really well because there's a clear ownership hierarchy. e.g. message belongs to a single thread partition, or a social post belongs to a feed partition.
slt2021 2y ago
would redis/in memory DB be a better suited for this task? I don't think a regular rdbms will meet this requirement at scale
- bob1029 2y ago
  A regular RDBMS is insufficient for managing user accounts and sessions? What kind of scenario do you have in mind?
kgeist 2y ago
>I've never worked on a product where 100% of the schema could be partitioned like this SaaS for B2B works well with this, you partition by organization. Each organization has their own DB. Most organizations are typically small (no more than 100 users), and it greatly simplifies things. However, it's still problematic with large organizations (200k-300k is where it starts to fall apart).
- NathanFlurry 2y ago
  Yep. Nile (https://www.thenile.dev/ https://www.thenile.dev/) is trying to address this use case with a fully isolated PG databases. Though, I don't know how they handle scaling/sharding.
- skrebbel 2y ago
  But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs. Similarly, admin tools for customer support etc easily become very hard to build. I'd love to know if anybody has a solution for this because otherwise DB-per-tenant sounds very nice indeed.
  - kgeist 2y ago
    >But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs Create a separate DWH storage where all necessary data from all databases is pulled and processed/aggregated in the background (via change data capture) and saved in a format that is more suitable for analytical queries. The downside is that it requires additional preparation and some upfront design. >Similarly, admin tools for customer support etc easily become very hard to build. Our CRM understands the concept of organizations/tenants, so when a customer files a ticket, we know their organization, and from there, for a tech support person, accessing "coolcompany.mysaas.com" is no different from accessing "mysaas.com". In my opinion, it makes tech support easier, because you have far less data/fewer logs to analyze/sift through (only a specific organization's DB) when investigating a problem.
  - 2y ago
NathanFlurry 2y ago
> The obvious caveat here is any situation where you need global tables A lot of people still end up storing data that's not frequently updated in a traditional OLTP database like Postgres. However: I think it always helps to think about these problems as "how would you do it in Cassandra/DynamoDB?" In the case of Cassandra/DynamoDB, the relevant data (e.g. user ID, channel ID, etc) is always in the partitioning key. For Durable Objects, you can do the same thing by building a key that's something like: ``` // for a simple keys: env.USER_DO.idFromName(userId); // or for composite keys: env.DIRECT_MESSAGE_CHANNEL_DO.idFromName(`${userAId}:${userBId}`); // assumes user A and B are sorted ``` I've spoken with a lot of companies using _only_ this architecture for Durable Objects and it's working well.
stepanhruda 2y ago
You could have another sqlite with this global information related to users / sessions / passwords etc
apitman 2y ago
Agree with your overall point, but for user session specifically, once a user has entered an identifier can't you use their specific DB for sessions? You also have things like JWTs/PASETO that can carry the DB mapping in the token.
hinkley 2y ago
Global tables with anything but a tiny rate of write operations is going to bring Amdahl's Law in to ruin several years of your life while you fight against it. I had a service that was adding 30 ms to TTFB because most of the other decisions we made during page render were predicated on this response. I would later find about a quarter of this time was in bookkeeping code that got out of hand, but that's a story for another day. The biggest problem was that this data requires a capital-P Process to change, so it took about 20 minutes to change it and fifteen seconds to roll it back. There had been plans to have the service do more but in the end it looked more like a remote request for a feature toggle. We were already broadcasting feature toggle changes through Consul, which is great for this kind of data. So I did more of the same and got to decom a cluster. Moral of the story is, it matters what kind of global data you require to build your system, and that's the sort of fact you should keep in mind while designing the system because you can pick a design that will scale or one that absolutely won't, because you've given the users features that make the entire system 10x more expensive per request.
jitl 2y ago
Put your global tables in Postgres/CockroachDB/Vitess/etc. You don't need to force everything to be a SQLite table. Use the right tool for the job. SQLite can be the right tool, and it can not be the right tool.
- superq 2y ago
  Agreed, or even just start with sqlite for your globals and then scale to those later.

manmal 2y ago

Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? Don’t they need to coordinate themselves outside of transactions?

bob1029 2y ago
> Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? If you have hundreds of services hammering the datastore concurrently, then the ability to offer transactional guarantees seems absolutely mandatory for me. > Don’t they need to coordinate themselves outside of transactions? I think we're back at the beginning of our journey here.
- manmal 2y ago
  Do you mean via distributed transactions? I‘m clueless in that area, and don’t understand how hundreds of self sufficient services would partake in transactions that span different sets of those services, without entangling them badly.
  - refulgentis 2y ago
    I find this topic difficult to reason about because I'm not intimately familiar with DBs at scale. That being said: my understanding is we're always going to have something that needs to maintain its own state that's global, and you're naming that problem as well. For example, let's say we partition users based on the first letter of their email addresses. This works great for most user-specific queries (e.g., fetching a user profile). But what happens when someone registers a new account? At that point, we must ensure the email is globally unique. A purely partitioned approach won't help here—we'll need some kind of global database or service maintaining a single source of truth for email uniqueness checks. (then it gets complicated, because of the simple level at which I can understand and communicate about it. Why not just partition based on the first letter of an email? Well, yes, then we just have to deal with emails changing. Maybe a better example is session tokens, because they don't come with an email. But we could require that, or do some bespoke thing...there's security concerns there but they seem overrated...but to your point, you end up adding a ton of complexity just so you can fit a square peg in a round hole)
    - humodz 2y ago
      Do you mind elaborating why a db partitioned like that is not enough for your registration example? If the partitioning is based on the email address, then you know where the new user's email has to be if exists, you don't need to query all partitions. For example, following your partitioning logic, if the user registers as john.smith@example.com, we'd need to query only partition j.
      - juliuskiesian 2y ago
        What if the users are partitioned by ID instead of email? You would have to iterate through all the partitions.
        TylerE 2y ago
        Not much of a partition if it's on what is essentially an opaque unique key.
      - refulgentis 2y ago
        You're right, the email address example isn't clearcut -- its not an issue at all at registration. From there, you could never allow an email change. Or you could just add a layer for coordination, ex. we can imagine some global index that's only used for email changes and then somehow coordinates the partition change My broad understanding is that you can always "patch" or "work around" any single objection to partitioning or sharding—like using extra coordination services, adding more layers, or creating special-case code. But each of these patches adds complexity, reduces flexibility, and constrains your ability to cleanly refactor or adapt later. Sure, partitioning email addresses might neatly solve registration checks initially, but then email changes require extra complexity (such as maintaining global indices and coordinating between partitions). In other words, the real issue isn't that partitioning fails in a single obvious way—it usually doesn’t—but rather that global state always emerges somewhere, inevitably. You can try to bury this inevitability with clever workarounds and layers, but eventually you find yourself buried under a mountain of complexity. At some point, the question becomes: are we building complexity to solve genuine problems, or just to preserve the appearance that we're fully partitioned? (My visceral objection to it is, coming from client-side dev virtually my entire career: if you don't need global state, why do you have the server at all? Just give use a .sqlite for my account, and store it for me on S3 for retrieval at will. And if you do need global state...odds are you or a nearby experienced engineer has Seen Some Shit, i.e. the horror that arises in a codebase worked on over years, doubling down on an seemingly small, innocuous, initial decision. and knows it'll never just be one neat design decision or patch)
NathanFlurry 2y ago
> Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? If a single request frequently touches multiple partitions, your use cases may not work well. It's the same deal as Cassandra & DynamoDB: use cases like chat threads or social feeds fit really well because there's a clear ownership hierarchy. e.g. message belongs to a single thread partition, or a social post belongs to a feed partition.

slt2021 2y ago

would redis/in memory DB be a better suited for this task? I don't think a regular rdbms will meet this requirement at scale

bob1029 2y ago
A regular RDBMS is insufficient for managing user accounts and sessions? What kind of scenario do you have in mind?

kgeist 2y ago

>I've never worked on a product where 100% of the schema could be partitioned like this SaaS for B2B works well with this, you partition by organization. Each organization has their own DB. Most organizations are typically small (no more than 100 users), and it greatly simplifies things. However, it's still problematic with large organizations (200k-300k is where it starts to fall apart).

NathanFlurry 2y ago
Yep. Nile (https://www.thenile.dev/ https://www.thenile.dev/) is trying to address this use case with a fully isolated PG databases. Though, I don't know how they handle scaling/sharding.
skrebbel 2y ago
But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs. Similarly, admin tools for customer support etc easily become very hard to build. I'd love to know if anybody has a solution for this because otherwise DB-per-tenant sounds very nice indeed.
- kgeist 2y ago
  >But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs Create a separate DWH storage where all necessary data from all databases is pulled and processed/aggregated in the background (via change data capture) and saved in a format that is more suitable for analytical queries. The downside is that it requires additional preparation and some upfront design. >Similarly, admin tools for customer support etc easily become very hard to build. Our CRM understands the concept of organizations/tenants, so when a customer files a ticket, we know their organization, and from there, for a tech support person, accessing "coolcompany.mysaas.com" is no different from accessing "mysaas.com". In my opinion, it makes tech support easier, because you have far less data/fewer logs to analyze/sift through (only a specific organization's DB) when investigating a problem.
- 2y ago

NathanFlurry 2y ago

> The obvious caveat here is any situation where you need global tables A lot of people still end up storing data that's not frequently updated in a traditional OLTP database like Postgres. However: I think it always helps to think about these problems as "how would you do it in Cassandra/DynamoDB?" In the case of Cassandra/DynamoDB, the relevant data (e.g. user ID, channel ID, etc) is always in the partitioning key. For Durable Objects, you can do the same thing by building a key that's something like: ``` // for a simple keys: env.USER_DO.idFromName(userId); // or for composite keys: env.DIRECT_MESSAGE_CHANNEL_DO.idFromName(`${userAId}:${userBId}`); // assumes user A and B are sorted ``` I've spoken with a lot of companies using _only_ this architecture for Durable Objects and it's working well.

stepanhruda 2y ago

You could have another sqlite with this global information related to users / sessions / passwords etc

apitman 2y ago

Agree with your overall point, but for user session specifically, once a user has entered an identifier can't you use their specific DB for sessions? You also have things like JWTs/PASETO that can carry the DB mapping in the token.

hinkley 2y ago

Global tables with anything but a tiny rate of write operations is going to bring Amdahl's Law in to ruin several years of your life while you fight against it. I had a service that was adding 30 ms to TTFB because most of the other decisions we made during page render were predicated on this response. I would later find about a quarter of this time was in bookkeeping code that got out of hand, but that's a story for another day. The biggest problem was that this data requires a capital-P Process to change, so it took about 20 minutes to change it and fifteen seconds to roll it back. There had been plans to have the service do more but in the end it looked more like a remote request for a feature toggle. We were already broadcasting feature toggle changes through Consul, which is great for this kind of data. So I did more of the same and got to decom a cluster. Moral of the story is, it matters what kind of global data you require to build your system, and that's the sort of fact you should keep in mind while designing the system because you can pick a design that will scale or one that absolutely won't, because you've given the users features that make the entire system 10x more expensive per request.

jitl 2y ago

Put your global tables in Postgres/CockroachDB/Vitess/etc. You don't need to force everything to be a SQLite table. Use the right tool for the job. SQLite can be the right tool, and it can not be the right tool.

superq 2y ago
Agreed, or even just start with sqlite for your globals and then scale to those later.