PostgreSQL 17-18: New Features and Ecosystem Evolution

-- Hash Right Semi Join
-- Improved performance for queries like:
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users);

-- Now uses efficient hash semi-join instead of nested loops
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users);

-- Better parallel query execution
-- Improved parallel index scans
-- More efficient parallel aggregates

-- pg_stat_all_tables now includes vacuum and analyze metrics
SELECT 
    schemaname,
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autovacuum_analyze_count,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_all_tables;

-- pg_backend_memory_contexts new fields
-- More detailed memory tracking
SELECT name, type, total_bytes, free_bytes, used_bytes
FROM pg_backend_memory_contexts;

-- pg_stat_checkpointer improvements
-- Better checkpoint monitoring
SELECT * FROM pg_stat_checkpointer;
-- Now includes num_done field for clearer monitoring

-- JSON_TABLE now supports more options
SELECT jt.*
FROM json_data,
JSON_TABLE(
    data,
    '$.items[*]'
    COLUMNS (
        id INT PATH '$.id',
        name TEXT PATH '$.name',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt;

-- Improved JSON path performance
-- Better optimization for common patterns
SELECT * FROM documents 
WHERE data->>'status' = 'active';

-- JSON path with predicates
SELECT * FROM events
WHERE data @? '$.attendees[*] ? (@.name == "John")';

-- Synchronous logical replication
-- Now supported with streaming mode
ALTER PUBLICATION mypub WITH (sync_stream = true);

-- Logical decoding on replicas
-- Replica can now perform logical decoding
hot_standby_feedback = on
logical_replication_slot = on

-- Improved conflict detection
-- Better handling of conflicts during logical replication

-- Column-level security for partitioned tables
-- Now properly enforced
CREATE TABLE orders (
    id INT,
    region TEXT,
    amount DECIMAL(10,2)
) PARTITION BY LIST (region);

-- Row-level security now works with partitions
CREATE POLICY "region_policy" ON orders
    FOR SELECT USING (region = current_user_region());

-- Improved password hashing
-- scram-sha-256 now default
-- Better protection against brute force

-- While pgvector is an extension, PostgreSQL 17 improved vector support
-- Native vector data type support improved

-- Install pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- Create vector column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI embeddings are 1536-dim
);

-- Create HNSW index for fast similarity search
CREATE INDEX idx_documents_embedding 
ON documents 
USING HNSW (embedding vector_cosine_ops);

-- Vector similarity search
SELECT id, content, 
       1 - (embedding <=> $query_embedding) AS similarity
FROM documents
ORDER BY embedding <=> $query_embedding
LIMIT 5;

-- JSON path improvements
-- Better performance for complex paths
SELECT data->>'$.store.book[0].author' FROM documents;

-- JSON_TABLE improvements
-- More flexible column definitions
SELECT *
FROM JSON_TABLE(
    '[{"a":1,"b":2},{"a":3,"b":4}]',
    '$[*]'
    COLUMNS (
        a INT PATH '$.a',
        b INT PATH '$.b'
    )
) AS jt;

-- Partition pruning improvements
-- Better optimization for partitioned tables
EXPLAIN SELECT * FROM orders_2026 WHERE order_date = '2026-03-01';

-- List partitioning now more flexible
CREATE TABLE products (
    id INT,
    category TEXT,
    name TEXT
) PARTITION BY LIST (category);

-- Attach partitions online
ALTER TABLE products ATTACH PARTITION products_electronics 
    FOR VALUES IN ('electronics');

-- Default partition
CREATE TABLE products_default PARTITION OF products DEFAULT;

-- COPY now supports binary format more efficiently
\copy table FROM 'data.csv' WITH (FORMAT csv, HEADER true);

-- Improved error handling
-- Better error messages for malformed data

-- Parallel COPY (PostgreSQL 17)
-- Export/import data in parallel
\copy (SELECT * FROM large_table) TO 'file.csv' WITH (FORMAT csv);

-- Incremental Sort
-- Sort data incrementally for better performance
EXPLAIN SELECT * FROM orders ORDER BY status, amount;

-- Now uses incremental sort when beneficial

-- BRIN index improvements
-- Better for time-series data
CREATE INDEX idx_logs ON logs USING BRIN(created_at) 
    WITH (pages_per_range = 128);

-- Parallel VACUUM
-- VACUUM can now use parallel workers
VACUUM (PARALLEL 4) large_table;

-- pg_stat_activity improvements
-- More detailed connection information
SELECT * FROM pg_stat_activity;

-- Now includes:
-- - application_name
-- - client_hostname
-- - wait_event_type
-- - wait_event

-- pg_stat_wait_events
-- Better wait event reporting
SELECT * FROM pg_stat_activity 
WHERE state = 'active';

-- pg_stat_statements: Query performance analysis
CREATE EXTENSION pg_stat_statements;

-- View slowest queries
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    rows,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- pg_trgm: Trigram similarity
CREATE EXTENSION pg_trgm;

-- Similarity search
SELECT similarity('hello world', 'hello postgres');
CREATE INDEX idx_trgm ON documents USING GIST (content gist_trgm_ops);

-- citext: Case-insensitive text
CREATE EXTENSION citext;
CREATE TABLE users (
    email CITEXT PRIMARY KEY,
    name TEXT
);

-- hstore: Key-value store
CREATE EXTENSION hstore;
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    attributes HSTORE
);
INSERT INTO products (attributes) VALUES ('color=>red, size=>medium');

-- TimescaleDB: Time-series database
CREATE EXTENSION timescaledb;

-- Convert to hypertable
SELECT create_hypertable('conditions', 'time');

-- Time-series functions
SELECT time_bucket('1 hour', time) AS bucket,
       AVG(temperature)
FROM conditions
WHERE time >= NOW() - INTERVAL '1 day'
GROUP BY bucket
ORDER BY bucket;

-- Continuous aggregates
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       AVG(temperature) AS avg_temp,
       MAX(temperature) AS max_temp
FROM conditions
GROUP BY bucket;

-- Built-in full-text search
-- Create tsvector
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;

-- Create index
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- Search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & tutorial')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'postgresql & tutorial')) DESC;

-- pg_search (PostgreSQL 17+ enhanced)
-- Better full-text search integration

-- PostGIS: Geospatial database
CREATE EXTENSION postgis;

-- Create spatial table
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)
);

-- Insert spatial data
INSERT INTO locations (name, geom) VALUES 
    ('Office', ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));

-- Spatial queries
SELECT name 
FROM locations 
WHERE ST_DWithin(
    geom,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326),
    1000  -- meters
);

# Kubernetes operator for PostgreSQL
# Install operator
kubectl apply -f https://operatorhub.io/install/postgresql.yaml

# Deploy PostgreSQL cluster
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-cluster
spec:
  instances: 3
  storage:
    size: 10Gi
  bootstrap:
    initdb:
      database: app
      owner: appuser

# Amazon RDS PostgreSQL
# Connection
import psycopg2

conn = psycopg2.connect(
    host="mydb.xxxx.us-east-1.rds.amazonaws.com",
    port=5432,
    database="mydb",
    user="master",
    password="password"
)

# Google Cloud SQL PostgreSQL
conn = psycopg2.connect(
    host="35.xxx.xxx.xxx",
    database="postgres",
    user="postgres",
    password="password"
)

# Azure Database for PostgreSQL
conn = psycopg2.connect(
    host="mypgserver.postgres.database.azure.com",
    database="postgres",
    user="mylogin",
    password="password"
)

-- What's coming in PostgreSQL 19+:

-- 1. Better vector search integration
-- Native vector operations without extension

-- 2. Improved JSON performance
-- Binary JSON format
-- Faster JSON path evaluation

-- 3. Enhanced partitioning
-- More partition types
-- Better partition pruning

-- 4. SQL/AI integration
-- Built-in ML functions
-- Better integration with ML frameworks

-- 5. Improved recovery
-- Faster PITR
-- Better replication lag handling

# pg_dumpall approach
pg_dumpall -p 5432 > all_databases.sql
pg_ctl stop -D $PGDATA
# Install new version
initdb -D $PGDATA
pg_ctl start -D $PGDATA
psql -f all_databases.sql postgres

# pg_upgrade (in-place)
pg_upgrade -b /old/bin -B /new/bin -d /old/data -D /new/data

# Using pg_dump logical backup
pg_dump -Fc mydb > mydb.dump
# Install new version
pg_restore -d mydb mydb.dump

-- Watch for deprecated features
-- Check for warnings in logs

-- Common migration issues:
-- 1. renamed/removed functions
-- 2. Changed behavior of some settings
-- 3. Extension version compatibility
-- 4. Query plan changes

-- Test thoroughly before production migration

-- Adopt new features gradually:
-- 1. Test in development first
-- 2. Check extension compatibility
-- 3. Review performance impact
-- 4. Monitor for issues

-- Use new features appropriately:
-- 1. Vector search: Only if needed for AI/ML
-- 2. JSON: When semi-structured data required
-- 3. Partitioning: For large tables with natural partitions
-- 4. Extensions: Only install what's needed