PostgreSQL - Advanced Relational Database

Official site: https://www.postgresql.org
GitHub: https://github.com/postgres/postgres (14K+ stars)
Documentation: https://www.postgresql.org/docs/

Key features:
- ACID transactions with MVCC
- Extensible (user-defined types, functions, operators)
- Advanced indexing (B-tree, GIN, GiST, BRIN, etc.)
- Full JSON/JSONB support
- Foreign data wrappers for distributed queries
- Point-in-time recovery (PITR)
- Tablespaces for storage management

# Option 1: Docker (recommended for development)
docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=mydatabase \
  -p 5432:5432 \
  postgres:16

# Option 2: Homebrew (macOS)
brew install postgresql@16
brew services start postgresql@16

# Option 3: APT (Debian/Ubuntu)
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

# Option 4: YUM (RHEL/CentOS/Fedora)
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl start postgresql

# Option 5: Windows (Chocolatey)
choco install postgresql

# Verify installation
psql --version
psql -U postgres -c "SELECT version();"

# Connect to database
psql -U postgres
# or with password:
psql -U postgres -W

# Install build dependencies
sudo apt update
sudo apt install -y \
  build-essential \
  libreadline-dev \
  zlib1g-dev \
  libssl-dev \
  libxml2-dev \
  libjson-c-dev \
  libkrb5-dev \
  wget \
  curl

# Download PostgreSQL source (version 16 latest stable)
wed https://ftp.postgresql.org/pub/source/v16.3/postgresql-16.3.tar.gz
tar xzf postgresql-16.3.tar.gz
cd postgresql-16.3

# Configure build
# Default install location: /usr/local/pgsql
./configure --prefix=/usr/local/pgsql \
            --with-openssl \
            --with-libxml \
            --with-libjson \
            --with-python \
            --with-perl \
            --with-tcl

# Build (use -j for parallel compilation)
make -j$(nproc)

# Run regression tests
make install-world

# Install
sudo make install

# Create directories for data and configuration
sudo mkdir -p /usr/local/pgsql/data
sudo chown $USER /usr/local/pgsql/data

# Initialize database cluster
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

# Start PostgreSQL
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

# Add to PATH
export PATH=/usr/local/pgsql/bin:$PATH

# Verify installation
psql --version
psql -c "SELECT version();"

# postgresql.conf - Essential settings

# Connections
listen_addresses = '*'          # Accept connections from anywhere (use 'localhost' for local only)
port = 5432                     # Default port
max_connections = 100           # Increase for production

# Memory Tuning (adjust based on available RAM)
shared_buffers = 256MB          # 25% of RAM, minimum 128MB
effective_cache_size = 2GB      # 50-75% of RAM
work_mem = 16MB                # Per-operation memory
maintenance_work_mem = 256MB    # For VACUUM, CREATE INDEX

# WAL Settings
wal_level = replica            # 'minimal', 'replica', or 'logical'
max_wal_size = 2GB             # Before autovacuum triggers
min_wal_size = 1GB

# Logging (enable for production)
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'ddl'          # Log all DDL or 'mod' for DML too
log_min_duration_statement = 1000  # Log queries taking > 1s

# Autovacuum (keep enabled!)
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

# pg_hba.conf - Client authentication (simplified)
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

-- Start psql
psql -U postgres

-- Create a database
CREATE DATABASE myapp;

-- Connect to it
\c myapp

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true
);

-- Insert data
INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

-- Query data
SELECT * FROM users;
SELECT username, email FROM users WHERE is_active = true;

-- Update data
UPDATE users SET is_active = false WHERE username = 'bob';

-- Delete data
DELETE FROM users WHERE username = 'charlie';

-- Complex query with aggregation
SELECT 
    COUNT(*) as total_users,
    SUM(CASE WHEN is_active THEN 1 ELSE 0 END) as active_users
FROM users;

-- Use CTEs (Common Table Expressions)
WITH active_users AS (
    SELECT * FROM users WHERE is_active = true
)
SELECT * FROM active_users;

-- Window functions
SELECT 
    username,
    email,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num,
    RANK() OVER (ORDER BY created_at DESC) as rank
FROM users;

-- Exit psql
\q

-- Create table with JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB,
    metadata JSONB
);

-- Insert JSON data
INSERT INTO products (name, attributes, metadata) VALUES
    ('Laptop', 
     '{"cpu": "i7", "ram": 16, "storage": "512GB SSD"}',
     '{"color": "silver", "warranty": "2 years", "tags": ["electronics", "portable"]}');

-- Query JSONB data
SELECT name, attributes->>'cpu' as cpu FROM products;
SELECT name, attributes->'ram' as ram FROM products;

-- Query nested JSON
SELECT * FROM products 
WHERE attributes @> '{"cpu": "i7"}';

-- Check if JSON contains key
SELECT * FROM products 
WHERE attributes ? 'cpu';

-- Array operations
SELECT metadata->'tags'->>0 as first_tag FROM products;

-- Update JSON field
UPDATE products 
SET attributes = attributes || '{"battery": "8 hours"}'
WHERE id = 1;

-- Create index on JSON field for faster queries
CREATE INDEX idx_products_cpu ON products ((attributes->>'cpu'));
CREATE INDEX idx_products_tags ON products USING GIN (metadata->'tags');

-- Query with JSON aggregation
SELECT json_agg(row_to_json(p.*))
FROM (
    SELECT id, name, attributes->>'cpu' as cpu
    FROM products
) p;

-- Create table with full-text search
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    search_vector tsvector
);

-- Create search vector from text
UPDATE articles 
SET search_vector = to_tsvector('english', title || ' ' || content);

-- Create GIN index for fast search
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Search for documents
SELECT id, title 
FROM articles 
WHERE search_vector @@ to_tsquery('english', 'database & postgresql');

-- Ranked search (most relevant first)
SELECT id, title, 
       ts_rank(search_vector, to_tsquery('english', 'database optimization')) as rank
FROM articles 
WHERE search_vector @@ to_tsquery('english', 'database optimization')
ORDER BY rank DESC;

-- Phrase search
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('english', ''"database optimization"'');

-- Search with stemming (finds "optimize", "optimized", etc.)
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('english', 'optimization');

-- Update trigger for automatic search vector maintenance
CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_vector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Fuzzy search with plainto_tsquery (more user-friendly)
SELECT * FROM articles 
WHERE search_vector @@ plainto_tsquery('english', 'database AND postgresql');

-- Highlighting search results
SELECT id, title,
       highlight(search_vector, to_tsquery('english', 'database')) as snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');

-- B-tree index (default, most common)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active);

-- Composite index for multi-column queries
CREATE INDEX idx_users_active_email ON users(is_active, email);

-- Partial index (only for subset of data)
CREATE INDEX idx_users_active_only ON users(email) WHERE is_active = true;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- GIN index for JSONB columns
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

-- GIN index for arrays
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name TEXT[]
);
CREATE INDEX idx_tags_name ON tags USING GIN(name);

-- BRIN index for large tables with natural ordering
CREATE TABLE sensor_readings (
    id SERIAL,
    timestamp TIMESTAMP,
    value DECIMAL
);
CREATE INDEX idx_sensor_timestamp ON sensor_readings USING BRIN (timestamp);

-- Concurrent index creation (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column);

-- Check index usage
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- View indexes on table
\d users

-- Index statistics
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

# Check current configuration
psql -c "SHOW shared_buffers;"
psql -c "SHOW work_mem;"
psql -c "SHOW effective_cache_size;"

# Analyze slow queries
psql -c "SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;"

# Check table statistics
psql -c "SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;"

# Run VACUUM to clean up dead tuples
VACUUM ANALYZE;

# Check index usage
psql -c "SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC;"

# Analyze specific query
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

# Autovacuum configuration (postgresql.conf)
# autovacuum = on
# autovacuum_max_workers = 3
# autovacuum_naptime = 1min
# autovacuum_vacuum_threshold = 50
# autovacuum_analyze_threshold = 50

# Connection pooling with pgbouncer
# sudo apt install pgbouncer
# Edit /etc/pgbouncer/pgbouncer.ini
# [databases]
# postgres = host=127.0.0.1 port=5432
# [pgbouncer]
# listen_port = 6432
# auth_type = md5

# Backup single database (custom format, compressed)
pg_dump -h localhost -U postgres -Fc -f backup.dump mydatabase

# Backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql

# Backup specific table
pg_dump -h localhost -U postgres -t users -f users.sql mydatabase

# Restore from custom format
pg_restore -h localhost -U postgres -d mydatabase backup.dump

# Restore from SQL dump
psql -h localhost -U postgres mydatabase < backup.sql

# Create backup with WAL for point-in-time recovery
# Enable in postgresql.conf:
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

# Schedule daily backups with cron
crontab -e
# 0 2 * * * pg_dump -U postgres mydatabase | gzip > /backups/mydatabase-$(date +\%Y\%m\%d).sql.gz

# Verify backup
pg_restore -l backup.dump

# Backup size check
ls -lh /backups/*.sql.gz

# Point-in-time recovery
# 1. Stop PostgreSQL
# 2. Remove data directory contents
# 3. Restore from base backup
# 4. Configure recovery target in postgresql.conf
# recovery_target_time = '2024-01-15 10:00:00'
# 5. Start PostgreSQL

-- List available extensions
\dx

-- Install pg_stat_statements for query analysis
CREATE EXTENSION pg_stat_statements;

-- View query statistics
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Install UUID support
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create table with UUID primary key
CREATE TABLE sessions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert with UUID
INSERT INTO sessions (user_id) VALUES (1);
SELECT * FROM sessions;

-- Install PostGIS for GIS (requires system package)
-- sudo apt install postgresql-16-postgis-3
CREATE EXTENSION postgis;

-- Create table with geometry
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

-- Insert geographic data
INSERT INTO locations (name, geom) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326));

-- Query distance between points
SELECT name, 
       ST_Distance(
           geom, 
           ST_GeomFromText('POINT(-73.9352 40.7306)', 4326)
       ) as distance_meters
FROM locations;

-- Install pg_trgm for text similarity
CREATE EXTENSION pg_trgm;

-- Similar string search
SELECT 'postgresql' || '' as similarity,
       similarity('postgresql', 'postgres') as similarity_score;

-- Create trigram index
CREATE INDEX idx_users_username_trgm ON users USING GIN (username gin_trgm_ops);

-- Fuzzy search
SELECT * FROM users 
WHERE username ILIKE '%john%';

-- Install pgcrypto for encryption
CREATE EXTENSION pgcrypto;

-- Encrypt/decrypt data
SELECT pgp_sym_encrypt('secret data', 'secret key');
SELECT pgp_sym_decrypt(encrypted_column, 'secret key') FROM table;

-- Install hstore for key-value
CREATE EXTENSION hstore;

-- Use hstore column
CREATE TABLE config (
    id SERIAL PRIMARY KEY,
    settings hstore
);

INSERT INTO config (settings) VALUES ('key1=>value1, key2=>value2');
SELECT settings->'key1' FROM config;

# Configure primary server (postgresql.conf)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64MB
hot_standby = on

# Configure pg_hba.conf for replica connections
host    replication     replicator      10.0.0.0/24       scram-sha-256

# Create replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

# Take base backup on primary
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/backup -Fp -Xs -P

# Configure standby server (postgresql.conf)
primary_conninfo = 'host=primary_host port=5432 user=replicator password=secure_password'
hot_standby = on

# Recovery configuration (standby.signal file)
# Create empty file to indicate this is a standby
touch /var/lib/postgresql/data/standby.signal

# Start standby server
pg_ctl -D /var/lib/postgresql/data start

# Verify replication status (on primary)
SELECT * FROM pg_stat_replication;

# Check replica lag
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

# Query on standby (read-only)
psql -h standby_host -U postgres -c "SELECT * FROM users;"

# Synchronous replication (stronger consistency)
# In postgresql.conf:
synchronous_standby_names = 'standby1'

# Patroni for automatic failover (recommended for production)
# Install Patroni on all nodes
# pip install patroni
# Configure with etcd or Consul for consensus

# Python with psycopg3 (latest)
import psycopg

conn = psycopg.connect(
    host="localhost",
    port=5432,
    database="mydb",
    user="postgres",
    password="mypassword"
)

with conn.cursor() as cur:
    cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)",
                ("alice", "alice@example.com"))
    cur.execute("SELECT * FROM users WHERE is_active = %s", (True,))
    rows = cur.fetchall()
    for row in rows:
        print(row)

conn.commit()
conn.close()

# Python with SQLAlchemy ORM
from sqlalchemy import create_engine, Column, Integer, String, Boolean
from sqlalchemy.orm import declarative_base, sessionmaker

engine = create_engine('postgresql://postgres:mypassword@localhost/mydb')
Base = declarative_base()
SessionLocal = sessionmaker(bind=engine)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    email = Column(String(100), unique=True)
    is_active = Column(Boolean, default=True)

# Create tables
Base.metadata.create_all(engine)

# Use session
session = SessionLocal()
user = User(username="bob", email="bob@example.com")
session.add(user)
session.commit()

# Query
users = session.query(User).filter(User.is_active == True).all()
for u in users:
    print(u.username)

# Node.js with pg
const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'postgres',
    password: 'mypassword'
});

async function query() {
    const result = await pool.query('SELECT * FROM users WHERE is_active = $1', [true]);
    console.log(result.rows);
}

query().then(() => pool.end());

# Go with pgx
import "github.com/jackc/pgx/v5"

conn, err := pgx.Connect(ctx, "postgres://postgres:mypassword@localhost:5432/mydb")
if err != nil {
    log.Fatal("Failed to connect:", err)
}
defer conn.Close(ctx)

var username string
err = conn.QueryRow(ctx, "SELECT username FROM users WHERE id = $1", 1).Scan(&username)
if err != nil {
    log.Fatal("Query failed:", err)
}
fmt.Println(username)

# Java with JDBC
Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/mydb",
    "postgres", "mypassword"
);

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
stmt.setInt(1, 1);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
    String username = rs.getString("username");
    System.out.println(username);
}

-- View current queries
SELECT pid, usename, application_name, state, query, 
       state_change::text as duration
FROM pg_stat_activity
WHERE state != 'idle';

-- Database size
SELECT pg_size_pretty(pg_database_size('mydatabase')) as size;

-- Table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index sizes
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Cache hit ratio (should be >95%)
SELECT 
    datname,
    round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 2) as cache_hit_ratio
FROM pg_statio_user_tables
GROUP BY datname;

-- Locks and blocking queries
SELECT 
    blocked_locks.pid as blocked_pid,
    blocked_activity.usename as blocked_user,
    blocked_activity.query as blocked_query,
    blocking_locks.pid as blocking_pid,
    blocking_activity.usename as blocking_user,
    blocking_activity.query as blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

-- Slow queries (requires pg_stat_statements)
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
WHERE mean_time > 100  -- queries taking >100ms
ORDER BY mean_time DESC
LIMIT 20;

-- Connection count by database
SELECT datname, numbackends as connections
FROM pg_stat_database
WHERE datname != 'template0' AND datname != 'template1';

-- Terminate problematic query
SELECT pg_terminate_backend(12345);

-- Check for table bloat
SELECT 
    schemaname,
    tablename,
    n_live_tup as live_rows,
    n_dead_tup as dead_rows,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_percent DESC;

Official site: https://www.postgresql.org
GitHub: https://github.com/postgres/postgres
Documentation: https://www.postgresql.org/docs/
Tutorial: https://www.postgresqltutorial.com/

Downloads: https://www.postgresql.org/download/
Extensions catalog: https://pgextensions.org/
Status (versions): https://www.postgresql.org/support/versioning/