pg_jsonschema: enforce JSON Schema on jsonb columns at 15x the speed of PL/pgSQL

TL;DR

pg_jsonschema is a Supabase-built PostgreSQL extension that validates json and jsonb values against a JSON Schema document, designed to be dropped inside a CHECK constraint. Because it is a thin Rust wrapper (built with pgrx) around the jsonschema crate, it is roughly 10–15x faster than the PL/pgSQL alternative, and the new compiled schema type cuts another ~45% off insert latency by skipping per-row schema recompilation. Apache-2.0, PostgreSQL 12+, latest version v0.3.4.

What's new

The core extension shipped on the Supabase platform back in August 2022, but the recent v0.3.4 release (Feb 11) is what makes it worth a fresh look. The headline addition is a first-class jsonschema SQL type: cast a schema to it once, and the validator is compiled and cached per callsite instead of being parsed on every row.

The full surface area is still tiny — four functions plus the new compiled variant:

• json_matches_schema(schema json, instance json) → boolean
• jsonb_matches_schema(schema json, instance jsonb) → boolean
• jsonschema_is_valid(schema json) → boolean
• jsonschema_validation_errors(schema json, instance json) → text[]
• jsonb_matches_compiled_schema(schema jsonschema, instance jsonb) → boolean

Why it matters

jsonb is the easiest way to get away with not designing a schema. That convenience is also its weakness: a column declared metadata jsonb not null happily accepts '42', '"hello"', '{}', and a webhook payload from three product versions ago — all in the same column. By the time a downstream consumer crashes on a missing key, the bad row is already on disk.

The standard escape hatches are both bad. Application-level validation drifts the moment a second service writes to the table. Normalizing the document into proper tables means architecting joins (5+ joins to reconstruct a typical webhook is common) just to recover the original payload. pg_jsonschema lets you keep the document model and push the contract back into the database, where it actually gets enforced on every write.

Technical facts

• Implementation: Rust 52.3% of the repo, built on pgrx, wrapping the jsonschema Rust crate plus serde_json.
• Postgres compatibility: 12 and higher.
• License: Apache-2.0. Free and open source.
• Distribution: PGXN, Docker images, and .deb artifacts on GitHub Releases for pg14/15/16 (amd64 and arm64).
• Adoption: 1.2k stars, 7 releases, 22 contributors.
• Compiled-schema benchmark (2024 MacBook Pro M4 Max, PostgreSQL 16.13, 20k inserts on the same schema):

That's a ~1.8x win, and it scales with schema complexity because the compilation cost is amortized across every row that hits the constraint.

Comparison vs prior art

The ~10–15x Rust-vs-PL/pgSQL gap is the headline number, and Supabase notes the gap grows as the schema gets more complex — the Rust validator scales better than the PL/pgSQL interpreter.

Use cases

The textbook example is webhook ingestion: you want to keep the raw provider payload as a single document so you don't have to refactor the database every time a vendor adds a field, but you still want to reject obvious garbage. Drop the schema into a CHECK constraint and Postgres does the gatekeeping:

create extension pg_jsonschema;

create table some_table (
  id serial primary key,
  metadata json not null,
  check (
    json_matches_schema(
      schema := '{
        "type": "object",
        "properties": {"foo": {"type": "string"}},
        "required": ["foo"],
        "additionalProperties": false
      }',
      instance := metadata
    )
  )
);

An insert that violates the schema gets the standard check-constraint failure (SQLSTATE 23514):

ERROR: new row for relation "some_table" violates check constraint "some_table_metadata_check"

For high-throughput tables, swap to the compiled form: cast the schema to jsonschema once and use jsonb_matches_compiled_schema in the constraint. Other natural fits: API request/response logs, user settings blobs, AI tool-call arguments, plugin manifests — anything where you want JSON's flexibility but need a contract.

Limitations & pricing

The extension is free under Apache-2.0; there's no separate license to chase. A few things to know before you commit:

• Draft coverage is whatever the upstream jsonschema Rust crate supports — check its docs if you depend on a specific draft (2019-09, 2020-12, etc.).
• CHECK constraints run on every write. If your schema is huge and you're not using the compiled variant, validation cost is paid per row.
• Schema migrations on the JSON shape mean dropping and re-adding the constraint, which scans the table.
• Building from source requires pgrx; most users should grab the prebuilt .deb for their Postgres major version instead.

Hosting options: enable in one click on Supabase (Dashboard → Database → Extensions → search pg_jsonschema), or self-host via PGXN, the Docker image, or the prebuilt .deb artifacts for pg14/15/16. Quick local trial: docker-compose up from the repo gives you a DB at postgresql://postgres:password@localhost:5407/app.

What's next

If you already lean on jsonb for webhooks, audit logs, or AI tool I/O, this is a small, low-risk change with an outsized payoff: stricter data, fewer downstream surprises, and validation that runs in the same transaction as the write. Start by switching one offending column to a CHECK + jsonb_matches_schema, then upgrade the hot tables to the compiled variant once you've tuned the schema.

Sources: supabase/pg_jsonschema, Supabase blog, Supabase docs.