3 ms·

> Unlimited tables If you ever find yourself in a position where you need a database table per instance of an abstraction, you've almost certainly failed to mo

> Unlimited tables If you ever find yourself in a position where you need a database table per instance of an abstraction, you've almost certainly failed to model the domain appropriately. If you find yourself in a position where you think you need to write your own database engine because nothing on the market can even approach your problem, you probably need to go take a nap. The only instancing that makes sense to me is at the database level (aka one per customer/board/etc), and if you're going to be doing a lot of database files... Did SQLite even remotely cross the mental threshold here? I can see several ways to make it solve this problem if you are going to insist on one table/file per real world thing. One SQLite database per board with everything defined in SQL command text using application defined functions to bind to platform features. Customization can be performed deeply and at the grain of each board. You could allow enterprise customers to bring custom schemas or even code modules to inject and call. Would also make import/export absolutely trivial for any customer. Or we can spend all our special innovation tokens on developing a goddamn database engine from zero...

benjaminwootton 3y ago
One table or schema per customer is possibly a valid model for segregation in a multi tenant system. I’ve never felt the need to do it before, but maybe it could work, if you squint?
- baq 3y ago
  If you never touch the schema in any way, perhaps...? It sounds like an operational nightmare otherwise.
  - benjaminwootton 3y ago
    But the advantages are - better for compliance, faster analytical queries, possible to upgrade people at different paces. I agree it would be painful from a database migration perspective but I can see situations where it might be the right call.
  - throwaway0365 3y ago
    I have read about table inheritance but never used it. It seems like it would make it possible to alter the schema of the root table, but it probably introduces other issues that I am not aware of. You would still need to manage indexes, constraints and default values for each child table, but the structure of the table itself would be inherited.
nijave 3y ago
How do you manage all these sqlite dbs? Throw them on 1 large server, develop some scheme to sprinkle them across multiple servers, something else? Redis and Cassandra already handle distribution out of the box. It seems like there product is basically a web GUI for creating db tables with workflow automation so it doesn't seem like too far of a stretch to have 1 table per customer table. I guess the alternative is designing some system for shoving arbitrary schemes into another schema. I guess https://en.m.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model https://en.m.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%... is an option
robust-cactus 3y ago
Eh - sometimes you gotta solve hard problems and no amount of simplification is going to net good results.
- shafyy 3y ago
  Eh - we're talking about a glorified project management application here.
sgarland 3y ago
To have any hope at HA with SQLite, you’d have to use something like RQLite [0], and at that point you’re already building a far more complex system than Cassandra + Redis. They didn’t build a DB, they taped some products together with middleware. That isn’t to say what they did is bad, just that it’s not “let’s write a DB from scratch” as the headline implies. [0]: https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- otoolep 3y ago
  rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
  - sgarland 3y ago
    Hi! Your product looks quite good to be clear, and that was in no way a slam on it. I’ve long wanted a reasonable excuse to use it, just to see how it works at scale. One thing that stopped me from trying it in the past for a project was this: > Is rqlite a good match for a network of nodes that come and go – perhaps thousands of them? > Unlikely. While I wasn’t going to get to thousands, it was going to be hosted on K8s, and it seemed like I’d have to write lifecyclehooks to ensure nodes gracefully left consensus, and others felt that was an unnecessary complexity for the problem. Can you speak more to this point, since in the FAQ you also say it can run on K8s? If a node dies due to, say, hardware failure, when it rejoins is it seen as a slightly out-of-date existing node since it’s part of statefulset? If so, assuming quorum is maintained, this is probably a lot easier than I was making it out to be.
    - otoolep 3y ago
      When I say "thousands coming and going" I'm talking about trying to run rqlite on, say, a network of cellphones. When people ask me about this, it usually means they have the wrong mental model of what rqlite is. However, running it on Kubernetes should work quite well. Check out the documentation at https://rqlite.io/docs/guides/kubernetes/ https://rqlite.io/docs/guides/kubernetes/ >If a node dies due to, say, hardware failure, when it rejoins is it seen as a slightly out-of-date existing node since it’s part of statefulset? If so, assuming quorum is maintained, this is probably a lot easier than I was making it out to be. Assuming I follow you, yes, this should work fine -- and exactly as you expect. I suggest you try it out, and if you don't understand what you're seeing file a ticket on GitHub, or join the rqlite Slack[1]. [1] https://rqlite.slack.com/ https://rqlite.slack.com/
renegade-otter 3y ago
There are RARE exceptions. AirTable developed their own DB, but their case may be justified. I listened to an interview with a founder once - very thoughtful and reasonable. They fully understood what they were getting into. But by default, NEVER: * Write your own DB * Write your own search engine to match ElasticSearch or other serious technologies It will take over your life, and then ruin it.
- tablet 3y ago
  Not sure why they needed own DB. Fibery.io has similar domain and we built everything on Postgres. Works like a charm and you even don't have Airtable bases connectivity problem. We have schema-per-customer and table-per-entity-type model, performance is quite good.
dsagal 3y ago
Shhh, don't tell them about SQLite! It's the secret sauce behind Grist (I am a founder), and we are sort of competitors. Wouldn't want Monday.com knowing about our competitive advantage!

benjaminwootton 3y ago

One table or schema per customer is possibly a valid model for segregation in a multi tenant system. I’ve never felt the need to do it before, but maybe it could work, if you squint?

baq 3y ago
If you never touch the schema in any way, perhaps...? It sounds like an operational nightmare otherwise.
- benjaminwootton 3y ago
  But the advantages are - better for compliance, faster analytical queries, possible to upgrade people at different paces. I agree it would be painful from a database migration perspective but I can see situations where it might be the right call.
- throwaway0365 3y ago
  I have read about table inheritance but never used it. It seems like it would make it possible to alter the schema of the root table, but it probably introduces other issues that I am not aware of. You would still need to manage indexes, constraints and default values for each child table, but the structure of the table itself would be inherited.

nijave 3y ago

How do you manage all these sqlite dbs? Throw them on 1 large server, develop some scheme to sprinkle them across multiple servers, something else? Redis and Cassandra already handle distribution out of the box. It seems like there product is basically a web GUI for creating db tables with workflow automation so it doesn't seem like too far of a stretch to have 1 table per customer table. I guess the alternative is designing some system for shoving arbitrary schemes into another schema. I guess https://en.m.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model https://en.m.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%... is an option

robust-cactus 3y ago

Eh - sometimes you gotta solve hard problems and no amount of simplification is going to net good results.

shafyy 3y ago
Eh - we're talking about a glorified project management application here.

sgarland 3y ago

To have any hope at HA with SQLite, you’d have to use something like RQLite [0], and at that point you’re already building a far more complex system than Cassandra + Redis. They didn’t build a DB, they taped some products together with middleware. That isn’t to say what they did is bad, just that it’s not “let’s write a DB from scratch” as the headline implies. [0]: https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite

otoolep 3y ago
rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
- sgarland 3y ago
  Hi! Your product looks quite good to be clear, and that was in no way a slam on it. I’ve long wanted a reasonable excuse to use it, just to see how it works at scale. One thing that stopped me from trying it in the past for a project was this: > Is rqlite a good match for a network of nodes that come and go – perhaps thousands of them? > Unlikely. While I wasn’t going to get to thousands, it was going to be hosted on K8s, and it seemed like I’d have to write lifecyclehooks to ensure nodes gracefully left consensus, and others felt that was an unnecessary complexity for the problem. Can you speak more to this point, since in the FAQ you also say it can run on K8s? If a node dies due to, say, hardware failure, when it rejoins is it seen as a slightly out-of-date existing node since it’s part of statefulset? If so, assuming quorum is maintained, this is probably a lot easier than I was making it out to be.
  - otoolep 3y ago
    When I say "thousands coming and going" I'm talking about trying to run rqlite on, say, a network of cellphones. When people ask me about this, it usually means they have the wrong mental model of what rqlite is. However, running it on Kubernetes should work quite well. Check out the documentation at https://rqlite.io/docs/guides/kubernetes/ https://rqlite.io/docs/guides/kubernetes/ >If a node dies due to, say, hardware failure, when it rejoins is it seen as a slightly out-of-date existing node since it’s part of statefulset? If so, assuming quorum is maintained, this is probably a lot easier than I was making it out to be. Assuming I follow you, yes, this should work fine -- and exactly as you expect. I suggest you try it out, and if you don't understand what you're seeing file a ticket on GitHub, or join the rqlite Slack[1]. [1] https://rqlite.slack.com/ https://rqlite.slack.com/

renegade-otter 3y ago

There are RARE exceptions. AirTable developed their own DB, but their case may be justified. I listened to an interview with a founder once - very thoughtful and reasonable. They fully understood what they were getting into. But by default, NEVER: * Write your own DB * Write your own search engine to match ElasticSearch or other serious technologies It will take over your life, and then ruin it.

tablet 3y ago
Not sure why they needed own DB. Fibery.io has similar domain and we built everything on Postgres. Works like a charm and you even don't have Airtable bases connectivity problem. We have schema-per-customer and table-per-entity-type model, performance is quite good.

dsagal 3y ago

Shhh, don't tell them about SQLite! It's the secret sauce behind Grist (I am a founder), and we are sort of competitors. Wouldn't want Monday.com knowing about our competitive advantage!