PostgreSQL Explained — Beginner's Guide

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql

# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Verify
psql --version  # psql (PostgreSQL) 16.x

# Connect as the postgres user
sudo -u postgres psql

# Or set a password and connect remotely
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'new_password';"
psql -h localhost -U postgres -d postgres

-- Create a database
CREATE DATABASE blog WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8';

-- Connect to it
\c blog

-- Create a table with PostgreSQL-specific types
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    tags TEXT[],  -- Array type!
    metadata JSONB,  -- Binary JSON (indexable!)
    published_at TIMESTAMPTZ DEFAULT NOW(),
    view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
    status VARCHAR(20) DEFAULT 'draft'
        CHECK (status IN ('draft', 'published', 'archived'))
);

-- Insert data with JSON metadata
INSERT INTO posts (title, content, tags, metadata) VALUES
(
    'Getting Started with PostgreSQL',
    'PostgreSQL is an advanced database...',
    ARRAY['database', 'postgresql', 'beginners'],
    '{
        "reading_time": 8,
        "author": "admin",
        "featured_image": "postgres-basics.jpg",
        "seo": {
            "keywords": ["postgresql", "database", "tutorial"],
            "description": "Learn PostgreSQL basics"
        }
    }'::jsonb
),
(
    'Understanding JSON in PostgreSQL',
    'JSONB provides powerful querying capabilities...',
    ARRAY['json', 'postgresql', 'advanced'],
    '{
        "reading_time": 12,
        "author": "admin",
        "featured_image": "json-postgres.jpg",
        "seo": {
            "keywords": ["json", "jsonb", "postgresql"],
            "description": "Deep dive into JSONB"
        }
    }'::jsonb
);

-- Posts with reading time > 10 minutes
SELECT title, metadata->>'reading_time' AS reading_time
FROM posts
WHERE (metadata->>'reading_time')::int > 10;

-- Posts where SEO description mentions 'JSONB'
SELECT title FROM posts
WHERE metadata->'seo'->>'description' LIKE '%JSONB%';

-- Create a GIN index on JSONB for fast queries
CREATE INDEX idx_posts_metadata ON posts USING GIN (metadata);

-- Now this query uses the index:
SELECT title FROM posts
WHERE metadata @> '{"author": "admin"}'::jsonb;

-- GIN index (Generalized Inverted Index) — for arrays, JSONB, full-text
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- GiST index (Generalized Search Tree) — for geometric data, full-text
CREATE INDEX idx_posts_content_fts ON posts USING GIN (to_tsvector('english', content));

-- BRIN index (Block Range Index) — for large, naturally ordered data
CREATE INDEX idx_orders_created ON orders USING BRIN (created_at)
    WITH (pages_per_range = 32);

-- Partial index — only index relevant rows
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';

-- Full-text search: MySQL vs PostgreSQL style

-- PostgreSQL: built-in, powerful
SELECT title
FROM posts
WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & json');

-- With ranking (MySQL can't do this without extra configuration)
SELECT title, ts_rank(to_tsvector('english', content), to_tsquery('postgresql & json')) AS rank
FROM posts
WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & json')
ORDER BY rank DESC;

-- See available extensions
SELECT * FROM pg_available_extensions;

-- Enable popular extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";      -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"; -- Query performance stats
CREATE EXTENSION IF NOT EXISTS "pgcrypto";        -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS "hstore";          -- Key-value store
CREATE EXTENSION IF NOT EXISTS "postgis";         -- Geospatial (install separately)

-- uuid-ossp: generate UUIDs (better than sequential IDs)
CREATE TABLE api_keys (
    key_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO api_keys (user_id) VALUES (1);
SELECT key_id FROM api_keys;
-- Output: 550e8400-e29b-41d4-a716-446655440000

-- After installing PostGIS
CREATE EXTENSION postgis;

-- Create a table with location data
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOGRAPHY(Point, 4326)  -- Latitude/longitude
);

-- Insert points
INSERT INTO locations (name, geom) VALUES
    ('DodaTech HQ', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography),
    ('Central Park', ST_SetSRID(ST_MakePoint(-73.9654, 40.7829), 4326)::geography);

-- Find locations within 5km of DodaTech HQ
SELECT name, ST_Distance(
    geom,
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography
) AS distance_meters
FROM locations
WHERE ST_DWithin(
    geom,
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography,
    5000  -- 5km
);

EXPLAIN ANALYZE SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

CREATE TABLE articles (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    -- instead of: id SERIAL PRIMARY KEY
);

SELECT title, metadata->>'reading_time' AS minutes
FROM posts
WHERE metadata @> '{"author": "admin"}'::jsonb
  AND (metadata->>'reading_time')::int BETWEEN 5 AND 15;

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 5 slowest queries
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_time_ms,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- Reset statistics when tuning
SELECT pg_stat_statements_reset();

# Install and connect
sudo apt install postgresql postgresql-contrib
sudo -u postgres psql

-- Inside psql:
CREATE DATABASE test_jsonb;
\c test_jsonb

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO documents (data) VALUES
    ('{"title": "Doc 1", "tags": ["a", "b"], "views": 100}'),
    ('{"title": "Doc 2", "tags": ["b", "c"], "views": 200}'),
    ('{"title": "Doc 3", "tags": ["a", "c"], "views": 150}');

-- Query JSONB
SELECT data->>'title' AS title,
       (data->>'views')::int AS views
FROM documents
ORDER BY views DESC;

-- Create GIN index
CREATE INDEX idx_doc_data ON documents USING GIN (data);

-- Find documents with tag "a"
SELECT data->>'title' FROM documents
WHERE data @> '{"tags": ["a"]}'::jsonb;