3 ms·
love citus! this is useful. one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL ob
by asah 3y ago
love citus! this is useful.
one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL object names than a real level of indirection. Schema objects can't be manipulated as a batch, they don't provide the isolation / access control of databases, and can't be manipulated in DML and require stored procedures to manipulate.
- mslot 3y agoTrue, though I'd phrase it more as a middle ground. Which model is best depends on your needs. A big advantage of schemas over databases is that you can access different schemas over the same connection, which allows much higher density, since connections are a scarce resource in PostgreSQL. You can also have shared tables. A big advantage over row-based sharding is that you can have variations of the table definitions or a different set of tables altogether in each schema. Plus for the purpose of sharding it's easier to use, since normalized data models complicate row-based sharding.
- paulddraper 3y ago> Schema objects can't be manipulated as a batch, I would make a partitioned table, and put each partition in its own schema.
- nine_k 3y agoSchemas are namespaces, a convenience feature, not a security feature.
- rad_gruchalski 3y agoThat's exactly the reason I went for YugabyteDB tablespaces implementation on one of past projects. Dedicated database locked down to selected nodes for full isolation.