Skip to main content

Dan Aharon

Polymorphic Relations in PostgreSQL Using Table Inheritance

Summary

Notes on using PostgreSQL’s Table Interitance using a hypothetical alerting system. The system receives a stream of system metrics and has different types of triggers which are associated with multiple types of subscribers.

Demonstrated with two types of trigger: system load and filesystem usage.

Tables

Example table diagram with triggers and subscribers, where each is associated with a user. The triggers and subscribers are also linked to each other via a many-to-many (junction) table.

erDiagram USER { uuid id } TRIGGER_BASE { uuid id uuid user_id boolean active timestamptz created_at } USER ||--|{ TRIGGER_BASE : user_id TRIGGER_LOAD { numeric one_minute numeric five_minute numeric fifteen_minute } TRIGGER_BASE ||--|| TRIGGER_LOAD : INHERITS TRIGGER_FILESYSTEM { text mount_point numeric usage } TRIGGER_BASE ||--|| TRIGGER_FILESYSTEM : INHERITS SUBSCRIBER_BASE { uuid id uuid user_id boolean active timestamptz created_at } USER ||--|{ SUBSCRIBER_BASE : user_id SUBSCRIBER_EMAIL { text email } SUBSCRIBER_BASE ||--|| SUBSCRIBER_EMAIL : INHERITS SUBSCRIBER_SMS { text phone_number } SUBSCRIBER_BASE ||--|| SUBSCRIBER_SMS : INHERITS SUBSCRIBER_TO_TRIGGER { uuid subscriber_id uuid trigger_id } SUBSCRIBER_TO_TRIGGER }|--|| TRIGGER_BASE : trigger_id SUBSCRIBER_TO_TRIGGER }|--|| SUBSCRIBER_BASE : subscriber_id

The SQL for this schema design:

CREATE TABLE IF NOT EXISTS public.user
(
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid()
);

-- TRIGGERS
CREATE SCHEMA IF NOT EXISTS triggers;

CREATE TABLE IF NOT EXISTS triggers.base
(
    id         UUID PRIMARY KEY         NOT NULL DEFAULT gen_random_uuid(),
    user_id    UUID                     NOT NULL REFERENCES public.user (id),
    active     BOOLEAN                  NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS triggers."load"
(
    one_minute     NUMERIC NOT NULL,
    five_minute    NUMERIC NOT NULL,
    fifteen_minute NUMERIC NOT NULL,

    PRIMARY KEY (id)
) INHERITS (triggers.base);

CREATE TABLE IF NOT EXISTS triggers.filesystem
(
    mount_point TEXT    NOT NULL,
    usage       NUMERIC NOT NULL,

    PRIMARY KEY (id),
    UNIQUE (user_id, mount_point)
) INHERITS (triggers.base);

-- SUBSCRIBERS
CREATE SCHEMA IF NOT EXISTS subscribers;

CREATE TABLE IF NOT EXISTS subscribers.base
(
    id         UUID PRIMARY KEY         NOT NULL DEFAULT gen_random_uuid(),
    user_id    UUID                     NOT NULL REFERENCES public.user (id),
    active     BOOLEAN                  NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subscribers.email
(
    email TEXT NOT NULL,

    PRIMARY KEY (id),
    UNIQUE (user_id, email)
) INHERITS (subscribers.base);

CREATE TABLE IF NOT EXISTS subscribers.sms
(
    phone_number TEXT NOT NULL,

    PRIMARY KEY (id),
    UNIQUE (user_id, phone_number)
) INHERITS (subscribers.base);

-- JUNCTION TABLE
CREATE TABLE IF NOT EXISTS public.trigger_to_subscriber
(
    trigger_id    UUID NOT NULL, -- REFERENCES triggers.base (id),
    subscriber_id UUID NOT NULL, -- REFERENCES subscribers.base (id),

    PRIMARY KEY (trigger_id, subscriber_id)
);
CREATE INDEX ON public.trigger_to_subscriber (trigger_id);
CREATE INDEX ON public.trigger_to_subscriber (subscriber_id);

Note: Special cases with indexes and foreign-keys.

  • Primary keys and unique indexes are defined for each child table.
  • The REFERENCES clauses in public.trigger_to_subscriber are commented out. This is because the foreign keys are not inherited by the child tables.
    The junction-table can be used between the parent or child tables, but the constraints will not be enforced.
  • From the PostgreSQL documenation:

    A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children.

2023-05-09
https://dan.aharon.dev/posts/polymorphic-relations-in-postgresql-using-table-inheritance/ Dan Aharon

Read next