Re:Shardines: SQLite3 Database-per-Tenant with ActiveRecord
by digianarchist on Sunday April 27, 2025 @02:32PM
(#43814079)
I’d heard of using Postgres schemas before but not RLS. Interesting…
https://www.crunchydata.com/blog/row-level-security-for-tena...
Re:Shardines: SQLite3 Database-per-Tenant with ActiveRecord
by julik on Sunday April 27, 2025 @02:46PM
(#43814177)
That is true, but in my experience this is less of an issue if your migration allow running against "current - 1". If your migration borks on one of the tenants - your system should still work, and you can grab that tenant and investigate.
This problem also does happen with sharded databases - which is why most co's have a structured approach of deploying schema changes, observing them having applied everywhere, and only then deploying the feature relying on the schema changes.
Re:Shardines: SQLite3 Database-per-Tenant with ActiveRecord
by andrewstuart on Sunday April 27, 2025 @02:50PM
(#43814211)
Yeah but “schemas out of sync!” Is a never ending development and operations problem with SQLite database per tenant.
Postgres row level access control gives all the benefits of strong isolation with none of the pain of schemas getting out of sync.
Re:Shardines: SQLite3 Database-per-Tenant with ActiveRecord
by julik on Sunday April 27, 2025 @07:09PM
(#43815900)
You do need to keep tabs on whether they are or not, that is true - and you will have a bit of management to do if they are under constant heavy write load. For me, this all works out because I don't have too many tenants - and I have tools to block writes when running migrations.
It's all tradeoffs after all.
Re:Shardines: SQLite3 Database-per-Tenant with ActiveRecord
by devnull3 on Monday April 28, 2025 @07:27AM
(#43820056)
Shouldn't the service daemon upgrade schema and perform migrations on initialisation?
Before performing upgrade make a copy of the db file. Also multiple DBs can be inited in parallel.
I fail to see why this might be so hard.
Postgres with row based access control is a much better solution to database per tenant/strong isolation.