PostgreSQL: The World's Most Advanced Open Source Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- List available extensions
SELECT * FROM pg_available_extensions WHERE name LIKE '%geo%';
 
-- Install an extension
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Check installed extensions
SELECT extname, extversion FROM pg_extension;
 
-- Upgrade extension to new version
ALTER EXTENSION postgis UPDATE TO '3.4.0';
 
-- Extension dependencies are handled automatically
CREATE EXTENSION postgis_raster;  -- Automatically installs postgis if needed
 
-- View extension objects
SELECT 
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments
FROM pg_proc p
JOIN pg_depend d ON d.objid = p.oid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'postgis'
LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- Enable PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;
 
-- Create a table with geometry
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    address TEXT,
    location GEOMETRY(POINT, 4326)  -- 4326 = WGS 84 (GPS coordinates)
);
 
-- Insert with WKT (Well-Known Text)
INSERT INTO stores (name, address, location)
VALUES ('Downtown Store', '123 Main St', ST_GeomFromText('POINT(-73.9857 40.7484)', 4326));
 
-- Insert with helper function
INSERT INTO stores (name, address, location)
VALUES ('Uptown Store', '456 Park Ave', ST_SetSRID(ST_MakePoint(-73.9654, 40.7829), 4326));
 
-- Create spatial index (essential for performance)
CREATE INDEX idx_stores_location ON stores USING GIST(location);
 
-- Basic spatial queries
-- Find all stores within 1km of a point
SELECT name, ST_Distance(
    location::geography, 
    ST_SetSRID(ST_MakePoint(-73.9800, 40.7500), 4326)::geography
) AS distance_meters
FROM stores
WHERE ST_DWithin(
    location::geography,
    ST_SetSRID(ST_MakePoint(-73.9800, 40.7500), 4326)::geography,
    1000  -- 1000 meters
)
ORDER BY distance_meters;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- Find nearest neighbors (KNN) - extremely efficient with index
SELECT id, name, location <-> ST_SetSRID(ST_MakePoint(-73.98, 40.75), 4326) AS dist
FROM stores
ORDER BY location <-> ST_SetSRID(ST_MakePoint(-73.98, 40.75), 4326)
LIMIT 5;
 
-- Polygon containment: find all stores in a zone
WITH delivery_zone AS (
    SELECT ST_SetSRID(ST_MakePolygon(ST_GeomFromText(
        'LINESTRING(-74.0 40.7, -73.95 40.7, -73.95 40.75, -74.0 40.75, -74.0 40.7)'
    )), 4326) AS geom
)
SELECT s.name FROM stores s, delivery_zone d
WHERE ST_Contains(d.geom, s.location);
 
-- Buffer operations: find all within 500m of a road
SELECT b.name 
FROM businesses b, roads r
WHERE r.name = 'Highway 1'
AND ST_DWithin(b.location::geography, r.geom::geography, 500);
 
-- Intersection and union
SELECT ST_Intersection(zone_a.geom, zone_b.geom) AS overlap
FROM zones zone_a, zones zone_b
WHERE zone_a.name = 'Zone A' AND zone_b.name = 'Zone B';
 
-- Calculate areas and lengths
SELECT 
    name,
    ST_Area(geom::geography) / 1000000 AS area_km2,  -- Square kilometers
    ST_Perimeter(geom::geography) / 1000 AS perimeter_km
FROM regions;
 
-- Geocoding (with additional extension or service)
-- Reverse geocoding: coordinate to address
SELECT * FROM geocode(ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326));
 
-- Routing with pgRouting extension
SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM roads',
    start_node_id, end_node_id
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- Enable TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb;
 
-- Create a standard table
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION
);
 
-- Convert to hypertable (automatic time partitioning)
SELECT create_hypertable('sensor_data', 'time', 
    chunk_time_interval => INTERVAL '1 day'
);
 
-- Optional: add space partitioning for high-cardinality
SELECT add_dimension('sensor_data', 'sensor_id', number_partitions => 4);
 
-- Insert data normally - TimescaleDB handles routing
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, pressure)
VALUES 
    (NOW(), 1, 23.5, 45.2, 1013.25),
    (NOW() - INTERVAL '1 hour', 1, 22.8, 46.1, 1013.10);
 
-- Query just like regular PostgreSQL
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, sensor_id
ORDER BY hour DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

-- Continuous Aggregates: Pre-computed rollups that update automatically
CREATE MATERIALIZED VIEW hourly_sensor_stats
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    COUNT(*) AS reading_count
FROM sensor_data
GROUP BY hour, sensor_id;
 
-- Add refresh policy (automatically update aggregates)
SELECT add_continuous_aggregate_policy('hourly_sensor_stats',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);
 
-- Compression: Dramatically reduce storage for old data
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time'
);
 
-- Add compression policy (automatically compress old chunks)
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');
 
-- Check compression status
SELECT 
    chunk_name, 
    before_compression_total_bytes, 
    after_compression_total_bytes,
    (1 - after_compression_total_bytes::float / before_compression_total_bytes) * 100 AS compression_ratio
FROM chunk_compression_stats('sensor_data');
 
-- Data retention policy (automatically drop old data)
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');
 
-- Time-series specific functions
SELECT 
    time_bucket_gapfill('1 hour', time) AS hour,
    sensor_id,
    interpolate(AVG(temperature)) AS interpolated_temp,  -- Fill gaps with interpolation
    locf(AVG(temperature)) AS last_known_temp           -- Fill gaps with last known value
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours' AND sensor_id = 1
GROUP BY hour, sensor_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Enable (requires postgresql.conf change and restart)
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Find the slowest queries by total time
SELECT 
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_of_total,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
 
-- Find queries that could benefit from caching (high calls, same result)
SELECT 
    calls,
    rows / NULLIF(calls, 0) AS avg_rows_per_call,
    query
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY calls DESC;
 
-- Reset statistics
SELECT pg_stat_statements_reset();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- Similarity score (0-1, 1 = identical)
SELECT similarity('PostgreSQL', 'Postgres');  -- ~0.625
 
-- Fuzzy search
SELECT name, similarity(name, 'johnsn') AS sim
FROM users
WHERE similarity(name, 'johnsn') > 0.3
ORDER BY sim DESC;
 
-- Like but with typo tolerance
SELECT * FROM products WHERE name % 'posgres';  -- Finds 'PostgreSQL'
 
-- Create GIN index for fast fuzzy search
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);
 
-- Word similarity (matches within words)
SELECT word_similarity('database', 'PostgreSQL database server');  -- ~0.8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();  -- e.g., 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
 
-- Case-insensitive text
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (
    email CITEXT UNIQUE  -- 'User@Example.com' = 'user@example.com'
);
 
-- Hierarchical data with ltree
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE categories (
    path LTREE PRIMARY KEY,
    name TEXT
);
INSERT INTO categories VALUES ('electronics', 'Electronics');
INSERT INTO categories VALUES ('electronics.computers', 'Computers');
INSERT INTO categories VALUES ('electronics.computers.laptops', 'Laptops');
 
-- Find all descendants of 'electronics'
SELECT * FROM categories WHERE path <@ 'electronics';
 
-- Hypothetical index testing
CREATE EXTENSION IF NOT EXISTS hypopg;
SELECT hypopg_create_index('CREATE INDEX ON orders(customer_id)');
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;  -- Shows hypothetical index usage
SELECT hypopg_reset();  -- Clear hypothetical indexes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Enable AGE extension
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
 
-- Create a graph
SELECT create_graph('social_network');
 
-- Add nodes and edges
SELECT * FROM cypher('social_network', $$
    CREATE (alice:Person {name: 'Alice', age: 30})
    CREATE (bob:Person {name: 'Bob', age: 25})
    CREATE (alice)-[:KNOWS {since: 2020}]->(bob)
    RETURN alice, bob
$$) AS (alice agtype, bob agtype);
 
-- Query relationships
SELECT * FROM cypher('social_network', $$
    MATCH (p1:Person)-[:KNOWS]->(p2:Person)
    RETURN p1.name, p2.name
$$) AS (person1 agtype, person2 agtype);
 
-- Complex graph traversal
SELECT * FROM cypher('social_network', $$
    MATCH (p:Person {name: 'Alice'})-[:KNOWS*1..3]->(friend)
    RETURN DISTINCT friend.name
$$) AS (friend_name agtype);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;
 
-- Create table with vector column
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI ada-002 dimension
);
 
-- Insert vectors (typically from ML model)
INSERT INTO items (content, embedding)
VALUES ('PostgreSQL is a powerful database', '[0.1, 0.2, 0.3, ...]');
 
-- Create index for fast similarity search
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
 
-- Find similar items (cosine distance)
SELECT id, content, 1 - (embedding <=> query_vector) AS similarity
FROM items
ORDER BY embedding <=> query_vector
LIMIT 10;
 
-- Combine with filtering
SELECT id, content
FROM items
WHERE category = 'database'
ORDER BY embedding <=> query_vector
LIMIT 5;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- Check extension versions
SELECT extname, extversion, 
    (SELECT available_version 
     FROM pg_available_extension_versions 
     WHERE name = extname 
     ORDER BY available_version DESC 
     LIMIT 1) AS latest_available
FROM pg_extension;
 
-- Check for extension-provided objects
SELECT 
    e.extname,
    COUNT(DISTINCT p.proname) AS functions,
    COUNT(DISTINCT t.typname) AS types
FROM pg_extension e
LEFT JOIN pg_depend d ON d.refobjid = e.oid
LEFT JOIN pg_proc p ON p.oid = d.objid
LEFT JOIN pg_type t ON t.oid = d.objid
GROUP BY e.extname;
 
-- Identify dependencies
SELECT 
    pg_describe_object(classid, objid, objsubid) AS dependent_object,
    e.extname AS extension
FROM pg_depend d
JOIN pg_extension e ON e.oid = d.refobjid
WHERE classid != 'pg_extension'::regclass
LIMIT 20;