91–100 of 116 posts
Ok, hear me out: what if we make something that takes a postgres database dir, tars it together and encodes it as a binary blob in SQLite? We could have SQLite within postgres within sqlite within postgres! Is it practical or even slightly useful? Of course not - but it's SQL databases all the way down. Not that this is a good thing in itself.
If you’re using Postgres, multi tenancy has been solved with row level security. It’s super easy to add a tenant id column to every table and a policy that only allows connections to see data from one tenant
The common path of comparing some constant like the role name to some column in the table is fine, and it's fast enough as the policy checker already has the row in hand when it does the check, but the natural tendency for people to want to abstract their policies into a function like has_permission() will blow up fast.
The best approach I've seen from pyramation's launchql [1] which precomputes policies into a bitstring and then masks that against a query constant bitstring of required permissions. Flexible policy definitions compiled into the row as bits so the check is as fast as possible.
What are the use cases for this? I can't imagine designing a database schemas to use this in a typical product. Is it intended for hybrid applications to back up local user data directly with their account info?
I can think of plenty. The most interesting one for me is if you're running a SaaS product like Notion where your users create custom applications that manage their own small schema-based data tables. Letting users create full custom PostgreSQL tables can get complex - do you want to manage tens of thousands of weird custom tables in a PostgreSQL schema somewhere? I'd much rather manage tens of thousands of rows in a…
I normally don't like using JSONB when I could have a rigorous schema, but this sort of application seems reasonable.
Earlier quoted context omitted.
When do you consider the write/transaction to be completed? What do you do about out-of-sync read replicas? ACID gets real hard real fast when introducing replication.
> When do you consider the write/transaction to be completed? Sending a UPDATE/INSERT/DELETE statement to SQLite is not blocking? I would think it is, because in my code I can read the number of affected rows right after I sent the query. > What do you do about out-of-sync read replicas? Delete them and replace them by uploading a checkpoint and replaying a log of the queries since then.
Without that you will have drift from your master database.
With that, you have a whole new host of synchronization issues you need to deal with.
Earlier quoted context omitted.
> enforced schema I have bad news for you [0] about SQLite’s view on schema consistency. [0]: https://www.sqlite.org/quirks.html
I love using the database as the source of truth for data consistency, and constraining your data to only be allowed in your database as long as it's in a valid state. It's easy enough to replicate those constraints to the client if you want the client to do ahead of time validation, but your source of truth lives in the database... I wouldn't survive with SQLite.
Completely agree that the DB should be the arbiter of validity. Constraints are a good thing.
If you’re using Postgres, multi tenancy has been solved with row level security. It’s super easy to add a tenant id column to every table and a policy that only allows connections to see data from one tenant
Exactly what we need from a Show HN.
[flagged]