Relational columns or a JSON document?

The situation

The product is adding integrations: each customer organization can connect Slack, Jira or a webhook, each with its own settings. An AI assistant proposed the table.

CREATE TABLE integrations (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc   jsonb NOT NULL
);

-- example row
-- {"tenant_id": 7, "provider": "slack", "status": "active", "enabled": true,
--  "channel": "#alerts", "mappings": {...60 field mappings...}}

-- the integrations page
SELECT * FROM integrations WHERE doc ->> 'tenant_id' = '7';

CREATE TABLE integrations (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  integer NOT NULL REFERENCES tenants (id),
  provider   text NOT NULL,
  status     text NOT NULL CHECK (status IN ('active', 'paused', 'failed')),
  enabled    boolean NOT NULL DEFAULT true,
  settings   jsonb NOT NULL DEFAULT '{}'    -- provider-specific, read as a whole
);
CREATE INDEX ON integrations (tenant_id);

Guarding the document that remains

When a few keys inside a document must be right, a CHECK constraint can enforce them. Be careful with missing keys:

CREATE TEMP TABLE integ (id int PRIMARY KEY, config jsonb NOT NULL,
  CHECK (jsonb_typeof(config -> 'enabled') = 'boolean'));
INSERT INTO integ VALUES (1, '{"enabled": true}');     -- accepted
INSERT INTO integ VALUES (2, '{"enabled": "true"}');   -- rejected: a string
INSERT INTO integ VALUES (3, '{"enable": true}');      -- accepted!

Row 3 has the key misspelled. config -> 'enabled' is then NULL, so the check is NULL rather than false, and a CHECK constraint lets NULL through. Require the key as well:

CHECK (config ? 'enabled' AND jsonb_typeof(config -> 'enabled') = 'boolean')

With that, both bad rows are rejected.

How to decide

Prefer a column for anything that identifies or owns the row, anything you filter, join or sort on regularly, anything with a fixed type, and anything updated on its own.

Prefer a JSON document for attributes whose set of keys varies from row to row, that you mostly read and write as a whole, and that you rarely filter on.

Promote keys that become important. When a key inside a document starts appearing in WHERE clauses everywhere, move it into a column.

Check yourself

1. Why can’t the database protect tenant_id when it lives inside the document?

   Show answer

   Foreign keys and NOT NULL apply to columns, not to keys inside a JSON value. The document can omit the key, misspell it or give it the wrong type, and nothing rejects it.

   I could explain this without looking

2. Why did updating one key inside the document write 20 times more WAL than updating a column?

   Show answer

   A changed JSON value is a whole new value, so the entire document is written again. When only a small column changes, PostgreSQL keeps the stored document as it was.

   I could explain this without looking

3. Why did the first CHECK constraint accept {"enable": true}?

   Show answer

   The enabled key was missing, so config -> 'enabled' was NULL and the check evaluated to NULL. CHECK constraints only reject rows where the expression is false.

   I could explain this without looking