Built-in dimensions¶

pgcraft ships with three dimension types, each backed by a different storage strategy. All three share the same plugin-driven pipeline and expose a unified PostgREST API view.

Choose the type that matches your data:

Simple – one table, direct CRUD. Best for reference data.
Append-Only (SCD Type 2) – full change history via an append-only log. Best for slowly changing dimensions.
EAV – sparse attributes stored as rows and pivoted back to columns. Best for highly dynamic or optional fields.

All dimension types support declarative PGCraftCheck, PGCraftIndex, and PGCraftFK items in schema_items. See Constraints and indices for a full walkthrough with generated SQL.

Simple dimension¶

A single backing table with a corresponding API view. Suitable for reference data and simple lookups that don’t need change history.

Example configuration:

users = PGCraftSimple(
    tablename="users",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("email", String),
    ],
)

PostgRESTView(source=users)

Usage – all operations go through the API view:

-- All operations go through the API view.
-- The INSTEAD OF triggers route them to the backing table.

INSERT INTO api.users (name, email)
VALUES ('Alice', 'alice@example.com');

INSERT INTO api.users (name, email)
VALUES ('Bob', 'bob@example.com');

UPDATE api.users
SET email = 'alice@newdomain.com'
WHERE id = 1;

DELETE FROM api.users
WHERE id = 2;

Schema¶

A single backing table (public.users) with a thin api.users view on top.

=# \d public.users
                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |
 email  | character varying |           |          |
Indexes:
    "pk__users__id" PRIMARY KEY, btree (id)

Sample queries¶

=# SELECT * FROM public.users;
 id | name  |       email
----+-------+-------------------
  1 | Alice | alice@example.com
  2 | Bob   | bob@example.com
(2 rows)

The API view exposes the same columns.

=# SELECT * FROM api.users;
 id | name  |       email
----+-------+-------------------
  1 | Alice | alice@example.com
  2 | Bob   | bob@example.com
(2 rows)

Append-only dimension (SCD Type 2)¶

Tracks full change history using an append-only attributes log. Every update creates a new row in the attributes table; the root table points to the latest version. A join view presents the current state. Ideal for slowly changing dimensions where audit trails matter.

Example configuration:

employees = PGCraftAppendOnly(
    tablename="employees",
    schemaname="private",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("department", String),
    ],
)

PostgRESTView(source=employees)

Usage – inserts and updates go through the API view; the triggers manage the internal tables:

-- All operations go through the API view.
-- The triggers manage the root and attributes tables for you.

INSERT INTO api.employees (name, department)
VALUES ('Alice', 'Engineering');

INSERT INTO api.employees (name, department)
VALUES ('Bob', 'Marketing');

-- Alice moves to Management.  This appends a new row to the
-- attributes table rather than updating in place.
UPDATE api.employees
SET department = 'Management'
WHERE id = 1;

Schema¶

An append-only attributes table (private.employees_attributes) stores every version of each row. A root table (private.employees_root) points to the current version. Two views join these into a flat shape: private.employees and api.employees.

=# \d private.employees_attributes
                                           Table "private.employees_attributes"
   Column   |           Type           | Collation | Nullable |                          Default
------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id         | integer                  |           | not null | nextval('private.employees_attributes_id_seq'::regclass)
 created_at | timestamp with time zone |           |          | '2026-03-15 23:17:26.081837+00'::timestamp with time zone
 name       | character varying        |           | not null |
 department | character varying        |           |          |
Indexes:
    "pk__employees_attributes__id" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "private.employees_root" CONSTRAINT "fk__employees_root__employees_attributes_id__employees_dee9c74f" FOREIGN KEY (employees_attributes_id) REFERENCES private.employees_attributes(id)

=# \d private.employees_root
                                                    Table "private.employees_root"
         Column          |           Type           | Collation | Nullable |                          Default
-------------------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id                      | integer                  |           | not null | nextval('private.employees_root_id_seq'::regclass)
 created_at              | timestamp with time zone |           |          | '2026-03-15 23:17:26.081837+00'::timestamp with time zone
 employees_attributes_id | integer                  |           |          |
Indexes:
    "pk__employees_root__id" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk__employees_root__employees_attributes_id__employees_dee9c74f" FOREIGN KEY (employees_attributes_id) REFERENCES private.employees_attributes(id)

Sample queries¶

Each change appends a new row. Row 3 records Alice’s department change.

=# SELECT * FROM private.employees_attributes ORDER BY id;
 id |          created_at           | name  | department
----+-------------------------------+-------+-------------
  1 | 2026-03-15 23:17:26.081837+00 | Alice | Engineering
  2 | 2026-03-15 23:17:26.081837+00 | Bob   | Marketing
  3 | 2026-03-15 23:17:26.081837+00 | Alice | Management
(3 rows)

Points to the latest attribute row via the foreign key.

=# SELECT * FROM private.employees_root;
 id |          created_at           | employees_attributes_id
----+-------------------------------+-------------------------
  1 | 2026-03-15 23:17:26.081837+00 |                       3
  2 | 2026-03-15 23:17:26.081837+00 |                       2
(2 rows)

Joins root with the pointed-to attribute row, showing the latest values.

=# SELECT * FROM api.employees;
 id |          created_at           |          updated_at           | name  | department
----+-------------------------------+-------------------------------+-------+------------
  1 | 2026-03-15 23:17:26.081837+00 | 2026-03-15 23:17:26.081837+00 | Alice | Management
  2 | 2026-03-15 23:17:26.081837+00 | 2026-03-15 23:17:26.081837+00 | Bob   | Marketing
(2 rows)

EAV dimension (Entity-Attribute-Value)¶

Stores attributes as rows rather than columns, using typed value columns (string_value, integer_value, etc.) with a check constraint enforcing exactly one non-null value per row. A pivot view reconstructs the familiar columnar layout. Ideal for sparse or highly dynamic attributes where most entities only have a subset of possible fields.

Example configuration:

products = PGCraftEAV(
    tablename="products",
    schemaname="private",
    metadata=metadata,
    schema_items=[
        Column("color", String),
        Column("weight", Float),
        Column("is_active", Boolean),
        Column("price", Integer),
    ],
)

PostgRESTView(source=products)

Usage – the API view looks like a normal table; the triggers decompose columns into EAV rows behind the scenes:

-- All operations go through the API view.
-- The triggers decompose each column into attribute rows
-- in the underlying EAV tables.

INSERT INTO api.products (color, weight, is_active, price)
VALUES ('red', 2.5, TRUE, 999);

INSERT INTO api.products (color, weight, is_active, price)
VALUES ('blue', 1.0, TRUE, 499);

-- The pivot view reconstructs columns, so SELECTs look normal:
SELECT * FROM api.products;

Schema¶

An entity table (private.products_entity) holds one row per logical entity. An attribute table (private.products_attribute) stores each field as a separate row with typed value columns. Two pivot views reconstruct the columnar layout: private.products and api.products.

=# \d private.products_attribute
                                              Table "private.products_attribute"
     Column     |           Type           | Collation | Nullable |                          Default
----------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id             | integer                  |           | not null | nextval('private.products_attribute_id_seq'::regclass)
 entity_id      | integer                  |           | not null |
 attribute_name | text                     |           | not null |
 string_value   | character varying        |           |          |
 float_value    | double precision         |           |          |
 boolean_value  | boolean                  |           |          |
 integer_value  | integer                  |           |          |
 created_at     | timestamp with time zone |           |          | '2026-03-15 23:17:26.286727+00'::timestamp with time zone
Indexes:
    "pk__products_attribute__id" PRIMARY KEY, btree (id)
Check constraints:
    "products_attribute_one_value_ck" CHECK (num_nonnulls(string_value, float_value, boolean_value, integer_value) = 1)
Foreign-key constraints:
    "fk__products_attribute__entity_id__products_entity" FOREIGN KEY (entity_id) REFERENCES private.products_entity(id) ON DELETE CASCADE

=# \d private.products_entity
                                             Table "private.products_entity"
   Column   |           Type           | Collation | Nullable |                          Default
------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id         | integer                  |           | not null | nextval('private.products_entity_id_seq'::regclass)
 created_at | timestamp with time zone |           |          | '2026-03-15 23:17:26.286727+00'::timestamp with time zone
Indexes:
    "pk__products_entity__id" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "private.products_attribute" CONSTRAINT "fk__products_attribute__entity_id__products_entity" FOREIGN KEY (entity_id) REFERENCES private.products_entity(id) ON DELETE CASCADE

Sample queries¶

One row per logical entity.

=# SELECT * FROM private.products_entity;
 id |          created_at
----+-------------------------------
  1 | 2026-03-15 23:17:26.286727+00
  2 | 2026-03-15 23:17:26.286727+00
(2 rows)

Each attribute is a separate row. The check constraint ensures exactly one value column is non-null per row.

=# SELECT * FROM private.products_attribute ORDER BY id;
 id | entity_id | attribute_name | string_value | float_value | boolean_value | integer_value |          created_at
----+-----------+----------------+--------------+-------------+---------------+---------------+-------------------------------
  1 |         1 | color          | red          |             |               |               | 2026-03-15 23:17:26.286727+00
  2 |         1 | weight         |              |         2.5 |               |               | 2026-03-15 23:17:26.286727+00
  3 |         1 | is_active      |              |             | t             |               | 2026-03-15 23:17:26.286727+00
  4 |         1 | price          |              |             |               |           999 | 2026-03-15 23:17:26.286727+00
  5 |         2 | color          | blue         |             |               |               | 2026-03-15 23:17:26.286727+00
  6 |         2 | weight         |              |           1 |               |               | 2026-03-15 23:17:26.286727+00
  7 |         2 | is_active      |              |             | t             |               | 2026-03-15 23:17:26.286727+00
  8 |         2 | price          |              |             |               |           499 | 2026-03-15 23:17:26.286727+00
(8 rows)

Pivots attribute rows back into columns. This is what the API exposes.

=# SELECT * FROM api.products;
 id |          created_at           | color | weight | is_active | price
----+-------------------------------+-------+--------+-----------+-------
  2 | 2026-03-15 23:17:26.286727+00 | blue  |      1 | t         |   499
  1 | 2026-03-15 23:17:26.286727+00 | red   |    2.5 | t         |   999
(2 rows)

pgcraft

Navigation

Related Topics

Versions

Built-in dimensions¶

Simple dimension¶

Schema¶

Sample queries¶

Append-only dimension (SCD Type 2)¶

Schema¶

Sample queries¶

EAV dimension (Entity-Attribute-Value)¶

Schema¶

Sample queries¶