PostgreSQL Tutorial — Concepts, Setup & Advanced Queries

1 Core Concepts

MVCC

Multi-Version Concurrency Control — PostgreSQL never locks rows for reads. Each transaction sees a consistent snapshot. Writers create new row versions; VACUUM reclaims dead tuples. This enables high concurrency without read locks.

JSONB

Binary JSON column type with full indexing support. Store semi-structured agent tool results, LLM responses, and metadata. Use GIN indexes for fast containment queries (@>) and path operators (->>).

CTEs

Common Table Expressions (WITH clauses) break complex queries into readable named subqueries. Recursive CTEs traverse tree structures like agent conversation histories and org hierarchies.

Window Functions

Compute aggregates across row sets without collapsing groups. ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(). Essential for time-series analysis on agent execution logs.

Index Types

B-tree (default, equality/range), GIN (JSONB containment, full-text, arrays), GiST (geometric, range types), BRIN (large sequential data like time-series). Choose based on query patterns.

Partitioning

Split large tables into smaller partitions by range (date), list (category), or hash (even distribution). Enables partition pruning in queries, faster VACUUM, and independent partition maintenance.

LISTEN / NOTIFY

Built-in pub/sub messaging. Agents can LISTEN on channels and receive real-time NOTIFY events from triggers or other transactions — no external message broker required for simple event-driven workflows.

Extensions

PostgreSQL's plugin architecture. Key extensions: pgvector (vector search), pg_cron (scheduled jobs), pg_stat_statements (query analytics), PostGIS (geospatial), pgcrypto (encryption).

2 Index Types Comparison

Index	Best For	Operators	Use Case
B-tree	Equality, range, sorting	`=` `<` `>` `BETWEEN` `IN`	Primary keys, timestamps, foreign keys
GIN	Containment, full-text	`@>` `?` `@@` `&&`	JSONB queries, array lookups, tsvector
GiST	Geometric, range overlap	`&&` `@>` `<->` `~=`	PostGIS geospatial, range types, proximity
BRIN	Large sequential data	`=` `<` `>` `BETWEEN`	Time-series logs, append-only tables
Hash	Exact equality only	`=`	Session lookups, cache keys
HNSW (pgvector)	Vector similarity	`<=>` `<#>` `<->`	Embedding search, RAG retrieval

3 Setup Guide

Docker

# PostgreSQL 17 with pgvector
docker run -d \
--name postgres \
-p 5432:5432 \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_DB=agentdb \
-v pgdata:/var/lib/postgresql/data \
pgvector/pgvector:pg17
# Connect
psql -h localhost -U postgres -d agentdb

macOS (Homebrew)

# Install PostgreSQL
brew install postgresql@17
# Start service
brew services start postgresql@17
# Create database
createdb agentdb
# Connect
psql agentdb
# Install pgvector extension
brew install pgvector

Ubuntu / Debian

# Add official APT repo
sudo apt install -y \
postgresql-17
# Start and enable
sudo systemctl enable \
--now postgresql
# Create user + database
sudo -u postgres createuser myuser
sudo -u postgres createdb agentdb \
-O myuser

4 Usage Examples

JSONB Agent State Store

-- Agent execution log with JSONB
CREATE TABLE agent_runs (
id UUID DEFAULT gen_random_uuid(),
agent_name TEXT NOT NULL,
started_at TIMESTAMPTZ DEFAULT now(),
status TEXT DEFAULT 'running',
tool_calls JSONB DEFAULT '[]',
result JSONB,
PRIMARY KEY (id)
);
-- GIN index on JSONB
CREATE INDEX idx_tool_calls
ON agent_runs USING gin(tool_calls);
-- Query: find runs using a tool
SELECT id, agent_name, started_at
FROM agent_runs
WHERE tool_calls @>
'[{"name":"web_search"}]'::jsonb
ORDER BY started_at DESC
LIMIT 10;

CTEs & Window Functions

-- Agent performance over time
WITH daily_stats AS (
SELECT
date_trunc('day',
started_at) AS day,
agent_name,
count(*) AS total_runs,
count(*) FILTER (
WHERE status = 'success'
) AS successes
FROM agent_runs
GROUP BY 1, 2
)
SELECT
day, agent_name,
total_runs,
round(100.0 * successes /
total_runs, 1)
AS success_pct,
LAG(total_runs) OVER (
PARTITION BY agent_name
ORDER BY day
) AS prev_day_runs
FROM daily_stats
ORDER BY day DESC;

5 Best Practices

Connection Pooling

Each PostgreSQL connection consumes ~10 MB of RAM. Use PgBouncer (transaction-mode pooling) in front of Postgres. Set max_connections=100 on the server, pool size 20–30 in the application.

EXPLAIN ANALYZE

Always profile slow queries with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT). Look for Seq Scans on large tables, high Rows Removed by Filter, and Sort Method: external merge (insufficient work_mem).

Autovacuum Tuning

Never disable autovacuum. For write-heavy agent tables, lower autovacuum_vacuum_scale_factor to 0.05 (5%) and increase autovacuum_vacuum_cost_limit to 1000+ for faster dead tuple cleanup.

Partial Indexes

Index only the rows you query: CREATE INDEX ON agent_runs(started_at) WHERE status = 'running'. Partial indexes are dramatically smaller and faster to maintain than full-table indexes.

pg_stat_statements

Enable this extension to track query execution statistics. Identify top queries by total_exec_time, calls, and mean_exec_time. Reset stats periodically after optimization rounds.

Streaming Replication

Configure synchronous or async streaming replicas for HA and read scaling. Route agent read queries to replicas, writes to primary. Use pg_basebackup for initial replica setup and WAL archiving for PITR.

6 Scaling & Production

Read Replicas

Scale reads horizontally with streaming replicas. Route agent read queries to replicas via PgBouncer or application-level routing. Each replica adds ~100% read throughput. Async replication lag is typically <100ms.

Citus (Horizontal Sharding)

Citus extends PostgreSQL with distributed tables. Shard by tenant_id or agent_id to distribute writes across nodes. Supports co-located joins within the same shard and reference tables for small lookup data.

Partitioning at Scale

Range-partition agent logs by month. Detach and archive old partitions to cold storage. pg_partman automates partition creation and retention. Queries on recent data only scan the latest partition — 10–100× faster on tables with 1B+ rows.

Logical Replication

Replicate specific tables between databases (unlike streaming replication which copies everything). Ideal for multi-datacenter deployments, zero-downtime major version upgrades, and feeding analytics databases from production.

Connection Scaling

Each PostgreSQL connection uses ~10 MB RAM. At 1000 agent workers, that's 10 GB just for connections. Use PgBouncer in transaction mode to multiplex 1000 app connections onto 30–50 server connections.

Managed Services

Cloud SQL (GCP), RDS/Aurora (AWS), Flexible Server (Azure) handle replication, backups, and patching. Aurora PostgreSQL supports 15 read replicas with shared storage and up to 128 TB per database.

7 FAQ

PostgreSQL vs. MySQL — when to choose Postgres?→

Choose PostgreSQL when you need JSONB, advanced indexing (GIN, GiST, BRIN), window functions, CTEs, custom types, or extensibility (pgvector, PostGIS). MySQL is simpler for basic CRUD but lacks PostgreSQL's analytical query capabilities and extensibility.

How do I optimize JSONB query performance?→

Create a GIN index on the JSONB column. For specific key lookups, use expression indexes: CREATE INDEX ON t ((data->>'status')). Avoid jsonb_path_query on unindexed columns. For large documents, extract frequently queried fields into regular columns.

What are the key postgresql.conf settings to tune?→

shared_buffers: 25% of system RAM. effective_cache_size: 75% of RAM. work_mem: 64–256 MB (per operation, careful with concurrency). maintenance_work_mem: 512 MB–1 GB. random_page_cost: 1.1 for SSD. wal_level: replica (for streaming replication).

When should I partition a table?→

Consider partitioning when a table exceeds 100M+ rows or queries consistently filter on a known dimension (e.g., date range). Range partitioning by month is ideal for agent execution logs. Partition pruning eliminates scanning irrelevant partitions automatically.

How does PostgreSQL compare to dedicated vector databases?→

With the pgvector extension, PostgreSQL handles vector search alongside relational queries in a single database. It's ideal for <10M vectors and teams that want to avoid managing a separate system. For billions of vectors, sub-ms latency, or advanced features like binary quantization, use Qdrant or Milvus.