-- Add a tsvector column for efficient searching
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

-- Update search vector from title and content
UPDATE articles SET search_vector =
    to_tsvector('english', title) ||
    to_tsvector('english', COALESCE(content, ''));

-- Auto-update on insert/update using a trigger
CREATE OR REPLACE FUNCTION articles_search_vector_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', NEW.title), 'A') ||    -- Title gets weight A (highest)
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');  -- Content gets B
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_vector_update();

-- Create index for fast searching
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Search queries
SELECT
    title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', content, query, 'MaxWords=20') AS excerpt
FROM articles, to_tsquery('english', 'postgresql & database') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- Rank articles by views within each category
SELECT
    title,
    category,
    views,
    RANK() OVER (PARTITION BY category ORDER BY views DESC) AS rank_in_category,
    DENSE_RANK() OVER (ORDER BY views DESC) AS overall_rank,
    ROW_NUMBER() OVER (ORDER BY created_at) AS article_number
FROM articles
WHERE published = TRUE;

-- Running total (cumulative sum)
SELECT
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY date) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_moving_average
FROM daily_sales;

-- Compare to previous row
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month_change,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

-- Percentile ranking
SELECT
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile,
    NTILE(4) OVER (ORDER BY salary) AS quartile  -- Divide into 4 groups
FROM employees;

-- WITH clause creates named temporary result sets
WITH published_articles AS (
    SELECT id, title, author_id, views
    FROM articles
    WHERE published = TRUE
),
top_authors AS (
    SELECT author_id, SUM(views) AS total_views
    FROM published_articles
    GROUP BY author_id
    HAVING SUM(views) > 10000
)
SELECT
    u.name,
    ta.total_views,
    COUNT(pa.id) AS article_count
FROM top_authors ta
JOIN users u ON u.id = ta.author_id
JOIN published_articles pa ON pa.author_id = ta.author_id
GROUP BY u.name, ta.total_views
ORDER BY ta.total_views DESC;

-- Categories with parent-child relationships
WITH RECURSIVE category_tree AS (
    -- Base case: top-level categories (no parent)
    SELECT id, name, parent_id, 0 AS depth, name::TEXT AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: child categories
    SELECT
        c.id, c.name, c.parent_id,
        ct.depth + 1,
        ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, depth, path
FROM category_tree
ORDER BY path;

-- Result:
-- Technology                          (depth: 0)
-- Technology > Programming            (depth: 1)
-- Technology > Programming > Python   (depth: 2)
-- Technology > Programming > Java     (depth: 2)

-- LATERAL allows subquery to reference columns from previous FROM items
SELECT
    u.name,
    recent_posts.title,
    recent_posts.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT title, created_at
    FROM articles
    WHERE author_id = u.id          -- References outer query!
    ORDER BY created_at DESC
    LIMIT 3                         -- Last 3 articles per user
) recent_posts;

# Memory settings (most impactful)
shared_buffers = 256MB        # 25% of RAM — PostgreSQL's cache
effective_cache_size = 1GB    # Estimate of OS + PostgreSQL cache (75% of RAM)
work_mem = 4MB                # Per sort/hash operation per connection
maintenance_work_mem = 64MB   # For VACUUM, CREATE INDEX, etc.

# WAL settings
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Connection settings
max_connections = 100         # Limit connections — use PgBouncer for pooling

# Query planner settings
random_page_cost = 1.1        # For SSD storage (default 4.0 is for spinning disk)
effective_io_concurrency = 200  # For SSD

# Logging
log_min_duration_statement = 1000  # Log queries taking over 1 second
log_checkpoints = on

-- PostgreSQL uses MVCC — old row versions pile up
-- VACUUM reclaims space

-- Manual vacuum
VACUUM articles;

-- Analyze — update statistics for query planner
ANALYZE articles;

-- Vacuum and analyze together
VACUUM ANALYZE articles;

-- Autovacuum — runs automatically in background
-- Check autovacuum status
SELECT schemaname, tablename, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Problem: PostgreSQL creates a new OS process per connection
         100 connections = 100 processes = significant memory

Solution: PgBouncer sits between app and PostgreSQL
          App → PgBouncer (manages 100 app connections)
          PgBouncer → PostgreSQL (maintains 10-20 actual connections)

-- Find slow queries
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Table sizes
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

-- Unused indexes
SELECT
    indexname, tablename,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY idx_scan;

# Install PostgreSQL (Ubuntu)
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL
sudo systemctl start postgresql

# Connect as postgres user
sudo -u postgres psql

# In psql shell:
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE myapp OWNER myuser;
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;
\q

# Connect to database
psql -U myuser -d myapp -h localhost

# Using Docker (easiest for development)
docker run -d \
    --name postgres \
    -e POSTGRES_USER=myuser \
    -e POSTGRES_PASSWORD=mypassword \
    -e POSTGRES_DB=myapp \
    -p 5432:5432 \
    -v postgres_data:/var/lib/postgresql/data \
    postgres:16

import psycopg2
import psycopg2.extras

conn = psycopg2.connect(
    host="localhost",
    database="myapp",
    user="myuser",
    password="mypassword"
)

cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cursor.execute("SELECT * FROM articles WHERE published = TRUE LIMIT 10")
articles = cursor.fetchall()

for article in articles:
    print(article["title"])

const { Pool } = require("pg");

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 20,                    // Maximum pool size
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
});

const { rows } = await pool.query(
    "SELECT * FROM articles WHERE published = $1 LIMIT $2",
    [true, 10]
);