Overview

SveltyCMS supports PostgreSQL as an alternative to MongoDB and MariaDB through the database adapter pattern. The PostgreSQL adapter uses Drizzle ORM with the postgres.js driver for optimal performance and type safety.

Architecture

The PostgreSQL adapter follows a modular pattern, leveraging Drizzle ORM for schema-agnostic operations and raw SQL for initial schema setup.

File Structure

src/databases/postgresql/
├── postgres-adapter.ts      # Concrete adapter (entry point)
├── adapter-core.ts          # Connection, pool, date normalization, registry
├── fts-adapter.ts           # Full-text search (ILIKE + tsvector paths)
├── schema.ts                # Drizzle ORM schema definitions
└── transaction-module.ts    # Drizzle transaction wrapper

Schema Initialization (Boot Bootstrap)

Unlike MongoDB which is schema-less, PostgreSQL requires table definitions. SveltyCMS renders the system schema at boot directly from the single declarative source of truth (src/databases/system-schema-spec.ts) via src/databases/core/system-schema-bootstrap.ts — there are no per-engine migration files to keep in sync, so the three relational engines can never drift.

sequenceDiagram
    participant S as Setup Wizard / System Init
    participant A as PostgreSQLAdapter
    participant B as Schema Bootstrap (core)
    participant D as PostgreSQL DB

    S->>A: connect()
    A->>B: bootstrapSystemSchema("postgresql", sql)
    B->>D: CREATE TABLE IF NOT EXISTS ...
    D-->>B: Tables Created/Verified
    B-->>A: { success: true }
    A-->>S: Ready
    Note over B,D: skipped when the stored fingerprint matches

An unchanged release skips this pass entirely: bootstrapSystemSchema compares a sha-256 of SYSTEM_SCHEMA (per dialect) stored in svelty_schema_state and returns { success: true, skipped: true } without touching the database — measured 2026-09-22: 118 statements / 124 ms per boot → ~2 ms. Any spec change re-runs the pass, and a pass with a failed statement does not store the marker, so a half-applied schema is retried instead of trusted.

Connection Pool Configuration (2026-09 Hardening)

SveltyCMS configures the postgres.js driver with production-tuned connection pool settings. All timing values are configurable via environment variables so operators can tune without code changes:

Connection Configuration

PostgreSQL connections are configured in config/private.ts or during the setup wizard:

// Configuration Object
{
    type: 'postgresql',
    host: 'localhost',
    port: '5432',
    name: 'sveltycms',
    user: 'postgres',
    password: 'your_password'
}

Drizzle Schema

The PostgreSQL schema mirrors the CMS data model but is optimized for relational performance.

Key Implementation Details

Current Implementation Status

✅ All Modules Implemented

PostgreSQL-Specific Optimizations

• JSONB (not JSON): All 20 metadata columns use binary JSONB with efficient containment operators (@>, ?, ?|)

• GIN Indexes: the 5 spec GIN indexes on high-query JSONB columns (content_nodes.data, content_nodes.metadata, media_items.metadata, roles.permissions, auth_users.roleIds), plus a jsonb_path_ops <table>_data_gin on every dynamic collection table (createModel; opt out with schema.jsonIndex === false)

• Trigram Search: High-performance ilike searching for media filenames using pg_trgm GIN indexes (media_items_filename_trgm_idx).

• gen_random_uuid(): Native UUID generation via pgcrypto extension

• Tenants Table: Full multi-tenancy with quota, usage, and settings JSONB columns

• Website Tokens: website_tokens via shared relational-system.ts — SHA-256 hash at rest, unique index on token, compound { tenantId, name }, parallel list+count. See Credential Storage.

• Raw findById: Tagged-template prepared SELECT (stable SQL text → postgres.js statement cache) — FIND ONE ~1.3–1.5k RPS.

• Raw INSERT fast path (no-read-back): One-statement insert via a flat unsafe(text, values, { prepare: true }) template (postgres.js 3.x removed sql.join — the earlier implementation silently fell back to Drizzle on every insert, ~225 RPS). Quotes identifiers (unquoted camelCase folds to lowercase) and binds dates/objects as strings (describe-phase Bind quirk). The returned row is synthesized from the prepared values + Drizzle column defaults (status 'draft', isDeleted false, timestamps, NULLs) instead of RETURNING * — exact parity for CMS tables (no triggers/generated columns; the Drizzle def mirrors the DDL) and it removes the row materialization + jsonb parse from the write round trip. Measured: 217 → ~290–310 RPS on a loaded host (+21% same-run); raw SQL alone ~413 (92% of the 450 RPS 1c ceiling).

• Raw multi-VALUES insertMany: one prepared statement per chunk (union column set; undefined values bind as literal DEFAULT to keep the prepared bind count exact) — BULK INSERT (100) 87 → 233 RPS (+168%) vs the old Drizzle .returning() path.

• Targeted column UPDATE: rawUpdateReturning builds SET only for prepared keys. Shared prepareUpdateValues() never writes createdAt and omits an empty data={} blob on partial updates.

• Partial updates merge the JSONB blob: the data column’s SET expression becomes COALESCE("data",'{}'::jsonb) || $n::jsonb (shallow, keeps explicit nulls) whenever the write is a patch rather than a complete document — so a PATCH {views: 1} no longer replaces title/body/… in the same row. The replace form is kept for replaceData: true / skipReturning: true callers, and the same merge covers crud.updateMany, batch.bulkUpdate and query-builder updateMany. See Write Path.

• Indexed dynamic-field filters (containment + GIN): an equality filter on a field that lives in the data blob (i.e. not materialized into a column) is emitted as data @> '{"field": value}'::jsonb — the form the jsonb_path_ops GIN index serves — instead of data->>'field' = $1, which can only be a sequential scan. createModel provisions <table>_data_gin for every dynamic collection table; schema.jsonIndex === false opts out for write-only collections. Measured at 100k rows: 7.9–10.9 ms Seq Scan → 0.1–1.7 ms Bitmap Index Scan (index build 347 ms once; write cost within noise). JSON type fidelity is preserved by probing the raw value, so {views: 101} matches a stored number and not a stored "101" (SQLite and MongoDB already agreed; MariaDB’s text-based JSON comparison remains looser). Dotted paths probe a nested object (a.b → {"a":{"b":…}}), matching the data#>>'{a,b}' extraction form.

• Prepared JSON/array binds: bindPgParam() serializes objects and arrays as JSON text; JSON/jsonb columns use $n::jsonb so postgres.js does not emit PG array literals {…} (those force a describe-phase recompile).

• crud.aggregate() (native translation): MongoDB-style pipelines compile to one statement via core/aggregation-translator.ts — $match, $group (scalar _id plus $sum/$avg/$min/$max/$count), $sort, $skip, $limit, $count and include-only $project. $min/$max on a non-materialized field are refused with error.code === "NOT_SUPPORTED" (the ->> extraction is TEXT — MIN over {10, 2} returns "10" — and there is no min(jsonb)); declare the field indexed/materialize: true to aggregate it. SUM/AVG cast the extraction to numeric (getJsonNumericField), and any other unsupported stage fails closed the same way.

• Numeric range filters on blob fields: $gt/$gte/$lt/$lte compile to jsonb_typeof(data->'x') = 'number' AND data->'x' >= '4'::jsonb (getJsonCompare) — comparing the extracted TEXT was lexicographic (views >= 4 missed stored 16/32), and the jsonb_typeof guard is required because jsonb orders String below Number, so an unguarded < would admit stored strings.

Development Guide

To extend the PostgreSQL adapter:

1. Schema Updates

Modify src/databases/postgresql/schema.ts. For schema shape changes that affect fresh installs, update the shared declarative spec src/databases/system-schema-spec.ts once — the boot bootstrap (src/databases/core/system-schema-bootstrap.ts) renders it for all three SQL engines. The spec↔schema.ts drift guard lives in tests/unit/databases/schema-spec-parity.test.ts.

2. Implementing Methods

Implement dialect hooks in src/databases/postgresql/adapter-core.ts; the shared CRUD implementation lives in src/databases/core/sql-adapter-core.ts and the concrete entry point is postgres-adapter.ts. Use the wrap() helper for consistent error handling and logging.

public readonly crud = {
  findOne: async (collection: string, query: Record<string, unknown>) => {
    return this.wrap(async () => {
      const table = this.getTable(collection);
      const where = this.mapQuery(table, query);
      const result = await this.db!.select().from(table).where(where).limit(1);
      return result[0] || null;
    }, 'CRUD_FIND_ONE_FAILED');
  }
};

Performance Benchmarks

The PostgreSQL adapter is optimized for relational efficiency and JSONB search performance. For detailed performance metrics and comparisons, please refer to the Performance Benchmarks document.

Shared list / count contract (SqlAdapterCore)

PostgreSQL inherits findPage and count({ mode }) from SqlAdapterCore:

Measured (2026-08-04, Docker PG, no Redis): FIND PAGE 1.006 ms vs legacy LIST+COUNT 4.845 ms (~4.8×); COUNT CACHED 0.024 ms vs exact 2.160 ms (~90×). See Performance Architecture.

Key PostgreSQL highlights:

• JSONB with GIN Indexing: 0.063 ms NATIVE UPSERT, 0.083 ms FIND MANY, 1.201 ms FIND ONE.
• Partial Indexes: Optimized performance for active sessions and tokens.
• Connection Health: Robust pooling via postgres.js with integrated health checks.

Key Optimizations

• JSONB with GIN Indexing: All 20 metadata columns use PostgreSQL’s binary jsonb type with 5 GIN indexes for sub-5ms search on complex containment queries.
• Partial Indexes: Only index active sessions and unconsumed tokens — reduces index size and speeds up common auth queries.
• Connection Health: Optimized pooling via postgres.js with health checks, reconnection, keepalive, and query pipelining for up to 3× throughput on parallel workloads.
• TTL-Equivalent Cleanup: cleanupExpiredData() method purges expired sessions and consumed tokens (equivalent to MongoDB’s TTL indexes).
• Tamper-Evident Logs: Relational structure with transactional integrity for cryptographic audit log chaining.

• Drizzle ORM Documentation
• postgres.js GitHub
• MariaDB Implementation

🚀 Production Scaling with PgBouncer (Enterprise)

For high-concurrency Postgres deployments or managed databases with connection limits (RDS, Azure, Cloud SQL), SveltyCMS recommends deploying PgBouncer as a connection proxy layer. This follows the same pattern used at scale by Instagram, OpenAI, and other Postgres-heavy platforms.

Why PgBouncer

• Connection Multiplexing: PgBouncer sits between your app instances and Postgres, pooling hundreds of lightweight client connections onto a small pool of real Postgres backend connections. This prevents “too many clients” errors on managed DBs (often limited to 50-200 connections).
• Lower Latency: App connections to PgBouncer are cheap (Unix socket or localhost). Real Postgres connection setup (~50ms) is avoided on every request — PgBouncer reuses existing backend connections.
• Reduced Postgres CPU: Fewer Postgres backend processes mean fewer context switches and less memory pressure in Postgres’s multi-process model.
• Horizontal Scaling: Adding more app instances doesn’t require increasing Postgres max_connections — they all share PgBouncer’s pool.

Deployment Pattern

Deploy PgBouncer as a sidecar on each app instance (localhost:6432) or as a central proxy behind a lightweight load balancer. The sidecar pattern minimizes network latency.

Recommended Configuration

Create /etc/pgbouncer/pgbouncer.ini:

[databases]
sveltycms = host=127.0.0.1 port=5432 dbname=sveltycms

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; Transaction pooling — best efficiency for web workloads.
;; Connections are returned to the pool immediately after each transaction.
pool_mode = transaction

;; Conservative defaults. Tune based on CPU cores and workload.
default_pool_size = 25
max_client_conn = 500

;; Cleanup between client uses (prevents session state leakage)
server_reset_query = DISCARD ALL

;; Logging for observability
log_connections = 1
log_disconnections = 1
stats_period = 60

SveltyCMS Integration

When using PgBouncer in transaction mode, set the following in your environment or config/private.ts:

# Point the app to PgBouncer instead of direct Postgres
DB_HOST=127.0.0.1
DB_PORT=6432

# Required: Disable server-side prepared statements when behind PgBouncer
# (PgBouncer tx mode cannot guarantee the same backend connection between
# prepare and execute, causing "prepared statement does not exist" errors)
DATABASE_PREPARE=false

The adapter automatically detects DATABASE_PREPARE=false and sets prepare: false in the postgres.js driver. This trades the ~40% parse/plan win from server-side prepared statements for seamless PgBouncer compatibility. The query planner cache in Postgres still provides significant optimization.

Monitoring

Check PgBouncer health via its admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
> SHOW POOLS;
> SHOW STATS;
> SHOW CLIENTS;

SveltyCMS’s built-in ConnectionPoolOptions and getConnectionPoolStats() work transparently whether connecting directly or through PgBouncer. For production, monitor:

• cl_waiting (should stay near 0 — increase default_pool_size if growing)
• avg_wait_time (should be < 1ms)
• Postgres active connections (should be stable, not climbing)

Related

• Database Architecture
• Performance Benchmarks
• Architecture Overview