Mastering PostgreSQL Administration: A Complete WordPress Blog Series

Blog Post 1: Introduction to PostgreSQL Administration

**Category:** PostgreSQL Basics
**Tags:** postgresql, database-administration, dba, introduction

### What is PostgreSQL?

PostgreSQL is an advanced object-relational database system emphasizing correctness, extensibility, and long-term reliability. Unlike many commercial databases, PostgreSQL exposes its internals transparently, which allows administrators to reason deeply about behavior under load.

### The Role of a PostgreSQL Administrator

A PostgreSQL administrator (Postgres DBA) is responsible for:

* **Availability and durability**: Ensuring the database is accessible and data is protected against loss.
* **Performance and scalability**: Tuning systems to handle growing workloads efficiently.
* **Security and compliance**: Implementing access controls, encryption, and audit trails.
* **Backup, recovery, and disaster readiness**: Designing and testing procedures for data restoration.
* **Capacity planning and cost control**: Monitoring growth trends and optimizing resource utilization.

The modern DBA role extends beyond traditional boundaries into infrastructure-as-code, cloud operations, and developer collaboration.

### PostgreSQL Philosophy: Why It Matters

Key design principles that shape PostgreSQL’s behavior:

* **Correctness over convenience**: PostgreSQL will error rather than silently corrupt data or make ambiguous assumptions.
* **MVCC for concurrency**: Multi-Version Concurrency Control eliminates reader-writer locks but requires active maintenance.
* **No silent data corruption**: Checksums and careful write semantics protect integrity.
* **Extensibility via extensions**: The database can be enhanced with custom data types, functions, and indexing methods.

Understanding this philosophy helps predict how PostgreSQL will behave under edge cases and informs operational decisions.

### The PostgreSQL Ecosystem

PostgreSQL exists within a rich ecosystem:
* **Major versions**: Annual releases with minor updates every quarter.
* **Distributions**: Community PostgreSQL, EnterpriseDB, cloud vendor variants.
* **Tools and extensions**: Hundreds of contributed modules for specialized needs.
* **Cloud offerings**: Managed services from AWS, Google Cloud, Azure, and others.

Choosing the right combination for your organization requires balancing control, features, and operational overhead.

—

## Blog Post 2: PostgreSQL Architecture and Internals Explained

**Category:** PostgreSQL Internals
**Tags:** architecture, internals, processes, memory, mvcc

### Process Architecture: Why PostgreSQL Uses Processes, Not Threads

PostgreSQL uses a *process-per-connection* model, not threads.
Client
|
v
Postmaster (PID 1)
|
+– Backend Process (per connection)
+– Background Processes
+– Replication Processes (if configured)

Shared Memory:

Shared Buffers (cached data pages)

WAL Buffers (write-ahead log buffers)

Lock Tables (concurrency control)

CLOG (commit status)

Background Processes:

Checkpointer (writes dirty buffers)

WAL Writer (flushes WAL buffers)

Autovacuum Launcher (starts worker processes)

Background Writer (writes dirty buffers gradually)

Stats Collector (gathers statistics)

text

Each backend process handles one client connection, providing isolation but with higher memory overhead compared to thread-based models.

### Memory Architecture: How PostgreSQL Manages Memory

* **shared_buffers**: The primary disk cache, typically 25% of available RAM. This holds copies of table and index pages from disk.
* **work_mem**: Memory available for sort operations, hash tables, and bitmap operations per operation. Multiple operations in a query can each use this amount.
* **maintenance_work_mem**: Memory for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE.
* **wal_buffers**: Buffer for write-ahead log data before it’s written to disk.
* **temp_buffers**: Memory for temporary tables.

“`conf
# Example memory configuration for 64GB server
shared_buffers = 16GB
work_mem = 64MB
maintenance_work_mem = 2GB
wal_buffers = 16MB
MVCC Internals: How PostgreSQL Handles Concurrency
PostgreSQL implements Multi-Version Concurrency Control by storing multiple versions of rows. Each row has system columns:

xmin: Transaction ID that created the row version

xmax: Transaction ID that deleted/updated the row (or 0 if alive)

ctid: Physical location of this row version

Visibility rules determine which transactions see which row versions based on transaction IDs and snapshot information.

sql
— View system columns (normally hidden)
SELECT xmin, xmax, ctid, * FROM orders WHERE id = 1;

— Check current transaction ID
SELECT txid_current();
MVCC avoids locks for readers but creates “bloat” from dead row versions that must be cleaned up by VACUUM.

Write-Ahead Logging: Ensuring Durability
Every change is first written to the Write-Ahead Log (WAL) before being applied to data files. This ensures:

Durability: Changes survive crashes

Replication: WAL can be shipped to replicas

Point-in-time recovery: WAL segments allow recovery to any moment

The WAL consists of 16MB segments stored in pg_wal directory. Checkpoints flush dirty buffers to disk and create recovery points.

Blog Post 3: PostgreSQL Installation and Configuration Best Practices
Category: PostgreSQL Setup
Tags: installation, configuration, tuning, best-practices

Cluster Initialization: Getting Started Right
bash
# Create PostgreSQL data directory
mkdir -p /pgdata/15/main
chown postgres:postgres /pgdata/15/main

# Initialize database cluster
sudo -u postgres initdb \
-D /pgdata/15/main \
–locale=en_US.UTF-8 \
–encoding=UTF8 \
–data-checksums

# Start PostgreSQL
sudo -u postgres pg_ctl -D /pgdata/15/main start
Checksums should always be enabled at initialization (cannot be changed later). They detect storage corruption.

Understanding PostgreSQL Directory Layout
text
/pgdata/15/main/
├── base/ # Table data (database OID subdirectories)
├── global/ # Cluster-wide tables (pg_authid, pg_database)
├── pg_wal/ # Write-ahead log segments (16MB each)
├── pg_xact/ # Transaction commit status (CLOG)
├── pg_stat_tmp/ # Temporary statistics files
├── pg_subtrans/ # Subtransaction information
├── pg_twophase/ # Prepared transactions
├── pg_logical/ # Logical decoding data
├── postgresql.conf # Main configuration
├── pg_hba.conf # Client authentication
└── pg_ident.conf # User name mapping
Essential PostgreSQL Configuration
conf
# CONNECTIONS
listen_addresses = ‘*’ # Listen on all interfaces
port = 5432 # Default port
max_connections = 300 # Adjust based on workload

# MEMORY
shared_buffers = 8GB # 25% of RAM for dedicated DB
work_mem = 32MB # Per-operation sort/hash memory
maintenance_work_mem = 1GB # For VACUUM, CREATE INDEX
effective_cache_size = 24GB # Planner estimate of disk cache

# WAL
wal_level = replica # For replication, use ‘logical’ for logical decoding
max_wal_size = 4GB # Soft limit for WAL size
min_wal_size = 1GB # Minimum to keep for reuse

# CHECKPOINTS
checkpoint_timeout = 15min # Time between checkpoints
checkpoint_completion_target = 0.9 # Spread checkpoint I/O

# AUTOVACUUM
autovacuum = on # Essential for bloat management
log_autovacuum_min_duration = 0 # Log all autovacuum activity
autovacuum_max_workers = 5 # Concurrent autovacuum workers
autovacuum_vacuum_scale_factor = 0.05 # Trigger after 5% changes
autovacuum_analyze_scale_factor = 0.02 # Trigger stats update after 2%
Authentication and Security Configuration
conf
# TYPE DATABASE USER ADDRESS METHOD
# Allow replication connections from localhost
local replication all peer

# SSL connections from application servers
hostssl all app_user 10.0.1.0/24 scram-sha-256

# Local connections
local all all peer

# Read-only user from monitoring host
host all monitoring 192.168.1.100/32 md5

# Reject everything else
host all all 0.0.0.0/0 reject
Operating System and Filesystem Tuning
bash
# Set appropriate mount options in /etc/fstab
/dev/sdb1 /pgdata xfs defaults,noatime,nodiratime 0 0

# Set kernel parameters in /etc/sysctl.conf
vm.swappiness = 1
vm.dirty_ratio = 3
vm.dirty_background_ratio = 2
vm.overcommit_memory = 2

# Set limits in /etc/security/limits.conf
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 16384
postgres hard nproc 16384
Blog Post 4: PostgreSQL Storage, WAL, and Replication Internals
Category: PostgreSQL Storage
Tags: storage, replication, wal, partitioning, indexing

Heap Storage Model: How PostgreSQL Stores Data
Tables are stored as heaps of fixed-size pages (default 8KB):

text
Table “orders”
├─ Page 0 (Header, free space map, row pointers, rows)
├─ Page 1
├─ …
└─ Page N

Each row has:
– Heap tuple header (24 bytes)
– Null bitmap (if nullable columns)
– Object ID (if WITH OIDS)
– User data columns
Rows are referenced by CTID (page_number, tuple_index). Updates create new row versions (MVCC) with new CTIDs.

Index Types and When to Use Them
B-tree: Default, balanced tree for equality and range queries

Hash: For equality only (PostgreSQL 10+ improved)

GiST: Generalized Search Tree for complex data types

SP-GiST: Space-partitioned GiST for non-balanced structures

GIN: Generalized Inverted Index for array, JSON, full-text search

BRIN: Block Range Index for large, naturally ordered tables

sql
— Create index with fillfactor to control page density
CREATE INDEX idx_orders_date ON orders(order_date)
WITH (fillfactor = 90);

— Create partial index for subset of rows
CREATE INDEX idx_orders_active ON orders(id)
WHERE status = ‘active’;

— Create covering index with INCLUDE
CREATE INDEX idx_orders_customer ON orders(customer_id)
INCLUDE (order_date, total_amount);
WAL Lifecycle and Archiving for Point-in-Time Recovery
text
1. Transaction makes change
2. Change written to WAL buffer in shared memory
3. WAL writer flushes buffer to WAL segment in pg_wal/
4. Checkpointer writes dirty data pages to heap/index files
5. Archiver copies completed WAL segments to archive storage
Enable archiving for point-in-time recovery:

conf
wal_level = replica
archive_mode = on
archive_command = ‘cp %p /archive/%f && aws s3 cp /archive/%f s3://backup-bucket/wal/%f’
archive_timeout = 300 # Force segment switch every 5 minutes
Replication: Physical vs Logical
Physical replication (streaming replication):

Byte-for-byte copy of WAL

Entire database cluster replicated

Replica is read-only (unless using bi-directional tools)

sql
— On primary:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD ‘secret’;
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET wal_keep_size = 1024; # MB of WAL to keep

— On replica:
pg_basebackup -h primary-host -D /pgdata/15/main -U replicator -X stream
echo “primary_conninfo = ‘host=primary-host user=replicator password=secret'” > recovery.conf
Logical replication:

Row-level replication

Can replicate subsets of tables

Can have different schemas/tables on replica

sql
— On source (publisher):
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

— On target (subscriber):
CREATE SUBSCRIPTION sub_orders
CONNECTION ‘host=source-db dbname=app’
PUBLICATION pub_orders;
Understanding Replication Slots
Prevent WAL deletion until consumed by all replicas:

sql
— Create replication slot
SELECT * FROM pg_create_physical_replication_slot(‘replica1_slot’);

— Monitor slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;
Warning: If replica disappears, slot prevents WAL cleanup and can fill disk.

Blog Post 5: PostgreSQL Security Best Practices
Category: PostgreSQL Security
Tags: security, roles, rls, encryption, audit

Role-Based Access Control in PostgreSQL
PostgreSQL uses role-based access control. Roles can be login roles (users) or group roles.

sql
— Create login role with password
CREATE ROLE app_user WITH LOGIN PASSWORD ‘secure-password’
VALID UNTIL ‘2025-12-31’;

— Create group role
CREATE ROLE finance_team NOLOGIN;

— Add user to group
GRANT finance_team TO app_user;

— Grant schema privileges
GRANT USAGE ON SCHEMA public TO finance_team;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO finance_team;

— Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO finance_team;
Implementing Row-Level Security (RLS)
Fine-grained access control at row level:

sql
— Enable RLS on table
CREATE TABLE patient_records (
id SERIAL PRIMARY KEY,
patient_id INTEGER,
doctor_id INTEGER,
notes TEXT
);

ALTER TABLE patient_records ENABLE ROW LEVEL SECURITY;

— Create policies
— Doctors can see their own patients
CREATE POLICY doctor_access ON patient_records
FOR SELECT USING (doctor_id = current_user_id());

— Patients can see their own records
CREATE POLICY patient_access ON patient_records
FOR SELECT USING (patient_id = current_user_id());

— Doctors can update their patients’ records
CREATE POLICY doctor_update ON patient_records
FOR UPDATE USING (doctor_id = current_user_id());

— Check active policies
SELECT * FROM pg_policies WHERE tablename = ‘patient_records’;
Column-Level Security Techniques
sql
— Revoke access to sensitive columns
GRANT SELECT(id, name, department) ON employees TO hr_staff;
REVOKE SELECT(salary, ssn) ON employees FROM hr_staff;

— Create view with masked columns
CREATE VIEW employee_directory AS
SELECT id, name, department,
‘*****’ AS salary_masked,
‘***-**-‘ || RIGHT(ssn, 4) AS ssn_masked
FROM employees;

GRANT SELECT ON employee_directory TO all_employees;
Encryption: In Transit and At Rest
In transit encryption:

conf
# postgresql.conf
ssl = on
ssl_cert_file = ‘server.crt’
ssl_key_file = ‘server.key’
ssl_ca_file = ‘root.crt’
ssl_min_protocol_version = ‘TLSv1.2’

# pg_hba.conf
hostssl all all 0.0.0.0/0 scram-sha-256
At rest encryption options:

Use filesystem encryption (LUKS, BitLocker)

Use cloud storage encryption (EBS encryption, Azure Disk Encryption)

Use transparent data encryption extensions (pg_tde, pgcrypto for selective encryption)

sql
— Column-level encryption with pgcrypto
CREATE EXTENSION pgcrypto;

INSERT INTO sensitive_data
VALUES (encrypt(‘secret value’, ‘encryption-key’, ‘aes’));
Audit Logging for Compliance
sql
— Install audit extension
CREATE EXTENSION pgaudit;

— Configure in postgresql.conf
pgaudit.log = ‘read, write, ddl, role’
pgaudit.log_relation = on
pgaudit.log_catalog = off

— Or use native logging
ALTER SYSTEM SET log_statement = ‘ddl’;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
Blog Post 6: PostgreSQL Backup, Recovery, and High Availability
Category: PostgreSQL Operations
Tags: backup, recovery, ha, replication, failover

Backup Strategies: Logical vs Physical
Logical backups (pg_dump/pg_dumpall):

bash
# Single database, custom format (supports parallel restore)
pg_dump -Fc -d mydb -f mydb.dump

# Directory format (parallel dump/restore)
pg_dump -Fd -d mydb -j 4 -f /backup/mydb/

# Plain SQL format
pg_dump -Fp -d mydb -f mydb.sql

# Global objects (roles, tablespaces)
pg_dumpall -g -f globals.sql
Physical backups:

bash
# Base backup with pg_basebackup
pg_basebackup -D /backup/basebackup -X stream -P -U replicator

# Manual base backup
SELECT pg_start_backup(‘weekly_backup’, false);
— Use filesystem tools to copy data directory
SELECT pg_stop_backup();
Point-in-Time Recovery (PITR): Your Safety Net
text
Timeline:
Base Backup –> WAL Segments –> Recovery to 2024-01-15 14:30:00
^
|
Target Recovery Point
Restore base backup

Configure recovery parameters:

conf
# recovery.conf (PostgreSQL 12+ uses postgresql.conf)
restore_command = ‘cp /archive/wal/%f %p’
recovery_target_time = ‘2024-01-15 14:30:00’
recovery_target_action = ‘promote’
Create recovery.signal file: touch /pgdata/recovery.signal

Start PostgreSQL

High Availability Patterns
Primary-Replica with Automated Failover:

text
Primary -> Replica1 (sync standby)
-> Replica2 (async standby)

Failover: Replica1 promotes when Primary fails
Using Patroni for automatic failover:

yaml
scope: mycluster
name: node1

restapi:
listen: 0.0.0.0:8008
connect_address: 10.0.1.1:8008

etcd:
hosts: 10.0.1.10:2379,10.0.1.11:2379,10.0.1.12:2379

bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: replica
hot_standby: “on”
Multi-Master Solutions:

BDR (Bi-Directional Replication) for PostgreSQL 10+

Citus for distributed PostgreSQL (sharding)

Application-level conflict resolution

Backup Verification and Monitoring
sql
— Monitor backup age
SELECT now() – pg_last_xact_replay_timestamp() AS replication_lag;

— Check WAL archive status
SELECT archive_mode,
archived_count,
last_archived_wal,
last_archived_time,
failed_count,
last_failed_wal,
last_failed_time
FROM pg_stat_archiver;

— Verify backups with pgBackRest verify
pgbackrest –stanza=mydb verify
Blog Post 7: PostgreSQL Performance Tuning and Query Optimization
Category: PostgreSQL Performance
Tags: performance, tuning, optimization, indexes, queries

Understanding the Query Planner
sql
— Basic explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

— Explain with actual execution statistics
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 123;

— Explain with verbose output
EXPLAIN (VERBOSE, COSTS, SETTINGS, WAL, BUFFERS, TIMING, SUMMARY, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;

— Generate query plan as text diagram
EXPLAIN (FORMAT YAML) SELECT * FROM orders;
Key plan operations:

Seq Scan: Full table scan

Index Scan: Index lookup then heap fetch

Index Only Scan: All data from index

Bitmap Heap Scan: Multiple index scans combined

Nested Loop: Small tables joined

Hash Join: Medium tables with equality joins

Merge Join: Sorted tables with range/equality joins

Indexing Strategies for Optimal Performance
When to index:

Columns in WHERE, JOIN, ORDER BY, GROUP BY clauses

Foreign key columns (improves CASCADE operations)

Columns with high selectivity

When NOT to index:

Tables with frequent bulk updates

Columns with low cardinality (unless using partial indexes)

Small tables (< 10,000 rows)

Specialized indexes:

sql
— Multi-column index for specific query patterns
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

— Expression index for case-insensitive search
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

— Partial index for subset of data
CREATE INDEX idx_orders_active ON orders(id)
WHERE status = ‘active’;

— Covering index with INCLUDE
CREATE INDEX idx_orders_customer_cover ON orders(customer_id)
INCLUDE (order_date, total_amount);

— BRIN for large, sorted tables
CREATE INDEX idx_sensor_readings_time ON sensor_readings
USING BRIN(timestamp);
Autovacuum Tuning: Critical for Performance
Autovacuum is critical for PostgreSQL performance. Monitor with:

sql
— Check autovacuum activity
SELECT schemaname, relname,
last_vacuum, last_autovacuum,
last_analyze, last_autoanalyze,
vacuum_count, autovacuum_count
FROM pg_stat_user_tables;

— Check table bloat
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_total_relation_size(relid) – pg_relation_size(relid)) AS bloat_size,
n_dead_tup,
n_live_tup
FROM pg_stat_user_tables
ORDER BY bloat_size DESC NULLS LAST;
Tuning parameters:

conf
# Global settings
autovacuum_vacuum_cost_limit = 2000 # Increase from default 200
autovacuum_vacuum_cost_delay = 2ms # Reduce from default 20ms
autovacuum_max_workers = 5 # Default 3, increase for many tables

# Table-specific settings
ALTER TABLE large_table SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_threshold = 5000,
autovacuum_analyze_threshold = 2000
);
Query Optimization Techniques
Common optimizations:

sql
— 1. Avoid SELECT * when not needed
SELECT id, name FROM users; — Instead of SELECT *

— 2. Use EXISTS instead of IN for subqueries
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.active);

— 3. Use UNION ALL instead of UNION when duplicates don’t matter
SELECT id FROM table1
UNION ALL
SELECT id FROM table2;

— 4. Avoid functions on indexed columns in WHERE clauses
— Bad: WHERE UPPER(name) = ‘JOHN’
— Good: WHERE name = ‘john’ (with case-sensitive) or use expression index

— 5. Use LIMIT for exploratory queries
SELECT * FROM large_table LIMIT 100;

— 6. Batch updates and deletes
— Instead of one-per-row in application loop
UPDATE orders SET status = ‘processed’
WHERE id IN (SELECT id FROM pending_orders LIMIT 1000);

— 7. Use TEMP tables for complex multi-step operations
CREATE TEMP TABLE temp_results AS
SELECT … FROM … WHERE …;
CREATE INDEX ON temp_results(…);
SELECT … FROM temp_results JOIN …;
Connection Pooling: Essential for Production
Prevent connection overload with pooling:

pgbouncer configuration (transaction pooling):

ini
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
Application-side pooling:

python
# Python with psycopg2 pool
from psycopg2 import pool

connection_pool = pool.ThreadedConnectionPool(
minconn=5,
maxconn=20,
host=’localhost’,
database=’mydb’,
user=’app_user’
)
Blog Post 8: PostgreSQL Monitoring and Troubleshooting
Category: PostgreSQL Monitoring
Tags: monitoring, troubleshooting, observability, diagnostics

Essential Monitoring Views
sql
— Active queries
SELECT pid, usename, application_name, client_addr,
query_start, state, query
FROM pg_stat_activity
WHERE state != ‘idle’
ORDER BY query_start;

— Query statistics (requires pg_stat_statements)
SELECT query, calls, total_time, mean_time, rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

— Database statistics
SELECT datname, numbackends, xact_commit, xact_rollback,
blks_read, blks_hit,
100.0 * blks_hit / nullif(blks_hit + blks_read, 0) AS cache_hit_ratio
FROM pg_stat_database;

— Table access patterns
SELECT schemaname, relname,
seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch,
n_tup_ins, n_tup_upd, n_tup_del,
n_tup_hot_upd, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
Diagnosing Lock Issues
sql
— Current locks
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
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;

— Lock waits
SELECT pid, wait_event_type, wait_event, query, age(clock_timestamp(), query_start) AS query_age
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL;
Troubleshooting Methodology
text
1. SYMPTOM: Slow queries / high CPU / connections spiking
|
2. METRICS: Check pg_stat_activity, pg_stat_statements
|
3. DIAGNOSE: Identify blocking queries, missing indexes, lock contention
|
4. ROOT CAUSE:
├─ Query pattern issue
├─ Missing index
├─ Lock contention
├─ Resource exhaustion
└─ Configuration problem
|
5. FIX: Apply appropriate remediation
├─ Terminate problematic queries
├─ Add missing indexes
├─ Optimize queries
├─ Adjust configuration
└─ Scale resources
Alerting and Dashboarding Best Practices
Key metrics to alert on:

Replication lag > 30 seconds

Dead tuples > 10% of live tuples

Cache hit ratio < 90%

Disk usage > 85%

Failed connections per minute

Long-running queries (> 5 minutes)

Prometheus exporters:

postgres_exporter for PostgreSQL metrics

pgbackrest_exporter for backup metrics

Grafana dashboard queries:

sql
— Connection count over time
SELECT time_bucket(‘5 minutes’, created_at) AS time,
COUNT(*) as connections
FROM pg_stat_activity
GROUP BY time_bucket(‘5 minutes’, created_at)
ORDER BY time;
Blog Post 9: Scaling PostgreSQL: Vertical and Horizontal Strategies
Category: PostgreSQL Scaling
Tags: scaling, partitioning, sharding, replication, performance

Vertical Scaling: Scaling Up
Increase resources on single instance:

CPU: More cores for parallel queries

RAM: Larger shared_buffers, work_mem

Disk: Faster storage (NVMe), more IOPS

Network: Higher throughput

Configuration for large instances (> 64GB RAM):

conf
shared_buffers = 32GB
effective_cache_size = 48GB
work_mem = 128MB
maintenance_work_mem = 4GB
max_parallel_workers_per_gather = 4
max_worker_processes = 16
max_parallel_workers = 16
Read Scaling with Replicas
text
Primary -> Replica1 (sync, for HA)
-> Replica2 (async, for reporting)
-> Replica3 (async, for backups)
Load balancing configuration:

yaml
# HAProxy configuration
backend postgres_read
balance leastconn
option httpchk OPTIONS /master
server primary 10.0.1.1:5432 check port 8008
server replica1 10.0.1.2:5432 check port 8008 backup
server replica2 10.0.1.3:5432 check port 8008 backup
Partitioning Large Tables
Declarative partitioning (PostgreSQL 10+):

sql
— Create partitioned table
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

— Create partitions
CREATE TABLE measurement_y2023 PARTITION OF measurement
FOR VALUES FROM (‘2023-01-01’) TO (‘2024-01-01’);

CREATE TABLE measurement_y2024 PARTITION OF measurement
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);

— Create index on partitioned table
CREATE INDEX ON measurement (logdate, city_id);

— Automatic partition creation with pg_partman
SELECT partman.create_parent(‘public.measurement’, ‘logdate’, ‘native’, ‘monthly’);
Partitioning strategies:

Range: Time series, numeric ranges

List: Discrete values (regions, categories)

Hash: Even distribution

Sharding with Citus
sql
— Enable Citus extension
CREATE EXTENSION citus;

— Create distributed table
SELECT create_distributed_table(‘orders’, ‘customer_id’);

— Add worker nodes
SELECT * from master_add_node(‘10.0.2.1’, 5432);
SELECT * from master_add_node(‘10.0.2.2’, 5432);

— Query across shards
SELECT customer_id, COUNT(*)
FROM orders
WHERE order_date >= NOW() – INTERVAL ’30 days’
GROUP BY customer_id;
Connection Pooling and Caching Layers
Application-level caching:

Redis/Memcached for frequent reads

Materialized views for complex aggregations

Application-level result caching

Read-through cache pattern:

python
def get_user(user_id):
# Try cache first
user = cache.get(f”user:{user_id}”)
if user:
return user

# Cache miss, read from database
user = db.query(“SELECT * FROM users WHERE id = %s”, user_id)

# Store in cache with expiry
cache.setex(f”user:{user_id}”, 300, user)
return user
Blog Post 10: PostgreSQL Automation and DevOps
Category: PostgreSQL DevOps
Tags: automation, devops, terraform, ansible, cicd

Infrastructure as Code for PostgreSQL
Terraform for cloud PostgreSQL:

hcl
resource “aws_db_instance” “postgres” {
identifier = “prod-postgres”
engine = “postgres”
engine_version = “15.3”
instance_class = “db.r5.2xlarge”

allocated_storage = 500
storage_type = “gp3”

db_name = “appdb”
username = var.db_username
password = var.db_password

vpc_security_group_ids = [aws_security_group.db.id]
db_subnet_group_name = aws_db_subnet_group.main.name

backup_retention_period = 30
backup_window = “03:00-04:00”
maintenance_window = “sun:04:00-sun:05:00”

deletion_protection = true
skip_final_snapshot = false
}
Ansible for PostgreSQL configuration:

yaml
– name: Configure PostgreSQL
hosts: dbservers
vars:
postgres_version: 15
postgres_data_dir: “/pgdata/{{ postgres_version }}/main”

tasks:
– name: Install PostgreSQL
apt:
name: “postgresql-{{ postgres_version }}”
state: present

– name: Create data directory
file:
path: “{{ postgres_data_dir }}”
state: directory
owner: postgres
group: postgres

– name: Configure postgresql.conf
template:
src: postgresql.conf.j2
dest: “{{ postgres_data_dir }}/postgresql.conf”
owner: postgres
group: postgres
CI/CD for Database Changes
Flyway migration example:

sql
— V1__create_users_table.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— V2__add_user_profile.sql
ALTER TABLE users ADD COLUMN profile JSONB;
CREATE INDEX idx_users_profile ON users USING GIN(profile);
Sqitch migration tool:

bash
# Add change
sqitch add add_users_table -n “Create users table”

# Deploy to staging
sqitch deploy db:pg://user@staging-db/appdb

# Verify deployment
sqitch verify db:pg://user@staging-db/appdb

# Promote to production
sqitch deploy db:pg://user@prod-db/appdb
Blue-Green Deployments for Zero Downtime
text
Stage 1: Old Cluster (Green)
|
Stage 2: Create New Cluster (Blue) with logical replication
|
Stage 3: Sync data between Green and Blue
|
Stage 4: Cutover: Point apps to Blue
|
Stage 5: Decommission Green
Implementation with logical replication:

sql
— On new cluster (Blue)
CREATE PUBLICATION blue_pub FOR ALL TABLES;

— On old cluster (Green)
CREATE SUBSCRIPTION green_to_blue
CONNECTION ‘host=blue-cluster dbname=app’
PUBLICATION blue_pub;

— Verify sync
SELECT * FROM pg_stat_subscription;

— Cutover: Update application connection strings
— Stop subscription
ALTER SUBSCRIPTION green_to_blue DISABLE;
Blog Post 11: PostgreSQL vs Oracle: Key Differences
Category: Database Comparison
Tags: postgresql, oracle, comparison, migration

Architectural Comparison
Feature Oracle PostgreSQL
Concurrency Model Read consistency with UNDO segments MVCC with heap tuples
Storage Model Segments, Extents, Data Blocks Heap files, TOAST for large values
Memory Model SGA (Shared Global Area) Shared buffers + per-backend memory
Process Model Multi-threaded server process Process-per-connection
Data Dictionary Static data dictionary views System catalogs as regular tables
Key Syntax and Behavior Differences
Sequences:

sql
— Oracle
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 CACHE 20;

— PostgreSQL
CREATE SEQUENCE order_seq START 1 INCREMENT 1 CACHE 20;

— Oracle usage
INSERT INTO orders (id, …) VALUES (order_seq.NEXTVAL, …);

— PostgreSQL usage
INSERT INTO orders (id, …) VALUES (nextval(‘order_seq’), …);
— Or with SERIAL type (auto-increment)
CREATE TABLE orders (id SERIAL PRIMARY KEY, …);
String concatenation:

sql
— Oracle
SELECT first_name || ‘ ‘ || last_name FROM employees;

— PostgreSQL (same syntax, but careful with NULL)
SELECT first_name || ‘ ‘ || last_name FROM employees;
— Better with NULL handling
SELECT CONCAT(first_name, ‘ ‘, last_name) FROM employees;
Date/Time handling:

sql
— Oracle
SELECT SYSDATE FROM dual;
SELECT TO_DATE(‘2024-01-15’, ‘YYYY-MM-DD’) FROM dual;

— PostgreSQL
SELECT CURRENT_DATE;
SELECT TO_DATE(‘2024-01-15’, ‘YYYY-MM-DD’);
— Or better (ISO format)
SELECT ‘2024-01-15’::DATE;
Transaction and Locking Differences
Oracle: Readers don’t block writers, writers don’t block readers (UNDO-based)

PostgreSQL: Readers never block, but VACUUM required to clean old versions

Oracle: Default isolation is READ COMMITTED with statement-level consistency

PostgreSQL: Default isolation is READ COMMITTED with transaction-level consistency

Deadlock handling:

sql
— Oracle: Automatically detects and rolls back one transaction
— PostgreSQL: Also detects deadlocks, but error message differs
ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456;
blocked by process 789.
Blog Post 12: Oracle to PostgreSQL Migration Strategy
Category: Database Migration
Tags: migration, oracle, postgresql, strategy, planning

Migration Phases
text
┌─────────────────────────────────────────────────────────┐
│ Phase 1: Assessment & Planning │
│ • Inventory applications and databases │
│ • Analyze complexity and dependencies │
│ • Estimate effort and create migration plan │
└─────────────────────────────────────────────────────────┘
│
┌─────────────────────────────────────────────────────────┐
│ Phase 2: Schema Conversion │
│ • Convert DDL with migration tools │
│ • Map data types and objects │
│ • Handle Oracle-specific features │
└─────────────────────────────────────────────────────────┘
│
┌─────────────────────────────────────────────────────────┐
│ Phase 3: Data Migration │
│ • Extract data from Oracle │
│ • Transform to PostgreSQL format │
│ • Load into PostgreSQL │
└─────────────────────────────────────────────────────────┘
│
┌─────────────────────────────────────────────────────────┐
│ Phase 4: Code Migration │
│ • Convert PL/SQL to PL/pgSQL │
│ • Rewrite application SQL │
│ • Handle application logic changes │
└─────────────────────────────────────────────────────────┘
│
┌─────────────────────────────────────────────────────────┐
│ Phase 5: Testing & Validation │
│ • Functional testing │
│ • Performance testing │
│ • Data integrity verification │
└─────────────────────────────────────────────────────────┘
│
┌─────────────────────────────────────────────────────────┐
│ Phase 6: Cutover & Optimization │
│ • Production migration │
│ • Post-migration tuning │
│ • Monitoring and support │
└─────────────────────────────────────────────────────────┘
Migration Tools Overview
Commercial tools:

AWS SCT (Schema Conversion Tool): Free from AWS, good for schema conversion

Ispirer MnMTK: Comprehensive tool for schema and code migration

EnterpriseDB Migration Toolkit: From Oracle to EDB Postgres

Open source tools:

ora2pg: Perl-based, highly configurable

pgloader: Data loading with transformation capabilities

Foreign Data Wrappers (oracle_fdw): Direct data access

Example with ora2pg:

bash
# Install ora2pg
sudo apt-get install ora2pg

# Create configuration
ora2pg –project_base /migration/project \
–init_project my_migration

# Edit configuration
vi /migration/project/my_migration/config/ora2pg.conf

# Generate migration report
ora2pg –project_base /migration/project \
–type SHOW_REPORT

# Export schema
ora2pg –project_base /migration/project \
–type TABLE \
–basedir /migration/project/my_migration \
–parallel 4
Risk Assessment Matrix
Risk Category Impact Mitigation
SQL Dialect Differences High Comprehensive testing, SQL translation layer
PL/SQL to PL/pgSQL Conversion High Use automated tools, manual review
Performance Assumptions Medium Load testing, query optimization
Data Type Mismatches Medium Thorough data validation
Application Framework Compatibility Medium Early POC with representative workload
Blog Post 13: Schema, Data, and Code Migration Techniques
Category: Database Migration
Tags: schema-migration, data-migration, plsql, conversion

Data Type Mapping Guide
Oracle Data Type PostgreSQL Equivalent Notes
NUMBER NUMERIC or DECIMAL Use same precision/scale
NUMBER(10) INTEGER
VARCHAR2(n) VARCHAR(n) Oracle VARCHAR2 is variable length
CHAR(n) CHAR(n) Fixed-length, blank-padded
DATE TIMESTAMP(0) Oracle DATE includes time
TIMESTAMP TIMESTAMP
CLOB TEXT PostgreSQL TEXT handles large strings
BLOB BYTEA Binary large object
RAW(n) BYTEA Variable raw binary
ROWID CTID Physical row identifier (system column)
BINARY_FLOAT REAL 32-bit floating point
BINARY_DOUBLE DOUBLE PRECISION 64-bit floating point
Special considerations:

sql
— Oracle implicit conversions
— Oracle: SELECT * FROM t WHERE char_column = 123; — Works
— PostgreSQL: ERROR: operator does not exist: character = integer
— Fix: SELECT * FROM t WHERE char_column = ‘123’;

— Date arithmetic
— Oracle: date_column + 1 (adds one day)
— PostgreSQL: date_column + INTERVAL ‘1 day’
Schema Object Conversion
Tables:

sql
— Oracle
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100) NOT NULL,
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(10,2),
commission_pct NUMBER(2,2),
photo BLOB
) TABLESPACE users;

— PostgreSQL
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
hire_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
salary NUMERIC(10,2),
commission_pct NUMERIC(2,2),
photo BYTEA
);
Indexes:

sql
— Oracle
CREATE INDEX idx_emp_name ON employees(emp_name) TABLESPACE index_ts;

— PostgreSQL
CREATE INDEX idx_emp_name ON employees(emp_name);

— Function-based index
— Oracle: CREATE INDEX idx_upper_name ON employees(UPPER(emp_name));
— PostgreSQL: CREATE INDEX idx_upper_name ON employees(UPPER(emp_name));
Sequences and Identity Columns:

sql
— Oracle 12c+ identity column
CREATE TABLE orders (
order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
…
);

— PostgreSQL
CREATE TABLE orders (
order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
…
);

— Legacy Oracle sequence usage
— Oracle: CREATE SEQUENCE order_seq;
— PostgreSQL: CREATE SEQUENCE order_seq;
— In application, replace .NEXTVAL with nextval()
PL/SQL to PL/pgSQL Conversion
Basic function conversion:

sql
— Oracle PL/SQL
CREATE OR REPLACE FUNCTION calculate_bonus(
p_salary NUMBER,
p_rating NUMBER
) RETURN NUMBER
IS
v_bonus NUMBER;
BEGIN
IF p_rating >= 4 THEN
v_bonus := p_salary * 0.20;
ELSIF p_rating >= 3 THEN
v_bonus := p_salary * 0.10;
ELSE
v_bonus := 0;
END IF;

RETURN v_bonus;
END;
/

— PostgreSQL PL/pgSQL
CREATE OR REPLACE FUNCTION calculate_bonus(
p_salary NUMERIC,
p_rating NUMERIC
) RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
v_bonus NUMERIC;
BEGIN
IF p_rating >= 4 THEN
v_bonus := p_salary * 0.20;
ELSIF p_rating >= 3 THEN
v_bonus := p_salary * 0.10;
ELSE
v_bonus := 0;
END IF;

RETURN v_bonus;
END;
$$;
Data Migration Techniques
Using ora_fdw (Foreign Data Wrapper):

sql
— Install oracle_fdw extension
CREATE EXTENSION oracle_fdw;

— Create foreign server
CREATE SERVER oracle_db
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver ‘//oracle-host:1521/ORCL’);

— Create user mapping
CREATE USER MAPPING FOR postgres
SERVER oracle_db
OPTIONS (user ‘oracle_user’, password ‘oracle_pass’);

— Create foreign table
CREATE FOREIGN TABLE oracle_employees (
emp_id INTEGER,
emp_name VARCHAR(100),
hire_date TIMESTAMP,
salary NUMERIC(10,2)
) SERVER oracle_db
OPTIONS (schema ‘HR’, table ‘EMPLOYEES’);

— Copy data to local table
INSERT INTO postgres_employees
SELECT * FROM oracle_employees;

— Or use CTAS (CREATE TABLE AS SELECT)
CREATE TABLE postgres_employees AS
SELECT * FROM oracle_employees;
Using pgloader:

bash
# pgloader configuration file
cat > migrate.load <<EOF
LOAD DATABASE
FROM oracle://user:password@oracle-host:1521/ORCL
INTO postgresql://user:password@postgres-host:5432/appdb

WITH include drop, create tables, create indexes,
reset sequences, foreign keys,
batch rows = 1000, batch size = 10MB

SET maintenance_work_mem to ‘128MB’,
work_mem to ’12MB’

CAST type NUMBER to numeric drop typemod,
type NUMBER(10) to integer,
type DATE to timestamptz drop default drop not null

BEFORE LOAD DO
$$ create schema if not exists hr; $$;
EOF

# Run pgloader
pgloader migrate.load
Blog Post 14: Application Migration and Testing Strategies
Category: Application Migration
Tags: application-migration, testing, compatibility, validation

Application SQL Compatibility Layer
Using schema compatibility functions:

sql
— Create compatibility functions for common Oracle functions
CREATE OR REPLACE FUNCTION to_date(text, text)
RETURNS TIMESTAMP
LANGUAGE sql
IMMUTABLE
AS $$
SELECT to_timestamp($1, $2)::TIMESTAMP;
$$;

CREATE OR REPLACE FUNCTION to_char(timestamp, text)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
SELECT to_char($1, $2);
$$;

— NVL equivalent
CREATE OR REPLACE FUNCTION nvl(anyelement, anyelement)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $$
SELECT COALESCE($1, $2);
$$;

— DECODE function (simplified)
CREATE OR REPLACE FUNCTION decode(anyelement, anyelement, anyelement)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE WHEN $1 = $2 THEN $3 ELSE NULL END;
$$;

CREATE OR REPLACE FUNCTION decode(anyelement, anyelement, anyelement, anyelement)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE WHEN $1 = $2 THEN $3 ELSE $4 END;
$$;
Application-level SQL rewriting:

python
# SQL rewriting layer in application
def rewrite_sql(oracle_sql):
“””Convert Oracle SQL to PostgreSQL compatible SQL”””
replacements = {
r'(?i)\bSYSDATE\b’: ‘CURRENT_TIMESTAMP’,
r'(?i)\bNVL\(([^,]+),\s*([^)]+)\)’: r’COALESCE(\1, \2)’,
r'(?i)\bTO_DATE\(([^,]+),\s*\’YYYY-MM-DD\’\)’: r’\1::DATE’,
r'(?i)\bROWNUM\b’: ‘ROW_NUMBER() OVER ()’,
# Add more patterns as needed
}

postgres_sql = oracle_sql
for pattern, replacement in replacements.items():
postgres_sql = re.sub(pattern, replacement, postgres_sql)

return postgres_sql
Connection and Transaction Management
Connection string differences:

python
# Oracle connection (Python cx_Oracle)
import cx_Oracle
dsn = cx_Oracle.makedsn(“oracle-host”, 1521, service_name=”ORCL”)
connection = cx_Oracle.connect(user=”username”, password=”password”, dsn=dsn)

# PostgreSQL connection (Python psycopg2)
import psycopg2
connection = psycopg2.connect(
host=”postgres-host”,
port=5432,
database=”appdb”,
user=”username”,
password=”password”
)
Transaction management:

python
# Oracle implicit behavior
# – DDL causes implicit commit
# – Some errors don’t roll back transaction

# PostgreSQL explicit behavior
# – All DDL is transactional (can be rolled back)
# – Most errors abort the transaction

# Good practice: Use explicit transaction control
try:
conn.autocommit = False
cursor = conn.cursor()

cursor.execute(“INSERT INTO table1 …”)
cursor.execute(“UPDATE table2 …”)

conn.commit()
except Exception as e:
conn.rollback()
raise e
finally:
cursor.close()
Testing Strategy for Database Migration
Testing pyramid for database migration:

text
┌──────────────────────┐
│ Load Testing │ ← Simulate production workload
│ (Performance) │
└──────────────────────┘
│
┌──────────────────────┐
│ Integration Testing │ ← Test application with migrated DB
│ (End-to-end) │
└──────────────────────┘
│
┌──────────────────────┐
│ Functional Testing │ ← Test individual components
│ (Unit tests) │
└──────────────────────┘
Data validation queries:

sql
— Row count comparison
SELECT ‘Oracle’ as source, COUNT(*) as row_count FROM oracle_table@dblink
UNION ALL
SELECT ‘PostgreSQL’, COUNT(*) FROM postgres_table;

— Data difference detection
WITH oracle_data AS (
SELECT id, name, amount FROM oracle_table@dblink
),
postgres_data AS (
SELECT id, name, amount FROM postgres_table
)
SELECT
COALESCE(o.id, p.id) as id,
CASE
WHEN o.id IS NULL THEN ‘Missing in Oracle’
WHEN p.id IS NULL THEN ‘Missing in PostgreSQL’
WHEN o.name != p.name OR o.amount != p.amount THEN ‘Data mismatch’
END as issue_type,
o.name as oracle_name, p.name as postgres_name,
o.amount as oracle_amount, p.amount as postgres_amount
FROM oracle_data o
FULL OUTER JOIN postgres_data p ON o.id = p.id
WHERE o.id IS NULL OR p.id IS NULL
OR o.name != p.name OR o.amount != p.amount;

— Performance comparison
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM orders WHERE customer_id = 123 AND order_date >= ‘2024-01-01’;
Blog Post 15: Post-Migration Optimization and Governance
Category: Post-Migration
Tags: optimization, governance, monitoring, performance

Post-Migration Checklist
Immediate post-cutover tasks:

Monitor error logs: Look for conversion issues

Verify replication (if using logical replication for cutover)

Check performance metrics: Compare with baseline

Validate backup procedures: Test backup/restore on PostgreSQL

Update documentation: Connection strings, procedures, runbooks

Performance optimization tasks:

sql
— 1. Update statistics
ANALYZE VERBOSE;

— 2. Reindex critical tables
REINDEX TABLE CONCURRENTLY large_table;

— 3. Check for missing indexes
— Install hypopg for hypothetical indexes
CREATE EXTENSION hypopg;

— Check query performance
SELECT query, calls, total_time, mean_time,
rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

— 4. Tune autovacuum for new workload
— Monitor bloat
SELECT schemaname, tablename,
n_live_tup, n_dead_tup,
round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_pct DESC;
Performance Comparison and Tuning
Common PostgreSQL performance issues after migration:

N+1 query problems: More obvious in PostgreSQL due to connection overhead

Missing indexes: Different query patterns may need different indexes

Transaction duration: Long-running transactions cause VACUUM issues

Connection management: Process-per-connection model needs pooling

Optimization examples:

sql
— Oracle style that needs optimization
— Original (may work okay in Oracle)
SELECT * FROM orders o
WHERE o.customer_id IN (
SELECT customer_id FROM customers WHERE region = ‘West’
);

— Optimized for PostgreSQL
— Option 1: Use EXISTS
SELECT o.* FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.customer_id = o.customer_id
AND c.region = ‘West’
);

— Option 2: Use JOIN
SELECT o.* FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.region = ‘West’;

— Batch updates instead of row-by-row
— Instead of application loop doing:
— UPDATE orders SET status = ‘processed’ WHERE id = ?
— Do:
UPDATE orders SET status = ‘processed’
WHERE id IN (SELECT id FROM pending_orders LIMIT 1000);
Governance and Operational Procedures
Change management:

sql
— Use migration files with version control
— migration_001_create_tables.sql
— migration_002_add_indexes.sql
— migration_003_fix_data_types.sql

— Use extension for schema versioning
CREATE TABLE schema_migrations (
version VARCHAR(50) PRIMARY KEY,
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
checksum VARCHAR(64),
description TEXT
);

— Record migration
INSERT INTO schema_migrations (version, description)
VALUES (‘001’, ‘Initial schema from Oracle migration’);
Access control review:

sql
— Audit role assignments
SELECT r.rolname,
ARRAY_AGG(m.rolname) as member_of,
r.rolsuper, r.rolinherit, r.rolcreaterole,
r.rolcreatedb, r.rolcanlogin
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolname NOT LIKE ‘pg_%’
GROUP BY r.oid, r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin
ORDER BY r.rolname;

— Review table privileges
SELECT grantee, table_schema, table_name,
string_agg(privilege_type, ‘, ‘) as privileges
FROM information_schema.role_table_grants
WHERE grantee NOT IN (‘postgres’, ‘PUBLIC’)
GROUP BY grantee, table_schema, table_name
ORDER BY table_schema, table_name, grantee;
Monitoring setup:

sql
— Install essential monitoring extensions
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pgstattuple;

— Create monitoring user
CREATE USER monitor WITH PASSWORD ‘monitor_pass’;
GRANT pg_monitor TO monitor;

— Configure monitoring queries
— Example: Long-running transactions
SELECT pid, usename, application_name, client_addr,
now() – xact_start as duration, state, query
FROM pg_stat_activity
WHERE state != ‘idle’
AND now() – xact_start > INTERVAL ‘5 minutes’
ORDER BY duration DESC;
Blog Post 16: Real-World PostgreSQL Case Studies
Category: Case Studies
Tags: case-studies, real-world, lessons-learned, migration

E-commerce Migration: Oracle to PostgreSQL
Background:

Large e-commerce platform with 500GB database

Peak: 10,000 orders/hour, 500 concurrent users

Complex PL/SQL packages for pricing and inventory

Challenges encountered:

Package conversion: 200+ PL/SQL packages with interdependencies

Performance regression: Some queries 10x slower initially

Data type issues: Oracle DATE vs PostgreSQL TIMESTAMP confusion

Application framework: ORM generated non-optimal SQL

Solutions implemented:

sql
— 1. Used ora2pg for initial conversion
— 2. Created compatibility schema with Oracle functions
CREATE SCHEMA oracle_compat;
CREATE FUNCTION oracle_compat.add_months(d DATE, m INT)
RETURNS DATE AS $$
SELECT (d + (m || ‘ months’)::INTERVAL)::DATE;
$$ LANGUAGE sql;

— 3. Implemented connection pooling with pgbouncer
— 4. Added strategic indexes missing in Oracle version
CREATE INDEX CONCURRENTLY idx_order_status_created
ON orders(status, created_at)
WHERE status IN (‘pending’, ‘processing’);

— 5. Rewrote problematic queries
— Original (slow in PostgreSQL):
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

— Optimized:
SELECT * FROM orders
WHERE order_date >= ‘2024-01-01’
AND order_date < ‘2025-01-01’;
Results:

30% reduction in licensing costs

40% better write performance

20% slower on some analytical queries (addressed with additional indexes)

Successfully handled Black Friday peak loads

Financial Services: Hybrid Migration Approach
Background:

Regulatory requirements for data retention (10+ years)

Real-time trading platform with microsecond latency requirements

Cannot afford extended downtime

Strategy:

Phased migration: Non-critical reports first, then OLTP

Dual-write approach: Write to both databases during transition

Data validation: Continuous comparison between Oracle and PostgreSQL

Implementation:

python
# Dual-write pattern
class DualWriteDatabase:
def __init__(self, oracle_conn, postgres_conn):
self.oracle = oracle_conn
self.postgres = postgres_conn

def execute(self, query, params):
# Execute in both databases
oracle_result = self.oracle.execute(query, params)
postgres_result = self.postgres.execute(
self.rewrite_sql(query), params
)

# Compare results (for SELECT queries)
if query.strip().upper().startswith(‘SELECT’):
self.validate_results(oracle_result, postgres_result)

return postgres_result # Return PostgreSQL result

def rewrite_sql(self, oracle_sql):
# SQL rewriting logic
# …
return postgres_sql
Lessons learned:

Dual-write adds complexity but reduces risk

Some financial functions (ROUND, TRUNC) behave differently

PostgreSQL’s stricter SQL compliance caught several bugs

Need careful transaction coordination between databases

SaaS Application: Multi-tenant Migration
Background:

2TB database with 5,000+ tenants

Each tenant isolated via schema

Need zero-downtime migration

Solution:

Tenant-by-tenant migration: Move tenants gradually

Logical replication per-tenant: Each tenant schema replicated independently

Blue-green deployment: Route tenants to old or new database based on migration status

sql
— Automated tenant migration script
CREATE OR REPLACE FUNCTION migrate_tenant(
tenant_id VARCHAR,
oracle_schema VARCHAR,
postgres_schema VARCHAR
) RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
start_time TIMESTAMP;
BEGIN
start_time := clock_timestamp();

— 1. Create publication in Oracle (via DB link)
EXECUTE format(
‘CREATE PUBLICATION pub_%s FOR TABLES IN SCHEMA %s’,
tenant_id, oracle_schema
);

— 2. Create schema in PostgreSQL
EXECUTE format(‘CREATE SCHEMA IF NOT EXISTS %I’, postgres_schema);

— 3. Create subscription
EXECUTE format(
‘CREATE SUBSCRIPTION sub_%s
CONNECTION ”host=oracle-db dbname=orcl”
PUBLICATION pub_%s’,
tenant_id, tenant_id
);

— 4. Wait for sync
PERFORM pg_sleep(60); — Wait for initial sync

— 5. Cutover (update routing)
UPDATE tenant_routing
SET database_host = ‘postgres-host’,
database_port = 5432
WHERE tenant_id = migrate_tenant.tenant_id;

— 6. Cleanup subscription
EXECUTE format(‘DROP SUBSCRIPTION sub_%s’, tenant_id);

RAISE NOTICE ‘Migrated tenant % in %’,
tenant_id, clock_timestamp() – start_time;
END;
$$;
Key takeaways:

Logical replication excellent for schema-level migration

Tenant isolation made rollback easier if issues

Need automation for large number of tenants

Monitoring crucial during phased migration

Blog Post 17: Future of PostgreSQL and Essential Resources
Category: PostgreSQL Future
Tags: roadmap, cloud-native, community, resources

PostgreSQL Roadmap and Emerging Features
PostgreSQL 16+ improvements:

Parallel query enhancements: Better utilization of multiple cores

Logical replication improvements: Two-phase commit, row filtering, column lists

Performance: IO prefetching, better planner statistics

Monitoring: More detailed wait events, better observability

Upcoming features to watch:

Built-in sharding: Native sharding support (beyond Citus)

Columnar storage: For analytical workloads

Better Oracle compatibility: More built-in functions and behaviors

Enhanced JSON support: JSON schema validation, better performance

Cloud-Native PostgreSQL
Managed services evolution:

AWS RDS/Aurora, Google Cloud SQL, Azure Database for PostgreSQL

Serverless PostgreSQL options

Cross-region replication and global databases

Kubernetes operators:

Crunchy Data Postgres Operator

Zalando Postgres Operator

CloudNativePG (CNPG)

yaml
# Example CloudNativePG manifest
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgres-cluster
spec:
instances: 3
storage:
size: 100Gi
storageClass: fast-ssd

backup:
barmanObjectStore:
destinationPath: s3://backup-bucket/postgres
s3Credentials:
accessKeyId:
name: aws-credentials
key: ACCESS_KEY_ID
secretAccessKey:
name: aws-credentials
key: SECRET_ACCESS_KEY
Final Recommendations
For new projects:

Start with PostgreSQL unless you have specific Oracle requirements

Use latest stable version (PostgreSQL 15+ as of 2024)

Design for PostgreSQL idioms from the beginning

For migrations:

Do thorough assessment before committing

Consider hybrid approach for complex migrations

Invest in testing and validation

Plan for post-migration optimization

For operational excellence:

Monitor comprehensively: Not just uptime, but performance, bloat, replication

Automate everything: Backups, failover, upgrades, monitoring

Plan for growth: Partitioning, read replicas, connection pooling

Stay current: Regular upgrades (security and performance)

Invest in knowledge: PostgreSQL evolves rapidly

The PostgreSQL Community Advantage
PostgreSQL’s open-source nature provides unique advantages:

Transparency: You can see and understand how everything works

Community support: Active mailing lists, conferences, local user groups

Extensibility: Can modify or extend to meet specific needs

No vendor lock-in: Control your own destiny

Getting involved:

Attend PostgreSQL conferences (PGConf, FOSDEM PGDay)

Participate in mailing lists (pgsql-general, pgsql-performance)

Contribute bug reports, documentation, or code

Join local user groups (PUGs)

Blog Post 18: Essential PostgreSQL Extensions
Category: PostgreSQL Extensions
Tags: extensions, tools, utilities, monitoring

Performance and Monitoring Extensions
pg_stat_statements:

sql
— Track query performance
CREATE EXTENSION pg_stat_statements;

— Top 10 slowest queries
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
pg_partman:

sql
— Automated partition management
CREATE EXTENSION pg_partman;

— Create partitioned table
CREATE TABLE sensor_data (
id BIGSERIAL,
sensor_id INTEGER,
reading NUMERIC,
recorded_at TIMESTAMP
) PARTITION BY RANGE (recorded_at);

— Set up automatic partitioning
SELECT partman.create_parent(
‘public.sensor_data’,
‘recorded_at’,
‘native’,
‘daily’
);
pg_repack:

bash
# Reorganize tables to reduce bloat (online)
pg_repack -d mydb -t large_table
Security and Compliance Extensions
pgaudit:

sql
— Detailed audit logging
CREATE EXTENSION pgaudit;

— Configure audit policies
ALTER SYSTEM SET pgaudit.log = ‘read, write, ddl, role’;
ALTER SYSTEM SET pgaudit.log_relation = on;
postgresql_anonymizer:

sql
— Data anonymization for testing
CREATE EXTENSION anon CASCADE;

— Define anonymization rules
SECURITY LABEL FOR anon ON COLUMN users.email
IS ‘MASKED WITH FUNCTION anon.fake_email()’;

— Anonymize data
SELECT anon.anonymize_database();
Oracle Compatibility Extensions
orafce:

sql
— Oracle compatibility functions
CREATE EXTENSION orafce;

— Use Oracle-style functions
SELECT add_months(CURRENT_DATE, 3);
SELECT to_char(CURRENT_TIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’);
Blog Post 19: PostgreSQL DBA Daily Checklist
Category: PostgreSQL Operations
Tags: checklist, monitoring, maintenance, best-practices

Morning Checks
sql
— 1. Database availability
SELECT now() as current_time, version();

— 2. Connection count
SELECT COUNT(*) as active_connections,
COUNT(*) FILTER (WHERE state != ‘idle’) as active_queries
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

— 3. Replication status
SELECT client_addr, usename, application_name,
state, sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as replay_lag_bytes,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_lag
FROM pg_stat_replication;

— 4. Disk space
SELECT pg_size_pretty(pg_database_size(current_database())) as db_size;

— Check tablespace usage (if using tablespaces)
SELECT spcname,
pg_size_pretty(pg_tablespace_size(oid)) as size
FROM pg_tablespace;
Performance Health Checks
sql
— 1. Long-running queries
SELECT pid, usename, application_name,
now() – query_start as duration,
state, query
FROM pg_stat_activity
WHERE state != ‘idle’
AND now() – query_start > INTERVAL ‘5 minutes’
ORDER BY duration DESC;

— 2. Index usage
SELECT schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

— 3. Cache hit ratio
SELECT datname,
blks_hit, blks_read,
round(blks_hit::numeric * 100 / (blks_hit + blks_read), 2) as hit_ratio
FROM pg_stat_database
WHERE blks_hit + blks_read > 0
ORDER BY hit_ratio;

— 4. Table bloat
SELECT schemaname, tablename,
n_live_tup, n_dead_tup,
round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 1000
AND round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) > 10
ORDER BY dead_pct DESC;
Backup Verification
bash
# Check backup completion
# For pg_basebackup/WAL archiving
SELECT now() – last_archived_time as last_archive_age
FROM pg_stat_archiver;

# For logical backups
# Check if dump file exists and is recent
find /backups -name “*.dump” -mtime -1

# Test restore on standby server (weekly)
pg_restore –dbname=test_db –create /backups/full.dump

# Verify point-in-time recovery capability
# Check WAL archive continuity
SELECT archived_count, failed_count,
last_archived_wal, last_failed_wal
FROM pg_stat_archiver;
Security Checks
sql
— 1. Failed login attempts
SELECT COUNT(*) as failed_logins
FROM pg_stat_database
WHERE datname = current_database();

— 2. Superuser connections
SELECT usename, client_addr, application_name
FROM pg_stat_activity
WHERE usename = ‘postgres’
OR usename IN (SELECT rolname FROM pg_roles WHERE rolsuper);

— 3. Password expiration
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolvaliduntil IS NOT NULL
AND rolvaliduntil < now() + INTERVAL ‘7 days’;

— 4. SSL connections
SELECT COUNT(*) FILTER (WHERE ssl) as ssl_connections,
COUNT(*) FILTER (WHERE NOT ssl) as non_ssl_connections
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid);
Weekly Tasks
Review logs: Check for patterns of errors or warnings

Update statistics: For tables with volatile data

Check for unused objects: Tables, indexes, functions

Review monitoring alerts: Tune thresholds if needed

Capacity planning: Check growth trends

Test failover: If using HA setup

Apply patches: OS and PostgreSQL minor updates

Monthly Tasks
Performance review: Analyze trends, identify degradation

Security review: Audit logs, access patterns

Backup testing: Full restore test

Documentation update: Update runbooks, procedures

Review compliance: Ensure meeting regulatory requirements

Cost optimization: Review resource utilization vs costs

Conclusion
This comprehensive PostgreSQL guide has covered everything from basic administration to advanced migration strategies. PostgreSQL rewards those who invest time in understanding its principles. The database’s transparency allows deep diagnosis of issues that would be opaque in other systems. Its extensibility enables solutions tailored to specific needs rather than forcing workarounds.

As you continue your PostgreSQL journey:

Start simple: Master the basics before advanced features

Automate early: Time spent on automation pays dividends

Monitor comprehensively: What gets measured gets managed

Test thoroughly: Especially for migrations and upgrades

Engage with the community: Learn from others’ experiences

PostgreSQL continues to evolve rapidly. Stay curious, keep learning, and contribute back to the community that makes this powerful database possible.

Resources and Further Reading
Official Documentation
PostgreSQL Documentation

PostgreSQL Wiki

PostgreSQL Mailing Lists

Books
“PostgreSQL: Up and Running” by Regina Obe and Leo Hsu

“PostgreSQL Administration Cookbook” by Simon Riggs and Gianni Ciolli

“Mastering PostgreSQL” by Hans-Jürgen Schönig

Tools
pgAdmin – Administration and development platform

pgBackRest – Reliable backup/restore

Patroni – HA template for PostgreSQL

Barman – Backup and recovery manager

Community
PostgreSQL Conference – Global events calendar

PostgreSQL Slack – Community Slack

r/PostgreSQL – Reddit community

Client
|
v
Postmaster (PID 1)
|
+– Backend Process (per connection)
+– Background Processes
+– Replication Processes (if configured)

Shared Memory:

• Shared Buffers (cached data pages)
• WAL Buffers (write-ahead log buffers)
• Lock Tables (concurrency control)
• CLOG (commit status)

Background Processes:

• Checkpointer (writes dirty buffers)
• WAL Writer (flushes WAL buffers)
• Autovacuum Launcher (starts worker processes)
• Background Writer (writes dirty buffers gradually)
• Stats Collector (gathers statistics)

text

Each backend process handles one client connection, providing isolation but with higher memory overhead compared to thread-based models.

### 2.2 Memory Architecture

* **shared_buffers**: The primary disk cache, typically 25% of available RAM. This holds copies of table and index pages from disk.
* **work_mem**: Memory available for sort operations, hash tables, and bitmap operations per operation. Multiple operations in a query can each use this amount.
* **maintenance_work_mem**: Memory for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE.
* **wal_buffers**: Buffer for write-ahead log data before it's written to disk.
* **temp_buffers**: Memory for temporary tables.

```conf
# Example memory configuration for 64GB server
shared_buffers = 16GB
work_mem = 64MB
maintenance_work_mem = 2GB
wal_buffers = 16MB

2.3 MVCC Internals

PostgreSQL implements Multi-Version Concurrency Control by storing multiple versions of rows. Each row has system columns:

• xmin: Transaction ID that created the row version
• xmax: Transaction ID that deleted/updated the row (or 0 if alive)
• ctid: Physical location of this row version

Visibility rules determine which transactions see which row versions based on transaction IDs and snapshot information.

sql

-- View system columns (normally hidden)
SELECT xmin, xmax, ctid, * FROM orders WHERE id = 1;

-- Check current transaction ID
SELECT txid_current();

MVCC avoids locks for readers but creates “bloat” from dead row versions that must be cleaned up by VACUUM.

2.4 Transaction Log and Write-Ahead Logging

Every change is first written to the Write-Ahead Log (WAL) before being applied to data files. This ensures:

• Durability: Changes survive crashes
• Replication: WAL can be shipped to replicas
• Point-in-time recovery: WAL segments allow recovery to any moment

The WAL consists of 16MB segments stored in pg_wal directory. Checkpoints flush dirty buffers to disk and create recovery points.

---

Chapter 3: Installation, Configuration, and Best Practices

3.1 Cluster Initialization

bash

# Create PostgreSQL data directory
mkdir -p /pgdata/15/main
chown postgres:postgres /pgdata/15/main

# Initialize database cluster
sudo -u postgres initdb \
  -D /pgdata/15/main \
  --locale=en_US.UTF-8 \
  --encoding=UTF8 \
  --data-checksums

# Start PostgreSQL
sudo -u postgres pg_ctl -D /pgdata/15/main start

Checksums should always be enabled at initialization (cannot be changed later). They detect storage corruption.

3.2 Directory Layout

text

/pgdata/15/main/
  ├── base/           # Table data (database OID subdirectories)
  ├── global/         # Cluster-wide tables (pg_authid, pg_database)
  ├── pg_wal/         # Write-ahead log segments (16MB each)
  ├── pg_xact/        # Transaction commit status (CLOG)
  ├── pg_stat_tmp/    # Temporary statistics files
  ├── pg_subtrans/    # Subtransaction information
  ├── pg_twophase/    # Prepared transactions
  ├── pg_logical/     # Logical decoding data
  ├── postgresql.conf # Main configuration
  ├── pg_hba.conf     # Client authentication
  └── pg_ident.conf   # User name mapping

3.3 Core Configuration

conf

# CONNECTIONS
listen_addresses = '*'          # Listen on all interfaces
port = 5432                     # Default port
max_connections = 300           # Adjust based on workload

# MEMORY
shared_buffers = 8GB            # 25% of RAM for dedicated DB
work_mem = 32MB                 # Per-operation sort/hash memory
maintenance_work_mem = 1GB      # For VACUUM, CREATE INDEX
effective_cache_size = 24GB     # Planner estimate of disk cache

# WAL
wal_level = replica             # For replication, use 'logical' for logical decoding
max_wal_size = 4GB             # Soft limit for WAL size
min_wal_size = 1GB             # Minimum to keep for reuse

# CHECKPOINTS
checkpoint_timeout = 15min      # Time between checkpoints
checkpoint_completion_target = 0.9  # Spread checkpoint I/O

# AUTOVACUUM
autovacuum = on                 # Essential for bloat management
log_autovacuum_min_duration = 0 # Log all autovacuum activity
autovacuum_max_workers = 5      # Concurrent autovacuum workers
autovacuum_vacuum_scale_factor = 0.05  # Trigger after 5% changes
autovacuum_analyze_scale_factor = 0.02 # Trigger stats update after 2%

3.4 Authentication Configuration (pg_hba.conf)

conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Allow replication connections from localhost
local   replication     all                                     peer

# SSL connections from application servers
hostssl all             app_user        10.0.1.0/24             scram-sha-256

# Local connections
local   all             all                                     peer

# Read-only user from monitoring host
host    all             monitoring      192.168.1.100/32        md5

# Reject everything else
host    all             all             0.0.0.0/0               reject

3.5 Filesystem and OS Tuning

bash

# Set appropriate mount options in /etc/fstab
/dev/sdb1 /pgdata xfs defaults,noatime,nodiratime 0 0

# Set kernel parameters in /etc/sysctl.conf
vm.swappiness = 1
vm.dirty_ratio = 3
vm.dirty_background_ratio = 2
vm.overcommit_memory = 2

# Set limits in /etc/security/limits.conf
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 16384
postgres hard nproc 16384

---

Chapter 4: Storage, WAL, and Replication Internals

4.1 Heap Storage Model

Tables are stored as heaps of fixed-size pages (default 8KB):

text

Table "orders"
 ├─ Page 0 (Header, free space map, row pointers, rows)
 ├─ Page 1
 ├─ ...
 └─ Page N

Each row has:
 - Heap tuple header (24 bytes)
 - Null bitmap (if nullable columns)
 - Object ID (if WITH OIDS)
 - User data columns

Rows are referenced by CTID (page_number, tuple_index). Updates create new row versions (MVCC) with new CTIDs.

4.2 Index Storage and Types

• B-tree: Default, balanced tree for equality and range queries
• Hash: For equality only (PostgreSQL 10+ improved)
• GiST: Generalized Search Tree for complex data types
• SP-GiST: Space-partitioned GiST for non-balanced structures
• GIN: Generalized Inverted Index for array, JSON, full-text search
• BRIN: Block Range Index for large, naturally ordered tables

sql

-- Create index with fillfactor to control page density
CREATE INDEX idx_orders_date ON orders(order_date) 
WITH (fillfactor = 90);

-- Create partial index for subset of rows
CREATE INDEX idx_orders_active ON orders(id) 
WHERE status = 'active';

-- Create covering index with INCLUDE
CREATE INDEX idx_orders_customer ON orders(customer_id) 
INCLUDE (order_date, total_amount);

4.3 WAL Lifecycle and Archiving

text

1. Transaction makes change
2. Change written to WAL buffer in shared memory
3. WAL writer flushes buffer to WAL segment in pg_wal/
4. Checkpointer writes dirty data pages to heap/index files
5. Archiver copies completed WAL segments to archive storage

Enable archiving for point-in-time recovery:

conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f && aws s3 cp /archive/%f s3://backup-bucket/wal/%f'
archive_timeout = 300  # Force segment switch every 5 minutes

4.4 Replication: Physical and Logical

Physical replication (streaming replication):

• Byte-for-byty copy of WAL
• Entire database cluster replicated
• Replica is read-only (unless using bi-directional tools)

sql

-- On primary:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET wal_keep_size = 1024;  # MB of WAL to keep

-- On replica:
pg_basebackup -h primary-host -D /pgdata/15/main -U replicator -X stream
echo "primary_conninfo = 'host=primary-host user=replicator password=secret'" > recovery.conf

Logical replication:

• Row-level replication
• Can replicate subsets of tables
• Can have different schemas/tables on replica

sql

-- On source (publisher):
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- On target (subscriber):
CREATE SUBSCRIPTION sub_orders 
CONNECTION 'host=source-db dbname=app' 
PUBLICATION pub_orders;

4.5 Replication Slots

Prevent WAL deletion until consumed by all replicas:

sql

-- Create replication slot
SELECT * FROM pg_create_physical_replication_slot('replica1_slot');

-- Monitor slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn 
FROM pg_replication_slots;

Danger: If replica disappears, slot prevents WAL cleanup and can fill disk.

---

Chapter 5: Security, Roles, and Access Control

5.1 Role and Privilege Model

PostgreSQL uses role-based access control. Roles can be login roles (users) or group roles.

sql

-- Create login role with password
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure-password' 
VALID UNTIL '2025-12-31';

-- Create group role
CREATE ROLE finance_team NOLOGIN;

-- Add user to group
GRANT finance_team TO app_user;

-- Grant schema privileges
GRANT USAGE ON SCHEMA public TO finance_team;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO finance_team;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO finance_team;

5.2 Row-Level Security (RLS)

Fine-grained access control at row level:

sql

-- Enable RLS on table
CREATE TABLE patient_records (
    id SERIAL PRIMARY KEY,
    patient_id INTEGER,
    doctor_id INTEGER,
    notes TEXT
);

ALTER TABLE patient_records ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Doctors can see their own patients
CREATE POLICY doctor_access ON patient_records
FOR SELECT USING (doctor_id = current_user_id());

-- Patients can see their own records
CREATE POLICY patient_access ON patient_records
FOR SELECT USING (patient_id = current_user_id());

-- Doctors can update their patients' records
CREATE POLICY doctor_update ON patient_records
FOR UPDATE USING (doctor_id = current_user_id());

-- Check active policies
SELECT * FROM pg_policies WHERE tablename = 'patient_records';

5.3 Column-Level Security

sql

-- Revoke access to sensitive columns
GRANT SELECT(id, name, department) ON employees TO hr_staff;
REVOKE SELECT(salary, ssn) ON employees FROM hr_staff;

-- Create view with masked columns
CREATE VIEW employee_directory AS
SELECT id, name, department, 
       '*****' AS salary_masked,
       '***-**-' || RIGHT(ssn, 4) AS ssn_masked
FROM employees;

GRANT SELECT ON employee_directory TO all_employees;

5.4 Encryption

In transit:

conf

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
ssl_min_protocol_version = 'TLSv1.2'

# pg_hba.conf
hostssl all all 0.0.0.0/0 scram-sha-256

At rest:

• Use filesystem encryption (LUKS, BitLocker)
• Use cloud storage encryption (EBS encryption, Azure Disk Encryption)
• Use transparent data encryption extensions (pg_tde, pgcrypto for selective encryption)

sql

-- Column-level encryption with pgcrypto
CREATE EXTENSION pgcrypto;

INSERT INTO sensitive_data 
VALUES (encrypt('secret value', 'encryption-key', 'aes'));

5.5 Audit Logging

sql

-- Install audit extension
CREATE EXTENSION pgaudit;

-- Configure in postgresql.conf
pgaudit.log = 'read, write, ddl, role'
pgaudit.log_relation = on
pgaudit.log_catalog = off

-- Or use native logging
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

---

Chapter 6: Backup, Recovery, and High Availability

6.1 Backup Strategies

Logical backups (pg_dump/pg_dumpall):

bash

# Single database, custom format (supports parallel restore)
pg_dump -Fc -d mydb -f mydb.dump

# Directory format (parallel dump/restore)
pg_dump -Fd -d mydb -j 4 -f /backup/mydb/

# Plain SQL format
pg_dump -Fp -d mydb -f mydb.sql

# Global objects (roles, tablespaces)
pg_dumpall -g -f globals.sql

Physical backups:

bash

# Base backup with pg_basebackup
pg_basebackup -D /backup/basebackup -X stream -P -U replicator

# Manual base backup
SELECT pg_start_backup('weekly_backup', false);
-- Use filesystem tools to copy data directory
SELECT pg_stop_backup();

6.2 Point-in-Time Recovery (PITR)

text

Timeline:
Base Backup  -->  WAL Segments  -->  Recovery to 2024-01-15 14:30:00
   ^
   |
 Target Recovery Point

1. Restore base backup
2. Configure recovery parameters:conf# recovery.conf (PostgreSQL 12+ uses postgresql.conf) restore_command = ‘cp /archive/wal/%f %p’ recovery_target_time = ‘2024-01-15 14:30:00’ recovery_target_action = ‘promote’
3. Create recovery.signal file: touch /pgdata/recovery.signal
4. Start PostgreSQL

6.3 High Availability Patterns

Primary-Replica with Automated Failover:

text

Primary -> Replica1 (sync standby)
       -> Replica2 (async standby)
       
Failover: Replica1 promotes when Primary fails

Using Patroni for automatic failover:

yaml

scope: mycluster
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.1:8008

etcd:
  hosts: 10.0.1.10:2379,10.0.1.11:2379,10.0.1.12:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"

Multi-Master Solutions:

• BDR (Bi-Directional Replication) for PostgreSQL 10+
• Citus for distributed PostgreSQL (sharding)
• Application-level conflict resolution

6.4 Backup Verification and Monitoring

sql

-- Monitor backup age
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Check WAL archive status
SELECT archive_mode, 
       archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

-- Verify backups with pgBackRest verify
pgbackrest --stanza=mydb verify

---

Chapter 7: Performance Tuning and Query Optimization

7.1 Understanding the Query Planner

sql

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- Explain with actual execution statistics
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 123;

-- Explain with verbose output
EXPLAIN (VERBOSE, COSTS, SETTINGS, WAL, BUFFERS, TIMING, SUMMARY, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;

-- Generate query plan as text diagram
EXPLAIN (FORMAT YAML) SELECT * FROM orders;

Key plan operations:

• Seq Scan: Full table scan
• Index Scan: Index lookup then heap fetch
• Index Only Scan: All data from index
• Bitmap Heap Scan: Multiple index scans combined
• Nested Loop: Small tables joined
• Hash Join: Medium tables with equality joins
• Merge Join: Sorted tables with range/equality joins

7.2 Indexing Strategies

When to index:

• Columns in WHERE, JOIN, ORDER BY, GROUP BY clauses
• Foreign key columns (improves CASCADE operations)
• Columns with high selectivity

When NOT to index:

• Tables with frequent bulk updates
• Columns with low cardinality (unless using partial indexes)
• Small tables (< 10,000 rows)

Specialized indexes:

sql

-- Multi-column index for specific query patterns
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

-- Expression index for case-insensitive search
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Partial index for subset of data
CREATE INDEX idx_orders_active ON orders(id) 
WHERE status = 'active';

-- Covering index with INCLUDE
CREATE INDEX idx_orders_customer_cover ON orders(customer_id)
INCLUDE (order_date, total_amount);

-- BRIN for large, sorted tables
CREATE INDEX idx_sensor_readings_time ON sensor_readings 
USING BRIN(timestamp);

7.3 Autovacuum Tuning

Autovacuum is critical for PostgreSQL performance. Monitor with:

sql

-- Check autovacuum activity
SELECT schemaname, relname,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables;

-- Check table bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS bloat_size,
       n_dead_tup,
       n_live_tup
FROM pg_stat_user_tables
ORDER BY bloat_size DESC NULLS LAST;

Tuning parameters:

conf

# Global settings
autovacuum_vacuum_cost_limit = 2000  # Increase from default 200
autovacuum_vacuum_cost_delay = 2ms   # Reduce from default 20ms
autovacuum_max_workers = 5           # Default 3, increase for many tables

# Table-specific settings
ALTER TABLE large_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_threshold = 2000
);

7.4 Query Optimization Techniques

Common optimizations:

sql

-- 1. Avoid SELECT * when not needed
SELECT id, name FROM users; -- Instead of SELECT *

-- 2. Use EXISTS instead of IN for subqueries
SELECT * FROM orders o 
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.active);

-- 3. Use UNION ALL instead of UNION when duplicates don't matter
SELECT id FROM table1
UNION ALL
SELECT id FROM table2;

-- 4. Avoid functions on indexed columns in WHERE clauses
-- Bad: WHERE UPPER(name) = 'JOHN'
-- Good: WHERE name = 'john' (with case-sensitive) or use expression index

-- 5. Use LIMIT for exploratory queries
SELECT * FROM large_table LIMIT 100;

-- 6. Batch updates and deletes
-- Instead of one-per-row in application loop
UPDATE orders SET status = 'processed' 
WHERE id IN (SELECT id FROM pending_orders LIMIT 1000);

-- 7. Use TEMP tables for complex multi-step operations
CREATE TEMP TABLE temp_results AS
SELECT ... FROM ... WHERE ...;
CREATE INDEX ON temp_results(...);
SELECT ... FROM temp_results JOIN ...;

7.5 Connection Pooling

Prevent connection overload with pooling:

pgbouncer configuration (transaction pooling):

ini

[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

Application-side pooling:

python

# Python with psycopg2 pool
from psycopg2 import pool

connection_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    host='localhost',
    database='mydb',
    user='app_user'
)

---

Chapter 8: Monitoring, Observability, and Troubleshooting

8.1 Essential Monitoring Views

sql

-- Active queries
SELECT pid, usename, application_name, client_addr,
       query_start, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Query statistics (requires pg_stat_statements)
SELECT query, calls, total_time, mean_time, rows,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- Database statistics
SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit,
       100.0 * blks_hit / nullif(blks_hit + blks_read, 0) AS cache_hit_ratio
FROM pg_stat_database;

-- Table access patterns
SELECT schemaname, relname,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del,
       n_tup_hot_upd, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

8.2 Lock Diagnosis

sql

-- Current locks
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS current_statement_in_blocking_process
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;

-- Lock waits
SELECT pid, wait_event_type, wait_event, query, age(clock_timestamp(), query_start) AS query_age
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL;

8.3 Troubleshooting Methodology

text

1. SYMPTOM: Slow queries / high CPU / connections spiking
   |
2. METRICS: Check pg_stat_activity, pg_stat_statements
   |
3. DIAGNOSE: Identify blocking queries, missing indexes, lock contention
   |
4. ROOT CAUSE: 
   ├─ Query pattern issue
   ├─ Missing index
   ├─ Lock contention  
   ├─ Resource exhaustion
   └─ Configuration problem
   |
5. FIX: Apply appropriate remediation
   ├─ Terminate problematic queries
   ├─ Add missing indexes
   ├─ Optimize queries
   ├─ Adjust configuration
   └─ Scale resources

8.4 Alerting and Dashboarding

Key metrics to alert on:

• Replication lag > 30 seconds
• Dead tuples > 10% of live tuples
• Cache hit ratio < 90%
• Disk usage > 85%
• Failed connections per minute
• Long-running queries (> 5 minutes)

Prometheus exporters:

• postgres_exporter for PostgreSQL metrics
• pgbackrest_exporter for backup metrics

Grafana dashboard queries:

sql

-- Connection count over time
SELECT time_bucket('5 minutes', created_at) AS time,
       COUNT(*) as connections
FROM pg_stat_activity 
GROUP BY time_bucket('5 minutes', created_at)
ORDER BY time;

---

Chapter 9: Scaling PostgreSQL: Vertical and Horizontal

9.1 Vertical Scaling

Increase resources on single instance:

• CPU: More cores for parallel queries
• RAM: Larger shared_buffers, work_mem
• Disk: Faster storage (NVMe), more IOPS
• Network: Higher throughput

Configuration for large instances (> 64GB RAM):

conf

shared_buffers = 32GB
effective_cache_size = 48GB
work_mem = 128MB
maintenance_work_mem = 4GB
max_parallel_workers_per_gather = 4
max_worker_processes = 16
max_parallel_workers = 16

9.2 Read Scaling with Replicas

text

Primary -> Replica1 (sync, for HA)
        -> Replica2 (async, for reporting)
        -> Replica3 (async, for backups)

Load balancing configuration:

yaml

# HAProxy configuration
backend postgres_read
    balance leastconn
    option httpchk OPTIONS /master
    server primary 10.0.1.1:5432 check port 8008
    server replica1 10.0.1.2:5432 check port 8008 backup
    server replica2 10.0.1.3:5432 check port 8008 backup

9.3 Partitioning

Declarative partitioning (PostgreSQL 10+):

sql

-- Create partitioned table
CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

-- Create partitions
CREATE TABLE measurement_y2023 PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE measurement_y2024 PARTITION OF measurement
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Create index on partitioned table
CREATE INDEX ON measurement (logdate, city_id);

-- Automatic partition creation with pg_partman
SELECT partman.create_parent('public.measurement', 'logdate', 'native', 'monthly');

Partitioning strategies:

• Range: Time series, numeric ranges
• List: Discrete values (regions, categories)
• Hash: Even distribution

9.4 Sharding with Citus

sql

-- Enable Citus extension
CREATE EXTENSION citus;

-- Create distributed table
SELECT create_distributed_table('orders', 'customer_id');

-- Add worker nodes
SELECT * from master_add_node('10.0.2.1', 5432);
SELECT * from master_add_node('10.0.2.2', 5432);

-- Query across shards
SELECT customer_id, COUNT(*) 
FROM orders 
WHERE order_date >= NOW() - INTERVAL '30 days'
GROUP BY customer_id;

9.5 Connection Pooling and Caching Layers

Application-level caching:

• Redis/Memcached for frequent reads
• Materialized views for complex aggregations
• Application-level result caching

Read-through cache pattern:

python

def get_user(user_id):
    # Try cache first
    user = cache.get(f"user:{user_id}")
    if user:
        return user
    
    # Cache miss, read from database
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
    
    # Store in cache with expiry
    cache.setex(f"user:{user_id}", 300, user)
    return user

---

Chapter 10: Automation, DevOps, and Cloud Operations

10.1 Infrastructure as Code

Terraform for cloud PostgreSQL:

hcl

resource "aws_db_instance" "postgres" {
  identifier     = "prod-postgres"
  engine         = "postgres"
  engine_version = "15.3"
  instance_class = "db.r5.2xlarge"
  
  allocated_storage = 500
  storage_type     = "gp3"
  
  db_name  = "appdb"
  username = var.db_username
  password = var.db_password
  
  vpc_security_group_ids = [aws_security_group.db.id]
  db_subnet_group_name   = aws_db_subnet_group.main.name
  
  backup_retention_period = 30
  backup_window           = "03:00-04:00"
  maintenance_window      = "sun:04:00-sun:05:00"
  
  deletion_protection = true
  skip_final_snapshot = false
}

Ansible for PostgreSQL configuration:

yaml

- name: Configure PostgreSQL
  hosts: dbservers
  vars:
    postgres_version: 15
    postgres_data_dir: "/pgdata/{{ postgres_version }}/main"
  
  tasks:
  - name: Install PostgreSQL
    apt:
      name: "postgresql-{{ postgres_version }}"
      state: present
  
  - name: Create data directory
    file:
      path: "{{ postgres_data_dir }}"
      state: directory
      owner: postgres
      group: postgres
  
  - name: Configure postgresql.conf
    template:
      src: postgresql.conf.j2
      dest: "{{ postgres_data_dir }}/postgresql.conf"
      owner: postgres
      group: postgres

10.2 CI/CD for Database Changes

Flyway migration example:

sql

-- V1__create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- V2__add_user_profile.sql
ALTER TABLE users ADD COLUMN profile JSONB;
CREATE INDEX idx_users_profile ON users USING GIN(profile);

Sqitch migration tool:

bash

# Add change
sqitch add add_users_table -n "Create users table"

# Deploy to staging
sqitch deploy db:pg://user@staging-db/appdb

# Verify deployment
sqitch verify db:pg://user@staging-db/appdb

# Promote to production
sqitch deploy db:pg://user@prod-db/appdb

10.3 Blue-Green Deployments

text

Stage 1: Old Cluster (Green)
  |
Stage 2: Create New Cluster (Blue) with logical replication
  |
Stage 3: Sync data between Green and Blue
  |
Stage 4: Cutover: Point apps to Blue
  |
Stage 5: Decommission Green

Implementation with logical replication:

sql

-- On new cluster (Blue)
CREATE PUBLICATION blue_pub FOR ALL TABLES;

-- On old cluster (Green)
CREATE SUBSCRIPTION green_to_blue 
CONNECTION 'host=blue-cluster dbname=app' 
PUBLICATION blue_pub;

-- Verify sync
SELECT * FROM pg_stat_subscription;

-- Cutover: Update application connection strings
-- Stop subscription
ALTER SUBSCRIPTION green_to_blue DISABLE;

---

Chapter 11: PostgreSQL vs Oracle: Conceptual Differences

11.1 Architectural Comparison

Feature	Oracle	PostgreSQL
Concurrency Model	Read consistency with UNDO segments	MVCC with heap tuples
Storage Model	Segments, Extents, Data Blocks	Heap files, TOAST for large values
Memory Model	SGA (Shared Global Area)	Shared buffers + per-backend memory
Process Model	Multi-threaded server process	Process-per-connection
Data Dictionary	Static data dictionary views	System catalogs as regular tables

11.2 Key Syntax and Behavior Differences

Sequences:

sql

-- Oracle
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 CACHE 20;

-- PostgreSQL
CREATE SEQUENCE order_seq START 1 INCREMENT 1 CACHE 20;

-- Oracle usage
INSERT INTO orders (id, ...) VALUES (order_seq.NEXTVAL, ...);

-- PostgreSQL usage
INSERT INTO orders (id, ...) VALUES (nextval('order_seq'), ...);
-- Or with SERIAL type (auto-increment)
CREATE TABLE orders (id SERIAL PRIMARY KEY, ...);

String concatenation:

sql

-- Oracle
SELECT first_name || ' ' || last_name FROM employees;

-- PostgreSQL (same syntax, but careful with NULL)
SELECT first_name || ' ' || last_name FROM employees;
-- Better with NULL handling
SELECT CONCAT(first_name, ' ', last_name) FROM employees;

Date/Time handling:

sql

-- Oracle
SELECT SYSDATE FROM dual;
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM dual;

-- PostgreSQL
SELECT CURRENT_DATE;
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD');
-- Or better (ISO format)
SELECT '2024-01-15'::DATE;

11.3 Transaction and Locking Differences

• Oracle: Readers don’t block writers, writers don’t block readers (UNDO-based)
• PostgreSQL: Readers never block, but VACUUM required to clean old versions
• Oracle: Default isolation is READ COMMITTED with statement-level consistency
• PostgreSQL: Default isolation is READ COMMITTED with transaction-level consistency

Deadlock handling:

sql

-- Oracle: Automatically detects and rolls back one transaction
-- PostgreSQL: Also detects deadlocks, but error message differs
ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456; 
        blocked by process 789.

---

Chapter 12: Oracle to PostgreSQL Migration Strategy

12.1 Migration Phases

text

┌─────────────────────────────────────────────────────────┐
│  Phase 1: Assessment & Planning                         │
│  • Inventory applications and databases                 │
│  • Analyze complexity and dependencies                  │
│  • Estimate effort and create migration plan            │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│  Phase 2: Schema Conversion                             │
│  • Convert DDL with migration tools                     │
│  • Map data types and objects                           │
│  • Handle Oracle-specific features                      │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│  Phase 3: Data Migration                                │
│  • Extract data from Oracle                             │
│  • Transform to PostgreSQL format                       │
│  • Load into PostgreSQL                                 │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│  Phase 4: Code Migration                                │
│  • Convert PL/SQL to PL/pgSQL                           │
│  • Rewrite application SQL                              │
│  • Handle application logic changes                     │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│  Phase 5: Testing & Validation                          │
│  • Functional testing                                   │
│  • Performance testing                                  │
│  • Data integrity verification                          │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│  Phase 6: Cutover & Optimization                        │
│  • Production migration                                 │
│  • Post-migration tuning                                │
│  • Monitoring and support                               │
└─────────────────────────────────────────────────────────┘

12.2 Migration Tools Overview

Commercial tools:

• AWS SCT (Schema Conversion Tool): Free from AWS, good for schema conversion
• Ispirer MnMTK: Comprehensive tool for schema and code migration
• EnterpriseDB Migration Toolkit: From Oracle to EDB Postgres

Open source tools:

• ora2pg: Perl-based, highly configurable
• pgloader: Data loading with transformation capabilities
• Foreign Data Wrappers (oracle_fdw): Direct data access

Example with ora2pg:

bash

# Install ora2pg
sudo apt-get install ora2pg

# Create configuration
ora2pg --project_base /migration/project \
       --init_project my_migration

# Edit configuration
vi /migration/project/my_migration/config/ora2pg.conf

# Generate migration report
ora2pg --project_base /migration/project \
       --type SHOW_REPORT

# Export schema
ora2pg --project_base /migration/project \
       --type TABLE \
       --basedir /migration/project/my_migration \
       --parallel 4

12.3 Risk Assessment Matrix

Risk Category	Impact	Mitigation
SQL Dialect Differences	High	Comprehensive testing, SQL translation layer
PL/SQL to PL/pgSQL Conversion	High	Use automated tools, manual review
Performance Assumptions	Medium	Load testing, query optimization
Data Type Mismatches	Medium	Thorough data validation
Application Framework Compatibility	Medium	Early POC with representative workload

---

Chapter 13: Schema, Data, and Code Migration

13.1 Data Type Mapping

Oracle Data Type	PostgreSQL Equivalent	Notes
NUMBER	NUMERIC or DECIMAL	Use same precision/scale
NUMBER(10)	INTEGER
VARCHAR2(n)	VARCHAR(n)	Oracle VARCHAR2 is variable length
CHAR(n)	CHAR(n)	Fixed-length, blank-padded
DATE	TIMESTAMP(0)	Oracle DATE includes time
TIMESTAMP	TIMESTAMP
CLOB	TEXT	PostgreSQL TEXT handles large strings
BLOB	BYTEA	Binary large object
RAW(n)	BYTEA	Variable raw binary
ROWID	CTID	Physical row identifier (system column)
BINARY_FLOAT	REAL	32-bit floating point
BINARY_DOUBLE	DOUBLE PRECISION	64-bit floating point

Special considerations:

sql

-- Oracle implicit conversions
-- Oracle: SELECT * FROM t WHERE char_column = 123; -- Works
-- PostgreSQL: ERROR: operator does not exist: character = integer
-- Fix: SELECT * FROM t WHERE char_column = '123';

-- Date arithmetic
-- Oracle: date_column + 1 (adds one day)
-- PostgreSQL: date_column + INTERVAL '1 day'

13.2 Schema Object Conversion

Tables:

sql

-- Oracle
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    commission_pct NUMBER(2,2),
    photo BLOB
) TABLESPACE users;

-- PostgreSQL
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    hire_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
    salary NUMERIC(10,2),
    commission_pct NUMERIC(2,2),
    photo BYTEA
);

Indexes:

sql

-- Oracle
CREATE INDEX idx_emp_name ON employees(emp_name) TABLESPACE index_ts;

-- PostgreSQL
CREATE INDEX idx_emp_name ON employees(emp_name);

-- Function-based index
-- Oracle: CREATE INDEX idx_upper_name ON employees(UPPER(emp_name));
-- PostgreSQL: CREATE INDEX idx_upper_name ON employees(UPPER(emp_name));

Sequences and Identity Columns:

sql

-- Oracle 12c+ identity column
CREATE TABLE orders (
    order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

-- PostgreSQL
CREATE TABLE orders (
    order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

-- Legacy Oracle sequence usage
-- Oracle: CREATE SEQUENCE order_seq;
-- PostgreSQL: CREATE SEQUENCE order_seq;
-- In application, replace .NEXTVAL with nextval()

13.3 PL/SQL to PL/pgSQL Conversion

Basic function conversion:

sql

-- Oracle PL/SQL
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary NUMBER,
    p_rating NUMBER
) RETURN NUMBER
IS
    v_bonus NUMBER;
BEGIN
    IF p_rating >= 4 THEN
        v_bonus := p_salary * 0.20;
    ELSIF p_rating >= 3 THEN
        v_bonus := p_salary * 0.10;
    ELSE
        v_bonus := 0;
    END IF;
    
    RETURN v_bonus;
END;
/

-- PostgreSQL PL/pgSQL
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary NUMERIC,
    p_rating NUMERIC
) RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_bonus NUMERIC;
BEGIN
    IF p_rating >= 4 THEN
        v_bonus := p_salary * 0.20;
    ELSIF p_rating >= 3 THEN
        v_bonus := p_salary * 0.10;
    ELSE
        v_bonus := 0;
    END IF;
    
    RETURN v_bonus;
END;
$$;

Package conversion:

sql

-- Oracle Package
CREATE OR REPLACE PACKAGE employee_mgmt AS
    PROCEDURE hire_employee(
        p_name VARCHAR2,
        p_dept_id NUMBER
    );
    FUNCTION get_employee_count RETURN NUMBER;
END employee_mgmt;
/

CREATE OR REPLACE PACKAGE BODY employee_mgmt AS
    PROCEDURE hire_employee(
        p_name VARCHAR2,
        p_dept_id NUMBER
    ) IS
    BEGIN
        INSERT INTO employees(name, dept_id) VALUES(p_name, p_dept_id);
    END;
    
    FUNCTION get_employee_count RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count FROM employees;
        RETURN v_count;
    END;
END employee_mgmt;
/

-- PostgreSQL equivalent (using schema or extensions)
-- Option 1: Separate functions in a schema
CREATE SCHEMA employee_mgmt;

CREATE OR REPLACE FUNCTION employee_mgmt.hire_employee(
    p_name VARCHAR,
    p_dept_id INTEGER
) RETURns VOID
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees(name, dept_id) VALUES(p_name, p_dept_id);
END;
$$;

CREATE OR REPLACE FUNCTION employee_mgmt.get_employee_count()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
END;
$$;

-- Option 2: Use Oracle compatibility extensions (orafce)
CREATE EXTENSION orafce;
-- Provides some package-like functionality

13.4 Data Migration Techniques

Using ora_fdw (Foreign Data Wrapper):

sql

-- Install oracle_fdw extension
CREATE EXTENSION oracle_fdw;

-- Create foreign server
CREATE SERVER oracle_db
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//oracle-host:1521/ORCL');

-- Create user mapping
CREATE USER MAPPING FOR postgres
SERVER oracle_db
OPTIONS (user 'oracle_user', password 'oracle_pass');

-- Create foreign table
CREATE FOREIGN TABLE oracle_employees (
    emp_id INTEGER,
    emp_name VARCHAR(100),
    hire_date TIMESTAMP,
    salary NUMERIC(10,2)
) SERVER oracle_db
OPTIONS (schema 'HR', table 'EMPLOYEES');

-- Copy data to local table
INSERT INTO postgres_employees
SELECT * FROM oracle_employees;

-- Or use CTAS (CREATE TABLE AS SELECT)
CREATE TABLE postgres_employees AS
SELECT * FROM oracle_employees;

Using pgloader:

bash

# pgloader configuration file
cat > migrate.load <<EOF
LOAD DATABASE
    FROM oracle://user:password@oracle-host:1521/ORCL
    INTO postgresql://user:password@postgres-host:5432/appdb

WITH include drop, create tables, create indexes,
     reset sequences, foreign keys,
     batch rows = 1000, batch size = 10MB

SET maintenance_work_mem to '128MB',
    work_mem to '12MB'

CAST type NUMBER to numeric drop typemod,
     type NUMBER(10) to integer,
     type DATE to timestamptz drop default drop not null
     
BEFORE LOAD DO
    $$ create schema if not exists hr; $$;
EOF

# Run pgloader
pgloader migrate.load

---

Chapter 14: Application Migration and Testing

14.1 Application SQL Compatibility Layer

Using schema compatibility functions:

sql

-- Create compatibility functions for common Oracle functions
CREATE OR REPLACE FUNCTION to_date(text, text)
RETURNS TIMESTAMP
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT to_timestamp($1, $2)::TIMESTAMP;
$$;

CREATE OR REPLACE FUNCTION to_char(timestamp, text)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT to_char($1, $2);
$$;

-- NVL equivalent
CREATE OR REPLACE FUNCTION nvl(anyelement, anyelement)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT COALESCE($1, $2);
$$;

-- DECODE function (simplified)
CREATE OR REPLACE FUNCTION decode(anyelement, anyelement, anyelement)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT CASE WHEN $1 = $2 THEN $3 ELSE NULL END;
$$;

CREATE OR REPLACE FUNCTION decode(anyelement, anyelement, anyelement, anyelement)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT CASE WHEN $1 = $2 THEN $3 ELSE $4 END;
$$;

Application-level SQL rewriting:

python

# SQL rewriting layer in application
def rewrite_sql(oracle_sql):
    """Convert Oracle SQL to PostgreSQL compatible SQL"""
    replacements = {
        r'(?i)\bSYSDATE\b': 'CURRENT_TIMESTAMP',
        r'(?i)\bNVL\(([^,]+),\s*([^)]+)\)': r'COALESCE(\1, \2)',
        r'(?i)\bTO_DATE\(([^,]+),\s*\'YYYY-MM-DD\'\)': r'\1::DATE',
        r'(?i)\bROWNUM\b': 'ROW_NUMBER() OVER ()',
        # Add more patterns as needed
    }
    
    postgres_sql = oracle_sql
    for pattern, replacement in replacements.items():
        postgres_sql = re.sub(pattern, replacement, postgres_sql)
    
    return postgres_sql

14.2 Connection and Transaction Management

Connection string differences:

python

# Oracle connection (Python cx_Oracle)
import cx_Oracle
dsn = cx_Oracle.makedsn("oracle-host", 1521, service_name="ORCL")
connection = cx_Oracle.connect(user="username", password="password", dsn=dsn)

# PostgreSQL connection (Python psycopg2)
import psycopg2
connection = psycopg2.connect(
    host="postgres-host",
    port=5432,
    database="appdb",
    user="username",
    password="password"
)

Transaction management:

python

# Oracle implicit behavior
# - DDL causes implicit commit
# - Some errors don't roll back transaction

# PostgreSQL explicit behavior
# - All DDL is transactional (can be rolled back)
# - Most errors abort the transaction

# Good practice: Use explicit transaction control
try:
    conn.autocommit = False
    cursor = conn.cursor()
    
    cursor.execute("INSERT INTO table1 ...")
    cursor.execute("UPDATE table2 ...")
    
    conn.commit()
except Exception as e:
    conn.rollback()
    raise e
finally:
    cursor.close()

14.3 Testing Strategy

Testing pyramid for database migration:

text

        ┌──────────────────────┐
        │   Load Testing       │  ← Simulate production workload
        │  (Performance)       │
        └──────────────────────┘
                 │
        ┌──────────────────────┐
        │ Integration Testing  │  ← Test application with migrated DB
        │   (End-to-end)       │
        └──────────────────────┘
                 │
        ┌──────────────────────┐
        │ Functional Testing   │  ← Test individual components
        │   (Unit tests)       │
        └──────────────────────┘

Data validation queries:

sql

-- Row count comparison
SELECT 'Oracle' as source, COUNT(*) as row_count FROM oracle_table@dblink
UNION ALL
SELECT 'PostgreSQL', COUNT(*) FROM postgres_table;

-- Data difference detection
WITH oracle_data AS (
    SELECT id, name, amount FROM oracle_table@dblink
),
postgres_data AS (
    SELECT id, name, amount FROM postgres_table
)
SELECT 
    COALESCE(o.id, p.id) as id,
    CASE 
        WHEN o.id IS NULL THEN 'Missing in Oracle'
        WHEN p.id IS NULL THEN 'Missing in PostgreSQL'
        WHEN o.name != p.name OR o.amount != p.amount THEN 'Data mismatch'
    END as issue_type,
    o.name as oracle_name, p.name as postgres_name,
    o.amount as oracle_amount, p.amount as postgres_amount
FROM oracle_data o
FULL OUTER JOIN postgres_data p ON o.id = p.id
WHERE o.id IS NULL OR p.id IS NULL 
   OR o.name != p.name OR o.amount != p.amount;

-- Performance comparison
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2024-01-01';

---

Chapter 15: Post-Migration Optimization and Governance

15.1 Post-Migration Checklist

Immediate post-cutover tasks:

1. Monitor error logs: Look for conversion issues
2. Verify replication (if using logical replication for cutover)
3. Check performance metrics: Compare with baseline
4. Validate backup procedures: Test backup/restore on PostgreSQL
5. Update documentation: Connection strings, procedures, runbooks

Performance optimization tasks:

sql

-- 1. Update statistics
ANALYZE VERBOSE;

-- 2. Reindex critical tables
REINDEX TABLE CONCURRENTLY large_table;

-- 3. Check for missing indexes
-- Install hypopg for hypothetical indexes
CREATE EXTENSION hypopg;

-- Check query performance
SELECT query, calls, total_time, mean_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- 4. Tune autovacuum for new workload
-- Monitor bloat
SELECT schemaname, tablename,
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_pct DESC;

15.2 Performance Comparison and Tuning

Common PostgreSQL performance issues after migration:

1. N+1 query problems: More obvious in PostgreSQL due to connection overhead
2. Missing indexes: Different query patterns may need different indexes
3. Transaction duration: Long-running transactions cause VACUUM issues
4. Connection management: Process-per-connection model needs pooling

Optimization examples:

sql

-- Oracle style that needs optimization
-- Original (may work okay in Oracle)
SELECT * FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id FROM customers WHERE region = 'West'
);

-- Optimized for PostgreSQL
-- Option 1: Use EXISTS
SELECT o.* FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.customer_id = o.customer_id 
    AND c.region = 'West'
);

-- Option 2: Use JOIN
SELECT o.* FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.region = 'West';

-- Batch updates instead of row-by-row
-- Instead of application loop doing:
--   UPDATE orders SET status = 'processed' WHERE id = ?
-- Do:
UPDATE orders SET status = 'processed'
WHERE id IN (SELECT id FROM pending_orders LIMIT 1000);

15.3 Governance and Operational Procedures

Change management:

sql

-- Use migration files with version control
-- migration_001_create_tables.sql
-- migration_002_add_indexes.sql
-- migration_003_fix_data_types.sql

-- Use extension for schema versioning
CREATE TABLE schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    checksum VARCHAR(64),
    description TEXT
);

-- Record migration
INSERT INTO schema_migrations (version, description) 
VALUES ('001', 'Initial schema from Oracle migration');

Access control review:

sql

-- Audit role assignments
SELECT r.rolname, 
       ARRAY_AGG(m.rolname) as member_of,
       r.rolsuper, r.rolinherit, r.rolcreaterole,
       r.rolcreatedb, r.rolcanlogin
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolname NOT LIKE 'pg_%'
GROUP BY r.oid, r.rolname, r.rolsuper, r.rolinherit, 
         r.rolcreaterole, r.rolcreatedb, r.rolcanlogin
ORDER BY r.rolname;

-- Review table privileges
SELECT grantee, table_schema, table_name, 
       string_agg(privilege_type, ', ') as privileges
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
GROUP BY grantee, table_schema, table_name
ORDER BY table_schema, table_name, grantee;

Monitoring setup:

sql

-- Install essential monitoring extensions
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pgstattuple;

-- Create monitoring user
CREATE USER monitor WITH PASSWORD 'monitor_pass';
GRANT pg_monitor TO monitor;

-- Configure monitoring queries
-- Example: Long-running transactions
SELECT pid, usename, application_name, client_addr,
       now() - xact_start as duration, state, query
FROM pg_stat_activity
WHERE state != 'idle' 
  AND now() - xact_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

---

Chapter 16: Case Studies and Real-World Lessons

16.1 E-commerce Migration: Oracle to PostgreSQL

Background:

• Large e-commerce platform with 500GB database
• Peak: 10,000 orders/hour, 500 concurrent users
• Complex PL/SQL packages for pricing and inventory

Challenges encountered:

1. Package conversion: 200+ PL/SQL packages with interdependencies
2. Performance regression: Some queries 10x slower initially
3. Data type issues: Oracle DATE vs PostgreSQL TIMESTAMP confusion
4. Application framework: ORM generated non-optimal SQL

Solutions implemented:

sql

-- 1. Used ora2pg for initial conversion
-- 2. Created compatibility schema with Oracle functions
CREATE SCHEMA oracle_compat;
CREATE FUNCTION oracle_compat.add_months(d DATE, m INT) 
RETURNS DATE AS $$
    SELECT (d + (m || ' months')::INTERVAL)::DATE;
$$ LANGUAGE sql;

-- 3. Implemented connection pooling with pgbouncer
-- 4. Added strategic indexes missing in Oracle version
CREATE INDEX CONCURRENTLY idx_order_status_created 
ON orders(status, created_at)
WHERE status IN ('pending', 'processing');

-- 5. Rewrote problematic queries
-- Original (slow in PostgreSQL):
SELECT * FROM orders 
WHERE EXTRACT(YEAR FROM order_date) = 2024;

-- Optimized:
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

Results:

• 30% reduction in licensing costs
• 40% better write performance
• 20% slower on some analytical queries (addressed with additional indexes)
• Successfully handled Black Friday peak loads

16.2 Financial Services: Hybrid Migration Approach

Background:

• Regulatory requirements for data retention (10+ years)
• Real-time trading platform with microsecond latency requirements
• Cannot afford extended downtime

Strategy:

1. Phased migration: Non-critical reports first, then OLTP
2. Dual-write approach: Write to both databases during transition
3. Data validation: Continuous comparison between Oracle and PostgreSQL

Implementation:

python

# Dual-write pattern
class DualWriteDatabase:
    def __init__(self, oracle_conn, postgres_conn):
        self.oracle = oracle_conn
        self.postgres = postgres_conn
    
    def execute(self, query, params):
        # Execute in both databases
        oracle_result = self.oracle.execute(query, params)
        postgres_result = self.postgres.execute(
            self.rewrite_sql(query), params
        )
        
        # Compare results (for SELECT queries)
        if query.strip().upper().startswith('SELECT'):
            self.validate_results(oracle_result, postgres_result)
        
        return postgres_result  # Return PostgreSQL result
    
    def rewrite_sql(self, oracle_sql):
        # SQL rewriting logic
        # ...
        return postgres_sql

Lessons learned:

• Dual-write adds complexity but reduces risk
• Some financial functions (ROUND, TRUNC) behave differently
• PostgreSQL’s stricter SQL compliance caught several bugs
• Need careful transaction coordination between databases

16.3 SaaS Application: Multi-tenant Migration

Background:

• 2TB database with 5,000+ tenants
• Each tenant isolated via schema
• Need zero-downtime migration

Solution:

1. Tenant-by-tenant migration: Move tenants gradually
2. Logical replication per-tenant: Each tenant schema replicated independently
3. Blue-green deployment: Route tenants to old or new database based on migration status

sql

-- Automated tenant migration script
CREATE OR REPLACE FUNCTION migrate_tenant(
    tenant_id VARCHAR,
    oracle_schema VARCHAR,
    postgres_schema VARCHAR
) RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    start_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
    
    -- 1. Create publication in Oracle (via DB link)
    EXECUTE format(
        'CREATE PUBLICATION pub_%s FOR TABLES IN SCHEMA %s',
        tenant_id, oracle_schema
    );
    
    -- 2. Create schema in PostgreSQL
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', postgres_schema);
    
    -- 3. Create subscription
    EXECUTE format(
        'CREATE SUBSCRIPTION sub_%s
         CONNECTION ''host=oracle-db dbname=orcl''
         PUBLICATION pub_%s',
        tenant_id, tenant_id
    );
    
    -- 4. Wait for sync
    PERFORM pg_sleep(60);  -- Wait for initial sync
    
    -- 5. Cutover (update routing)
    UPDATE tenant_routing 
    SET database_host = 'postgres-host',
        database_port = 5432
    WHERE tenant_id = migrate_tenant.tenant_id;
    
    -- 6. Cleanup subscription
    EXECUTE format('DROP SUBSCRIPTION sub_%s', tenant_id);
    
    RAISE NOTICE 'Migrated tenant % in %', 
        tenant_id, clock_timestamp() - start_time;
END;
$$;

Key takeaways:

• Logical replication excellent for schema-level migration
• Tenant isolation made rollback easier if issues
• Need automation for large number of tenants
• Monitoring crucial during phased migration

---

Chapter 17: Future of PostgreSQL and Final Thoughts

17.1 PostgreSQL Roadmap and Emerging Features

PostgreSQL 16+ improvements:

• Parallel query enhancements: Better utilization of multiple cores
• Logical replication improvements: Two-phase commit, row filtering, column lists
• Performance: IO prefetching, better planner statistics
• Monitoring: More detailed wait events, better observability

Upcoming features to watch:

1. Built-in sharding: Native sharding support (beyond Citus)
2. Columnar storage: For analytical workloads
3. Better Oracle compatibility: More built-in functions and behaviors
4. Enhanced JSON support: JSON schema validation, better performance

17.2 Cloud-Native PostgreSQL

Managed services evolution:

• AWS RDS/Aurora, Google Cloud SQL, Azure Database for PostgreSQL
• Serverless PostgreSQL options
• Cross-region replication and global databases

Kubernetes operators:

• Crunchy Data Postgres Operator
• Zalando Postgres Operator
• CloudNativePG (CNPG)

yaml

# Example CloudNativePG manifest
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  instances: 3
  storage:
    size: 100Gi
    storageClass: fast-ssd
  
  backup:
    barmanObjectStore:
      destinationPath: s3://backup-bucket/postgres
      s3Credentials:
        accessKeyId:
          name: aws-credentials
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-credentials
          key: SECRET_ACCESS_KEY

17.3 Final Recommendations

For new projects:

• Start with PostgreSQL unless you have specific Oracle requirements
• Use latest stable version (PostgreSQL 15+ as of 2024)
• Design for PostgreSQL idioms from the beginning

For migrations:

• Do thorough assessment before committing
• Consider hybrid approach for complex migrations
• Invest in testing and validation
• Plan for post-migration optimization

For operational excellence:

1. Monitor comprehensively: Not just uptime, but performance, bloat, replication
2. Automate everything: Backups, failover, upgrades, monitoring
3. Plan for growth: Partitioning, read replicas, connection pooling
4. Stay current: Regular upgrades (security and performance)
5. Invest in knowledge: PostgreSQL evolves rapidly

17.4 The PostgreSQL Community Advantage

PostgreSQL’s open-source nature provides unique advantages:

• Transparency: You can see and understand how everything works
• Community support: Active mailing lists, conferences, local user groups
• Extensibility: Can modify or extend to meet specific needs
• No vendor lock-in: Control your own destiny

Getting involved:

• Attend PostgreSQL conferences (PGConf, FOSDEM PGDay)
• Participate in mailing lists (pgsql-general, pgsql-performance)
• Contribute bug reports, documentation, or code
• Join local user groups (PUGs)

---

Appendix A: Essential Extensions

A.1 Performance and Monitoring

pg_stat_statements:

sql

-- Track query performance
CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

pg_partman:

sql

-- Automated partition management
CREATE EXTENSION pg_partman;

-- Create partitioned table
CREATE TABLE sensor_data (
    id BIGSERIAL,
    sensor_id INTEGER,
    reading NUMERIC,
    recorded_at TIMESTAMP
) PARTITION BY RANGE (recorded_at);

-- Set up automatic partitioning
SELECT partman.create_parent(
    'public.sensor_data',
    'recorded_at',
    'native',
    'daily'
);

pg_repack:

bash

# Reorganize tables to reduce bloat (online)
pg_repack -d mydb -t large_table

A.2 Security and Compliance

pgaudit:

sql

-- Detailed audit logging
CREATE EXTENSION pgaudit;

-- Configure audit policies
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl, role';
ALTER SYSTEM SET pgaudit.log_relation = on;

postgresql_anonymizer:

sql

-- Data anonymization for testing
CREATE EXTENSION anon CASCADE;

-- Define anonymization rules
SECURITY LABEL FOR anon ON COLUMN users.email
IS 'MASKED WITH FUNCTION anon.fake_email()';

-- Anonymize data
SELECT anon.anonymize_database();

A.3 Oracle Compatibility

orafce:

sql

-- Oracle compatibility functions
CREATE EXTENSION orafce;

-- Use Oracle-style functions
SELECT add_months(CURRENT_DATE, 3);
SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');

---

Appendix B: DBA Daily Checklist

B.1 Morning Checks

sql

-- 1. Database availability
SELECT now() as current_time, version();

-- 2. Connection count
SELECT COUNT(*) as active_connections,
       COUNT(*) FILTER (WHERE state != 'idle') as active_queries
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

-- 3. Replication status
SELECT client_addr, usename, application_name,
       state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as replay_lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_lag
FROM pg_stat_replication;

-- 4. Disk space
SELECT pg_size_pretty(pg_database_size(current_database())) as db_size;

-- Check tablespace usage (if using tablespaces)
SELECT spcname, 
       pg_size_pretty(pg_tablespace_size(oid)) as size
FROM pg_tablespace;

B.2 Performance Health Checks

sql

-- 1. Long-running queries
SELECT pid, usename, application_name,
       now() - query_start as duration,
       state, query
FROM pg_stat_activity
WHERE state != 'idle' 
  AND now() - query_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

-- 2. Index usage
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 3. Cache hit ratio
SELECT datname,
       blks_hit, blks_read,
       round(blks_hit::numeric * 100 / (blks_hit + blks_read), 2) as hit_ratio
FROM pg_stat_database
WHERE blks_hit + blks_read > 0
ORDER BY hit_ratio;

-- 4. Table bloat
SELECT schemaname, tablename,
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 1000
  AND round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) > 10
ORDER BY dead_pct DESC;

B.3 Backup Verification

bash

# Check backup completion
# For pg_basebackup/WAL archiving
SELECT now() - last_archived_time as last_archive_age
FROM pg_stat_archiver;

# For logical backups
# Check if dump file exists and is recent
find /backups -name "*.dump" -mtime -1

# Test restore on standby server (weekly)
pg_restore --dbname=test_db --create /backups/full.dump

# Verify point-in-time recovery capability
# Check WAL archive continuity
SELECT archived_count, failed_count,
       last_archived_wal, last_failed_wal
FROM pg_stat_archiver;

B.4 Security Checks

sql

-- 1. Failed login attempts
SELECT COUNT(*) as failed_logins
FROM pg_stat_database
WHERE datname = current_database();

-- 2. Superuser connections
SELECT usename, client_addr, application_name
FROM pg_stat_activity
WHERE usename = 'postgres'
   OR usename IN (SELECT rolname FROM pg_roles WHERE rolsuper);

-- 3. Password expiration
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolvaliduntil IS NOT NULL
  AND rolvaliduntil < now() + INTERVAL '7 days';

-- 4. SSL connections
SELECT COUNT(*) FILTER (WHERE ssl) as ssl_connections,
       COUNT(*) FILTER (WHERE NOT ssl) as non_ssl_connections
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid);

B.5 Weekly Tasks

1. Review logs: Check for patterns of errors or warnings
2. Update statistics: For tables with volatile data
3. Check for unused objects: Tables, indexes, functions
4. Review monitoring alerts: Tune thresholds if needed
5. Capacity planning: Check growth trends
6. Test failover: If using HA setup
7. Apply patches: OS and PostgreSQL minor updates

B.6 Monthly Tasks

1. Performance review: Analyze trends, identify degradation
2. Security review: Audit logs, access patterns
3. Backup testing: Full restore test
4. Documentation update: Update runbooks, procedures
5. Review compliance: Ensure meeting regulatory requirements
6. Cost optimization: Review resource utilization vs costs

---

Conclusion

Mastering PostgreSQL administration requires understanding both the theoretical foundations and practical operations. This guide has covered:

1. Internals: How PostgreSQL actually works under the hood
2. Operations: Day-to-day administration and maintenance
3. Performance: Tuning for optimal efficiency
4. Migration: Moving from Oracle with confidence
5. Scaling: Growing from single instance to distributed systems

PostgreSQL rewards those who invest time in understanding its principles. The database’s transparency allows deep diagnosis of issues that would be opaque in other systems. Its extensibility enables solutions tailored to specific needs rather than forcing workarounds.

As you continue your PostgreSQL journey:

• Start simple: Master the basics before advanced features
• Automate early: Time spent on automation pays dividends
• Monitor comprehensively: What gets measured gets managed
• Test thoroughly: Especially for migrations and upgrades
• Engage with the community: Learn from others’ experiences

PostgreSQL continues to evolve rapidly. Stay curious, keep learning, and contribute back to the community that makes this powerful database possible.

---

Resources and Further Reading

Official Documentation

• PostgreSQL Documentation
• PostgreSQL Wiki
• PostgreSQL Mailing Lists

Books

• “PostgreSQL: Up and Running” by Regina Obe and Leo Hsu
• “PostgreSQL Administration Cookbook” by Simon Riggs and Gianni Ciolli
• “Mastering PostgreSQL” by Hans-Jürgen Schönig

Tools

• pgAdmin – Administration and development platform
• pgBackRest – Reliable backup/restore
• Patroni – HA template for PostgreSQL
• Barman – Backup and recovery manager

Community

• PostgreSQL Conference – Global events calendar
• PostgreSQL Slack – Community Slack
• r/PostgreSQL – Reddit community