Quasi-Vertical Partioning

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

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?



Related Questions




Optimise huge Postgres table for fast read

Updated July 31, 2015 13:02 PM