Skip to main content

Dan Aharon

Polymorphic Relations in PostgreSQL Using JSON

Summary

Notes on using PostgreSQL’s JSON data-type 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.

The type of the trigger and the subscriber is set in a column of the table. Using this type, we can know the schema of the jsonb object.

CREATE TYPE trigger_enum AS ENUM ('LOAD', 'FILESYSTEM');
CREATE TYPE subscriber_enum AS ENUM ('EMAIL', 'SMS');
  • TRIGGER.type
    • LOAD 
      { "one_minute": 7.0, "five_minute": 5.0, "fifteen_minute": 3.0 }
    • FILESYSTEM 
      { "mount_point": "/tmp", "usage": 90.0 }
  • SUBSCRIBER.type
erDiagram USER { uuid id } TRIGGER { uuid id uuid user_id boolean active trigger_enum type timestamptz created_at jsonb attributes } USER ||--|{ TRIGGER : user_id SUBSCRIBER { uuid id uuid user_id subscriber_enum type timestamptz created_at jsonb attributes } USER ||--|{ SUBSCRIBER : user_id SUBSCRIBER_TO_TRIGGER { uuid subscriber_id uuid trigger_id } SUBSCRIBER }|--|{ SUBSCRIBER_TO_TRIGGER : subscriber_id TRIGGER }|--|{ SUBSCRIBER_TO_TRIGGER : trigger_id

The SQL for this schema design:

CREATE TYPE trigger_enum AS ENUM ('LOAD', 'FILESYSTEM');
CREATE TYPE subscriber_enum AS ENUM ('EMAIL', 'SMS');

CREATE TABLE IF NOT EXISTS users
(
  id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid()
);

CREATE TABLE IF NOT EXISTS triggers
(
  id         UUID PRIMARY KEY         NOT NULL DEFAULT gen_random_uuid(),
  user_id    UUID                     NOT NULL REFERENCES users (id),
  active     BOOLEAN                  NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "type"     trigger_enum             NOT NULL,
  attributes jsonb                    NOT NULL
);
CREATE UNIQUE INDEX ON triggers (user_id, type, (attributes ->> 'mount_point'));

CREATE TABLE IF NOT EXISTS subscribers
(
  id         UUID PRIMARY KEY         NOT NULL DEFAULT gen_random_uuid(),
  user_id    UUID                     NOT NULL REFERENCES users (id),
  active     BOOLEAN                  NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "type"     subscriber_enum          NOT NULL,
  attributes jsonb                    NOT NULL
);
CREATE UNIQUE INDEX ON subscribers (user_id, "type", (attributes ->> 'email'));
CREATE UNIQUE INDEX ON subscribers (user_id, "type", (attributes ->> 'phone_number'));

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

  PRIMARY KEY (trigger_id, subscriber_id)
);

Note:

  • Indexes created based on jsonb field names using the (<jsonb-column> ->> 'field-name') syntax.
2023-05-09
https://dan.aharon.dev/posts/polymorphic-relations-in-postgresql-using-json/ Dan Aharon

Read next