Quasi-Vertical Partioning

by squarewav   Last Updated August 13, 2019 21:06 PM

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:

    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:

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?

Related Questions

Updated May 02, 2017 12:06 PM

Updated May 03, 2017 10:06 AM

Updated July 31, 2015 13:02 PM

Updated August 07, 2017 14:06 PM