PostgreSQL Operations Guide

Version: 1.1.0 Last Updated: 2026-05-30 Status: Active

Audience: operators and platform engineers who run the StellaOps control-plane database.

This guide covers PostgreSQL operations for StellaOps: setup, performance tuning, monitoring, backup/restore, and scaling. For the operational procedures behind the cross-cutting database patterns (row-level security, bitemporal unknowns, partitioning, generated columns), see the companion PostgreSQL Patterns Operational Runbook.

1. Overview

StellaOps uses PostgreSQL as the sole control-plane database with per-module schema isolation. The audited, digest-pinned image shipped in the reference compose stack is postgres:18.1 (POSTGRES_IMAGE in devops/compose/env/stellaops.env.example); override it via your secrets store for production. The default database name is stellaops_platform(POSTGRES_DB), not stellaops — the examples below use stellaops_platform. MongoDB is no longer part of the control plane; all persistence is PostgreSQL.

NOTE: Schemas live inside a single logical database. Each module declares its own schema via a DefaultSchemaName constant and auto-migrates on startup through AddStartupMigrations(schemaName, moduleName, migrationsAssembly) (see src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/MigrationServiceExtensions.cs). Many modules additionally create a <schema>_app login-role schema (for example vex_app, notify_app, policy_app) used for least-privilege runtime access.

1.1 Schema Topology

The control plane owns 50+ schemas inside one database. The diagram below shows a representative subset; see § 1.2 for the authoritative ownership map.

┌─────────────────────────────────────────────────────────────────┐
│                    PostgreSQL Cluster                             │
│  ┌─────────────────────────────────────────────────────────────┐│
│  │              stellaops_platform (database)                   ││
│  │  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐            ││
│  │  │authority│ │  vuln   │ │   vex   │ │scheduler│            ││
│  │  └─────────┘ └─────────┘ └─────────┘ └─────────┘            ││
│  │  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐            ││
│  │  │ notify  │ │ policy  │ │ packs   │ │ scanner │            ││
│  │  └─────────┘ └─────────┘ └─────────┘ └─────────┘            ││
│  │  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐            ││
│  │  │ signals │ │ signer  │ │proofchain│ │ findings│           ││
│  │  └─────────┘ └─────────┘ └─────────┘ └─────────┘            ││
│  │  ┌─────────┐  …and ~40 more (graph, timeline, sbom,         ││
│  │  │  audit  │   evidence, export_center, issuer, …)          ││
│  │  └─────────┘                                                ││
│  └─────────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────────┘

1.2 Module Schema Ownership

Schema names are taken from each module’s DefaultSchemaName constant and from the CREATE SCHEMA statements in its embedded migrations. The table below is a representative cross-section, not the full set.

2. Performance Configuration

2.1 Enable pg_stat_statements

The pg_stat_statements extension is essential for query performance analysis. Enable it in your PostgreSQL configuration:

postgresql.conf:

# Load the extension at startup
shared_preload_libraries = 'pg_stat_statements'

# Configuration
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on

Enable in database:

-- Create the extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Verify installation
SELECT * FROM pg_stat_statements LIMIT 1;

-- Reset statistics (useful after configuration changes)
SELECT pg_stat_statements_reset();

2.2 Recommended PostgreSQL Settings

Memory Configuration (adjust based on available RAM):

# For a server with 16GB RAM dedicated to PostgreSQL:
shared_buffers = 4GB                  # 25% of RAM
effective_cache_size = 12GB           # 75% of RAM
maintenance_work_mem = 1GB            # For VACUUM, CREATE INDEX
work_mem = 64MB                       # Per-operation sort memory

# Connection management
max_connections = 200                 # Adjust based on pooling

Write-Ahead Log (WAL):

wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

Query Planner:

random_page_cost = 1.1                # For SSDs (default 4.0 is for HDDs)
effective_io_concurrency = 200        # For SSDs
default_statistics_target = 100       # Increase for complex queries

Parallel Query:

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

2.3 Connection Pooling (PgBouncer)

Recommended PgBouncer configuration:

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60
query_timeout = 30

Session configuration (set on connection open):

-- Several schemas use Row-Level Security keyed on the app.tenant_id session
-- GUC (e.g. authority.* RLS reads current_setting('app.tenant_id'); the
-- Authority tenant_id is TEXT, not a UUID). Set it per connection/transaction.
SET app.tenant_id = '<tenant>';
SET timezone = 'UTC';
SET statement_timeout = '30s';

CAUTION: In transaction pool mode PgBouncer reuses server connections across clients, so a SET app.tenant_id issued once per connection will leak across tenants. Set RLS GUCs with SET LOCAL inside the transaction (or use set_config('app.tenant_id', $1, true)) when pooling in transaction mode. See the PostgreSQL Patterns Operational Runbook for RLS validation and admin-bypass procedures.

3. Query Performance Analysis

3.1 Identifying Slow Queries

Top queries by total time:

SELECT
    substring(query, 1, 100) as query_preview,
    calls,
    round(total_exec_time::numeric, 2) as total_ms,
    round(mean_exec_time::numeric, 2) as mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) over())::numeric, 2) as percent_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Queries with high mean execution time:

SELECT
    substring(query, 1, 100) as query_preview,
    calls,
    round(mean_exec_time::numeric, 2) as mean_ms,
    round(stddev_exec_time::numeric, 2) as stddev_ms,
    rows
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 20;

Queries with high buffer usage (I/O intensive):

SELECT
    substring(query, 1, 100) as query_preview,
    calls,
    shared_blks_hit + shared_blks_read as total_blks,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) as hit_ratio
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 20;

3.2 Using EXPLAIN ANALYZE

Basic usage:

-- vuln.advisories has no `state` column; active vs withdrawn is expressed via
-- withdrawn_at IS NULL. Severity is a TEXT CHECK column
-- ('critical','high','medium','low','unknown').
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM vuln.advisories
WHERE withdrawn_at IS NULL AND severity = 'critical'
ORDER BY modified_at DESC
LIMIT 100;

Understanding output - key indicators:

• Seq Scan on large tables = missing index
• Hash Join vs Nested Loop - consider data sizes
• Rows estimate vs actual - statistics accuracy
• Buffers: shared hit/read - cache effectiveness

Example analysis:

-- Bad: Sequential scan on large table
Seq Scan on advisories  (cost=0.00..50000.00 rows=1000 width=100)
  Filter: ((withdrawn_at IS NULL) AND (severity = 'critical'))
  Rows Removed by Filter: 99000

-- Good: Index scan
Index Scan using idx_advisories_severity on advisories
  Index Cond: (severity = 'critical')
  Filter: (withdrawn_at IS NULL)

3.3 Index Analysis

Find unused indexes:

SELECT
    schemaname || '.' || relname as table,
    indexrelname as index,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan as scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;

Find missing indexes (tables with high sequential scans):

SELECT
    schemaname || '.' || relname as table,
    seq_scan,
    seq_tup_read,
    idx_scan,
    round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 2) as idx_usage_pct
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 20;

Duplicate indexes:

SELECT
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
    array_agg(idx) as indexes,
    indrelid::regclass as table,
    indkey as columns
FROM (
    SELECT indexrelid::regclass as idx, indrelid, indkey
    FROM pg_index
) sub
GROUP BY indrelid, indkey
HAVING count(*) > 1;

4. Index Guidelines for StellaOps

4.1 Standard Index Patterns

All tenant-scoped tables should have composite indexes starting with tenant_id:

-- Standard tenant + primary lookup pattern
CREATE INDEX idx_<table>_tenant_<field> ON <schema>.<table>(tenant_id, <field>);

-- Time-based queries
CREATE INDEX idx_<table>_tenant_time ON <schema>.<table>(tenant_id, created_at DESC);

-- State/status filtering
CREATE INDEX idx_<table>_tenant_state ON <schema>.<table>(tenant_id, state)
    WHERE state IN ('active', 'pending');

4.2 Module-Specific Indexes

Authority schema:

CREATE INDEX idx_users_tenant ON authority.users(tenant_id);
CREATE INDEX idx_users_email ON authority.users(email) WHERE email IS NOT NULL;
CREATE INDEX idx_tokens_expires ON authority.tokens(expires_at) WHERE revoked_at IS NULL;

Vuln schema:

CREATE INDEX idx_advisories_primary_vuln ON vuln.advisories(primary_vuln_id);
CREATE INDEX idx_advisories_modified ON vuln.advisories(modified_at DESC);
CREATE INDEX idx_advisory_aliases_value ON vuln.advisory_aliases(alias_value);
-- vuln.advisory_affected stores the PURL in a column named `purl`
-- (with generated purl_type / purl_name columns), not `package_purl`.
CREATE INDEX idx_advisory_affected_purl ON vuln.advisory_affected(purl)
    WHERE purl IS NOT NULL;

Scheduler schema:

CREATE INDEX idx_runs_tenant_state ON scheduler.runs(tenant_id, state);
CREATE INDEX idx_runs_state_created ON scheduler.runs(state, created_at)
    WHERE state IN ('pending', 'queued', 'running');
CREATE INDEX idx_graph_jobs_tenant_status ON scheduler.graph_jobs(tenant_id, status);

4.3 JSONB Indexes

-- GIN index for containment queries (@>, ?, ?&, ?|)
CREATE INDEX idx_<table>_<column>_gin ON <schema>.<table> USING GIN (<column>);

-- Expression index for specific JSON paths
CREATE INDEX idx_<table>_<column>_path ON <schema>.<table> ((<column>->>'specific_key'));

5. Monitoring Setup

5.1 Key Metrics to Monitor

Connection metrics:

-- Current connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Connections by database/user
SELECT datname, usename, count(*)
FROM pg_stat_activity
GROUP BY datname, usename;

Cache effectiveness:

-- Database-level cache hit ratio (should be >99%)
SELECT
    datname,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) as cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'stellaops_platform';

Table bloat and maintenance:

-- Tables needing VACUUM
SELECT
    schemaname || '.' || relname as table,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

5.2 Prometheus Metrics

Use postgres_exporter for Prometheus integration. Key metrics:

# Alert rules for PostgreSQL
groups:
  - name: postgresql
    rules:
      - alert: PostgreSQLHighConnections
        expr: pg_stat_activity_count > (pg_settings_max_connections * 0.8)
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL connections at {{ $value | humanizePercentage }} of max"

      - alert: PostgreSQLLowCacheHitRatio
        expr: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.95
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL cache hit ratio below 95%"

      - alert: PostgreSQLDeadlocks
        expr: rate(pg_stat_database_deadlocks[5m]) > 0
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL deadlocks detected"

      - alert: PostgreSQLSlowQueries
        expr: pg_stat_activity_max_tx_duration > 300
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Long-running transaction detected (>5min)"

5.3 Grafana Dashboard

Import the PostgreSQL dashboard (ID: 9628) or create custom panels for:

1. Connection Pool - Active/idle/waiting connections
2. Query Performance - QPS, latency percentiles
3. Cache Hit Ratio - Database and table level
4. Disk I/O - Read/write IOPS and throughput
5. Replication Lag - For HA setups
6. Lock Waits - Blocked queries count

6. Performance Baselines

6.1 Expected Performance Targets

6.2 Baseline Queries

Run these periodically to establish baselines:

-- Authority: User lookup
-- authority.users is keyed on (tenant_id, username); there is no
-- `normalized_username` column. tenant_id is TEXT, not a UUID.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM authority.users
WHERE tenant_id = '<tenant>' AND username = 'testuser';

-- Vuln: Advisory search
-- vuln.advisories has a stored search_vector TSVECTOR column; prefer it over
-- recomputing to_tsvector at query time. There is no `state` column —
-- non-withdrawn advisories are withdrawn_at IS NULL.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM vuln.advisories
WHERE withdrawn_at IS NULL
  AND search_vector @@ plainto_tsquery('english', 'critical vulnerability')
ORDER BY modified_at DESC
LIMIT 50;

-- Scheduler: Pending jobs
-- scheduler.runs.tenant_id is TEXT; state is the scheduler.run_state enum.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM scheduler.runs
WHERE tenant_id = '<tenant>' AND state = 'pending'
ORDER BY created_at
LIMIT 100;

6.3 Load Testing

Use pgbench for baseline load testing:

# Initialize test data
pgbench -i -s 50 stellaops_platform

# Run benchmark (60 seconds, 10 clients)
pgbench -c 10 -j 4 -T 60 stellaops_platform

# Custom script benchmark
pgbench -c 10 -j 4 -T 60 -f custom_workload.sql stellaops_platform

7. Backup and Restore

7.1 Backup Strategy

Daily full backup with pg_dump:

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/var/backups/postgresql

pg_dump -Fc -Z 9 \
  --host="${PGHOST}" \
  --port="${PGPORT}" \
  --username="${PGUSER}" \
  --dbname=stellaops_platform \
  --file="${BACKUP_DIR}/stellaops_${DATE}.dump"

# Retain last 7 days
find ${BACKUP_DIR} -name "*.dump" -mtime +7 -delete

Continuous WAL archiving:

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

7.2 Point-in-Time Recovery

# Stop PostgreSQL
systemctl stop postgresql

# Restore base backup
pg_restore -C -d postgres /var/backups/postgresql/stellaops_backup.dump

# Create recovery.conf (PostgreSQL 12+: recovery.signal + postgresql.conf)
cat > ${PGDATA}/postgresql.auto.conf << EOF
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-12-10 14:30:00 UTC'
EOF

touch ${PGDATA}/recovery.signal

# Start PostgreSQL
systemctl start postgresql

7.3 Backup Verification

# Test restore to a different database
pg_restore -C -d postgres --dbname=stellaops_test /var/backups/postgresql/stellaops_backup.dump

# Verify data integrity
psql -d stellaops_test -c "SELECT count(*) FROM authority.users;"
psql -d stellaops_test -c "SELECT count(*) FROM vuln.advisories;"
psql -d stellaops_test -c "SELECT count(*) FROM vex.statements;"

# Cleanup
dropdb stellaops_test

8. Scaling Recommendations

8.1 Vertical Scaling

8.2 Horizontal Scaling

Read replicas for reporting:

# Primary for writes
primary:
  host: postgres-primary.internal
  port: 5432

# Replicas for reads (round-robin)
replicas:
  - host: postgres-replica-1.internal
    port: 5432
  - host: postgres-replica-2.internal
    port: 5432

Connection routing in application:

• Writes → Primary
• Heavy reads (reports, dashboards) → Replicas
• Scheduler impact queries → Replicas with acceptable lag

8.3 Table Partitioning

For high-volume tables (>100M rows), consider partitioning:

-- Partition scheduler.runs by created_at
CREATE TABLE scheduler.runs_partitioned (
    LIKE scheduler.runs INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE scheduler.runs_y2025m12
    PARTITION OF scheduler.runs_partitioned
    FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

-- Automate partition creation
-- See: pg_partman extension

For the StellaOps-native partition-management helpers (partition_mgmt.create_monthly_partitions, cleanup_old_partitions) and the partition maintenance cadence, see the PostgreSQL Patterns Operational Runbook.

8.4 Connection Pooling at Scale

For >1000 concurrent connections, deploy PgBouncer as a dedicated service:

# Compose-style PgBouncer service sketch
services:
  pgbouncer:
    image: pgbouncer/pgbouncer:1.21.0
    ports:
      - "6432:6432"

9. Troubleshooting

9.1 Common Issues

High connection count:

-- Identify connection sources
SELECT client_addr, usename, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

-- Terminate idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '30 minutes';

Lock contention:

-- Find blocking queries
SELECT
    blocked.pid as blocked_pid,
    blocked.query as blocked_query,
    blocking.pid as blocking_pid,
    blocking.query as blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Table bloat:

-- Check table and index sizes
SELECT
    schemaname || '.' || relname as table,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_table_size(relid)) as table_size,
    pg_size_pretty(pg_indexes_size(relid)) as index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

-- Manual VACUUM FULL for severe bloat (blocks writes!)
VACUUM (FULL, ANALYZE) scheduler.runs;

9.2 Emergency Procedures

Kill long-running queries:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '10 minutes'
  AND query NOT LIKE '%pg_stat%';

Force checkpoint (before maintenance):

CHECKPOINT;

Emergency read-only mode:

ALTER DATABASE stellaops_platform SET default_transaction_read_only = on;

10. Air-Gap Considerations

10.1 Offline Setup

PostgreSQL is bundled in the air-gap kit. See docs/OFFLINE_KIT.md for import instructions.

Docker image digest (pinned):

# The reference stack pins postgres:18.1 via the POSTGRES_IMAGE variable
# (devops/compose/env/stellaops.env.example). Pin by digest for air-gap:
postgres:
  image: docker.io/library/postgres:18.1@sha256:<pinned-digest>

10.2 Migrations in Air-Gap

Migrations are embedded as SQL resources in each module’s persistence assembly and are applied automatically on service startup via AddStartupMigrations(...) — there is no standalone migration-runner project to invoke, and no network access is required. The service converges to the correct schema on any fresh database (volume reset, CI, new install).

# Migrations run on startup; just bring the services up against the database.
# To force a service to re-run its migration pass, restart that service:
docker compose -f devops/compose/docker-compose.stella-ops.yml restart <service>

# The StellaOps CLI also exposes maintenance database operations:
#   stella db <subcommand>     (Concelier connector/database operations)
# but it does NOT replace the on-startup auto-migration described above.

10.3 Backup in Air-Gap

# Local backup with encryption
pg_dump -Fc stellaops_platform | gpg --encrypt -r backup@stellaops.local > backup.dump.gpg

# Restore
gpg --decrypt backup.dump.gpg | pg_restore -d stellaops_platform

10.4 EvidenceLocker Region Tags

EvidenceLocker stores the TASK-051-05 data-residency marker as a PostgreSQL row column named region_tag on capsule and evidence catalog tables. Capsule API writes stamp the value from a region claim, X-Stella-Region, or STELLAOPS_DEFAULT_REGION (default unspecified), and capsule reads reject cross-region access with HTTP 403.

The residency claim is no longer DB-row-bound only. As of SPRINT_20260519_080 (TASK-080-01/02) the rustfs/S3 object store also carries the marker: every evidence object write stamps the user-metadata header x-stella-region (transmitted by the S3 SDK as x-amz-meta-x-stella-region, preserved across S3 COPY) with the owning bundle row’s region_tag. The bundle and portable bundle download endpoints resolve the caller’s request region (same precedence as the capsule path) and enforce it against the stored object metadata: a mismatch returns the same region_forbidden HTTP 403 envelope the DB-row enforcer uses, before any bytes are streamed. The filesystem store backend persists the marker in a .x-stella-region sidecar and enforces identically. An operator who proves region_tag = eu-only for a row therefore now has a matching residency proof for the corresponding bytes on disk.

Appendix A: Quick Reference

Connection String Template

Host=<host>;Port=5432;Database=stellaops_platform;Username=<user>;Password=<pass>;
Pooling=true;MinPoolSize=5;Maximum Pool Size=20;ConnectionIdleLifetime=300;
CommandTimeout=30;Timeout=15;

The reference compose stack wires ConnectionStrings__Default as Host=db.stella-ops.local;Port=5432;Database=${POSTGRES_DB:-stellaops_platform};Username=${POSTGRES_USER:-stellaops};Password=${POSTGRES_PASSWORD};Maximum Pool Size=50. Npgsql uses Maximum Pool Size (with spaces) rather than MaxPoolSize.

Essential Commands

# Connect to database
psql -h localhost -U stellaops -d stellaops_platform

# Check version
psql -c "SELECT version();"

# List schemas
psql -c "\dn"

# List tables in schema
psql -c "\dt vuln.*"

# Table structure
psql -c "\d vuln.advisories"

# Current activity
psql -c "SELECT * FROM pg_stat_activity;"

Useful Extensions

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- Query statistics
CREATE EXTENSION IF NOT EXISTS pg_trgm;             -- Fuzzy text search
CREATE EXTENSION IF NOT EXISTS btree_gin;           -- GIN for scalars
CREATE EXTENSION IF NOT EXISTS pgcrypto;            -- Cryptographic functions