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,
    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:

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".

put on hold as unclear what you're asking by mustaccio, Marcello Miorelli, John Eisbrener, Colin 't Hart, Marco yesterday

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product and partitioning features depend heavily on the DBMS product. Please add a tag for the database product you are using – a_horse_with_no_name Aug 14 at 9:52
  • "The SQL would be written to always use the partids" - that is usual prerequisite when trying to use partitioning for performance reasons. Assuming you have thousands (if not millions) of part IDs, you probably want hash partitioning for that (or maybe range partitioning, but that's unlikely) – a_horse_with_no_name Aug 14 at 9:54
  • @a_horse_with_no_name This is not regular RDBMS partitioning. This is application layer controlled "partitioning". The application controls what "partitions" are visible. All of the SQL must always use the partid IN (...) clause everywhere or the security aspect is lost. I suppose that's the major downside for most people but it so happens it's not in my particular case. – squarewav Aug 14 at 14:54
  • But if you use the partid everywhere and always then it's a good partitioning key – a_horse_with_no_name Aug 14 at 15:04
  • The partids are supplied by the caller and can be switched at any moment (such as, for example, because the legal analyst is looking at a different case). Correct me if I'm wrong but you can't do that with conventional partitioning. – squarewav Aug 14 at 23:36

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.

Multi-tenancy is a general data access strategy, in that it allows multiple tenants of the same database to use it as if they were the only one using it. A tenant may be a single user, or it may contain multiple users. Data access frameworks that support multitenancy have ways to manage the tenant seamlessly, so you don't have to think too much about it. You just need to select the strategy (table/schema/database) and how the tenant is identified.

However at the end of your question you're describing something more akin to regular access control. A connecting client is given roles, and based on those can do things. This is usually done on the application side as it's a lot easier to handle complex logic there. Just because the database allows something, doesn't mean the application thinks identically (e.g. if the database allows accessing Table A, the application doesn't necessarily allow access to all rows in Table A).

The performance side is most likely irrelevant, but the security aspect is there. You can use multitenancy to give hard limits to the data, but you still need to handle the row level access yourself (within the same tenant). However you also need to consider whether there really is something to gain since it adds complexity, is unlikely to provide any performance benefits, and even the security requirements need careful consideration: is this necessary? is this something that should be handled on the db or the application side?

Note that in many applications you wouldn't perform wild select statements like in your example. To use your example of legal analyst, instead of synthetic partids you would have your analyst user naturally have a link to a case, the case then links to documents, and so on. Then it's up to you to make sure you don't write broken code and that your db schema is proper. But if they're not in order, no amount of clever partids will save you.

Not the answer you're looking for? Browse other questions tagged or ask your own question.