sql

-- Basic LATERAL join
SELECT
  u.username,
  recent.order_id,
  recent.total,
  recent.created_at

-- Unnormalized (0NF): Repeating groups
CREATE TABLE orders_bad (
  order_id INT,
  customer_name VARCHAR(100),
  customer_email VARCHAR(100),
  product1 VARCHAR(100),

-- Create materialized view
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  users.id,
  users.username,
  COUNT(DISTINCT orders.id) AS order_count,

-- Connection statistics
SELECT
  count(*) AS total_connections,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,

-- Basic EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN with cost and row estimates
-- Output shows: Seq Scan on users (cost=0.00..15.50 rows=1 width=100)

class CreateCustomerRevenueSummaries < ActiveRecord::Migration[7.0]
  def change
    create_view :customer_revenue_summaries, materialized: true, version: 1

    add_index :customer_revenue_summaries,
              :customer_id,

-- Create basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

ALTER TABLE snips ADD COLUMN IF NOT EXISTS summary TEXT;

-- backfill in batches from application code or a script

ALTER TABLE snips ALTER COLUMN summary SET NOT NULL;

-- Create table with JSONB column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  metadata JSONB DEFAULT '{}'::jsonb

-- ROLLUP for hierarchical subtotals
SELECT
  COALESCE(category, 'ALL CATEGORIES') AS category,
  COALESCE(subcategory, 'ALL SUBCATEGORIES') AS subcategory,
  SUM(revenue) AS total_revenue,
  COUNT(*) AS order_count

; PgBouncer configuration file

[databases]
; Database connection strings
mydb = host=localhost port=5432 dbname=mydb
analytics = host=replica.example.com port=5432 dbname=mydb