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, name TEXT NOT NULL, value TEXT 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?