pg_typed_demo

Benchmarks PostgreSQL unpartitioned vs pg_partman-partitioned time-series tables using typed SQL (pgTyped), a NestJS API, and a SvelteKit dashboard.

Dashboard

High-Level Design

graph TD
    classDef frontend fill:#4f46e5,stroke:#312e81,color:#fff
    classDef backend fill:#0f766e,stroke:#134e4a,color:#fff
    classDef db fill:#b45309,stroke:#78350f,color:#fff
    classDef trace fill:#6b21a8,stroke:#3b0764,color:#fff

    FE["SvelteKit Dashboard\n(ECharts)"]:::frontend
    API["NestJS API\n(:4000)"]:::backend
    PG["PostgreSQL 16\n(:5433)"]:::db
    JG["Jaeger UI\n(:16686)"]:::trace

    FE -->|"REST /api/*"| API
    API -->|"pgTyped queries"| PG
    API -->|"OTLP HTTP :4318"| JG

Technical Design

graph TD
    classDef setup fill:#475569,stroke:#1e293b,color:#fff
    classDef controller fill:#0369a1,stroke:#0c4a6e,color:#fff
    classDef service fill:#0f766e,stroke:#134e4a,color:#fff
    classDef repo fill:#065f46,stroke:#022c22,color:#fff
    classDef sql fill:#92400e,stroke:#451a03,color:#fff
    classDef frontend fill:#4f46e5,stroke:#312e81,color:#fff
    classDef db fill:#b45309,stroke:#78350f,color:#fff

    subgraph setup["apps/setup"]
        DC["docker-compose\nPostgres + Jaeger"]:::setup
        INIT["init/*.sql\nSchema bootstrap"]:::setup
        SEED["generate-seed-csv.sh\n→ seed-from-csv.sh\n1M rows × 2 tables"]:::setup
    end

    subgraph backend["apps/backend-pg-typed"]
        CTRL["Controllers\n/benchmark  /scenarios  /granular  /health"]:::controller
        SVC["Services\norchestrate runs × N"]:::service
        REPO["Repositories\npgTyped execution + timing"]:::repo
        SQL["src/queries/*.sql\nhand-authored CTE queries"]:::sql
        TYPES["src/types/*.types.ts\npgTyped-generated"]:::sql
    end

    subgraph frontend["apps/frontend"]
        PAGES["Routes\n/  /granular  /granular/[id]"]:::frontend
        CHARTS["ECharts Components\nOverview · Delta · WindowComparison\nDrilldownPanel · AnalysisCallout"]:::frontend
        APICLIENT["src/lib/api.ts"]:::frontend
    end

    DC --> INIT --> SEED
    SEED -->|"COPY INTO"| PG[("time_series_data\ntime_series_data_partman")]:::db
    SQL --> TYPES
    REPO --> SQL
    SVC --> REPO
    CTRL --> SVC
    APICLIENT --> CTRL
    PAGES --> APICLIENT
    PAGES --> CHARTS

Methodology

Each benchmark endpoint runs the same logical query against both tables — unpartitioned and partitioned — and collects:

Results are a single JSON payload with timing per table, plan output, and computed delta — consumed directly by the dashboard.

Three benchmark axes:

Benchmark Setup

Data

Both tables load from the same CSV via COPY, ensuring identical datasets.

Tables

Running from Scratch

Prerequisites

• Docker
• Node.js ≥ 20
• pnpm ≥ 9

1 — Environment

Create .env.local at the workspace root:

DATABASE_URL=postgresql://demo:demo@localhost:5433/demo
API_PORT=4000

2 — Install

pnpm install

3 — Start infrastructure

pnpm db:dev      # starts PostgreSQL, starts Jaeger, ensures seed CSV exists, then reseeds both tables
pnpm stop        # gracefully stops all compose services for this workspace
pnpm db:up       # PostgreSQL 16 on :5433
pnpm jaeger:up   # Jaeger all-in-one on :16686 (OTLP HTTP :4318)

4 — Seed database (~1–2 min first run)

pnpm db:seed:file   # generates apps/setup/db/data/time_series_seed_1m.csv (skips if exists)
pnpm db:provision   # wipes volume, re-runs schema init, bulk-loads 1M rows into both tables

Note: db:provision is destructive — it drops and recreates the Docker volume.
To reseed without wiping: pnpm db:seed

5 — Start backend

pnpm backend:start   # compiles TypeScript → runs dist/main.js on :4000

Verify:

curl http://localhost:4000/api/health

6 — Start frontend

pnpm frontend:dev   # SvelteKit dev server on :5173

7 — View traces in Jaeger

Open http://localhost:16686, select service pg-benchmark-api, and search.
Traces are emitted on every /api/benchmark, /api/scenarios, and /api/granular request via OTLP HTTP to Jaeger on :4318.

Service URLs

Other Commands

pnpm backend:type:gen       # regenerate pgTyped types after editing .sql files
pnpm backend:dev            # TypeScript watch mode (no auto-restart)
pnpm db:down                # stop containers (keeps volume)
pnpm db:reset               # wipe volume + restart postgres
pnpm jaeger:logs            # tail Jaeger container logs