PostgreSQL essentials for app developers
Running Postgres 18 in Docker, finding my way around psql, and sketching a job-processing schema whose constraints enforce tenant-scoped idempotency.
The PostgreSQL basics I need before writing Go against it: running it, getting around psql, how it organises things, and designing tables that reject bad data by themselves. The running example is a job-processing domain in the spirit of InferenceFlow: tenants submit jobs with an idempotency key, and workers make attempts at them. The tables are my practice sketch, not the project’s schema.
Running Postgres 18 in Docker
docker volume create pgdata
docker run -d --name pg \
-e POSTGRES_USER=inferenceflow \
-e POSTGRES_PASSWORD=inferenceflow \
-e POSTGRES_DB=inferenceflow \
-p 127.0.0.1:5432:5432 \
-v pgdata:/var/lib/postgresql \
postgres:18
POSTGRES_PASSWORDis required.POSTGRES_USERcreates a superuser andPOSTGRES_DBthe first database.- From 18 the image keeps its data in a version-specific directory (
/var/lib/postgresql/18/docker), so the volume belongs at/var/lib/postgresql. Older tutorials mount/var/lib/postgresql/data, which 18 refuses. The versioned path makespg_upgradepossible inside one volume. - Binding to
127.0.0.1keeps the database off the local network.
Getting around in psql
docker exec -it pg psql -U inferenceflow -d inferenceflow
Meta-commands start with a backslash and are handled by psql itself; anything else is SQL and needs a terminating ;.
| Command | What it does |
|---|---|
\l |
list databases |
\c dbname |
connect to another database |
\dn / \dt |
list schemas / tables |
\d jobs |
describe a table, its indexes and constraints |
\du |
list roles |
\x auto |
expanded output for wide rows |
\conninfo |
which database, user and host I am on |
How Postgres organises things
- A cluster is one running server holding several databases. A connection is always to exactly one database.
- Schemas are namespaces inside a database. Since Postgres 15 ordinary roles cannot create objects in
publicunless grantedCREATE. - Roles are both users and groups. A “user” is a role with
LOGIN.
Unlike Oracle, a schema is not tied to a user. The app should connect as a role that reads and writes rows but owns nothing; migrations run as the owner.
CREATE ROLE inferenceflow_app LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE inferenceflow TO inferenceflow_app;
GRANT USAGE ON SCHEMA public TO inferenceflow_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO inferenceflow_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO inferenceflow_app;
ON ALL TABLES only covers tables that exist now; the default privileges cover tables future migrations create.
Types worth choosing deliberately
| Need | Use | Avoid |
|---|---|---|
| Surrogate key | bigint GENERATED ... AS IDENTITY |
serial (legacy) |
| Public identifier | uuid, generated with uuidv7() (new in 18) |
exposing sequential ids |
| A point in time | timestamptz |
timestamp (no time zone) |
| Text | text, plus a CHECK for real rules |
char(n) |
| Semi-structured data | jsonb |
json |
timestamptz stores an absolute instant (in UTC) and converts on output using the session’s TimeZone; plain timestamp is a wall-clock reading with no zone, which is almost never what an application means. pgx scans it into time.Time. uuidv7() values start with a timestamp, so new rows land at the end of the primary key index rather than at random positions.
A job-processing schema sketch
CREATE TABLE tenants (
id text PRIMARY KEY CHECK (id <> ''),
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE jobs (
id uuid PRIMARY KEY DEFAULT uuidv7(),
tenant_id text NOT NULL REFERENCES tenants (id),
idempotency_key text NOT NULL CHECK (idempotency_key <> ''),
request_hash text NOT NULL,
document_ref text NOT NULL,
rule_set_version text NOT NULL,
status text NOT NULL DEFAULT 'queued'
CHECK (status IN ('received', 'queued', 'running', 'succeeded', 'failed', 'cancelled')),
attempt_count integer NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
next_attempt_at timestamptz NOT NULL DEFAULT now(),
lease_owner text,
lease_expires_at timestamptz,
lease_token bigint NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT jobs_tenant_key_uniq UNIQUE (tenant_id, idempotency_key),
CONSTRAINT jobs_lease_pair CHECK ((lease_owner IS NULL) = (lease_expires_at IS NULL))
);
CREATE TABLE job_attempts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
job_id uuid NOT NULL REFERENCES jobs (id) ON DELETE CASCADE,
attempt_no integer NOT NULL CHECK (attempt_no > 0),
worker_id text NOT NULL,
lease_token bigint NOT NULL,
leased_until timestamptz NOT NULL,
started_at timestamptz NOT NULL DEFAULT now(),
ended_at timestamptz,
outcome text CHECK (outcome IN ('succeeded', 'failed', 'retryable', 'lease_expired')),
UNIQUE (job_id, attempt_no)
);
Why each piece is there:
- NOT NULL by default; a nullable column should be a decision.
- CHECK on status makes an invented state impossible. It does not enforce transitions; conditional
UPDATEs do that (see the transactions note). jobs_lease_pairsays a job either has an owner and an expiry, or neither.- UNIQUE (tenant_id, idempotency_key) is the idempotency rule itself. The key is scoped to a tenant, so two tenants can both use
document-demo-001. Naming the constraint lets Go code recognise it in errors. - REFERENCES makes orphans impossible. Attempts cascade with their job; deleting a tenant that has jobs is refused.
- UNIQUE (job_id, attempt_no) also indexes
job_id, so the foreign key needs no separate index.
Idempotent submission without a race
Checking for the key with a SELECT and then inserting is broken: two concurrent requests can both see nothing and both insert. Let the unique constraint arbitrate instead:
INSERT INTO jobs (tenant_id, idempotency_key, request_hash, document_ref, rule_set_version)
VALUES ('acme', 'document-demo-001', 'sha256:9f2c', 'docs/acme/invoice-17.pdf', 'rules-v3')
ON CONFLICT (tenant_id, idempotency_key) DO NOTHING
RETURNING id, status, created_at;
The first call returns the new row; a repeat returns zero rows, because RETURNING only reports inserted rows. If another transaction is mid-insert with the same key, ON CONFLICT waits for it to finish. In Go with pgx:
var ErrIdempotencyConflict = errors.New("idempotency key reused with a different request")
// Submit returns the job's ID and whether this call created it.
func Submit(ctx context.Context, db *pgxpool.Pool, s Submission) (id string, created bool, err error) {
err = db.QueryRow(ctx, `
INSERT INTO jobs (tenant_id, idempotency_key, request_hash, document_ref, rule_set_version)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (tenant_id, idempotency_key) DO NOTHING
RETURNING id::text`,
s.TenantID, s.Key, s.RequestHash, s.DocumentRef, s.RuleSetVersion,
).Scan(&id)
if err == nil {
return id, true, nil
}
if !errors.Is(err, pgx.ErrNoRows) {
return "", false, err
}
// The key exists: the same request is a replay, a different one is a conflict.
var hash string
err = db.QueryRow(ctx, `
SELECT id::text, request_hash FROM jobs
WHERE tenant_id = $1 AND idempotency_key = $2`,
s.TenantID, s.Key,
).Scan(&id, &hash)
if err != nil {
return "", false, err
}
if hash != s.RequestHash {
return "", false, ErrIdempotencyConflict
}
return id, false, nil
}
With 20 goroutines submitting the same key, one created the job and 19 got its ID back. The follow-up SELECT sees the winner’s row because, under the default READ COMMITTED, each statement takes a fresh snapshot.
A plain INSERT fails with SQLSTATE 23505 instead, surfaced by pgx as a *pgconn.PgError. I check pgErr.Code == "23505" and pgErr.ConstraintName == "jobs_tenant_key_uniq" and map it to a domain error rather than leaking a raw SQL message.
Gotchas
- DDL is transactional. Unlike Oracle,
CREATE TABLEinsideBEGIN ... ROLLBACKreally is rolled back, so a failed goose migration can leave nothing behind. - Empty string is not NULL. Oracle treats
''asNULL; Postgres does not, hence the<> ''checks. - Unquoted identifiers fold to lower case.
CREATE TABLE Jobscreatesjobs; quoting makes a name case-sensitive forever. - Deleting the container is fine; deleting the volume is not.
docker rm pgkeeps data inpgdata;docker volume rm pgdataordocker compose down -vis permanent. localhostinside a container is the container. Another container reaches Postgres by service name on a shared network, as the Docker Compose note shows.
Check yourself
- Where should the volume be mounted for
postgres:18, and why did the image move away from/var/lib/postgresql/data? - What does
timestamptzactually store, and what changes when the sessionTimeZonechanges? - Why is a
SELECTfollowed by anINSERTnot enough to enforce idempotency keys, and what does the unique constraint do instead? - Which constraints in the sketch create indexes automatically, and why does
job_attempts.job_idnot need its own? - Why does
INSERT ... ON CONFLICT DO NOTHING RETURNING idreturn no rows on a replay, and how does the Go code tell a replay from a conflict?