PostgreSQL

PostgreSQL is the most capable open-source relational database available. It handles everything from simple CRUD to complex analytical queries, native JSON documents, full-text search, geospatial data, and time-series workloads via TimescaleDB. If you're building an API, a SaaS product, or anything that involves non-trivial data relationships, PostgreSQL is almost certainly the right choice.

CloudSonic installs the latest stable PostgreSQL from the official PGDG repository -- not the Ubuntu package which is typically two major versions behind.

Architecture on CloudSonic

PostgreSQL doesn't sit directly on port 5432. Instead:

PostgreSQL listens on 127.0.0.1:5433 (direct access, admin only)
PgBouncer listens on 127.0.0.1:5432 (connection pooler, what your app connects to)

Your application always connects to port 5432. PgBouncer manages a pool of real database connections and hands them to your app on demand. This means you can have 200 Node.js workers hammering your API without each one holding an open PostgreSQL connection -- PgBouncer keeps the actual connection count manageable.

Note When using pgdb-create or connecting as the superuser for admin tasks, connect directly to port 5433. The credentials file saves DB_PORT=5432 for your app to use via PgBouncer.

How it's configured

RAM-based tuning happens automatically at install time:

Server RAM	shared_buffers	effective_cache_size	work_mem
16 GB	3 GB	6 GB	16 MB
8 GB	1.5 GB	3 GB	8 MB
4 GB	768 MB	1.5 GB	4 MB
Less than 4 GB	384 MB	768 MB	2 MB

shared_buffers is PostgreSQL's internal cache -- roughly 25% of RAM. effective_cache_size is a hint to the query planner about how much memory the OS has available for caching (it doesn't actually allocate this). work_mem is the memory available per sort or join operation -- conservative by default to avoid OOM when many queries run at once.

Other defaults:

random_page_cost = 1.1 -- tuned for SSD, tells the planner that random reads are nearly as fast as sequential reads
WAL compression enabled -- reduces disk writes on write-heavy workloads
pg_stat_statements preloaded -- tracks query performance, integrated with Prometheus/Grafana monitoring
TimescaleDB preloaded -- available in any database without any extra setup
scram-sha-256 authentication -- modern and secure

Extensions installed

Every database created with pgdb-create gets these extensions enabled automatically:

Extension	What it does
`uuid-ossp`	Generates UUIDs (`uuid_generate_v4()`)
`pgcrypto`	Cryptographic functions, `gen_random_uuid()`
`pg_trgm`	Trigram indexes for fast LIKE/ILIKE queries
`hstore`	Key-value pairs in a single column
`pg_stat_statements`	Query performance tracking
`timescaledb`	Time-series tables (hypertables)

Creating a database

# Auto-generates a username and password
pgdb-create myapi

# Specify credentials
pgdb-create myapi myapi_user Str0ngPassword123

Output:

==> PostgreSQL database created!

    Database   : myapi
    User       : myapi_user
    Password   : mR7pL2xQ9nK4vB6w
    Host       : 127.0.0.1
    Port       : 5432 (via PgBouncer)

    Extensions : uuid-ossp, pgcrypto, pg_trgm, hstore, pg_stat_statements

    Credentials saved to: /etc/postgresql/databases/myapi.conf

    Connection string:
      postgresql://myapi_user:mR7pL2xQ9nK4vB6w@127.0.0.1:5432/myapi

Connecting to PostgreSQL

# Connect as superuser (direct, port 5433)
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) psql -U postgres -h 127.0.0.1 -p 5433

# Connect as an application user (via PgBouncer, port 5432)
PGPASSWORD=mR7pL2xQ9nK4vB6w psql -U myapi_user -h 127.0.0.1 -p 5432 myapi

# List all databases
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) psql -U postgres -h 127.0.0.1 -p 5433 -c '\l'

Common database operations

Create a table

\c myapi

CREATE TABLE users (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    role       TEXT NOT NULL DEFAULT 'member',
    metadata   JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

Notice a few things here that you don't get in MariaDB: UUID as a native primary key type, JSONB for structured metadata with GIN indexing, and TIMESTAMPTZ which stores timezone-aware timestamps.

Insert data

INSERT INTO users (email, name, role, metadata)
VALUES (
    'sarah@example.com',
    'Sarah Chen',
    'admin',
    '{"plan": "pro", "seats": 5, "trial_ends": null}'
);

-- Insert and return the generated ID
INSERT INTO users (email, name)
VALUES ('james@example.com', 'James Okafor')
RETURNING id, created_at;

Query data

-- Basic query
SELECT id, email, name, created_at
FROM users
WHERE role = 'admin'
ORDER BY created_at DESC;

-- Query inside JSONB
SELECT email, metadata->>'plan' AS plan
FROM users
WHERE metadata->>'plan' = 'pro';

-- JSONB containment query (uses GIN index -- fast)
SELECT email, name
FROM users
WHERE metadata @> '{"plan": "pro"}';

-- Full-text search using pg_trgm
SELECT email, name
FROM users
WHERE name ILIKE '%chen%';

-- Fuzzy search with similarity score
SELECT email, name, similarity(name, 'sarah chen') AS score
FROM users
WHERE similarity(name, 'sarah chen') > 0.3
ORDER BY score DESC;

Update data

-- Update a row
UPDATE users SET role = 'owner' WHERE email = 'sarah@example.com';

-- Update a value inside JSONB
UPDATE users
SET metadata = jsonb_set(metadata, '{plan}', '"enterprise"')
WHERE email = 'sarah@example.com';

-- Update and return the modified row
UPDATE users
SET role = 'member'
WHERE id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
RETURNING id, email, role;

Delete data

-- Delete a specific row
DELETE FROM users WHERE email = 'james@example.com';

-- Delete and return what was deleted
DELETE FROM users
WHERE created_at < NOW() - INTERVAL '1 year'
AND role = 'member'
RETURNING email, name;

Warning Always run a SELECT with the same WHERE clause before running DELETE or UPDATE on production data. It takes 10 seconds and has saved many people from an unpleasant afternoon.

Transactions

BEGIN;

UPDATE accounts SET balance = balance - 250.00 WHERE id = 101;
UPDATE accounts SET balance = balance + 250.00 WHERE id = 207;

-- Check it looks right before committing
SELECT id, balance FROM accounts WHERE id IN (101, 207);

COMMIT;

-- Or if something looks wrong:
-- ROLLBACK;

Working with JSONB

PostgreSQL's JSONB column type is one of its killer features. You get flexible schema-less storage with proper indexing and querying -- not just "store a JSON string".

-- Create a table with a JSONB column
CREATE TABLE events (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users(id),
    type       TEXT NOT NULL,
    payload    JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_events_payload ON events USING GIN (payload);
CREATE INDEX idx_events_type_time ON events (type, created_at DESC);

-- Insert an event
INSERT INTO events (user_id, type, payload)
VALUES (
    'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
    'subscription.upgraded',
    '{"from": "starter", "to": "pro", "mrr_change": 49, "source": "billing_page"}'
);

-- Query events by payload content
SELECT user_id, payload->>'from' AS from_plan, payload->>'to' AS to_plan, created_at
FROM events
WHERE type = 'subscription.upgraded'
AND (payload->>'mrr_change')::int > 0
ORDER BY created_at DESC
LIMIT 20;

-- Aggregate over JSONB values
SELECT
    payload->>'to' AS plan,
    COUNT(*) AS upgrades,
    SUM((payload->>'mrr_change')::int) AS total_mrr
FROM events
WHERE type = 'subscription.upgraded'
AND created_at > NOW() - INTERVAL '30 days'
GROUP BY payload->>'to';

Add an index

-- Standard B-tree index
CREATE INDEX idx_events_user ON events (user_id);

-- Partial index (only indexes rows matching a condition -- smaller and faster)
CREATE INDEX idx_events_recent ON events (created_at DESC)
WHERE created_at > NOW() - INTERVAL '30 days';

-- Concurrent index build (doesn't lock the table -- safe on production)
CREATE INDEX CONCURRENTLY idx_events_type ON events (type);

-- List indexes on a table
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'events';

Alter a table

-- Add a column
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

-- Set a default on an existing column
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';

-- Add a foreign key
ALTER TABLE events ADD CONSTRAINT fk_events_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Rename a column
ALTER TABLE users RENAME COLUMN name TO full_name;

Importing and exporting

Export a database

# Plain SQL dump
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) \
  pg_dump -U postgres -h 127.0.0.1 -p 5433 myapi > myapi-backup.sql

# Compressed dump (recommended for large databases)
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) \
  pg_dump -U postgres -h 127.0.0.1 -p 5433 -Fc myapi > myapi-$(date +%Y%m%d).dump

# Dump a single table
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) \
  pg_dump -U postgres -h 127.0.0.1 -p 5433 -t events myapi > events-backup.sql

Import a database

# From plain SQL
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) \
  psql -U postgres -h 127.0.0.1 -p 5433 myapi < backup.sql

# From a compressed dump (pg_restore)
PGPASSWORD=$(sudo cat /etc/postgresql/superuser.conf) \
  pg_restore -U postgres -h 127.0.0.1 -p 5433 -d myapi myapi-20250610.dump

TimescaleDB -- time-series data

TimescaleDB is preloaded on every CloudSonic PostgreSQL install. It extends PostgreSQL with hypertables -- tables that automatically partition data by time, making time-series queries dramatically faster.

Good use cases: storing metrics, API request logs, IoT sensor readings, financial tick data, or any data where you're always querying by a time range.

-- Create a metrics table
CREATE TABLE server_metrics (
    time        TIMESTAMPTZ NOT NULL,
    server_id   TEXT NOT NULL,
    cpu_percent DOUBLE PRECISION,
    ram_mb      INTEGER,
    disk_io_mb  DOUBLE PRECISION
);

-- Convert it to a hypertable (partitioned by time automatically)
SELECT create_hypertable('server_metrics', 'time');

-- Create an index on server_id for fast per-server queries
CREATE INDEX idx_metrics_server ON server_metrics (server_id, time DESC);

-- Insert metrics
INSERT INTO server_metrics (time, server_id, cpu_percent, ram_mb)
VALUES
    (NOW(), 'web-01', 23.4, 1842),
    (NOW(), 'web-02', 45.1, 2103),
    (NOW() - INTERVAL '1 minute', 'web-01', 21.8, 1840);

-- Query the last hour for a specific server
SELECT time, cpu_percent, ram_mb
FROM server_metrics
WHERE server_id = 'web-01'
AND time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC;

-- Average CPU per 5-minute bucket over the last 24 hours
SELECT
    time_bucket('5 minutes', time) AS bucket,
    server_id,
    AVG(cpu_percent) AS avg_cpu
FROM server_metrics
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, server_id
ORDER BY bucket DESC;

Info TimescaleDB is what powers the Grafana/Prometheus metrics stack on CloudSonic servers. If you've enabled monitoring, your server performance data is already being stored in a TimescaleDB hypertable.

Managing users

-- List all users
\du

-- Create a read-only user
CREATE USER analytics WITH PASSWORD 'ReadOnly456!';
GRANT CONNECT ON DATABASE myapi TO analytics;
GRANT USAGE ON SCHEMA public TO analytics;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics;

-- Make new tables automatically readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analytics;

-- Drop a user
REVOKE ALL PRIVILEGES ON DATABASE myapi FROM olduser;
DROP USER olduser;

Useful psql commands

\l              -- list databases
\c myapi        -- connect to a database
\dt             -- list tables
\d users        -- describe a table (columns, indexes, constraints)
\di             -- list indexes
\du             -- list users
\x              -- toggle expanded output (useful for wide rows)
\timing         -- show query execution time
\q              -- quit

Checking database size

-- Size of all databases
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Size of tables in current database
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::text)) AS total_size,
    pg_size_pretty(pg_relation_size(tablename::text)) AS table_size,
    pg_size_pretty(pg_indexes_size(tablename::text)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::text) DESC;

Performance tips

Use EXPLAIN ANALYZE to understand slow queries:

EXPLAIN ANALYZE
SELECT u.email, COUNT(e.id) AS event_count
FROM users u
LEFT JOIN events e ON e.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.email
ORDER BY event_count DESC
LIMIT 20;

Look for Seq Scan on large tables -- that's a sign an index is missing. Index Scan or Bitmap Index Scan means the planner found an index and used it.

Run VACUUM and ANALYZE regularly. PostgreSQL handles this automatically with autovacuum, but after a large import or bulk delete it's worth running manually:

VACUUM ANALYZE events;

Check pg_stat_statements for the slowest queries across all sessions:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;