Skip to main content

Some PostgreSQL Database Objects: A Fast Revision Sheet

Updated 11 min readMarkdown
Some PostgreSQL Database Objects: A Fast Revision Sheet

One schema (shop), one tiny e-commerce model, and every object type below used in context. It's meant as a refresher for people who already know SQL, so there's no hand-holding. Tested against PostgreSQL 15+.


1. Setup: Schema, Tables, Dummy Data

Schemas

A schema is a namespace plus a permission boundary. Use one per bounded context, and set search_path per role rather than per session.

CREATE SCHEMA IF NOT EXISTS shop;
SET search_path = shop, public;

-- per-role defaults + least privilege
-- ALTER ROLE app_rw SET search_path = shop;
-- GRANT USAGE ON SCHEMA shop TO app_ro;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA shop GRANT SELECT ON TABLES TO app_ro;

Tables

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text        NOT NULL UNIQUE,
    name        text        NOT NULL,
    country     char(2)     NOT NULL,
    referred_by bigint      REFERENCES customers(id) ON DELETE SET NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku    text           NOT NULL UNIQUE,
    name   text           NOT NULL,
    price  numeric(10,2)  NOT NULL CHECK (price > 0),
    cost   numeric(10,2)  NOT NULL CHECK (cost >= 0),
    stock  int            NOT NULL DEFAULT 0 CHECK (stock >= 0),
    margin numeric(10,2)  GENERATED ALWAYS AS (price - cost) STORED,
    CONSTRAINT price_gte_cost CHECK (price >= cost)
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint      NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status      text        NOT NULL DEFAULT 'new'
                CHECK (status IN ('new','paid','shipped','cancelled')),
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id   bigint        NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint        NOT NULL REFERENCES products(id),
    qty        int           NOT NULL CHECK (qty > 0),
    unit_price numeric(10,2) NOT NULL,
    line_total numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED,
    PRIMARY KEY (order_id, product_id)
);

-- Partitioned table + explicit sequence (see sections below)
CREATE SEQUENCE event_seq START 1000 CACHE 50;

CREATE TABLE order_events (
    id         bigint      NOT NULL DEFAULT nextval('shop.event_seq'),
    order_id   bigint      NOT NULL REFERENCES orders(id),
    event      text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)          -- PK must include the partition key
) PARTITION BY RANGE (created_at);

ALTER SEQUENCE event_seq OWNED BY order_events.id;

CREATE TABLE order_events_2026_10 PARTITION OF order_events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE order_events_2026_11 PARTITION OF order_events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE order_events_default PARTITION OF order_events DEFAULT;

-- Trigger target
CREATE TABLE price_audit (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id bigint        NOT NULL REFERENCES products(id),
    old_price  numeric(10,2) NOT NULL,
    new_price  numeric(10,2) NOT NULL,
    changed_at timestamptz   NOT NULL DEFAULT now()
);

-- Staging table (no WAL)
CREATE UNLOGGED TABLE stg_product_import (
    sku text, name text, price numeric(10,2), cost numeric(10,2), stock int
);

Dummy Data

INSERT INTO customers (email, name, country, referred_by, created_at) VALUES
 ('alice@example.com','Alice','US',NULL,'2026-09-01'),
 ('bob@example.com',  'Bob',  'DE',1,   '2026-09-10'),
 ('carol@example.com','Carol','BD',2,   '2026-09-20'),
 ('dave@example.com', 'Dave', 'GB',NULL,'2026-10-02');

INSERT INTO products (sku, name, price, cost, stock) VALUES
 ('KB-01','Mechanical Keyboard',120.00, 70.00, 50),
 ('MS-01','Wireless Mouse',       45.00, 20.00, 100),
 ('MN-01','27" Monitor',         320.00,230.00, 20),
 ('HS-01','Headset',              80.00, 40.00, 35);

INSERT INTO orders (customer_id, status, created_at) VALUES
 (1,'shipped','2026-10-05 10:00+00'),
 (2,'paid',   '2026-10-18 14:30+00'),
 (3,'paid',   '2026-11-02 09:15+00'),
 (1,'new',    '2026-11-05 16:45+00'),
 (4,'new',    '2026-09-25 08:00+00');

INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
 (1,1,1,120.00),(1,2,2,45.00),
 (2,3,1,320.00),
 (3,4,2,80.00),(3,2,1,45.00),
 (4,1,1,120.00),
 (5,2,1,45.00);

INSERT INTO order_events (order_id, event, created_at) VALUES
 (1,'created','2026-10-05 10:00+00'),(1,'shipped','2026-10-07 12:00+00'),
 (2,'created','2026-10-18 14:30+00'),(2,'paid','2026-10-18 14:35+00'),
 (3,'created','2026-11-02 09:15+00'),(3,'paid','2026-11-02 09:20+00'),
 (4,'created','2026-11-05 16:45+00');

2. ER Diagram

Here is our example DDL in ER diagram

Paste into mermaid.live:

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    CUSTOMERS |o--o{ CUSTOMERS : refers
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "sold in"
    ORDERS ||--o{ ORDER_EVENTS : logs
    PRODUCTS ||--o{ PRICE_AUDIT : "audited by"

    CUSTOMERS {
        bigint id PK
        text email UK
        text name
        char country
        bigint referred_by FK
        timestamptz created_at
    }
    PRODUCTS {
        bigint id PK
        text sku UK
        text name
        numeric price "CHECK > 0"
        numeric cost
        int stock "CHECK >= 0"
        numeric margin "GENERATED STORED"
    }
    ORDERS {
        bigint id PK
        bigint customer_id FK
        text status "CHECK enum"
        timestamptz created_at
    }
    ORDER_ITEMS {
        bigint order_id PK, FK
        bigint product_id PK, FK
        int qty "CHECK > 0"
        numeric unit_price
        numeric line_total "GENERATED STORED"
    }
    ORDER_EVENTS {
        bigint id PK "event_seq"
        timestamptz created_at PK "PARTITION KEY"
        bigint order_id FK
        text event
    }
    PRICE_AUDIT {
        bigint id PK
        bigint product_id FK
        numeric old_price
        numeric new_price
        timestamptz changed_at
    }
    STG_PRODUCT_IMPORT {
        text sku
        text name
        numeric price
        numeric cost
        int stock
    }

3. The Objects

Views

A stored query with no data of its own. Simple views are auto-updatable, and security_barrier matters if you use them for row filtering.

CREATE VIEW v_order_totals AS
SELECT o.id AS order_id, o.customer_id, c.email, o.status, o.created_at,
       sum(oi.line_total) AS total
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.email;

Materialized Views and REFRESH

A materialized view stores the result physically and is indexable, but it goes stale until you refresh it. CONCURRENTLY avoids blocking readers, but it requires a unique index and an already-populated matview.

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT o.created_at::date AS day,
       count(DISTINCT o.id) AS orders,
       sum(oi.line_total)   AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid','shipped')
GROUP BY 1;

CREATE UNIQUE INDEX ON mv_daily_revenue (day);

REFRESH MATERIALIZED VIEW mv_daily_revenue;               -- ACCESS EXCLUSIVE lock
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;  -- diff-based, readers keep working

Postgres has no incremental refresh and no built-in scheduler. Use pg_cron or your orchestrator.

Functions

Functions return a value, run inside the caller's transaction, and can be used in queries. Declare volatility honestly (IMMUTABLE/STABLE/VOLATILE) because the planner relies on it. Simple LANGUAGE sql functions can be inlined.

CREATE FUNCTION fn_customer_ltv(p_customer_id bigint)
RETURNS numeric
LANGUAGE sql STABLE AS $$
    SELECT coalesce(sum(total), 0)
    FROM v_order_totals
    WHERE customer_id = p_customer_id AND status IN ('paid','shipped');
$$;

SELECT name, fn_customer_ltv(id) FROM customers;
/*
name |fn_customer_ltv|
-----+---------------+
Alice|         210.00|
Bob  |         320.00|
Carol|         205.00|
Dave |              0|
*/

Procedures

Procedures are invoked with CALL, return no value, and can control transactions (COMMIT/ROLLBACK), which functions cannot.

CREATE PROCEDURE sp_place_order(p_customer bigint, p_product bigint, p_qty int)
LANGUAGE plpgsql AS $$
DECLARE
    v_price numeric;
    v_order bigint;
BEGIN
    UPDATE products SET stock = stock - p_qty   -- CHECK (stock >= 0) guards oversell
    WHERE id = p_product
    RETURNING price INTO v_price;

    IF NOT FOUND THEN RAISE EXCEPTION 'unknown product %', p_product; END IF;

    INSERT INTO orders (customer_id) VALUES (p_customer) RETURNING id INTO v_order;
    INSERT INTO order_items VALUES (v_order, p_product, p_qty, v_price);
    INSERT INTO order_events (order_id, event) VALUES (v_order, 'created');
    COMMIT;
END $$;

CALL sp_place_order(2, 4, 1);

Temporary Tables

Temp tables are session-scoped and unlogged. They are invisible to other sessions and can be analyzed and indexed. ON COMMIT DROP keeps things clean.

BEGIN;
CREATE TEMP TABLE tmp_big_orders ON COMMIT DROP AS
    SELECT order_id, total FROM v_order_totals WHERE total > 100;
ANALYZE tmp_big_orders;                    -- autovacuum can't see temp tables
SELECT * FROM tmp_big_orders ORDER BY total DESC;
COMMIT;

Unlogged Tables

An unlogged table skips WAL, so writes are much faster. The table is truncated after a crash and not replicated. It suits staging and ETL landing zones, never source-of-truth data.

COPY stg_product_import FROM '/tmp/products.csv' CSV HEADER;  -- or INSERT ... 

INSERT INTO products (sku, name, price, cost, stock)
SELECT sku, name, price, cost, stock FROM stg_product_import
ON CONFLICT (sku) DO UPDATE
   SET price = EXCLUDED.price, cost = EXCLUDED.cost, stock = EXCLUDED.stock;

TRUNCATE stg_product_import;
-- ALTER TABLE stg_product_import SET LOGGED;   -- flip when needed (rewrites table)

Sequences

Sequences are non-transactional, so rollbacks leave gaps, and CACHE can skip values across sessions. GENERATED ... AS IDENTITY is the SQL-standard replacement for serial, and event_seq above is the explicit variant.

SELECT nextval('event_seq'), currval('event_seq');
SELECT setval('event_seq', (SELECT max(id) FROM order_events));  -- resync after bulk loads

Generated Columns

STORED generated columns are computed on write and can be indexed. Postgres 18 adds VIRTUAL. They can't reference other generated columns or non-immutable functions.

SELECT sku, price, cost, margin FROM products;   -- margin = price - cost
-- UPDATE products SET margin = 1;               -- ERROR: can only be updated to DEFAULT
CREATE INDEX ON order_items (line_total);

Table Partitioning

Declarative partitioning supports range, list, and hash. The planner uses partition pruning, so always filter on the key. Primary and unique keys must include the partition key. Retention becomes DETACH plus DROP instead of a giant DELETE.

EXPLAIN SELECT * FROM order_events
WHERE created_at >= '2026-11-01' AND created_at < '2026-12-01';  -- scans only _2026_11

-- Add the next partition ahead of time
CREATE TABLE order_events_2026_12 PARTITION OF order_events
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- Retention
ALTER TABLE order_events DETACH PARTITION order_events_2026_10 CONCURRENTLY;

A DEFAULT partition is a safety net, but it makes adding new partitions slower because it must be scanned for conflicting rows.

Declarative Constraints: CHECK, UNIQUE, FOREIGN KEY

Everything is already in the DDL above. A few things worth remembering:

-- CHECK: NULL passes (the condition is "not false"), so pair with NOT NULL
-- UNIQUE: NULLs are distinct by default; PG15+ can change that
ALTER TABLE customers ADD CONSTRAINT uq_email_per_country
    UNIQUE NULLS NOT DISTINCT (email, country);

-- Add constraints to big tables without a long lock
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id)
    REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_cust;   -- only needs SHARE UPDATE EXCLUSIVE

-- FK columns are NOT auto-indexed. Index them yourself
CREATE INDEX ON orders (customer_id);

Triggers

Triggers are row- or statement-level and fire BEFORE/AFTER/INSTEAD OF. A WHEN clause and UPDATE OF col keep them cheap.

CREATE FUNCTION trg_price_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO price_audit (product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
    RETURN NEW;
END $$;

CREATE TRIGGER products_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION trg_price_audit();

UPDATE products SET price = 130 WHERE sku = 'KB-01';
SELECT * FROM price_audit;
/*
id|product_id|old_price|new_price|changed_at                   |
--+----------+---------+---------+-----------------------------+
 1|         1|   120.00|   130.00|2026-11-15 12:27:28.297 +0600|
*/

Triggers are invisible side effects. Use them for audit and invariants, not for business workflows.

Foreign Data Wrappers

FDWs let you query remote sources as local tables. postgres_fdw pushes down filters, joins, and aggregates where it can. Check with EXPLAIN VERBOSE.

CREATE EXTENSION postgres_fdw;

CREATE SERVER crm_srv FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'crm.internal', dbname 'crm', port '5432');

CREATE USER MAPPING FOR CURRENT_USER SERVER crm_srv
    OPTIONS (user 'reader', password '***');

CREATE FOREIGN TABLE crm_leads (lead_id bigint, email text, source text)
    SERVER crm_srv OPTIONS (schema_name 'public', table_name 'leads');

-- or import many at once
-- IMPORT FOREIGN SCHEMA public LIMIT TO (leads) FROM SERVER crm_srv INTO shop;

SELECT l.source, count(*)
FROM crm_leads l JOIN customers c USING (email)
GROUP BY 1;

Common Table Expressions

Since PG12, non-recursive CTEs referenced once are inlined. Use MATERIALIZED or NOT MATERIALIZED to override that. Recursive CTEs handle trees and graphs, and CTEs can also contain data-modifying statements.

-- Recursive: full referral chain
WITH RECURSIVE chain AS (
    SELECT id, name, referred_by, 1 AS depth
    FROM customers WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.referred_by, ch.depth + 1
    FROM customers c JOIN chain ch ON c.referred_by = ch.id
)
SELECT * FROM chain;
/*
id|name |referred_by|depth|
--+-----+-----------+-----+
 1|Alice|           |    1|
 2|Bob  |          1|    2|
 3|Carol|          2|    3|
*/

-- Writable CTE: cancel stale orders and log it atomically
WITH cancelled AS (
    UPDATE orders SET status = 'cancelled'
    WHERE status = 'new' AND created_at < now() - interval '30 days'
    RETURNING id
)
INSERT INTO order_events (order_id, event)
SELECT id, 'auto_cancelled' FROM cancelled;

-- Force a single evaluation (optimization fence)
WITH big AS MATERIALIZED (SELECT * FROM v_order_totals WHERE total > 100)
SELECT count(*) FROM big;
/*
count|
-----+
    4|
*/

4. Takeaways

Need Reach for
Reusable query, always fresh View
Expensive aggregate, tolerates staleness Materialized view + REFRESH ... CONCURRENTLY
Logic in a query Function
Multi-step logic with transaction control Procedure
Fast scratch space, per session Temp table
Fast ETL landing, loss-tolerant Unlogged table
Derived values, always consistent Generated column
Time-series data and cheap retention Partitioning
Correctness enforced by the DB Constraints
Audit and invariants Triggers
Cross-database queries without ETL FDW
Readable steps, trees, writable pipelines CTEs

No comments yet. Be the first to comment.