PostgreSQL Database Management

Contents

Introduction to PostgreSQL

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development. It's known for reliability, feature robustness, and performance.

Key Features:

  • ACID compliance
  • Complex queries
  • Custom data types
  • Table inheritance
  • Multi-Version Concurrency Control (MVCC)
  • Extensibility

Why PostgreSQL?

  • Advanced features beyond standard SQL
  • Strong data integrity
  • Extensible architecture
  • Active community
  • Enterprise-grade reliability

Installation and Setup

Installation Steps

# Ubuntu/Debian sudo apt update sudo apt install postgresql postgresql-contrib # Start service sudo systemctl start postgresql sudo systemctl enable postgresql # Switch to postgres user sudo -i -u postgres # Access PostgreSQL psql

Basic Configuration

# postgresql.conf max_connections = 100 shared_buffers = 256MB effective_cache_size = 768MB maintenance_work_mem = 64MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 4 effective_io_concurrency = 2 work_mem = 6553kB min_wal_size = 1GB max_wal_size = 4GB

Advanced Features

Inheritance

-- Create parent table CREATE TABLE cities ( name text, population float, elevation int ); -- Create child table inheriting from cities CREATE TABLE capitals ( state char(2) ) INHERITS (cities);

Partitioning

-- Create partitioned table CREATE TABLE measurements ( city_id int not null, logdate date not null, temperature int ) PARTITION BY RANGE (logdate); -- Create partitions CREATE TABLE measurements_y2025m01 PARTITION OF measurements FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

JSON Support

-- Create table with JSON CREATE TABLE orders ( id serial PRIMARY KEY, info jsonb ); -- Query JSON data SELECT info->>'customer' as customer FROM orders WHERE info @> '{"status": "completed"}';

Data Types and Extensions

Custom Types

-- Create enum type CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); -- Create composite type CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric ); -- Create domain CREATE DOMAIN posint AS integer CHECK (VALUE > 0);

Extensions

-- Enable PostGIS extension CREATE EXTENSION postgis; -- Create spatial table CREATE TABLE locations ( id SERIAL PRIMARY KEY, name VARCHAR(50), location GEOMETRY(Point, 4326) ); -- Enable full-text search CREATE EXTENSION pg_trgm; CREATE INDEX articles_gin_idx ON articles USING gin(to_tsvector('english', body));

Advanced Queries

Window Functions

-- Rank results SELECT name, department, salary, rank() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; -- Running totals SELECT date, amount, sum(amount) OVER (ORDER BY date) FROM transactions;

Common Table Expressions (CTE)

WITH RECURSIVE subordinates AS ( -- Base case SELECT employee_id, manager_id, name FROM employees WHERE manager_id = 1 UNION ALL -- Recursive case SELECT e.employee_id, e.manager_id, e.name FROM employees e INNER JOIN subordinates s ON s.employee_id = e.manager_id ) SELECT * FROM subordinates;

Database Administration

Role Management

-- Create role CREATE ROLE app_user WITH LOGIN PASSWORD 'secure123' CONNECTION LIMIT 100; -- Grant privileges GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user; -- Create role hierarchy CREATE ROLE admin_user; GRANT app_user TO admin_user;

Backup and Recovery

-- Backup database pg_dump dbname > backup.sql -- Restore database psql dbname < backup.sql -- Point-in-time recovery postgresql.conf: wal_level = replica archive_mode = on archive_command = 'cp %p /archive/%f' -- Restore to point in time recovery.conf: restore_command = 'cp /archive/%f %p' recovery_target_time = '2025-01-01 00:00:00'

Performance Optimization

Indexing Strategies

-- B-tree index CREATE INDEX idx_users_email ON users(email); -- Partial index CREATE INDEX idx_orders_status ON orders(status) WHERE status = 'pending'; -- Multi-column index CREATE INDEX idx_users_name_email ON users(last_name, first_name, email);

Query Analysis

-- Analyze query plan EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@example.com'; -- Table statistics ANALYZE users; -- View index usage SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes;

Security Features

Row-Level Security

-- Enable RLS ALTER TABLE documents ENABLE ROW LEVEL SECURITY; -- Create policy CREATE POLICY user_docs ON documents FOR SELECT USING (user_id = current_user_id());

Encryption

-- Enable SSL ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' -- Data encryption CREATE EXTENSION pgcrypto; -- Encrypt data UPDATE users SET password = crypt('mypass', gen_salt('bf'));