PostgreSQL - "polymorphic table" vs 3 tables

Viewed 4764

I am using PostgreSQL 9.5 (but upgrade is possible to say 9.6).

I have permissions table:

CREATE TABLE public.permissions
(
  id integer NOT NULL DEFAULT nextval('permissions_id_seq'::regclass),
  item_id integer NOT NULL,
  item_type character varying NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT permissions_pkey PRIMARY KEY (id)
)
-- skipping indices declaration, but they would be present
-- on item_id, item_type

And 3 tables for many-to-many associations

-companies_permissions (+indices declaration)

CREATE TABLE public.companies_permissions
(
  id integer NOT NULL DEFAULT nextval('companies_permissions_id_seq'::regclass),
  company_id integer,
  permission_id integer,
  CONSTRAINT companies_permissions_pkey PRIMARY KEY (id),
  CONSTRAINT fk_rails_462a923fa2 FOREIGN KEY (company_id)
      REFERENCES public.companies (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_rails_9dd0d015b9 FOREIGN KEY (permission_id)
      REFERENCES public.permissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE INDEX index_companies_permissions_on_company_id
  ON public.companies_permissions
  USING btree
  (company_id);

CREATE INDEX index_companies_permissions_on_permission_id
  ON public.companies_permissions
  USING btree
  (permission_id);

CREATE UNIQUE INDEX index_companies_permissions_on_permission_id_and_company_id
  ON public.companies_permissions
  USING btree
  (permission_id, company_id);

-permissions_user_groups (+indices declaration)

CREATE TABLE public.permissions_user_groups
(
  id integer NOT NULL DEFAULT nextval('permissions_user_groups_id_seq'::regclass),
  permission_id integer,
  user_group_id integer,
  CONSTRAINT permissions_user_groups_pkey PRIMARY KEY (id),
  CONSTRAINT fk_rails_c1743245ea FOREIGN KEY (permission_id)
      REFERENCES public.permissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_rails_e966751863 FOREIGN KEY (user_group_id)
      REFERENCES public.user_groups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE UNIQUE INDEX index_permissions_user_groups_on_permission_and_user_group
  ON public.permissions_user_groups
  USING btree
  (permission_id, user_group_id);

CREATE INDEX index_permissions_user_groups_on_permission_id
  ON public.permissions_user_groups
  USING btree
  (permission_id);

CREATE INDEX index_permissions_user_groups_on_user_group_id
  ON public.permissions_user_groups
  USING btree
  (user_group_id);

-permissions_users (+indices declaration)

CREATE TABLE public.permissions_users
(
  id integer NOT NULL DEFAULT nextval('permissions_users_id_seq'::regclass),
  permission_id integer,
  user_id integer,
  CONSTRAINT permissions_users_pkey PRIMARY KEY (id),
  CONSTRAINT fk_rails_26289d56f4 FOREIGN KEY (user_id)
      REFERENCES public.users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_rails_7ac7e9f5ad FOREIGN KEY (permission_id)
      REFERENCES public.permissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE INDEX index_permissions_users_on_permission_id
  ON public.permissions_users
  USING btree
  (permission_id);

CREATE UNIQUE INDEX index_permissions_users_on_permission_id_and_user_id
  ON public.permissions_users
  USING btree
  (permission_id, user_id);

CREATE INDEX index_permissions_users_on_user_id
  ON public.permissions_users
  USING btree
  (user_id);

I will have to run SQL query like this a lot times:

SELECT
"permissions".*,
"permissions_users".*,
"companies_permissions".*,
"permissions_user_groups".* 
FROM "permissions"
LEFT OUTER JOIN
  "permissions_users" ON "permissions_users"."permission_id" = "permissions"."id"
LEFT OUTER JOIN
  "companies_permissions" ON "companies_permissions"."permission_id" = "permissions"."id"
LEFT OUTER JOIN
  "permissions_user_groups" ON "permissions_user_groups"."permission_id" = "permissions"."id"
WHERE
  (companies_permissions.company_id = <company_id> OR
  permissions_users.user_id in (<user_ids> OR NULL) OR
  permissions_user_groups.user_group_id IN (<user_group_ids> OR NULL)) AND
permissions.item_type = 'Topic' 

Let's say we have about 10000+ permissions and similar amount of records inside other tables.

Do I need to worry about performance?

I mean... I have 4 LEFT OUTER JOINs and it should return results pretty fast (say <200ms).

I was thinking about declaring 1 "polymorphic" table, something like:

CREATE TABLE public.permissables
(
  id integer NOT NULL DEFAULT nextval('permissables_id_seq'::regclass),
  permission_id integer,
  resource_id integer NOT NULL,
  resource_type character varying NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT permissables_pkey PRIMARY KEY (id)
)
-- skipping indices declaration, but they would be present

Then I could run query like this:

SELECT
  permissions.*,
  permissables.*
FROM permissions
LEFT OUTER JOIN
  permissables ON permissables.permission_id = permissions.id
WHERE
  permissions.item_type = 'Topic' AND
  (permissables.owner_id IN (<user_ids>) AND permissables.owner_type = 'User') OR
  (permissables.owner_id = <company_id> AND permissables.owner_type = 'Company') OR
  (permissables.owner_id IN (<user_groups_ids>) AND permissables.owner_type = 'UserGroup')

QUESTIONS:

  1. Which options is better/faster? Maybe there is better way to do this?

a) 4 tables (permissions, companies_permissions, user_groups_permissions, users_permissions) b) 2 tables (permissions, permissables)

  1. Do I need to declare different indexes than btree on permissions.item_type ?

  2. Do I need to run a few times per day vacuum analyze for tables to make indices work (both options)?


EDIT1:

SQLFiddle examples:

  1. wildplasser suggestion (from comment), not working: http://sqlfiddle.com/#!15/9723f8/1
  2. Original query (4 tables): http://sqlfiddle.com/#!15/9723f8/2

{ I also removed backticks in wrong places thanks @wildplasser }

4 Answers
Related