Embedded PostgreSQL Server with TRUE Concurrent Connections
\n\n\n ![\"npm](\"https://img.shields.io/npm/v/pgserve?style=flat-square&color=00D9FF\")
![\"Node.js\"](\"https://img.shields.io/badge/node-%3E%3D18-green?style=flat-square\"){kind=icon}
![\"PostgreSQL\"](\"https://img.shields.io/badge/PostgreSQL-18-blue?style=flat-square\"){kind=icon}
![\"License\"](\"https://img.shields.io/badge/license-MIT-green?style=flat-square\"){kind=icon}
![\"Discord\"](\"https://img.shields.io/discord/1095114867012292758?style=flat-square&color=00D9FF&label=discord\")
npx pgserve and it just works, no credentials needed. Zero config, auto-provision databases, unlimited concurrent connections.
\n\n\n Quick Start •\n Features •\n CLI •\n API •\n Performance\n
\n| Real PostgreSQL 18 | \nNative binaries, not WASM — full compatibility, extensions support | \n
| Unlimited Concurrency | \nNative PostgreSQL process forking — no connection locks | \n
| Zero Config | \nJust run pgserve, connect to any database name | \n
| Auto-Provision | \nDatabases created automatically on first connection | \n
| Memory Mode | \nFast and ephemeral for development (default) | \n
| RAM Mode | \nUse --ram for /dev/shm storage (Linux, 2x faster) | \n
| Persistent Mode | \nUse --data ./path for durable storage | \n
| Async Replication | \nSync to real PostgreSQL with minimal overhead | \n
| pgvector Built-in | \nUse --pgvector for auto-enabled vector similarity search | \n
| Cross-Platform | \nLinux x64, macOS ARM64/x64, Windows x64 | \n
| Any Client Works | \npsql, node-postgres, Prisma, Drizzle, TypeORM | \n
Database patterns to sync (comma-separated)\n --pgvector Auto-enable pgvector extension on new databases\n --max-connections \n MIT License — Copyright (c) 2025 Namastex Labs\n \n GitHub •\n npm •\n Issues\n \n Made with love by Namastex Labs\n \n\n\nExamples
\n\n```bash\n# Development (memory mode, auto-clusters on multi-core)\npgserve\n\n# RAM mode (Linux only, 2x faster)\npgserve --ram\n\n# Persistent storage\npgserve --data /var/lib/pgserve\n\n# Custom port\npgserve --port 5433\n\n# Enable pgvector for AI/RAG applications\npgserve --pgvector\n\n# RAM mode + pgvector (fastest for AI workloads)\npgserve --ram --pgvector\n\n# Sync to production PostgreSQL\npgserve --sync-to \"postgresql://user:pass@db.example.com:5432/prod\"\n```\n\n
\n\n## Daemon mode\n\n`pgserve@2` ships a singleton daemon that binds a Unix control socket\ninside `$XDG_RUNTIME_DIR/pgserve` (fallback `/tmp/pgserve`). One daemon\nper host serves every consumer on the box — no port conflicts, no\ncredentials, kernel-rooted identity. Run it under PM2 or systemd so it\nrestarts automatically.\n\n```bash\n# Foreground (for debugging)\npgserve daemon\n\n# Stop a running daemon\npgserve daemon stop\n```\n\nA second `pgserve daemon` invocation while the first is running exits with\n`already running, pid N`. A daemon killed with `kill -9` leaves an orphan\nPID file + socket; the next `pgserve daemon` boot detects the dead pid and\ncleans both up automatically.\n\nConnect from any libpq client (no host/port/user/password required —\nthe daemon authenticates via SO_PEERCRED on accept):\n\n```bash\npsql -h \"${XDG_RUNTIME_DIR:-/tmp}/pgserve\" -d myapp\n# or via connection URI\npsql \"postgresql:///myapp?host=${XDG_RUNTIME_DIR:-/tmp}/pgserve\"\n```\n\n### Supervised by PM2 — `pgserve install` (recommended)\n\n`pgserve install` registers pgserve as a hardened pm2 process in one\ncommand. Idempotent: re-running it is a no-op when already installed.\n\n```bash\npgserve install # one-shot register + start under pm2\npgserve install --port 8442 # custom port\npgserve install --data /data/pg # custom data dir\n\npgserve url # postgres://localhost:8432/postgres\npgserve port # 8432\npgserve status # pm2 + on-disk config snapshot\npgserve uninstall # remove from pm2; keep data dir\n```\n\n**Hardened defaults** (tuned for production-grade Postgres workloads,\nnot toy-machine values):\n\n| Flag | Default | Why |\n|------|---------|-----|\n| `--max-memory-restart` | `4G` | Postgres realistic working set: shared_buffers + autovacuum + connection backends. 1G OOM-kills under modest load. Override with `PGSERVE_MAX_MEMORY=8G pgserve install`. |\n| `--max-restarts` | `50` | Tolerates extended outages (NATS reconnect storms, host pressure). Combined with `--min-uptime`, only RAPID failures count. |\n| `--min-uptime` | `10000` ms | Restart counts against the cap only when the process crashed within 10s of starting. Healthy long-uptime crashes don't burn the budget. |\n| `--restart-delay` | `4000` ms | Initial gap between restarts. |\n| `--exp-backoff-restart-delay` | `100` → ~60000 ms | Exponential spread on repeated failures so we don't hammer pm2 + the host on persistent issues. |\n| `--kill-timeout` | `60000` ms | Postgres needs time to flush WAL on graceful shutdown; 60s headroom. |\n| `--log-date-format` | `YYYY-MM-DD HH:mm:ss.SSS` | Operator-friendly timestamps in pm2 logs. |\n| `--output` / `--error` | `~/.pgserve/logs/pgserve-{out,error}.log` | Rotates via pm2-logrotate (install separately). |\n\nConfig: `~/.pgserve/config.json` (override the directory with\n`PGSERVE_CONFIG_DIR`). Memory ceiling: env-tunable via\n`PGSERVE_MAX_MEMORY` at install time.\n\nDownstream services that need a Postgres connection can shell out to\n`pgserve install` (no-op if already running) and read the canonical URL\nfrom `pgserve url` instead of spinning up their own embedded pgserve.\n\n#### Manual ecosystem.config.cjs (legacy)\n\n```javascript\nmodule.exports = {\n apps: [{\n name: 'pgserve',\n script: 'pgserve',\n args: 'daemon',\n autorestart: true,\n max_memory_restart: '1G',\n env: { XDG_RUNTIME_DIR: '/run/user/1000' },\n }],\n};\n```\n\n```bash\npm2 start ecosystem.config.cjs && pm2 save\n```\n\n### Supervised by systemd\n\n`/etc/systemd/user/pgserve.service`:\n\n```ini\n[Unit]\nDescription=pgserve daemon\nAfter=default.target\n\n[Service]\nType=simple\nExecStart=/usr/bin/env npx pgserve daemon\nRestart=on-failure\nRestartSec=5\n\n[Install]\nWantedBy=default.target\n```\n\nEnable for the current user:\n\n```bash\nsystemctl --user enable --now pgserve\njournalctl --user -u pgserve -f\n```\n\nThe systemd user unit inherits `XDG_RUNTIME_DIR` automatically; the daemon\nbinds `${XDG_RUNTIME_DIR}/pgserve/control.sock` (mode 0600, dir mode 0700)\nplus a `.s.PGSQL.5432` symlink so off-the-shelf PostgreSQL clients connect\nwithout further configuration.\n\n
\n\n## Fingerprint isolation\n\nEach consumer is identified by a **kernel-rooted fingerprint** derived from\nthe peer's `SO_PEERCRED` plus the resolved `package.json` `name`, collapsed\nto 12 hex chars. The daemon auto-creates one database per fingerprint —\n`app_
\n\n## Long-running apps: `pgserve.persist`\n\nDefault lifecycle is **ephemeral**: a database whose `liveness_pid` is dead\nAND whose `last_connection_at` is older than 24h is dropped on the next GC\nsweep (boot, hourly, sampled on-connect). Reaped DBs emit\n`db_reaped_ttl` or `db_reaped_liveness` audit events.\n\nIf your app holds state worth keeping past 24h of idle — genie's wish/agent\nstore, internal dashboards, anything you'd be unhappy to lose — declare\npersistence in `package.json`:\n\n```jsonc\n{\n \"name\": \"my-long-lived-app\",\n \"pgserve\": { \"persist\": true }\n}\n```\n\nPersisted databases are **never** reaped, regardless of liveness or TTL.\nDev workloads with long debug cycles do not normally need this — any new\nconnection slides the TTL window forward. Reach for `pgserve.persist` when\nthe app is genuinely long-lived (production daemon, dashboard, durable\nagent state), not just for convenience.\n\n
\n\n## Console (`autopg ui`)\n\nA local web console for inspecting and editing the running cluster.\nRuns in-process via `node:http`, binds 127.0.0.1 only, single-user dev\ntool — no auth, no TLS, never expose it.\n\n```bash\nautopg ui # walk 8433–8533 picking the first free port\nautopg ui --port 8500 # bind exactly 8500\nautopg ui --no-open # skip browser launch (CI / headless)\n```\n\nThe first stateful screen — **Settings** — is functional today: it\nrenders the 6-section schema (server / runtime / sync / supervision /\npostgres / ui), validates inline, and round-trips through\n`~/.autopg/settings.json` with optimistic concurrency (sha256 etag +\n`If-Match`). The other 10 screens (Databases, Tables, SQL, Optimizer,\nSecurity, Ingress, Health, Sync, RLM-trace, RLM-sim) are scaffolded\nas `[ coming soon ]` placeholders — Health ships next.\n\nThe UI shells out to the CLI for every mutation (`autopg config set`\nunder PUT, `autopg restart` under POST). The daemon stays untouched\n— no HTTP API, no signal-based reload — so the console works even\nwhen no daemon is running.\n\nSee [`console/README.md`](./console/README.md) for the local dev loop\nand design-system source.\n\n
\n\n## Configuration\n\nThe CLI is the source of truth. Settings live at\n`~/.autopg/settings.json` (override the directory with\n`AUTOPG_CONFIG_DIR`; the legacy `PGSERVE_CONFIG_DIR` is still honored\nand falls back to `~/.pgserve/`). Every write is atomic, chmod 0600,\nand tagged with a sha256 etag for optimistic concurrency on the UI\nhelper's PUT path.\n\nSchema sections (one per `~/.autopg/settings.json` top-level key):\n\n| Section | Purpose |\n|---------|---------|\n| `server` | Router port/host, backend socket, superuser credentials |\n| `runtime` | Log level, auto-provision, pgvector, data dir |\n| `sync` | WAL-based logical replication toggle |\n| `supervision` | pm2 hardening defaults (memory, restart, kill timeout) |\n| `postgres` | 15 curated GUCs (`shared_buffers`, `wal_level`, …) + `_extra` raw passthrough |\n| `ui` | Console theme / phosphor / density / CRT toggle |\n\n```bash\nautopg config init # write defaults\nautopg config list # KEY VALUE SOURCE table\nautopg config get postgres.shared_buffers # machine-friendly value\nautopg config set postgres.shared_buffers 256MB # validates + atomic write\nautopg config edit # opens $EDITOR on settings.json\nautopg config path # absolute path (honors AUTOPG_CONFIG_DIR)\n```\n\n**Precedence:** `default < file < env`. `AUTOPG_*` env vars beat\n`PGSERVE_*` (the legacy form is still honored with a one-time\ndeprecation log per process, so existing operators keep working).\nThe console shows a yellow `OVERRIDDEN BY ENV` chip on rows whose\nenv var is currently set.\n\n**GUC passthrough:** `postgres._extra` is a free-form `{ gucName: scalar }`\nmap for any PostgreSQL setting outside the curated 15. Names must match\n`^[a-z][a-z0-9_]*$`; values must be string / number / boolean (no\nnewlines, no leading `-`). Both layers are revalidated at boot, so a\ntypo logs a `logger.warn` and is dropped — postgres still starts.\n\n**One-shot migration:** on first run, if `~/.pgserve/` exists and\n`~/.autopg/` does not, the contents are copied (preserving mtimes)\nand a `MIGRATED-FROM-PGSERVE.md` marker is dropped in the old dir.\nIdempotent — second run is a no-op.\n\nFull schema reference: [`docs/settings-schema.md`](./docs/settings-schema.md).\n\n
\n\n## Compat TCP via `--listen`\n\nTCP is **off by default** in v2. Bring it back only when you need it\n(Kubernetes pods, remote sync, legacy clients that cannot speak Unix\nsockets) by opting in:\n\n```bash\npgserve daemon --listen :5432\n# Repeatable for multiple binds:\npgserve daemon --listen :5432 --listen 0.0.0.0:5433\n```\n\nTCP peers cannot use `SO_PEERCRED`, so they **must** authenticate at\nconnect time. Issue a bearer token bound to a known fingerprint:\n\n```bash\n# Prints the token ONCE; the daemon stores only its hash.\npgserve daemon issue-token --fingerprint a1b2c3d4e5f6\n\n# TCP client passes it via libpq application_name:\n# ?fingerprint=a1b2c3d4e5f6&token=
\n\n## API\n\nDaemon-first apps can let the first caller install/start the singleton and\nthen connect through the Unix socket. The daemon derives the app identity\nfrom kernel peer credentials and routes it to that app's signed fingerprint\ndatabase.\n\n```javascript\nimport { daemonClientOptions, ensureDaemon } from 'pgserve';\nimport postgres from 'postgres';\n\nawait ensureDaemon({\n dataDir: `${process.env.HOME}/.pgserve/data`,\n logLevel: 'warn',\n});\n\nconst sql = postgres(daemonClientOptions());\nawait sql`SELECT current_database()`;\n```\n\nThe classic TCP router API remains available for explicit v1-compatible\nembedded servers:\n\n```javascript\nimport { startMultiTenantServer } from 'pgserve';\n\nconst server = await startMultiTenantServer({\n port: 8432,\n host: '127.0.0.1',\n baseDir: null, // null = memory mode\n logLevel: 'info',\n autoProvision: true,\n enablePgvector: true, // Auto-enable pgvector on new databases\n syncTo: null, // Optional: PostgreSQL URL for replication\n syncDatabases: null // Optional: patterns like \"myapp,tenant_*\"\n});\n\n// Get stats\nconsole.log(server.getStats());\n\n// Graceful shutdown\nawait server.stop();\n```\n\n
\n\n## Framework Integration\n\n\n\n\nnode-postgres
\n\n```javascript\nimport pg from 'pg';\n\nconst client = new pg.Client({\n connectionString: 'postgresql://localhost:8432/myapp'\n});\n\nawait client.connect();\nawait client.query('CREATE TABLE users (id SERIAL, name TEXT)');\nawait client.query(\"INSERT INTO users (name) VALUES ('Alice')\");\nconst result = await client.query('SELECT * FROM users');\nconsole.log(result.rows);\nawait client.end();\n```\n\n\n\n\nPrisma
\n\n```prisma\n// prisma/schema.prisma\ndatasource db {\n provider = \"postgresql\"\n url = env(\"DATABASE_URL\")\n}\n```\n\n```bash\n# .env\nDATABASE_URL=\"postgresql://localhost:8432/myapp\"\n\n# Run migrations\nnpx prisma migrate dev\n```\n\n\n\n\nDrizzle
\n\n```typescript\nimport { drizzle } from 'drizzle-orm/node-postgres';\nimport { Pool } from 'pg';\n\nconst pool = new Pool({\n connectionString: 'postgresql://localhost:8432/myapp'\n});\n\nconst db = drizzle(pool);\nconst users = await db.select().from(usersTable);\n```\n\n
\n\n## Async Replication\n\nSync ephemeral pgserve data to a real PostgreSQL database. Uses native logical replication for **zero performance impact** on the hot path.\n\n```bash\n# Sync all databases\npgserve --sync-to \"postgresql://user:pass@db.example.com:5432/mydb\"\n\n# Sync specific databases (supports wildcards)\npgserve --sync-to \"postgresql://...\" --sync-databases \"myapp,tenant_*\"\n```\n\n> Replication is handled by PostgreSQL's WAL writer process, completely off the runtime event loop. Sync failures don't affect main server operation.\n\n
\n\n## pgvector (Vector Search)\n\npgvector is **built-in** — no separate installation required. Just enable it:\n\n```bash\n# Auto-enable pgvector on all new databases\npgserve --pgvector\n\n# Combined with RAM mode for fastest vector operations\npgserve --ram --pgvector\n```\n\nWhen `--pgvector` is enabled, every new database automatically has the vector extension installed. No SQL setup required.\n\n\n\n\nUsing pgvector
\n\n```sql\n-- Create table with vector column (1536 = OpenAI embedding size)\nCREATE TABLE documents (id SERIAL, content TEXT, embedding vector(1536));\n\n-- Insert with embedding\nINSERT INTO documents (content, embedding) VALUES ('Hello', '[0.1, 0.2, ...]');\n\n-- k-NN similarity search (L2 distance)\nSELECT content FROM documents ORDER BY embedding <-> $1 LIMIT 10;\n```\n\nSee [pgvector documentation](https://github.com/pgvector/pgvector) for full API reference.\n\n\n\n> pgvector 0.8.1 is bundled with the PostgreSQL binaries. Supports L2 distance (`<->`), inner product (`<#>`), and cosine distance (`<=>`).\n\nWithout --pgvector flag
\n\nIf you don't use `--pgvector`, you can still enable pgvector manually per database:\n\n```sql\nCREATE EXTENSION IF NOT EXISTS vector;\n```\n\n
\n\n## Performance\n\n### CRUD Benchmarks\n\n\n
\n\n### Vector Benchmarks (pgvector)\n\n\n \n Scenario \n SQLite \n PostgreSQL \n pgserve 1.2.0 \n pgserve v2 \n pgserve v2 --ram \n \n \n Concurrent Writes (10 agents) \n 91 qps \n 204 qps \n 1,667 qps \n 2,273 qps \n 4,167 qps 🏆 \n \n \n Mixed Workload \n 383 qps \n 484 qps \n 507 qps \n 1,133 qps \n 2,109 qps 🏆 \n \n \nWrite Lock (50 writers) \n 111 qps \n 228 qps \n 2,857 qps \n 3,030 qps \n 4,348 qps 🏆 \n \n
\n\n> Why pgserve wins on writes: RAM mode uses \n \n Metric \n PostgreSQL \n pgserve 1.2.0 \n pgserve v2 \n pgserve v2 --ram \n \n \n Vector INSERT (1000 × 1536-dim) \n 152/sec \n 392/sec \n 387/sec \n 1,082/sec 🏆 \n \n \n k-NN Search (k=10, 10k corpus) \n 22 qps \n 33 qps \n 31 qps \n 30 qps \n \n \nRecall@10 \n 100% \n 100% \n 100% \n 100% \n /dev/shm (tmpfs), eliminating fsync latency. Vector search is CPU-bound, so RAM mode shows minimal benefit there.\n\n### Final Score\n\n\n
\n\n> Methodology: Recall@k measured against brute-force ground truth (industry standard). PostgreSQL baseline is Docker \n \n Engine \n CRUD QPS \n Vec QPS \n Recall \n P50 \n P99 \n Score \n \n \n SQLite \n 195 \n N/A \n N/A \n 6.3ms \n 17.3ms \n 117 \n \n \n pgserve 1.2.0 \n 305 \n 65 \n 100% \n 3.3ms \n 7.0ms \n 209 \n \n \n PostgreSQL \n 1,677 \n 152 \n 100% \n 6.0ms \n 19.0ms \n 1,067 \n \n \n pgserve v2 \n 2,145 \n 149 \n 100% \n 5.3ms \n 13.0ms \n 1,347 \n \n \npgserve v2 --ram \n 3,541 \n 381 \n 100% \n 3.3ms \n 10.7ms \n 2,277 🏆 \n pgvector/pgvector:pg18. RAM mode available on Linux and WSL2.\n>\n> Run benchmarks yourself: bun tests/benchmarks/runner.js --include-vector\n\n
\n\n## Use Cases\n\n\n
\n\n\n \n \n \n Development & Testing
\n \n
\n \n \n AI & Agents
\n \n
\n \n \n\n \n Multi-Tenant & SaaS
\n \n
\n \n \n Edge & Embedded
\n \n
\n
\n\n## Requirements\n\n- **Runtime**: Node.js >= 18 (npm/npx)\n- **Platform**: Linux x64, macOS ARM64/x64, Windows x64\n\n
\n\n## Development\n\nContributors: This project uses Bun internally for development:\n\n```bash\n# Install dependencies\nbun install\n\n# Run tests\nbun test\n\n# Run benchmarks\nbun tests/benchmarks/runner.js\n\n# Lint\nbun run lint\n```\n\n
\n\n## Contributing\n\nContributions welcome! Fork the repo, create a feature branch, add tests, and submit a PR.\n\n
\n\n---\n\n\n \n","readmeFilename":"README.md"}