PostgreSQL

The world's most advanced open source relational database. ACID compliant with JSON support and built-in full-text search. Top developer choice for 2 consecutive years in Stack Overflow survey.

Database ServerRDBMSSQLOpen SourceAdvanced FeaturesACID ComplianceExtensibleFull-Text Search

Database Server

PostgreSQL

Overview

PostgreSQL is the world's most advanced open source relational database management system. Renowned for its robust feature set, SQL standards compliance, and extensibility, PostgreSQL has become the database of choice for developers building complex applications. With over 35 years of development history, PostgreSQL combines the reliability of traditional RDBMS with cutting-edge features like JSON support, full-text search, and advanced indexing. It's particularly favored for applications requiring complex queries, data integrity, and geographic information systems.

Details

PostgreSQL 2025 edition continues to lead the database innovation with PostgreSQL 17 delivering significant performance improvements and new features. The system offers full ACID compliance, Multi-Version Concurrency Control (MVCC), and supports both SQL and NoSQL workloads through native JSON and JSONB data types. PostgreSQL features a powerful extension system allowing developers to add custom data types, operators, and functions. Advanced features include sophisticated query planning, parallel query execution, table partitioning, and logical replication. The database excels in geographic information systems through PostGIS extension, full-text search capabilities, and support for complex data types including arrays, ranges, and custom composite types.

Key Features

Full SQL Standards Compliance: Comprehensive support for SQL:2016 standard features
ACID Compliance: Complete transactional integrity with sophisticated concurrency control
Extensibility: Rich extension ecosystem including PostGIS, pgcrypto, and custom types
Advanced Data Types: JSON/JSONB, arrays, ranges, geometric types, and user-defined types
Full-Text Search: Built-in text search capabilities with multiple language support
Performance Optimization: Query planner, parallel execution, and multiple indexing methods

Pros and Cons

Pros

Top developer satisfaction for 2 consecutive years in Stack Overflow Developer Survey
Superior SQL standards compliance and advanced features compared to other open source databases
Excellent performance for complex queries and analytical workloads with sophisticated query optimization
Strong community support with extensive documentation and active development
Advanced features like JSON support, full-text search, and geographic data processing
High reliability and data integrity with proven track record in enterprise environments

Cons

Steeper learning curve compared to simpler databases due to rich feature set
Higher memory consumption and resource requirements for optimal performance
Replication setup can be more complex than MySQL for simple master-slave configurations
Slower write performance compared to MySQL in certain high-throughput scenarios
Default configuration may require tuning for optimal performance in production environments
Limited built-in clustering solutions compared to some commercial databases

Reference Pages

Code Examples

Installation and Initial Setup

# Ubuntu/Debian installation
sudo apt update
sudo apt install postgresql postgresql-contrib

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

# Switch to postgres user and access psql
sudo -i -u postgres
psql

# CentOS/RHEL installation
sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Docker installation
docker run --name postgres-server \
  -e POSTGRES_PASSWORD=my-secret-pw \
  -e POSTGRES_DB=myapp \
  -e POSTGRES_USER=appuser \
  -p 5432:5432 \
  -d postgres:17

# Connect to PostgreSQL
psql -h localhost -U appuser -d myapp

# Create user and database
sudo -u postgres createuser --interactive
sudo -u postgres createdb myapp

Database and User Management

-- Create database with specific encoding
CREATE DATABASE myapp 
WITH ENCODING 'UTF8' 
LC_COLLATE='en_US.UTF-8' 
LC_CTYPE='en_US.UTF-8' 
TEMPLATE=template0;

-- Create user with specific privileges
CREATE USER appuser WITH ENCRYPTED PASSWORD 'secure_password';
CREATE USER readonly_user WITH ENCRYPTED PASSWORD 'readonly_pass';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

-- Set role properties
ALTER USER appuser CREATEDB;
ALTER USER appuser WITH SUPERUSER;

-- List databases and users
\l
\du

-- Connect to database
\c myapp

-- Change user password
ALTER USER appuser WITH PASSWORD 'new_password';

-- Database backup
pg_dump -U appuser -h localhost myapp > backup.sql

-- Database restore
psql -U appuser -h localhost myapp < backup.sql

Advanced Table Creation and Data Types

-- Create table with various PostgreSQL data types
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    uuid UUID DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    profile JSONB,
    tags TEXT[],
    age_range INT4RANGE,
    location POINT,
    status user_status DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT valid_age_range CHECK (NOT isempty(age_range))
);

-- Create custom enum type
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'deleted');

-- Create table with foreign key and advanced constraints
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    slug VARCHAR(255) UNIQUE,
    metadata JSONB,
    tags TEXT[],
    search_vector TSVECTOR,
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT valid_slug CHECK (slug ~* '^[a-z0-9-]+$'),
    CONSTRAINT title_length CHECK (length(title) BETWEEN 5 AND 255)
);

-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_profile_gin ON users USING GIN(profile);
CREATE INDEX idx_users_tags_gin ON users USING GIN(tags);
CREATE INDEX idx_posts_search_vector_gin ON posts USING GIN(search_vector);
CREATE INDEX idx_posts_published_at ON posts(published_at) WHERE published_at IS NOT NULL;

-- Create partial index
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Create expression index
CREATE INDEX idx_users_lower_username ON users(lower(username));

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

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

JSON/JSONB Operations and Advanced Queries

-- Insert data with JSONB
INSERT INTO users (username, email, first_name, last_name, profile, tags, age_range, location) VALUES
('john_doe', '[email protected]', 'John', 'Doe', 
 '{"age": 30, "city": "New York", "skills": ["PostgreSQL", "Python", "JavaScript"], "preferences": {"theme": "dark", "notifications": true}}',
 ARRAY['developer', 'postgresql', 'python'],
 '[25,35)', 
 POINT(40.7128, -74.0060)),
('jane_smith', '[email protected]', 'Jane', 'Smith',
 '{"age": 28, "city": "Los Angeles", "skills": ["React", "Node.js", "PostgreSQL"], "preferences": {"theme": "light", "notifications": false}}',
 ARRAY['frontend', 'react', 'nodejs'],
 '[25,30)',
 POINT(34.0522, -118.2437));

-- JSONB queries
SELECT username, profile->>'city' as city, profile->'age' as age
FROM users 
WHERE profile->>'city' = 'New York';

-- JSONB array operations
SELECT username, jsonb_array_elements_text(profile->'skills') as skill
FROM users
WHERE profile ? 'skills';

-- JSONB containment
SELECT username, profile
FROM users
WHERE profile @> '{"preferences": {"theme": "dark"}}';

-- JSONB path queries (PostgreSQL 12+)
SELECT username, jsonb_path_query_array(profile, '$.skills[*]') as skills
FROM users
WHERE jsonb_path_exists(profile, '$.skills[*] ? (@ == "PostgreSQL")');

-- Update JSONB data
UPDATE users 
SET profile = jsonb_set(profile, '{last_login}', to_jsonb(CURRENT_TIMESTAMP), true),
    profile = jsonb_set(profile, '{login_count}', to_jsonb(COALESCE((profile->>'login_count')::int, 0) + 1), true)
WHERE username = 'john_doe';

-- Array operations
SELECT username, unnest(tags) as tag
FROM users
WHERE 'postgresql' = ANY(tags);

-- Range operations
SELECT username, age_range
FROM users
WHERE age_range @> 28  -- Contains age 28
OR age_range && '[30,35)'::int4range;  -- Overlaps with range

-- Full-text search
SELECT title, content, ts_rank(search_vector, plainto_tsquery('english', 'PostgreSQL database')) as rank
FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'PostgreSQL database')
ORDER BY rank DESC;

-- Complex aggregation with JSONB
SELECT 
    profile->>'city' as city,
    COUNT(*) as user_count,
    AVG((profile->>'age')::int) as avg_age,
    array_agg(username) as usernames,
    jsonb_agg(profile->'skills') as all_skills
FROM users 
WHERE profile->>'city' IS NOT NULL
GROUP BY profile->>'city';

Performance Optimization and Query Tuning

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT u.username, p.title, p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = 'active' 
AND p.published_at > CURRENT_DATE - INTERVAL '30 days'
ORDER BY p.created_at DESC;

-- Create covering index
CREATE INDEX idx_posts_user_published_covering 
ON posts(user_id, published_at) 
INCLUDE (title, created_at)
WHERE published_at IS NOT NULL;

-- Update table statistics
ANALYZE users;
ANALYZE posts;

-- Monitor query performance
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    stddev_exec_time,
    rows
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

-- Check index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- Monitor connection and activity
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    LEFT(query, 100) as query
FROM pg_stat_activity 
WHERE state != 'idle'
ORDER BY query_start;

-- Database size monitoring
SELECT 
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- Table and index size analysis
SELECT 
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Replication and High Availability

-- Primary server configuration (postgresql.conf)
/*
# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
*/

-- Create replication user
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replica_password';

-- Create replication slot
SELECT pg_create_physical_replication_slot('replica_1');

-- Check replication status on primary
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state
FROM pg_stat_replication;

-- Standby server setup (recovery.conf or postgresql.conf in PG 12+)
/*
# Standby settings
standby_mode = 'on'
primary_conninfo = 'host=primary-server port=5432 user=replicator password=replica_password'
primary_slot_name = 'replica_1'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
*/

-- Check replication lag on standby
SELECT 
    CASE 
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
        ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
    END AS lag_seconds;

-- Logical replication setup
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Create subscription on replica
CREATE SUBSCRIPTION my_subscription 
CONNECTION 'host=primary-server port=5432 user=replicator password=replica_password dbname=myapp' 
PUBLICATION my_publication;

-- Monitor logical replication
SELECT 
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    latest_end_time
FROM pg_stat_subscription;

Advanced Features and Extensions

# Install and enable extensions
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS postgis;"
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS uuid-ossp;"
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS hstore;"

-- PostGIS geographic data
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(POINT, 4326),
    region GEOMETRY(POLYGON, 4326)
);

-- Insert geographic data
INSERT INTO locations (name, location) VALUES
('Tokyo', ST_SetSRID(ST_MakePoint(139.6917, 35.6895), 4326)),
('New York', ST_SetSRID(ST_MakePoint(-74.0059, 40.7128), 4326));

-- Geographic queries
SELECT 
    name,
    ST_Distance(location, ST_SetSRID(ST_MakePoint(139.6917, 35.6895), 4326)) / 1000 as distance_km
FROM locations
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(139.6917, 35.6895), 4326), 50000)
ORDER BY distance_km;

-- Encryption functions
SELECT 
    username,
    crypt('password', gen_salt('bf', 8)) as encrypted_password
FROM users
WHERE id = 1;

-- UUID generation
INSERT INTO users (uuid, username, email) VALUES
(uuid_generate_v4(), 'test_user', '[email protected]');

-- Custom functions
CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN DATE_PART('year', AGE(birth_date));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Window functions
SELECT 
    username,
    profile->>'city' as city,
    (profile->>'age')::int as age,
    ROW_NUMBER() OVER (PARTITION BY profile->>'city' ORDER BY (profile->>'age')::int DESC) as age_rank_in_city,
    AVG((profile->>'age')::int) OVER (PARTITION BY profile->>'city') as avg_age_in_city
FROM users
WHERE profile->>'age' IS NOT NULL;

-- Common Table Expressions (CTEs)
WITH user_stats AS (
    SELECT 
        profile->>'city' as city,
        COUNT(*) as user_count,
        AVG((profile->>'age')::int) as avg_age
    FROM users
    WHERE profile->>'city' IS NOT NULL
    GROUP BY profile->>'city'
),
city_rankings AS (
    SELECT 
        city,
        user_count,
        avg_age,
        ROW_NUMBER() OVER (ORDER BY user_count DESC) as popularity_rank
    FROM user_stats
)
SELECT * FROM city_rankings WHERE popularity_rank <= 5;

Backup and Recovery Strategies

# Logical backup with pg_dump
pg_dump -U appuser -h localhost \
  --verbose \
  --clean \
  --create \
  --format=custom \
  myapp > myapp_backup.dump

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

# Compressed backup
pg_dump -U appuser -h localhost \
  --format=custom \
  --compress=9 \
  myapp > myapp_compressed.dump

# Parallel backup
pg_dump -U appuser -h localhost \
  --format=directory \
  --jobs=4 \
  --file=myapp_parallel_backup \
  myapp

# Physical backup (requires pg_basebackup)
pg_basebackup -U replicator -h localhost \
  --pgdata=/var/lib/postgresql/backup \
  --format=tar \
  --gzip \
  --progress \
  --verbose

# Point-in-time recovery backup
pg_basebackup -U replicator -h localhost \
  --pgdata=/var/lib/postgresql/pitr_backup \
  --wal-method=stream \
  --checkpoint=fast

# Restore from custom format
pg_restore -U appuser -h localhost \
  --verbose \
  --clean \
  --create \
  --dbname=postgres \
  myapp_backup.dump

# Restore specific table
pg_restore -U appuser -h localhost \
  --table=users \
  --dbname=myapp \
  myapp_backup.dump

# Continuous archiving setup (postgresql.conf)
# archive_mode = on
# archive_command = 'test ! -f /mnt/backup/archive/%f && cp %p /mnt/backup/archive/%f'
# wal_level = replica