Self-Hosting PostgreSQL 17: A Production-Ready Starter

PostgreSQL is the right answer to “what database should I use” more often than anything else. It’s relational, transactional, ACID-compliant, has decades of stability, and has been quietly absorbing every interesting database feature people ask for — JSON columns, full-text search, vector indexes, time-series extensions, you name it.

This post covers what you actually need to know to run PostgreSQL 17 on Witchly’s Elite Applications tier in production: the setup, the auth model, basic schema design, connection pooling, backups, and the things people forget until it’s too late.

When to use Postgres (and when to skip it)

Use PostgreSQL when:

• Your data has a clear structure — users, orders, transactions, anything where the schema is consistent
• You need transactional integrity (“either all of these writes succeed or none do”)
• You’ll do queries that join multiple tables (“show me every user who ordered something this month”)
• You want something boring, stable, and well-understood by every dev tool ever made

Skip PostgreSQL and use MongoDB when:

• Your data has variable shape (every document looks different)
• You’re storing what looks like API payloads, JSON-in-JSON-out
• You’re doing simple lookups by ID, no joins

Skip both and use Redis when:

• You need sub-millisecond reads
• You’re storing ephemeral session data, rate-limit counters, cached query results
• Persistence is nice-to-have, not critical

For most “real” projects with users, billing, content, or anything that needs to be correct, PostgreSQL is the safe default.

Why self-host

Managed Postgres services (the obvious ones) start cheap and get expensive fast. Connection-count limits hit you. Storage scales as a metered cost. Egress is metered. You pay $5/mo and then realise you’ve crept up to $25/mo because your project grew.

Self-hosted PostgreSQL on Witchly Elite:

• Predictable monthly cost ($2-8/mo)
• All connections, all queries, all egress are inside Witchly’s network if your app lives there too
• Full version control and config control
• The dashboard you already use for game servers manages your DB

The cost-of-self-hosting is operational responsibility — backups, monitoring, occasional version upgrades. For a small-to-medium project, that’s a fair trade.

Plan recommendations

PostgreSQL 17 is Elite-only on Witchly. Pick a plan based on how much working data and how many concurrent connections you expect:

Plan	RAM	Disk	Best for
The Helper ($2/mo)	1 GB	small	Side projects, dev/staging environments, low-traffic apps
The Daemon ($4/mo)	2 GB	medium	Production app with hundreds of users, moderate query volume
The Orchestrator ($8/mo)	4 GB	large	Heavier workloads, more concurrent connections, data-heavy reporting

PostgreSQL’s RAM goes mostly to shared_buffers (caching frequently-read pages) and work_mem (sort/join workspace per query). Both default to conservative values that are fine for small apps. You’d tune them only if you’re noticing slow queries that profile as I/O-bound or memory-pressured.

Deploying

In the dashboard’s deploy wizard:

1. Applications category → Postgres 17
2. Set the server name (e.g., myapp-db)
3. Pick your Elite plan
4. The Configure step exposes the superuser password and the initial database/user names — set them now
5. Deploy

After provisioning, the Console tab streams Postgres’s startup. When you see:

LOG:  database system is ready to accept connections

it’s live.

The Network tab shows the public host and port. Witchly’s Elite Applications port range has firewall rules and panel allocations registered, so the address is reachable from the internet.

The connection string

A standard PostgreSQL connection URI:

postgresql://username:[email protected]:5432/databasename

Pieces:

• username / password — set during the Configure step (you can add more users later via SQL)
• host / port — from the dashboard Network tab
• databasename — the specific DB inside Postgres (Postgres can hold many)

Add ?sslmode=require only if you’ve configured SSL on the Postgres side (the egg ships with SSL disabled by default for simplicity; production-grade setups should enable it — see the docs).

This URI goes into your app’s DATABASE_URL environment variable.

Auth and roles, briefly

Postgres has a flexible role system, but for most projects you only need to know three things:

1. The superuser

The user you set in the Configure step is the superuser. It can do anything. Don’t have your app connect as the superuser.

2. App users

Create a user (a “role” in Postgres terminology) for each app:

CREATE ROLE myapp WITH LOGIN PASSWORD 'long-random-password';
CREATE DATABASE myapp_prod OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_prod TO myapp;

Now your app connects as myapp, not as the superuser. If the app’s password ever leaks, you rotate it without affecting your admin access.

3. Read-only users (for reporting/BI)

If you ever want a script or dashboard tool to query the DB without write capability:

CREATE ROLE readonly WITH LOGIN PASSWORD 'another-long-random-password';
GRANT CONNECT ON DATABASE myapp_prod TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

The last line is the key trick — without it, new tables you create later won’t be readable by readonly.

Designing your schema

A few patterns that pay off:

Use id BIGSERIAL PRIMARY KEY for every table

Don’t use UUIDs unless you have a specific reason. BIGSERIAL (auto-incrementing 64-bit integer) is faster, smaller in indexes, and easier to read in logs. UUIDs make sense when records are generated client-side or distributed across multiple DBs; for a single Postgres instance, integers win.

Add created_at and updated_at timestamps to every table

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

TIMESTAMPTZ (timestamp with timezone) is the right choice; never use plain TIMESTAMP for events.

Index everything you query on

If you WHERE email = 'x' regularly, email needs an index (the UNIQUE constraint above creates one automatically). Same for foreign keys, frequently-filtered columns, and ORDER BY columns.

Postgres won’t tell you “you should index this” — you have to look at slow queries and add indexes manually.

Use foreign keys

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  ...
);

The REFERENCES constraint enforces that user_id always points to a real user. ON DELETE CASCADE says “when a user is deleted, their posts are deleted too.” This is invaluable; it prevents orphan data and silent corruption.

Connection pooling

A subtle thing about Postgres: each connection costs ~10 MB of RAM. If your app spawns a connection per request, you’ll run out of RAM fast on a small plan.

Use a connection pool. Most ORM-style libraries (Prisma, SQLAlchemy, ActiveRecord, knex.js) include one — make sure you’re using it.

For Node.js with pg directly:

import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                     // max connections
  idleTimeoutMillis: 30000,
})

// Each query checks out a connection, runs, releases it back.
const result = await pool.query('SELECT id FROM users WHERE email = $1', ['[email protected]'])

For higher-traffic apps, consider PgBouncer as a middleman — it sits in front of Postgres and multiplexes thousands of app connections onto a small fixed pool of real DB connections. Beyond the scope of a starter post, but it’s the right escape hatch when you outgrow naive pooling.

Backups: pg_dump is your friend

PostgreSQL’s logical backup tool, pg_dump, creates a portable SQL dump you can restore to any Postgres instance.

Schedule a daily pg_dump task via the Witchly Schedules tab:

pg_dump --no-owner --no-privileges --format=custom \
  --dbname=postgresql://myapp:[email protected]:5432/myapp_prod \
  --file=/home/container/backups/$(date +%Y%m%d).dump

The --format=custom produces a binary dump that’s smaller than plain SQL and supports pg_restore --jobs N for parallel restore.

Combine with the dashboard’s auto-backup (whole-disk snapshot) and you’re triple-protected:

• Tier 1: Logical, portable pg_dump daily
• Tier 2: Whole-disk Witchly auto-backup
• Tier 3: Off-platform weekly download

Test the restore at least once a quarter — see the backup strategies post for the testing approach.

Common production gotchas

”It works in dev but the queries are slow in production”

You forgot indexes. EXPLAIN ANALYZE SELECT ... on slow queries will show full-table scans (Seq Scan) where index scans (Index Scan) should be.

”Too many clients” error

Connection-pool misconfiguration. Either your app is opening too many connections, or you’ve set the pool size higher than Postgres’s max_connections. Default max_connections = 100 is plenty for most apps; the issue is almost always app-side.

Data type surprises

Postgres is strict. INTEGER overflow causes errors (use BIGINT if you’re tracking large counters). VARCHAR(50) rejects 51-char strings (use TEXT unless you have a specific reason). JSON vs JSONB — almost always use JSONB (binary, indexable, faster).

Charset issues

Default encoding on the Witchly egg is UTF-8, which is what you want. Don’t override unless you have a specific reason; you’ll regret it the moment a user submits an emoji.

Character escaping in connection strings

If your password contains special characters (@, :, /, ?, #), URL-encode them or your driver will misparse the URI. mypa@ss becomes mypa%40ss. Easier solution: use passwords without special chars, just long random alphanumerics.

Connecting from your stack

Node.js (Prisma)

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

npx prisma db push

Node.js (raw pg)

import { Pool } from 'pg'
const pool = new Pool({ connectionString: process.env.DATABASE_URL })
const { rows } = await pool.query('SELECT NOW()')

Python (psycopg)

import psycopg
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT NOW()")
        print(cur.fetchone())

Go (pgx)

ctx := context.Background()
conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
if err != nil { panic(err) }
defer conn.Close(ctx)

All look identical because the DATABASE_URL is the only thing that changes between environments.

Production checklist

• App connects as a non-superuser role with only the privileges it needs
• Strong, randomly-generated passwords for both superuser and app roles
• Connection pooling on the app side (10-20 connections is plenty for a small app)
• Daily pg_dump scheduled
• Witchly auto-backup enabled
• You’ve successfully restored a pg_dump to a fresh DB at least once
• Indexes exist on every column you frequently filter or sort on
• You’ve enabled log_min_duration_statement = 200 (logs slow queries) so you can find performance issues before users do
• Foreign keys exist between related tables; cascade behaviour is intentional

Wrapping up

PostgreSQL 17 on Witchly Elite is the boring, reliable database choice — and “boring and reliable” is the highest praise you can give a database. It will outlast every fad and be where most of your data ends up living for the long haul.

Deploy from the Applications pricing page, or follow the dashboard walkthrough in the Postgres setup doc.