pg_json_schema

High-Performance JSON Validation for Postgres

pg_json_schema is a PostgreSQL extension written in Rust using the pgrx framework. It provides lightning-fast JSON Schema validation directly within your database, utilizing an LRU cache to minimize overhead.

Features

• Native Operator: Use @@ to validate jsonb data against a schema.
• LRU Caching: Compiled schemas are cached in memory per connection for maximum performance.
• Detailed Diagnostics: Easily find why a JSON object failed validation with path-specific error messages.
• Configurable: Adjust cache limits on-the-fly via Postgres GUC settings.
• Memory Safe: Leverages Rust's safety guarantees to prevent common C-based vulnerabilities.

Installation

Prerequisites

• Rust toolchain
• pgrx (cargo install cargo-pgrx)

Build and Install

# Initialize pgrx (if not already done)
cargo pgrx init

# Compile and install the extension
cargo pgrx install

Usage

Basic Validation

Use the @@ operator for clean, readable queries:

-- Check if a JSON object matches a schema
SELECT '{"name": "Alice", "age": 30}'::jsonb @@ 
       '{"required": ["name", "age"]}'::jsonb;
-- Returns: true

Table Constraints

Ensure data integrity at the database level:

CREATE TABLE support_tickets (
    id serial PRIMARY KEY,
    details jsonb,
    -- This constraint ensures "email" is a string and "priority" is at least 1
    CONSTRAINT valid_ticket CHECK (
        details @@ '{
            "required": ["email"],
            "properties": {
                "email": {"type": "string", "format": "email"},
                "priority": {"type": "integer", "minimum": 1}
            }
        }'::jsonb
    )
);

-- This will SUCCEED
INSERT INTO support_tickets (details) 
VALUES ('{"email": "help@example.com", "priority": 5}');

-- This will FAIL (missing email)
INSERT INTO support_tickets (details) 
VALUES ('{"priority": 1}');

-- This will FAIL (priority too low)
INSERT INTO support_tickets (details) 
VALUES ('{"email": "user@test.com", "priority": 0}');

Debugging Failures

If validation fails, use explain_json_schema_errors to see exactly what went wrong:

SELECT * FROM explain_json_schema_errors(
    '{"type": "integer"}'::jsonb, 
    '"not-an-int"'::jsonb
);
-- Result: {"At : \"not-an-int\" is not of type \"integer\""}

Another Example

-- 1. Create a table for IoT Sensor logs
CREATE TABLE sensor_readings (
    id serial PRIMARY KEY,
    data jsonb,
    -- Simple constraint: Must have a 'temp' (number) and 'status' (string)
    CONSTRAINT valid_reading CHECK (
        data @@ '{
            "required": ["temp", "status"],
            "properties": {
                "temp": {"type": "number"},
                "status": {"type": "string", "enum": ["ok", "error"]}
            }
        }'::jsonb
    )
);

-- 2. This works
INSERT INTO sensor_readings (data) VALUES ('{"temp": 22.5, "status": "ok"}');

-- 3. This fails (status is not in the enum)
INSERT INTO sensor_readings (data) VALUES ('{"temp": 30, "status": "unknown"}');

Configuration

You can tune the performance of the extension without restarting the server.

Setting	Description	Default
json_validator.cache_size	Max compiled schemas stored in memory per connection.	100

-- Increase cache size for a high-volume session
SET json_validator.cache_size = 500;

Development & Testing

Running Tests

cargo pgrx test pg16
# OR
# cargo pgrx test pg18
# OR
# cargo pgrx test pg<VERSION>

Test Coverage

• Operator Logic: Validates @@ returns correct booleans for matching and non-matching JSON.
• Schema Diagnostics: Ensures error messages correctly identify the instance_path and reason for failure.
• GUC Integration: Confirms that changing json_validator.cache_size dynamically resizes the internal LRU cache without crashing.
• SPI Interaction: Verifies the extension behaves correctly when called from within SQL functions or triggers.

Technical Architecture

• jsonschema: For high-performance validation logic.
• lru: To manage memory and prevent OOM errors.
• once_cell: For thread-safe global state management within the Postgres backend.