domains / data / postgresql-administration-and-schema-design / postgresql

postgresql

Skill card

combined

The three copies cover two distinct, non-overlapping procedures. The BagelHole copy is an operational runbook for installing, configuring, securing, backing up, and replicating PostgreSQL servers. The rmyndharis and sickn33 copies are identical schema design guides covering data types, constraints, indexing, and normalization. Because the operational copy lacks schema design steps and the schema copies lack operational administration steps, crowning one would hide a real alternative procedure.

Repositories

Other copies

Combined skill

# PostgreSQL Administration and Schema Design

This skill covers two distinct areas of PostgreSQL management: server administration and schema design. Use the relevant section based on your task.

## Part 1: Server Administration

Use this section when installing, configuring, securing, backing up, or replicating PostgreSQL servers.

### Installation and Setup

```bash
# Debian / Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# RHEL / Amazon Linux
sudo dnf install -y postgresql15-server postgresql15-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Verify
psql --version
sudo systemctl status postgresql
```

### Initial User and Database Setup

```bash
sudo -u postgres psql
```

```sql
CREATE USER myapp WITH PASSWORD 'strong_password_here';
CREATE DATABASE mydb OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;
```

### Configuration Tuning

Edit `/etc/postgresql/15/main/postgresql.conf` (path varies by OS and version).

```ini
listen_addresses = '*'
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB
wal_buffers = 64MB
checkpoint_completion_target = 0.9
min_wal_size = 1GB
max_wal_size = 4GB
random_page_cost = 1.1
effective_io_concurrency = 200
log_min_duration_statement = 250
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
```

```bash
sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo systemctl restart postgresql
```

### Client Authentication (pg_hba.conf)

```ini
local   all       postgres                  peer
host    mydb      myapp     10.0.0.0/8      scram-sha-256
host    all       all       0.0.0.0/0       reject
```

```bash
sudo systemctl reload postgresql
```

### Backup and Restore

```bash
# Logical backup
pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump

# Restore
pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump

# Physical backup
pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F) \
  --wal-method=stream --checkpoint=fast --progress --verbose
```

### Streaming Replication

**Primary:**
```sql
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';
```
```ini
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
```

**Replica:**
```bash
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main/*
sudo -u postgres pg_basebackup \
  -h 10.0.0.1 -U replicator \
  -D /var/lib/postgresql/15/main \
  --wal-method=stream --checkpoint=fast --progress
sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal
```
```ini
primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=repl_secret'
hot_standby = on
```
```bash
sudo systemctl start postgresql
```

### Monitoring

```sql
SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;
```

## Part 2: Schema Design

Use this section when designing tables, selecting data types, or planning indexes.

### Instructions

1. Capture entities, access patterns, and scale targets (rows, QPS, retention).
2. Choose data types and constraints that enforce invariants.
3. Add indexes for real query paths and validate with `EXPLAIN`.
4. Plan partitioning or RLS where required by scale or access control.
5. Review migration impact and apply changes safely.

### Safety

- Avoid destructive DDL on production without backups and a rollback plan.
- Use migrations and staging validation before applying schema changes.

### Core Rules

- Define a **PRIMARY KEY** for reference tables. Prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed.
- **Normalize first (to 3NF)**; denormalize only for measured, high-ROI reads.
- Add **NOT NULL** everywhere it’s semantically required; use **DEFAULT**s for common values.
- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.
- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values.

### PostgreSQL Gotchas

- **Identifiers**: unquoted → lowercased. Use `snake_case`.
- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL.
- **FK indexes**: PostgreSQL does not auto-index FK columns. Add them.
- **No silent coercions**: length/precision overflows error out.
- **Sequences/identity have gaps**: This is expected behavior.
- **Heap storage**: no clustered PK by default.
- **MVCC**: updates/deletes leave dead tuples; vacuum handles them.

### Data Types

- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred; `UUID` for distributed systems.
- **Integers**: prefer `BIGINT` unless storage space is critical.
- **Floats**: prefer `DOUBLE PRECISION`; use `NUMERIC` for exact decimal arithmetic.
- **Strings**: prefer `TEXT`; use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`.
- **Money**: `NUMERIC(p,s)` (never float).
- **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations.
- **Booleans**: `BOOLEAN` with `NOT NULL` constraint.
- **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets; use TEXT + CHECK for evolving values.
- **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Index with GIN for containment queries.
- **Range types**: `daterange`, `numrange`, `tstzrange`. Index with GiST.
- **Network types**: `INET`, `CIDR`, `MACADDR`.
- **Geometric types**: `POINT`, `LINE`, `POLYGON`, `CIRCLE`. Index with GiST.
- **Text search**: `TSVECTOR` for full-text search documents.