I have a table that stores generic data for a wide variety of purposes that are almost totally independent of one another (created by different users for example). So I'm thinking about adding an indexed int column called "partid" to "partition" data by use like:
CREATE TABLE dat ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), partid int NOT NULL, foo varchar(128) NOT NULL, bar varchar(256) NOT NULL, ... ); CREATE INDEX idx ON dat (partid);
and then code can limit queries, updates and deletes to only records within a "partition" like:
SELECT * FROM dat WHERE partid IN (123, 345, 456)
The idea is that this should both improve performance and provide security by excluding records that the user does not even have permission to see.
So is this a good idea or is there a better way?
Note I'm actually not doing EAV or "proper" partitioning. Single-table multi-tenant is logically what I am describing. However from reading about it I get the impression that "tenant" refers to clients / entities whereas what I'm describing is a general data access strategy.
For example, imagine legal analysts working on litigation. A different
partid could be used to keep documents, case files and evidence separate. I use the prefix "quasi" because this is not traditional vertical partitioning. It is a type of SQL-level lateral partitioning using a column as a discriminator.
The SQL would be written to always use the partids supplied by the calling context. It's up to the code to decide what partids would be used but a session concept is a good example - when a client connects, they would acquire a preferably small list of partids representing what they are allowed to access / do (perhaps as an authorization step). The idea is that it is effectively a filter or mask. And importantly it would be applied in SQL and not in the application layer. It is a sort of "defensive programming".