PostgreSQL Table Inheritance and Polymorphism: Multi-Table Hierarchies, Type Routing, and Partitioning

Database polymorphism — storing different subtypes of an entity in the same or related tables — comes up constantly in SaaS applications: notifications (email, SMS, push), events (user events, system events, billing events), payments (card, bank transfer, crypto), and content (article, video, podcast). PostgreSQL offers several approaches, each with meaningful tradeoffs.

Pattern Comparison

Single-Table Inheritance (Most Common in SaaS)

-- Simplest: one table with a type discriminator
CREATE TYPE notification_channel AS ENUM ('email', 'sms', 'push', 'webhook');

CREATE TABLE notifications (
  id           UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  workspace_id UUID        NOT NULL REFERENCES workspaces(id),
  user_id      UUID        REFERENCES users(id),
  channel      notification_channel NOT NULL,

  -- Shared fields
  subject      TEXT,
  body         TEXT        NOT NULL,
  status       TEXT        NOT NULL DEFAULT 'pending'
                           CHECK (status IN ('pending', 'sent', 'failed', 'bounced')),
  sent_at      TIMESTAMPTZ,
  created_at   TIMESTAMPTZ DEFAULT NOW(),

  -- Email-specific (NULL for other channels)
  email_to     TEXT,
  email_from   TEXT,
  reply_to     TEXT,
  bounce_code  TEXT,

  -- SMS-specific
  phone_to     TEXT,
  sms_provider TEXT,

  -- Push-specific
  device_token TEXT,
  fcm_topic    TEXT,

  -- Webhook-specific
  webhook_url  TEXT,
  webhook_payload JSONB,
  http_status  INTEGER
);

CREATE INDEX ON notifications (workspace_id, channel, created_at DESC);
CREATE INDEX ON notifications (user_id, created_at DESC) WHERE user_id IS NOT NULL;
CREATE INDEX ON notifications (status, created_at) WHERE status = 'pending';

-- Query a specific subtype:
SELECT id, email_to, subject, status
FROM notifications
WHERE workspace_id = $1
  AND channel = 'email'
  AND created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

Class-Table Inheritance (Best Type Safety)

-- Parent table: shared fields only
CREATE TABLE events (
  id           UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  workspace_id UUID        NOT NULL REFERENCES workspaces(id),
  event_type   TEXT        NOT NULL,  -- Discriminator
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metadata     JSONB       NOT NULL DEFAULT '{}'
);

-- Extension table for user events
CREATE TABLE user_events (
  event_id     UUID        PRIMARY KEY REFERENCES events(id) ON DELETE CASCADE,
  user_id      UUID        NOT NULL REFERENCES users(id),
  session_id   TEXT,
  ip_address   INET,
  user_agent   TEXT
);

-- Extension table for billing events
CREATE TABLE billing_events (
  event_id      UUID        PRIMARY KEY REFERENCES events(id) ON DELETE CASCADE,
  amount_cents  INTEGER     NOT NULL,
  currency      TEXT        NOT NULL DEFAULT 'USD',
  payment_id    TEXT,
  invoice_id    UUID
);

-- Extension table for system events
CREATE TABLE system_events (
  event_id     UUID        PRIMARY KEY REFERENCES events(id) ON DELETE CASCADE,
  service_name TEXT        NOT NULL,
  severity     TEXT        NOT NULL CHECK (severity IN ('info', 'warn', 'error', 'critical')),
  error_code   TEXT
);

-- Insert a user event (two INSERTs, wrapped in transaction):
BEGIN;
INSERT INTO events (workspace_id, event_type) VALUES ($1, 'page_view') RETURNING id;
-- Use the returned id:
INSERT INTO user_events (event_id, user_id, ip_address) VALUES ($returned_id, $2, $3);
COMMIT;

-- Query user events with parent fields:
SELECT
  e.id,
  e.occurred_at,
  e.event_type,
  ue.user_id,
  ue.ip_address
FROM events e
JOIN user_events ue ON ue.event_id = e.id
WHERE e.workspace_id = $1
  AND e.event_type = 'page_view'
  AND e.occurred_at >= NOW() - INTERVAL '1 day'
ORDER BY e.occurred_at DESC;

-- Polymorphic query: get all event types in one result
SELECT
  e.id, e.occurred_at, e.event_type,
  ue.user_id,
  be.amount_cents,
  se.severity,
  CASE
    WHEN ue.event_id IS NOT NULL THEN 'user'
    WHEN be.event_id IS NOT NULL THEN 'billing'
    WHEN se.event_id IS NOT NULL THEN 'system'
  END AS category
FROM events e
LEFT JOIN user_events   ue ON ue.event_id = e.id
LEFT JOIN billing_events be ON be.event_id = e.id
LEFT JOIN system_events  se ON se.event_id = e.id
WHERE e.workspace_id = $1
  AND e.occurred_at >= NOW() - INTERVAL '24 hours'
ORDER BY e.occurred_at DESC
LIMIT 100;

PostgreSQL Native INHERITS: Range Partitioning

-- PostgreSQL INHERITS is most useful for manual partitioning
-- (before pg_partman; use declarative partitioning for new code)

CREATE TABLE audit_log (
  id           BIGSERIAL,
  workspace_id UUID        NOT NULL,
  action       TEXT        NOT NULL,
  occurred_at  TIMESTAMPTZ NOT NULL
);

-- Child tables inherit all columns + add partition constraint
CREATE TABLE audit_log_2027_q1 (
  CHECK (occurred_at >= '2027-01-01' AND occurred_at < '2027-04-01')
) INHERITS (audit_log);

CREATE TABLE audit_log_2027_q2 (
  CHECK (occurred_at >= '2027-04-01' AND occurred_at < '2027-07-01')
) INHERITS (audit_log);

-- Index on each partition
CREATE INDEX ON audit_log_2027_q1 (workspace_id, occurred_at DESC);
CREATE INDEX ON audit_log_2027_q2 (workspace_id, occurred_at DESC);

-- Routing function: INSERT into parent → trigger routes to child
CREATE OR REPLACE FUNCTION route_audit_log() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.occurred_at >= '2027-04-01' AND NEW.occurred_at < '2027-07-01' THEN
    INSERT INTO audit_log_2027_q2 VALUES (NEW.*);
  ELSIF NEW.occurred_at >= '2027-01-01' AND NEW.occurred_at < '2027-04-01' THEN
    INSERT INTO audit_log_2027_q1 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'No partition for occurred_at: %', NEW.occurred_at;
  END IF;
  RETURN NULL;  -- NULL suppresses insert to parent
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_log_insert_trigger
  BEFORE INSERT ON audit_log
  FOR EACH ROW EXECUTE FUNCTION route_audit_log();

-- ⚠️ Prefer declarative partitioning (PARTITION BY RANGE) for new code
-- INHERITS partitioning is shown here for understanding legacy systems

TypeScript: Class-Table Insert Pattern

// lib/events/create-event.ts
import { prisma } from "@/lib/prisma";

type UserEventData = {
  type:      "user";
  userId:    string;
  ipAddress?: string;
  userAgent?: string;
};

type BillingEventData = {
  type:        "billing";
  amountCents: number;
  currency:    string;
  paymentId?:  string;
};

type EventData = UserEventData | BillingEventData;

export async function createEvent(
  workspaceId: string,
  eventType:   string,
  data:        EventData
): Promise<string> {
  // Class-table inheritance requires two INSERTs — use $transaction
  return prisma.$transaction(async (tx) => {
    // 1. Insert parent row
    const [event] = await tx.$queryRaw<[{ id: string }]>`
      INSERT INTO events (workspace_id, event_type)
      VALUES (${workspaceId}::uuid, ${eventType})
      RETURNING id
    `;

    // 2. Insert extension row based on discriminator
    if (data.type === "user") {
      await tx.$executeRaw`
        INSERT INTO user_events (event_id, user_id, ip_address, user_agent)
        VALUES (
          ${event.id}::uuid,
          ${data.userId}::uuid,
          ${data.ipAddress ?? null}::inet,
          ${data.userAgent ?? null}
        )
      `;
    } else if (data.type === "billing") {
      await tx.$executeRaw`
        INSERT INTO billing_events (event_id, amount_cents, currency, payment_id)
        VALUES (
          ${event.id}::uuid,
          ${data.amountCents},
          ${data.currency},
          ${data.paymentId ?? null}
        )
      `;
    }

    return event.id;
  });
}

// Polymorphic query: get recent events with type-specific fields
export async function getRecentEvents(workspaceId: string) {
  return prisma.$queryRaw<{
    id:           string;
    event_type:   string;
    occurred_at:  Date;
    user_id:      string | null;
    amount_cents: number | null;
    severity:     string | null;
  }[]>`
    SELECT
      e.id,
      e.event_type,
      e.occurred_at,
      ue.user_id,
      be.amount_cents,
      se.severity
    FROM events e
    LEFT JOIN user_events    ue ON ue.event_id = e.id
    LEFT JOIN billing_events be ON be.event_id = e.id
    LEFT JOIN system_events  se ON se.event_id = e.id
    WHERE e.workspace_id = ${workspaceId}::uuid
      AND e.occurred_at >= NOW() - INTERVAL '24 hours'
    ORDER BY e.occurred_at DESC
    LIMIT 50
  `;
}

Which Pattern to Choose

Cost and Timeline Estimates

See Also

• PostgreSQL Partitioning
• PostgreSQL Lateral Joins
• PostgreSQL Window Functions
• PostgreSQL Row-Level Security
• PostgreSQL Constraints and Validation

---

Working With Viprasol

The right inheritance pattern depends on how the data is queried. Single-table is fastest to query (no JOINs) but creates a sparse schema with many nullable columns. Class-table is the cleanest from a type-safety perspective but requires two-table transactions on insert. We typically default to single-table for notification systems (few shared fields, always queried by type) and class-table for event systems (many shared fields, frequently queried polymorphically).

What we deliver:

• Single-table: notification_channel enum, discriminator column, indexes on (workspace_id, channel, created_at DESC), (status, created_at) partial index
• Class-table: events parent + user_events/billing_events/system_events extension tables, FK ON DELETE CASCADE
• Two-INSERT transaction: $queryRaw for parent RETURNING id, $executeRaw for extension by discriminator
• Polymorphic LEFT JOIN query: all event types with type-specific nullable columns
• INHERITS trigger routing: route_audit_log() BEFORE INSERT trigger routing to quarterly partition tables

Talk to our team about your data modeling strategy →

Or explore our cloud and data engineering services.