Postgres

Postgres is the only required dependency. It holds workflow state, the task queues, escalations, users, roles and config. This page covers what Long Tail needs from the database, how to tune it for durable execution, and what happens at boot when several containers migrate the same schema.

Requirements

Need	Detail
Version	PostgreSQL 15 or later. The reference stacks run 16.
Schema	Long Tail’s lt_* tables live in public. The HotMesh engine keeps its tables in its own schema (durable by default).
Connections	Direct, session-level connections. LISTEN/NOTIFY needs persistent sessions, so transaction-mode poolers (such as RDS Proxy) break NOTIFY delivery.
max_connections	200 or more. Each worker holds its own connections; concurrent workflows exhaust the default 100.
Privileges	The connecting role creates tables and indexes on first boot.

Connect

Pass a connection string or individual fields to start():

Connection string

await start({
  database: { connectionString: process.env.DATABASE_URL },
});

Fields

await start({
  database: {
    host: process.env.POSTGRES_HOST,
    port: Number(process.env.POSTGRES_PORT),
    user: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    database: process.env.POSTGRES_DB,
  },
});

A connectionString takes precedence over the individual fields.

TLS setting	Effect
ssl: true	TLS with Node defaults
ssl: { rejectUnauthorized: false }	TLS without certificate verification (VPC connections without a CA bundle)
sslmode=require in the connection string, no ssl	Inferred as { rejectUnauthorized: false }
PGSSLMODE=no-verify	The setting the boilerplate’s AWS stack uses

Long Tail’s connection pool pins search_path to public and fails a connection attempt that takes longer than 10 seconds. The HotMesh engine qualifies its own schema on every statement.

Caution

If a schema in the database shares the connecting role’s name, Postgres’s default search_path ("$user", public) would resolve unqualified tables there. Long Tail pins public to prevent that, and refuses to boot when it finds lt_migrations in any other schema: move the live lt_* tables to public first.

Migrations at startup

Every container runs migrations as the first step of start(). Many containers can boot at once against one database:

1. Each container takes a dedicated connection and waits on a Postgres advisory lock. One container holds it at a time.
2. The holder reads lt_migrations, the ledger of applied files, and applies each missing .sql file in name order.
3. Each file and its ledger row commit in one transaction. A crash mid-file rolls back both, and the next boot retries the file.
4. The holder releases the lock. Every waiting container then finds each file recorded and applies nothing.

The superadmin from seed.admin follows the same rule: when two containers create it at once, the unique external_id decides, and the loser adopts the winner’s row.

Migration files never use CREATE INDEX CONCURRENTLY, which cannot run inside a transaction.

Why tuning matters

Durable execution is write-heavy. Every workflow writes a job row, and every field change writes attribute rows: a simple three-step workflow writes about 100. A thousand concurrent workflows means hundreds of thousands of inserts in seconds. Stock Postgres settings target mixed workloads on modest hardware.

Recommended settings

Parameter	Default	Recommended	Why
shared_buffers	128MB	25% of RAM (256MB to 1GB)	Keeps the hot job and attribute pages cached
work_mem	4MB	16MB	Workflow queries join across partitions; avoids sorts spilling to disk
maintenance_work_mem	64MB	128MB to 256MB	Faster VACUUM after burst writes
wal_buffers	auto	16MB	Write bursts saturate the default WAL buffer
max_wal_size	1GB	1GB to 2GB	Fewer checkpoints during sustained bursts
checkpoint_completion_target	0.9	0.9	Spreads checkpoint I/O
effective_cache_size	4GB	50 to 75% of RAM	Planner hint for OS cache
synchronous_commit	on	off in dev and staging only	2 to 5 times the write throughput; WAL is still written, only the flush is deferred. Keep on in production.
max_connections	100	200	Connection per worker

Managed databases add connection hygiene. The boilerplate’s AWS parameter group sets:

Parameter	Value	Why
idle_in_transaction_session_timeout	60000	Ends abandoned transactions
tcp_keepalives_idle	60	Detects dead connections
tcp_keepalives_interval	10	Probe frequency
tcp_keepalives_count	6	Probes before a connection is declared dead

Apply the settings

Docker Compose

docker-compose.yml

postgres:
  image: postgres:16
  command:
    - postgres
    - -c
    - shared_buffers=256MB
    - -c
    - work_mem=16MB
    - -c
    - maintenance_work_mem=128MB
    - -c
    - wal_buffers=16MB
    - -c
    - max_wal_size=1GB
    - -c
    - checkpoint_completion_target=0.9
    - -c
    - effective_cache_size=512MB
    - -c
    - synchronous_commit=off
    - -c
    - max_connections=200
  shm_size: 512m

shm_size is required. Docker gives /dev/shm 64MB by default, and shared_buffers=256MB needs at least that much shared memory.

AWS RDS

# Custom parameter group
shared_buffers = {DBInstanceClassMemory/4}
work_mem = 16384          # 16MB in KB
maintenance_work_mem = 262144
wal_buffers = 16384
max_wal_size = 2048       # 2GB in MB
synchronous_commit = on
max_connections = 200

Cloud SQL

# Database flags
shared_buffers: 25% of instance RAM (auto-tuned by Cloud SQL)
work_mem: 16MB
maintenance_work_mem: 256MB
max_wal_size: 2GB
synchronous_commit: on
max_connections: 200

Find your bottleneck

Submit a burst of minimal workflows. basicEcho is one of the examples the long-tail repo’s Compose stack loads; any short workflow of yours works the same way:

time for i in $(seq 1 100); do
  curl -s -X POST http://localhost:3000/api/workflows/basicEcho/invoke \
    -H "Authorization: Bearer $TOKEN" \
    -H 'Content-Type: application/json' \
    -d '{"data":{"message":"test","sleepSeconds":0}}' > /dev/null
done

Then look at where Postgres spends its time:

-- Write pressure: a high buffers_checkpoint points at WAL
SELECT * FROM pg_stat_bgwriter;

-- Connection saturation
SELECT count(*) AS active, max_conn
FROM pg_stat_activity,
     (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc
WHERE state = 'active'
GROUP BY max_conn;

-- Dead tuples after burst writes
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC LIMIT 10;

Keep tables lean

Turn on the built-in maintenance cron. true runs the default rules weekly, Sundays at 2 AM; an object replaces the schedule and rules:

await start({
  database: { connectionString: process.env.DATABASE_URL },
  workers,
  maintenance: true,
});

After a load test or a batch of 10,000 or more workflows, vacuum the hot engine tables:

VACUUM ANALYZE durable.jobs_attributes;
VACUUM ANALYZE durable.engine_streams;

Related

Data modelThe tables and what each holds.

Run modesMany containers, one database.

AWS FargateAurora PostgreSQL with the parameter group above.

ObservabilityTelemetry and logs.