PostgreSQL — The World's Most Advanced Open-Source Database

Overview

PostgreSQL (also known as Postgres or PG) is a powerful, open-source object-relational database system with over 40 years of active development. Born at UC Berkeley in 1985 as a successor to the Ingres database, it has earned a reputation for reliability, feature robustness, and extensibility. PostgreSQL supports both relational (SQL) and non-relational (JSON) querying, making it a versatile choice for applications of any scale.

PostgreSQL uses Multi-Version Concurrency Control (MVCC) — every transaction gets a snapshot of the data, allowing extremely efficient concurrent reads without blocking writes.

[!TIP] PostgreSQL is often called "the Toyota of databases" — it's battle-tested, fast, and powers most major platforms. It has consistently ranked as the most loved database in developer surveys.

Installation

Linux (Debian / Ubuntu)

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# Start the service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
psql --version

Linux (RHEL / Fedora / Rocky)

# Install PostgreSQL
sudo dnf install postgresql-server postgresql-contrib

# Initialize and start
sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16
psql postgres

Docker

docker run --name pg -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres:16
psql -h localhost -U postgres -d postgres

[!NOTE] The default port is 5432. The default database created on init is named postgres.

Connect to PostgreSQL

# Full explicit form:
psql -h localhost -p 5432 -U <username> -d postgres

# Shortcut (same as above with default user):
psql postgres

CLI Tools Comparison

Tool	Description
psql	Official PostgreSQL CLI, minimal but powerful
pgcli	Third-party CLI with syntax highlighting and auto-completion

Install pgcli:

pip install pgcli
pgcli postgres

Useful psql Meta-Commands

Command	Description
\l	List all databases
\dt	List all tables in current database
\d <table>	Describe table schema
\du	List roles/users
\c <dbname>	Connect to another database
\q	Quit
\! <cmd>	Execute shell command (e.g., \! clear)

---

Core SQL Features

Creating a Database and Table

CREATE DATABASE myapp;

\c myapp

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

SELECT * FROM users;

Composite Types

PostgreSQL supports custom composite types — essentially user-defined structured data types stored in a single column.

CREATE TYPE address AS (
    street TEXT,
    city TEXT
);

CREATE TABLE customers (
    name TEXT,
    shipping_to address
);

-- Insert with ROW constructor
INSERT INTO customers VALUES ('Homer Simpson', ROW('742 Evergreen Terrace', 'Springfield'));

-- Insert without ROW (implicit)
INSERT INTO customers VALUES ('Marge Simpson', ('742 Evergreen Terrace', 'Springfield'));

-- Query nested fields
SELECT name, (shipping_to).city FROM customers;

-- Filter by nested field
SELECT * FROM customers WHERE (shipping_to).city = 'Springfield';

JSON / JSONB — Unstructured Data

PostgreSQL can store and query JSON documents, rivaling NoSQL databases.

• JSON — stores an exact copy of the input text (preserves formatting)
• JSONB — stores a decomposed binary format (slower to insert, but indexable and queryable)

CREATE TABLE users_json (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    profile JSONB
);

INSERT INTO users_json (name, profile) VALUES
    ('Homer Simpson', '{"city": "Springfield", "age": 39, "occupation": "Safety Inspector"}');

-- Query JSON fields with -> and ->> operators
SELECT name, profile->>'city' AS city FROM users_json;
SELECT * FROM users_json WHERE profile->>'city' = 'Springfield';

-- Check if a key exists (? operator)
SELECT * FROM users_json WHERE profile ? 'occupation';

-- Update a JSON field
UPDATE users_json
SET profile = JSONB_SET(profile, '{age}', '20'::JSONB)
WHERE name = 'Homer Simpson';

Operator	Description	Example
->	Get JSON field as JSON	profile->'city'
->>	Get JSON field as text	profile->>'city'
?	Does key exist?	profile ? 'occupation'
@>	Does JSON contain?	profile @> '{"city":"Springfield"}'

Transactions and MVCC

PostgreSQL's MVCC model gives each transaction a consistent snapshot of data. Writes do not block reads, but concurrent writes block each other.

-- Session 1
BEGIN;
SELECT * FROM users;  -- sees snapshot
UPDATE users SET name = 'Lisa' WHERE id = 1;
-- Don't commit yet

-- Session 2 (concurrent)
BEGIN;
SELECT * FROM users;  -- sees original data (not Session 1's uncommitted change)
COMMIT;

-- Back in Session 1
COMMIT;  -- now Session 2 sees the change on next read
-- or
ROLLBACK;  -- discard changes

[!TIP] Use BEGIN/COMMIT/ROLLBACK to safely test data manipulations on a snapshot before making them permanent.

---

Major Extensions

Extensions are PostgreSQL's superpower. They let you turn PG into a full backend stack: cache server, message queue, job scheduler, geospatial database, vector database, time-series database, and more.

Installing Extensions

# Most extensions come with postgresql-contrib
# For community extensions, use PGXN:
sudo apt install postgresql-16-pgxn  # Debian/Ubuntu
pgxn install <extension_name>

Once installed at the OS level, enable it inside the database:

CREATE EXTENSION <extension_name>;

To see all available and installed extensions:

SELECT * FROM pg_available_extensions;
SELECT * FROM pg_extension;

1. pg_stat_statements — Query Performance Monitoring

Purpose: Tracks execution statistics of all SQL statements for performance tuning.

CREATE EXTENSION pg_stat_statements;

-- Find the top 5 slowest queries
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Installation:

# Already included in postgresql-contrib
sudo apt install postgresql-contrib

2. uuid-ossp — UUID Generation

Purpose: Generate universally unique identifiers.

CREATE EXTENSION "uuid-ossp";

SELECT uuid_generate_v4();  -- random UUID

3. hstore — Key-Value Store

Purpose: Store sets of key-value pairs within a single column.

CREATE EXTENSION hstore;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes hstore
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop', 'color => silver, weight => 1.5kg, ram => 16GB');

-- Query keys
SELECT name, attributes->'color' AS color FROM products;
SELECT * FROM products WHERE attributes ? 'ram';
SELECT * FROM products WHERE attributes @> 'color => silver';

4. PostGIS — Geospatial Data

Purpose: Store, index, and query geographic objects. The most advanced open-source geospatial database extension.

# Installation
sudo apt install postgresql-16-postgis-3

CREATE EXTENSION postgis;

CREATE TABLE coffee_shops (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(Point, 4326)
);

-- Create spatial index
CREATE INDEX idx_coffee_location ON coffee_shops USING GIST (location);

-- Insert locations (longitude, latitude)
INSERT INTO coffee_shops (name, location) VALUES
    ('Central Perk', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)),
    ('Blue Bottle',  ST_SetSRID(ST_MakePoint(-73.9860, 40.7490), 4326));

-- Find nearest coffee shop (distance in meters)
SELECT name, ST_Distance(
    location,
    ST_SetSRID(ST_MakePoint(-73.9855, 40.7480), 4326)::GEOGRAPHY
) AS distance_meters
FROM coffee_shops
ORDER BY location <-> ST_SetSRID(ST_MakePoint(-73.9855, 40.7480), 4326)::GEOGRAPHY
LIMIT 1;

[!NOTE] PostGIS is production-grade — used by NASA, the US Census Bureau, and millions of mapping applications worldwide.

5. pgvector — Vector / Embedding Search

Purpose: Store and query vector embeddings for AI/ML similarity search (semantic search, RAG, recommendations).

# Installation
cd /tmp
git clone --branch v0.7.0 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

CREATE EXTENSION vector;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(384)  -- 384-dimensional embeddings
);

-- Create an index for fast approximate nearest neighbor search
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Insert embeddings
INSERT INTO documents (content, embedding) VALUES
    ('PostgreSQL is a powerful database', '[0.1, 0.2, ...]'::VECTOR(384)),
    ('Vector search enables semantic lookup', '[0.3, 0.1, ...]'::VECTOR(384));

-- Similarity search (nearest neighbors by cosine distance)
SELECT content, 1 - (embedding <=> '[0.15, 0.18, ...]'::VECTOR(384)) AS similarity
FROM documents
ORDER BY embedding <=> '[0.15, 0.18, ...]'::VECTOR(384)
LIMIT 5;

Operator	Distance Type
<->	Euclidean (L2)
<=>	Cosine
<#>	Inner product (dot)

6. pg_cron — Job Scheduling

Purpose: Run scheduled maintenance and automation jobs inside the database itself.

[!WARNING] pg_cron requires adding it to shared_preload_libraries in postgresql.conf and a server restart.

# Installation
sudo apt install postgresql-16-cron

-- Enable (requires config change + restart first)
CREATE EXTENSION pg_cron;

-- Schedule a job that runs every hour
SELECT cron.schedule('hourly-cache-cleanup', '0 * * * *', $$CALL expire_rows(60)$$);

-- View scheduled jobs
SELECT * FROM cron.job;

-- Remove a scheduled job
SELECT cron.unschedule('hourly-cache-cleanup');

Configuration (postgresql.conf):

shared_preload_libraries = 'pg_cron'
cron.database_name = 'myapp'

[!NOTE] Find your config file location with: SHOW config_file;

7. pgmq — Message Queue

Purpose: Lightweight message queue with API parity to AWS SQS.

pgxn install pgmq

CREATE EXTENSION pgmq;

-- Create a queue
SELECT pgmq.create('my_queue');

-- Send a message
SELECT pgmq.send('my_queue', '{"task": "process_order", "order_id": 123}');

-- Read a message (with visibility timeout — invisible for N seconds)
SELECT pgmq.read('my_queue', 30, 1);

-- Pop a message (read + delete immediately)
SELECT pgmq.pop('my_queue');

-- Archive a processed message
SELECT pgmq.archive('my_queue', 1);

-- Delete a message after processing
SELECT pgmq.delete('my_queue', 1);

[!TIP] Use pgmq.read() with a visibility timeout for reliable processing — if the worker crashes, the message reappears after the timeout. Use pgmq.pop() only when you're sure processing will succeed.

8. TimescaleDB — Time-Series Data

Purpose: Turn PostgreSQL into a time-series database, comparable to InfluxDB. Auto-partitions (chunks) data by time.

# Installation
sudo apt install timescaledb-2-postgresql-16
# Then add to shared_preload_libraries and restart

CREATE EXTENSION timescaledb;

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature FLOAT,
    humidity FLOAT
);

-- Convert to hypertable (auto-partitions by time)
SELECT create_hypertable('sensor_data', 'time');

-- Time-bucketed aggregation
SELECT time_bucket('1 hour', time) AS hour,
       sensor_id,
       AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY hour, sensor_id
ORDER BY hour DESC;

9. pg_trgm — Fuzzy Text Search

Purpose: Fast fuzzy string matching using trigrams. Replaces basic full-text search needs.

CREATE EXTENSION pg_trgm;

-- Find similar strings
SELECT similarity('postgresql', 'postgres');

-- GIN index for fast fuzzy lookups
CREATE INDEX ON articles USING GIN (title gin_trgm_ops);

-- Fuzzy search
SELECT * FROM articles WHERE title % 'postgress';

10. unaccent — Accent-Insensitive Search

Purpose: Remove diacritics from text for accent-insensitive search.

CREATE EXTENSION unaccent;

SELECT unaccent('PostgreSQL est génial');  -- 'PostgreSQL est genial'

-- Create a deterministic collation with unaccent
CREATE INDEX ON users (unaccent(name));
SELECT * FROM users WHERE unaccent(name) ILIKE unaccent('%genial%');

---

Advanced: Cache with Unlogged Tables

Unlogged tables skip the Write-Ahead Log (WAL), making them significantly faster for cache-like use cases. They are automatically truncated on crash (acceptable for cache).

-- Create an unlogged cache table
CREATE UNLOGGED TABLE cache (
    key TEXT PRIMARY KEY,
    value JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_cache_created ON cache (created_at);

-- Procedure to expire old entries
CREATE OR REPLACE PROCEDURE expire_rows(retention_minutes INT)
LANGUAGE SQL AS
$$
    DELETE FROM cache
    WHERE created_at < NOW() - (retention_minutes || ' minutes')::INTERVAL;
$$;

-- Schedule with pg_cron (runs every hour)
SELECT cron.schedule('cache-expiry', '0 * * * *', $$CALL expire_rows(60)$$);

---

Advanced: Pub/Sub with LISTEN / NOTIFY

PostgreSQL can act as a lightweight message broker using built-in LISTEN/NOTIFY.

-- Session 1 (listener)
LISTEN new_jobs;

-- Session 2 (notifier)
CREATE TABLE jobs (
    id SERIAL PRIMARY KEY,
    task TEXT,
    processed BOOLEAN DEFAULT FALSE
);

INSERT INTO jobs (task) VALUES ('Process order 123');
NOTIFY new_jobs, 'New job created: 1';

-- Back in Session 1 — a notification is received on the channel

[!NOTE] For production-grade queues, use pgmq instead of raw LISTEN/NOTIFY. The extension handles delivery guarantees, visibility timeouts, and dead-letter queues.

---

Configuration

Finding the Config File

SHOW config_file;

Performance Tuning

Key parameters to tweak (adjust based on available memory):

# ~25% of total RAM
shared_buffers = 2GB

# ~75% of total RAM for sorts/hash
work_mem = 128MB

# WAL size
wal_buffers = 64MB

# Max parallel workers (one per CPU core)
max_parallel_workers_per_gather = 4

[!TIP] Use PGTune to generate optimized configurations for your server specs.

---

Best Practices

1. Always use JSONB over JSON — JSONB is indexable and queryable; JSON is just text.
2. Use SERIAL or IDENTITY for auto-incrementing primary keys.
3. Create indexes on columns used in WHERE, JOIN, and ORDER BY.
4. Use EXPLAIN ANALYZE to understand query plans before optimizing.
5. For time-series data, use TimescaleDB hypertables.
6. For geospatial data, use PostGIS — not manual lat/lng columns.
7. Use pg_stat_statements to identify slow queries in production.
8. Backup regularly with pg_dump or pg_basebackup.
9. Monitor connection limits — each connection consumes ~10MB of RAM.
10. Use connection poolers (PgBouncer, Pgpool-II) in production.

---

References

• PostgreSQL Official Documentation
• PGXN — PostgreSQL Extension Network
• PostGIS Documentation
• pgvector GitHub
• TimescaleDB Documentation
• PGTune — Configuration Generator
• YouTube Reference: PostgreSQL Full Course by NetworkChuck / TechWithTim (transcript included in this article)