The Ultimate PostgreSQL Cheatsheet

-- Basic SELECT
SELECT first_name, last_name, email
FROM users
WHERE active = TRUE
  AND created_at > '2025-01-01'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- SELECT with DISTINCT
SELECT DISTINCT department
FROM employees
ORDER BY department;

-- SELECT with CASE expression
SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM students;

-- SELECT with COALESCE and NULLIF
SELECT
  COALESCE(phone, email, 'N/A') AS contact,
  NULLIF(age, 0) AS safe_age
FROM contacts;

-- SELECT with LIKE and ILIKE (case-insensitive)
SELECT *
FROM products
WHERE name ILIKE '%laptop%'
   OR description ~* '[0-9]{3}';  -- regex match (case-insensitive)

-- Single row insert
INSERT INTO users (first_name, last_name, email, created_at)
VALUES ('Alice', 'Smith', 'alice@example.com', NOW());

-- Multiple rows insert
INSERT INTO products (name, price, category, in_stock)
VALUES
  ('Widget A', 29.99, 'widgets', TRUE),
  ('Widget B', 49.99, 'widgets', TRUE),
  ('Gadget X', 99.99, 'gadgets', FALSE);

-- INSERT ... ON CONFLICT (upsert)
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON CONFLICT (email) DO UPDATE
SET login_count = users.login_count + EXCLUDED.login_count,
    updated_at = NOW()
RETURNING id, email, login_count;

-- INSERT ... ON CONFLICT DO NOTHING
INSERT INTO tags (name) VALUES ('postgreSQL')
ON CONFLICT (name) DO NOTHING;

-- UPDATE with WHERE
UPDATE users
SET last_login = NOW(), login_count = login_count + 1
WHERE email = 'alice@example.com'
RETURNING *;

-- UPDATE with FROM (join-like update)
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE o.id = s.order_id
  AND s.carrier = 'FedEx';

-- DELETE with RETURNING
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING session_id, user_id;

-- Safe delete: CTE + RETURNING
WITH deleted AS (
  DELETE FROM temp_logs
  WHERE created_at < NOW() - INTERVAL '30 days'
  RETURNING *
)
SELECT count(*) AS rows_deleted FROM deleted;

CREATE TABLE users (
  id            BIGSERIAL PRIMARY KEY,
  email         VARCHAR(255) NOT NULL UNIQUE,
  username      VARCHAR(50)  NOT NULL,
  display_name  VARCHAR(100),
  password_hash VARCHAR(255) NOT NULL,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  role          VARCHAR(20) NOT NULL DEFAULT 'member'
                CHECK (role IN ('admin', 'moderator', 'member', 'guest')),
  avatar_url    TEXT,
  bio           TEXT CHECK (LENGTH(bio) <= 500),
  metadata      JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- CREATE TABLE with FK and advanced constraints
CREATE TABLE orders (
  id            BIGSERIAL PRIMARY KEY,
  user_id       BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status        VARCHAR(20) NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped',
                                   'delivered', 'cancelled')),
  total         NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  shipping_addr JSONB NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- CREATE TABLE IF NOT EXISTS
CREATE TABLE IF NOT EXISTS audit_logs (
  id         BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  action     VARCHAR(10) NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
  old_data   JSONB,
  new_data   JSONB,
  performed_by BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with default and NOT NULL
ALTER TABLE users ADD COLUMN timezone VARCHAR(50)
  NOT NULL DEFAULT 'UTC';

-- Drop a column
ALTER TABLE users DROP COLUMN IF EXISTS legacy_field;

-- Rename a column
ALTER TABLE users RENAME COLUMN display_name TO full_name;

-- Change column type
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 2);

-- Add constraint
ALTER TABLE users ADD CONSTRAINT username_min_length
  CHECK (LENGTH(username) >= 3);

-- Add foreign key
ALTER TABLE orders
  ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id);

-- Add unique constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- Drop table (prevents accidental drops if FKs exist)
DROP TABLE IF EXISTS temp_logs CASCADE;

-- Drop schema
DROP SCHEMA IF EXISTS legacy_schema CASCADE;

-- Drop index
DROP INDEX IF EXISTS idx_users_email;

-- Drop database (cannot run inside a transaction)
DROP DATABASE IF EXISTS old_app_db;

-- Column with array type
CREATE TABLE posts (
  id        BIGSERIAL PRIMARY KEY,
  title     TEXT NOT NULL,
  tags      TEXT[] NOT NULL DEFAULT '{}',
  scores    INTEGER[]
);

-- Insert arrays
INSERT INTO posts (title, tags, scores)
VALUES ('PostgreSQL Tips', ARRAY['database', 'sql', 'tips'],
        ARRAY[9, 8, 10]);

-- Query array elements (ANY)
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- Query array elements (contains)
SELECT * FROM posts WHERE tags @> ARRAY['database'];

-- Array indexing (0-based)
SELECT title, tags[1], scores[1:3] FROM posts;

-- Array operators
SELECT * FROM posts
WHERE tags && ARRAY['sql', 'nosql'];  -- overlap

-- unnest: expand array to rows
SELECT title, unnest(tags) AS tag FROM posts;

-- Array aggregation
SELECT title, array_agg(tag ORDER BY tag) AS sorted_tags
FROM posts, unnest(tags) AS tag
GROUP BY title;

-- Composite primary key
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)
);

-- DEFERRABLE constraints (check at commit time)
ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  DEFERRABLE INITIALLY DEFERRED;

-- Multi-column unique
ALTER TABLE enrollments
  ADD CONSTRAINT uq_student_course
  UNIQUE (student_id, course_id);

-- Basic INNER JOIN
SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'shipped';

-- Multiple INNER JOINs
SELECT o.id, u.name, p.name AS product, oi.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2025-01-01';

-- LEFT JOIN: all users, even without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY order_count DESC;

-- LEFT JOIN to find missing data
SELECT u.name, u.email
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
WHERE p.user_id IS NULL;  -- users with no profile

-- RIGHT JOIN
SELECT d.name AS department, COUNT(e.id) AS employees
FROM departments d
RIGHT JOIN employees e ON d.id = e.dept_id
GROUP BY d.name;

-- FULL OUTER JOIN: find unmatched in either table
SELECT
  COALESCE(u.id, p.user_id) AS user_id,
  u.name,
  p.bio
FROM users u
FULL OUTER JOIN profiles p ON u.id = p.user_id
WHERE u.id IS NULL OR p.user_id IS NULL;

-- CROSS JOIN: generate all combinations
SELECT s.name AS student, c.name AS course
FROM students s
CROSS JOIN courses c;

-- Self join: manager-employee relationship
SELECT
  e.name AS employee,
  m.name AS manager,
  e.salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

-- Self join: find duplicate emails
SELECT a.id, a.email
FROM users a
INNER JOIN users b ON a.email = b.email AND a.id > b.id;

-- USING: when columns have the same name
SELECT order_id, user_id, total
FROM orders
INNER JOIN shipments USING (order_id);

-- Multiple columns with USING
SELECT *
FROM enrollments e
INNER JOIN grades g USING (student_id, course_id);

-- Scalar subquery in WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery in FROM (derived table)
SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE avg_salary > 75000;

-- Correlated subquery
SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

-- EXISTS / NOT EXISTS
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 500
);

-- IN subquery
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories
  WHERE is_featured = TRUE
);

-- Subquery with ANY / ALL
SELECT * FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category = 'sale'
);

-- LATERAL: subquery can reference columns from preceding tables
SELECT u.name, top_orders.order_id, top_orders.total
FROM users u
CROSS JOIN LATERAL (
  SELECT id AS order_id, total
  FROM orders
  WHERE user_id = u.id
  ORDER BY total DESC
  LIMIT 3
) AS top_orders;

-- LATERAL with LEFT JOIN (no rows = NULLs, not filtering out)
SELECT c.name, p.product_name
FROM customers c
LEFT JOIN LATERAL (
  SELECT p.name AS product_name
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  JOIN orders o ON oi.order_id = o.id
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 1
) p ON TRUE;

-- Basic CTE
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE is_active = TRUE
    AND last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM active_users WHERE name ILIKE '%john%';

-- Multiple CTEs
WITH
  monthly_revenue AS (
    SELECT
      DATE_TRUNC('month', created_at) AS month,
      SUM(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
  ),
  monthly_targets AS (
    SELECT month, target
    FROM sales_targets
    WHERE fiscal_year = 2025
  )
SELECT
  mr.month,
  mr.revenue,
  mt.target,
  (mr.revenue - mt.target) AS variance,
  ROUND((mr.revenue / NULLIF(mt.target, 0)) * 100, 1) AS pct_achieved
FROM monthly_revenue mr
LEFT JOIN monthly_targets mt ON mr.month = mt.month
ORDER BY mr.month;

-- CTE with DML (data-modifying CTE)
WITH new_order AS (
  INSERT INTO orders (user_id, total, status)
  VALUES (42, 199.99, 'pending')
  RETURNING id
),
new_items AS (
  INSERT INTO order_items (order_id, product_id, quantity)
  SELECT no.id, p.id, 1
  FROM new_order no
  CROSS JOIN (VALUES (101), (102)) AS p(id)
  RETURNING order_id
)
SELECT * FROM new_order JOIN new_items USING (order_id);

-- Employee hierarchy (org chart)
WITH RECURSIVE org_chart AS (
  -- Base case: top-level managers (no manager)
  SELECT id, name, manager_id, 1 AS level, ARRAY[name] AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees reporting to current level
  SELECT e.id, e.name, e.manager_id, oc.level + 1,
         oc.path || e.name
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, name, path
FROM org_chart
ORDER BY path;

-- Bill of materials (BOM explosion)
WITH RECURSIVE bom AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM components
  WHERE name = 'Electric Car'

  UNION ALL

  SELECT c.id, c.name, c.parent_id, b.depth + 1
  FROM components c
  INNER JOIN bom b ON c.parent_id = b.id
)
SELECT * FROM bom ORDER BY depth;

-- Fibonacci sequence
WITH RECURSIVE fib(n, a, b) AS (
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, b, a + b FROM fib WHERE n < 10
)
SELECT n, a AS fibonacci FROM fib;

-- UNION: combine result sets (removes duplicates)
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers
ORDER BY name;

-- UNION ALL: include duplicates (faster)
SELECT 'email' AS channel, email AS address FROM users
UNION ALL
SELECT 'phone', phone FROM users WHERE phone IS NOT NULL;

-- INTERSECT: rows present in both queries
SELECT product_id FROM featured_products
INTERSECT
SELECT product_id FROM in_stock_products;

-- EXCEPT: rows in first but not second
SELECT user_id FROM premium_users
EXCEPT
SELECT user_id FROM suspended_users;

-- Syntax:
-- FUNCTION() OVER (
--   [ PARTITION BY col1, col2, ... ]
--   [ ORDER BY col1 [ASC|DESC] ]
--   [ frame_clause ]
-- )

-- Simple ranking over entire table
SELECT name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rank
FROM employees;

-- PARTITION BY: reset per department
SELECT name, department, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees
ORDER BY department, salary DESC;

-- Results example (salary ties):
-- name  | dept  | salary | rn  | rnk | drnk
-- Carol | Eng   | 120000 |  1  |  1  |  1
-- Alice | Eng   | 110000 |  2  |  2  |  2
-- Bob   | Eng   | 110000 |  3  |  2  |  2   <- tie
-- Dave  | Eng   |  90000 |  4  |  4  |  3   <- drnk skips nothing

-- LAG: value from a previous row
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  LAG(revenue, 7) OVER (ORDER BY date) AS prev_week,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

-- LEAD: value from a following row
SELECT
  user_id,
  login_at,
  LEAD(login_at, 1) OVER (PARTITION BY user_id ORDER BY login_at) AS next_login,
  LEAD(login_at, 1) OVER (PARTITION BY user_id ORDER BY login_at)
    - login_at AS session_gap
FROM user_logins;

-- LAG with default value
SELECT date, temperature,
  COALESCE(
    LAG(temperature) OVER (ORDER BY date),
    temperature
  ) AS prev_temp
FROM weather_data;

-- FIRST_VALUE: first row in the window
SELECT
  department,
  name,
  salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS highest_paid
FROM employees;

-- LAST_VALUE: last row (careful with default frame!)
SELECT
  date,
  price,
  FIRST_VALUE(price) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS all_time_high,
  LAST_VALUE(price) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS all_time_low
FROM stock_prices;

-- NTILE: divide rows into N buckets
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- Practical: segment customers by spend
WITH customer_buckets AS (
  SELECT
    customer_id,
    total_spend,
    NTILE(5) OVER (ORDER BY total_spend DESC) AS spend_quintile
  FROM customer_summary
)
SELECT
  spend_quintile,
  COUNT(*) AS customers,
  MIN(total_spend) AS min_spend,
  MAX(total_spend) AS max_spend,
  AVG(total_spend) AS avg_spend
FROM customer_buckets
GROUP BY spend_quintile
ORDER BY spend_quintile;

-- Running totals
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Running average
SELECT
  date,
  sales,
  AVG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS 7_day_avg
FROM daily_sales;

-- Percentage of total
SELECT
  category,
  sales,
  ROUND(
    sales / SUM(sales) OVER () * 100, 2
  ) AS pct_of_total
FROM category_sales;

-- Percentage within partition
SELECT
  department,
  name,
  salary,
  ROUND(
    salary / MAX(salary) OVER (PARTITION BY department) * 100, 1
  ) AS pct_of_dept_max
FROM employees;

-- ROWS: count physical rows
SELECT date, sales,
  SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS 3_day_sum
FROM daily_sales;

-- RANGE: count by value (logical, not physical)
SELECT date, sales,
  SUM(sales) OVER (
    ORDER BY sales
    RANGE BETWEEN 50 PRECEDING AND 50 FOLLOWING
  ) AS nearby_sum
FROM daily_sales;

-- Common frame clauses
-- ROWS UNBOUNDED PRECEDING          -- from start
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- center
-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- from now
-- ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING     -- prev 3

-- Moving average with explicit frame
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS ma_30
FROM daily_revenue;

-- Create table with JSONB column
CREATE TABLE app_settings (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  settings   JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSONB data
INSERT INTO app_settings (user_id, settings)
VALUES (
  1,
  '{"theme": "dark", "language": "en", "notifications": {
    "email": true, "push": false, "sms": true
  }}'::jsonb
);

-- Insert using to_jsonb()
INSERT INTO app_settings (user_id, settings)
VALUES (2, to_jsonb(varchar 'test'));

-- Query with -> (returns JSONB)
SELECT settings->'theme' AS theme FROM app_settings;

-- Query with ->> (returns TEXT)
SELECT settings->>'theme' AS theme FROM app_settings;

-- Nested path access (PostgreSQL 12+)
SELECT settings->'notifications'->>'email' AS email_notif
FROM app_settings;

-- Chained path with -> and ->>
SELECT settings->'notifications'->'email' AS raw
FROM app_settings;

-- @> : contains (left JSONB contains right)
SELECT * FROM app_settings
WHERE settings @> '{"theme": "dark"}'::jsonb;

-- <@ : contained by (left is contained in right)
SELECT * FROM app_settings
WHERE '{"theme": "dark"}'::jsonb <@ settings;

-- ? : key exists (top-level)
SELECT * FROM app_settings
WHERE settings ? 'theme';

-- ?| : any key exists (any of the listed)
SELECT * FROM app_settings
WHERE settings ?| ARRAY['theme', 'language'];

-- ?& : all keys exist
SELECT * FROM app_settings
WHERE settings ?& ARRAY['theme', 'notifications'];

-- @?: JSON path exists (PostgreSQL 12+)
SELECT * FROM app_settings
WHERE settings @? '$.notifications.email';

-- @@: JSON path match (returns boolean)
SELECT * FROM app_settings
WHERE settings @@ '$.notifications.push == false';

-- jsonb_each: expand top-level key-value pairs
SELECT key, value
FROM jsonb_each('{"a": 1, "b": "two", "c": [1,2,3]}');

-- jsonb_each_text: values as text
SELECT key, value
FROM jsonb_each_text('{"a": 1, "b": "two"}');

-- jsonb_object_keys: just the keys
SELECT jsonb_object_keys('{"name": "Alice", "age": 30}');

-- jsonb_array_elements: expand JSON arrays into rows
SELECT value
FROM jsonb_array_elements('[1, 2, 3, 4, 5]');

-- jsonb_array_elements_text: elements as text
SELECT elem
FROM jsonb_array_elements_text('["apple", "banana", "cherry"]') AS elem;

-- jsonb_typeof: check type of a JSONB value
SELECT
  jsonb_typeof('null'::jsonb),       -- null
  jsonb_typeof('42'::jsonb),         -- number
  jsonb_typeof('"hello"'::jsonb),    -- string
  jsonb_typeof('true'::jsonb),       -- boolean
  jsonb_typeof('[1,2]'::jsonb),      -- array
  jsonb_typeof('{"a":1}'::jsonb);    -- object

-- jsonb_strip_nulls: remove keys with null values
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": 3}');

-- jsonb_pretty: pretty-print JSONB
SELECT jsonb_pretty(settings) FROM app_settings;

-- jsonb_path_query: returns matching JSONB values
SELECT jsonb_path_query(
  settings,
  '$.notifications.*'
) FROM app_settings;

-- jsonb_path_query_array: returns matching values as JSONB array
SELECT jsonb_path_query_array(
  settings,
  '$.notifications.* ? (@ == true)'
) FROM app_settings;

-- jsonb_path_query_first: first match only
SELECT jsonb_path_query_first(
  settings,
  '$.theme'
) FROM app_settings;

-- Path expression features
-- $.key          → top-level key
-- $.key.sub      → nested key
-- $.array[*]     → all array elements
-- $.array[0]     → first element
-- $[0 to 2]      → first three elements
-- ? (@.price > 50)  → filter predicate
-- ? (@.name starts with "A") → string filter

-- jsonb_set: set a key's value (returns modified JSONB)
UPDATE app_settings
SET settings = jsonb_set(
  settings,
  '{theme}',
  '"light"'::jsonb
)
WHERE user_id = 1
RETURNING settings;

-- Set nested value
UPDATE app_settings
SET settings = jsonb_set(
  settings,
  '{notifications,email}',
  'false'::jsonb
)
WHERE user_id = 1;

-- Delete a key (set to null + strip, or use - operator)
UPDATE app_settings
SET settings = settings - 'legacy_field'
WHERE settings ? 'legacy_field';

-- Delete multiple keys
UPDATE app_settings
SET settings = settings - ARRAY['temp1', 'temp2'];

-- Delete from nested object
UPDATE app_settings
SET settings = settings - '{notifications,sms}'
WHERE user_id = 1;

-- Add or update using || (merge)
UPDATE app_settings
SET settings = settings || '{"timezone": "America/New_York"}'::jsonb
WHERE user_id = 1;

-- Increment a numeric value inside JSONB
UPDATE app_settings
SET settings = jsonb_set(
  settings,
  '{login_count}',
  COALESCE(
    (settings->>'login_count')::int,
    0
  )::jsonb + 1
)
WHERE user_id = 1;

-- jsonb_agg: aggregate rows into a JSONB array
SELECT user_id,
  jsonb_agg(
    jsonb_build_object('name', name, 'amount', amount)
  ) AS orders_json
FROM orders
GROUP BY user_id;

-- jsonb_object_agg: build a JSONB object from key-value pairs
SELECT
  jsonb_object_agg(
    key,
    value
  ) AS settings_map
FROM app_settings
CROSS JOIN jsonb_each(settings);

-- jsonb_build_object: create object from key-value pairs
SELECT jsonb_build_object(
  'id', user_id,
  'name', display_name,
  'active', is_active,
  'created', created_at::text
) AS user_json
FROM users
WHERE id = 1;

-- jsonb_build_array: create array
SELECT jsonb_build_array(
  user_id, display_name, email
) AS user_arr FROM users WHERE id = 1;

-- to_jsonb / to_json: convert any type to JSONB
SELECT to_jsonb(t)
FROM (
  SELECT name, email, created_at FROM users LIMIT 5
) t;

-- Row to JSONB
SELECT to_jsonb(users) FROM users WHERE id = 1;

-- Default GIN index (jsonb_path_ops is recommended)
CREATE INDEX idx_settings_gin ON app_settings USING GIN (settings);

-- jsonb_path_ops: smaller index, supports @> and ? only
CREATE INDEX idx_settings_path_ops
  ON app_settings USING GIN (settings jsonb_path_ops);

-- The jsonb_path_ops index supports:
--   settings @> '{"key": "value"}'   ✅
--   settings ? 'key'                 ✅
--   settings ?| ARRAY['a','b']       ✅
--   settings ?& ARRAY['a','b']       ✅
--   settings->>'key' = 'value'       ❌ (use default GIN or expression index)

-- Expression index for specific key lookup
CREATE INDEX idx_settings_theme
  ON app_settings ((settings->>'theme'));

-- Partial GIN index
CREATE INDEX idx_settings_has_theme
  ON app_settings USING GIN (settings)
  WHERE settings ? 'theme';

-- Check index usage with EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM app_settings
WHERE settings @> '{"theme": "dark"}'::jsonb;

-- Create index (B-tree is default)
CREATE INDEX idx_users_email ON users (email);

-- Multi-column index (column order matters!)
CREATE INDEX idx_orders_user_date
  ON orders (user_id, created_at DESC);

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

-- Index with INCLUDE (covering index)
CREATE INDEX idx_orders_covering
  ON orders (user_id)
  INCLUDE (status, total);

-- Create index concurrently (no table lock!)
CREATE INDEX CONCURRENTLY idx_products_category
  ON products (category_id);

-- Conditional (partial) index
CREATE INDEX idx_active_users
  ON users (email)
  WHERE is_active = TRUE;

-- Index on expression
CREATE INDEX idx_users_lower_email
  ON users (LOWER(email));

-- Index on COALESCE
CREATE INDEX idx_users_sort_name
  ON users (COALESCE(display_name, email));

-- GIN on JSONB
CREATE INDEX idx_settings_gin
  ON app_settings USING GIN (settings);

-- GIN on JSONB with path_ops (smaller, faster for @>)
CREATE INDEX idx_settings_path_ops
  ON app_settings USING GIN (settings jsonb_path_ops);

-- GIN on array column
CREATE INDEX idx_posts_tags
  ON posts USING GIN (tags);

-- GIN on tsvector (full-text search)
CREATE INDEX idx_documents_search
  ON documents USING GIN (to_tsvector('english', content));

-- GIN with fastupdate (batch insert optimization)
CREATE INDEX idx_logs_tags
  ON logs USING GIN (tags)
  WITH (fastupdate = ON);

-- GIN index parameters
-- fastupdate: ON/OFF (default ON) - faster inserts, slightly slower search
-- gin_pending_list_limit: max pending list size before flushing

-- GiST index on range type
CREATE INDEX idx_reservations_during
  ON reservations USING GiST (during);

-- GiST with exclusion constraint (no overlaps)
CREATE TABLE room_reservations (
  room_id  INT NOT NULL,
  during   TSTRANGE NOT NULL,
  EXCLUDE USING GiST (
    room_id WITH =,
    during  WITH &&
  )
);

-- BRIN index for large, sequential data
CREATE INDEX idx_orders_created_brin
  ON orders USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- BRIN index on time-series table
CREATE INDEX idx_sensor_readings_time
  ON sensor_readings USING BRIN (timestamp)
  WITH (pages_per_range = 128);

-- SP-GiST for text prefix matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_docs_name_trgm
  ON documents USING SP-GiST (name gin_trgm_ops);

-- SP-GiST for CIDR
CREATE INDEX idx_blocks_network
  ON ip_blocks USING GiST (network);

-- Covering index with INCLUDE (PostgreSQL 11+)
-- All columns needed by the query are in the index → no table access
CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (status, total, created_at);

-- This query uses index-only scan:
SELECT status, total, created_at
FROM orders
WHERE user_id = 42;

-- Without INCLUDE, PostgreSQL needs to fetch table rows (heap fetch)
CREATE INDEX idx_orders_user_simple
  ON orders (user_id);
-- This query needs heap access for status and total

-- Verify with EXPLAIN
EXPLAIN ANALYZE
SELECT status, total
FROM orders
WHERE user_id = 42;

-- Basic EXPLAIN (shows plan, doesn't execute)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN ANALYZE (executes and shows actual timing)
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- EXPLAIN with buffers and format
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products WHERE price > 100;

-- Key output terms:
-- Seq Scan      → full table scan (usually slow)
-- Index Scan    → using an index + table fetch
-- Index Only Scan → using index alone (fastest)
-- Bitmap Scan   → index lookup → bitmap → table fetch
-- Nested Loop   → nested loop join
-- Hash Join     → hash join (good for large tables)
-- Merge Join    → merge join (sorted inputs)
-- Parallel Seq Scan → parallel full scan
-- Shared Hit    → page found in shared_buffers (cache hit)
-- Heap Fetch    → needed to go to table (not index-only)

-- Detailed EXPLAIN with all options
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT
  u.name,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2025-01-01'
GROUP BY u.id, u.name
HAVING SUM(o.total) > 1000
ORDER BY total_spent DESC
LIMIT 20;

-- Check for common problems:
-- 1. "Seq Scan" on large tables → missing index
-- 2. "Nested Loop" with high row estimates → stale statistics
-- 3. "Sort" with high cost → consider index on ORDER BY
-- 4. "Filter" removing many rows → partial index opportunity
-- 5. "Buffers: shared read=..." → disk I/O, not cache hits

-- Run ANALYZE to update statistics
ANALYZE users;
ANALYZE;  -- analyze all tables

-- Analyze with higher sample size for complex queries
ANALYZE users WITH (sample_percent = 25);

-- Check table statistics
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Check current query plans (auto-explain)
-- Add to postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '100ms'
-- auto_explain.log_analyze = on
-- auto_explain.log_buffers = on

-- View current running queries
SELECT pid, state, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Cancel a running query
SELECT pg_cancel_backend(pid);

-- Terminate a backend connection
SELECT pg_terminate_backend(pid);

-- Manual VACUUM (reclaims space, doesn't update stats)
VACUUM users;

-- VACUUM ANALYZE (reclaims space + updates stats)
VACUUM ANALYZE users;

-- VACUUM FULL (reclaims space, rewrites table, takes exclusive lock)
VACUUM FULL orders;

-- IMPORTANT: VACUUM FULL locks the table!
-- Use pg_repack or pg_squeeze for production instead.

-- Check autovacuum settings
SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

-- Key autovacuum settings (per-table overrides):
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- 5% changes trigger vacuum
  autovacuum_analyze_scale_factor = 0.02,  -- 2% changes trigger analyze
  autovacuum_vacuum_cost_delay = 10,        -- ms between vacuum sleeps
  autovacuum_max_workers = 4                -- max concurrent autovacuums
);

-- View dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- pg_bouncer modes:
-- session: one server connection per client connection
-- transaction: server connection pooled per transaction (RECOMMENDED)
-- statement: server connection returned after each statement

-- pg_bouncer.ini example:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600
listen_addr = 0.0.0.0
port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

-- Common issue: prepared statements don't work in transaction mode
-- Solution: use server_reset_query or prepared_statement_cache_size = 0

-- Range partitioning (time-series)
CREATE TABLE orders (
  id         BIGSERIAL,
  user_id    BIGINT NOT NULL,
  total      NUMERIC(12,2) NOT NULL,
  status     VARCHAR(20) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2025q1
  PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025q2
  PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

CREATE TABLE orders_2025q3
  PARTITION OF orders
  FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

CREATE TABLE orders_2025q4
  PARTITION OF orders
  FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');

-- Default partition (catch-all for unmatched rows)
CREATE TABLE orders_default
  PARTITION OF orders DEFAULT;

-- List partitioning (categorical data)
CREATE TABLE documents (
  id         BIGSERIAL,
  content    TEXT,
  category   VARCHAR(50) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY LIST (category);

CREATE TABLE docs_financial
  PARTITION OF documents
  FOR VALUES IN ('invoice', 'receipt', 'payment');

CREATE TABLE docs_hr
  PARTITION OF documents
  FOR VALUES IN ('resume', 'contract', 'review');

-- Hash partitioning (distribute evenly)
CREATE TABLE events (
  id         BIGSERIAL,
  event_type VARCHAR(50) NOT NULL,
  data       JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY HASH (id);

CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Attach partition to existing table
CREATE TABLE orders_2026q1 (LIKE orders INCLUDING DEFAULTS);
ALTER TABLE orders ATTACH PARTITION orders_2026q1
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

-- Detach partition (make it a regular table)
ALTER TABLE orders DETACH PARTITION orders_2025q1;

-- Check parallel query settings
SELECT name, setting
FROM pg_settings
WHERE name LIKE 'max_parallel%'
ORDER BY name;

-- Key settings:
-- max_parallel_workers_per_gather = 2  (default)
-- max_parallel_workers = 8              (default)
-- max_parallel_maintenance_workers = 2  (default)

-- Force parallel query for testing
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.001;
SET parallel_setup_cost = 100;

-- Run parallel query
EXPLAIN ANALYZE
SELECT category, SUM(total), COUNT(*)
FROM orders
GROUP BY category;

-- Create index in parallel (PostgreSQL 12+)
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY idx_orders_created
  ON orders (created_at);

-- Disable parallel for specific query
SET max_parallel_workers_per_gather = 0;

-- Create materialized view
CREATE MATERIALIZED VIEW mv_order_summary AS
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent,
  AVG(total) AS avg_order_value,
  MAX(created_at) AS last_order_at
FROM orders
WHERE status != 'cancelled'
GROUP BY user_id
WITH DATA;

-- Create index on materialized view
CREATE UNIQUE INDEX idx_mv_order_summary
  ON mv_order_summary (user_id);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW mv_order_summary;

-- Refresh concurrently (doesn't lock reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary;

-- Drop materialized view
DROP MATERIALIZED VIEW IF EXISTS mv_order_summary;

-- Enable pg_stat_statements
-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- Restart PostgreSQL, then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 slowest queries by total time
SELECT
  LEFT(query, 80) AS query_short,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total,
  rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- Queries with most calls
SELECT
  LEFT(query, 80) AS query_short,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Create roles
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
CREATE ROLE readonly NOLOGIN;
CREATE ROLE readwrite NOLOGIN;
CREATE ROLE admin WITH LOGIN PASSWORD 'admin_pass' SUPERUSER;

-- Grant role membership
GRANT readonly TO app_user;
GRANT readwrite TO app_user;

-- GRANT permissions
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES
  IN SCHEMA public TO readwrite;

GRANT USAGE, SELECT ON ALL SEQUENCES
  IN SCHEMA public TO readwrite;

-- Default privileges (auto-apply to future tables)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT INSERT, UPDATE, DELETE ON TABLES TO readwrite;

-- REVOKE permissions
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

-- Create user with expiration
CREATE ROLE temp_user WITH LOGIN PASSWORD 'temp_pass'
  VALID UNTIL '2025-12-31';

-- Modify role
ALTER ROLE app_user WITH PASSWORD 'new_secure_password';
ALTER ROLE app_user SET search_path = 'app_schema, public';

-- Create schema
CREATE SCHEMA IF NOT EXISTS app_schema
  AUTHORIZATION app_owner;

-- Set default schema
SET search_path TO app_schema, public;

-- Move a table to a different schema
ALTER TABLE my_table SET SCHEMA app_schema;

-- Grant schema permissions
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO app_user;

-- List all schemas
SELECT schema_name FROM information_schema.schemata;

-- Drop schema (must be empty or use CASCADE)
DROP SCHEMA IF EXISTS old_schema CASCADE;

-- List available extensions
SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;

-- pgcrypto: cryptographic functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT gen_random_uuid() AS uuid;       -- random UUID
SELECT digest('password', 'sha256');     -- hash
SELECT encrypt('data', 'key', 'aes');   -- encrypt
SELECT decrypt(encrypted, 'key', 'aes'); -- decrypt

-- uuid-ossp: UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v4();   -- random UUID
SELECT uuid_generate_v1();   -- MAC-address based
SELECT uuid_generate_v1mc(); -- random MAC

-- pg_trgm: trigram matching for fuzzy search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_name_trgm
  ON users USING GIN (name gin_trgm_ops);

-- Fuzzy search with similarity
SELECT name, similarity(name, 'jon') AS s
FROM users
WHERE name % 'jon'
ORDER BY s DESC;

-- Full-text search with tsvector
CREATE EXTENSION IF NOT EXISTS pg_trgm;
ALTER TABLE documents ADD COLUMN search_vector TSVECTOR
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;

CREATE INDEX idx_documents_search
  ON documents USING GIN (search_vector);

-- Full-text search query
SELECT title, ts_rank(search_vector, query) AS rank
FROM documents, plainto_tsquery('english', 'database performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# Full database backup (custom format, compressed)
pg_dump -Fc -f mydb_backup.dump mydb

# Plain SQL backup
pg_dump -f mydb_backup.sql mydb

# Backup specific tables
pg_dump -t users -t orders -f partial_backup.sql mydb

# Backup with data only (no schema)
pg_dump --data-only -f data_only.sql mydb

# Backup with schema only (no data)
pg_dump --schema-only -f schema_only.sql mydb

# Parallel backup (faster for large databases)
pg_dump -j 4 -Fc -f mydb_parallel.dump mydb

# Restore from custom format
pg_restore -d mydb -1 mydb_backup.dump

# Restore with clean (drops existing objects)
pg_restore --clean --if-exists -d mydb mydb_backup.dump

# Restore specific tables
pg_restore -d mydb -t users -t orders mydb_backup.dump

# Backup all databases
pg_dumpall -f all_databases_backup.sql

# Point-in-time recovery (PITR)
# 1. Enable WAL archiving in postgresql.conf:
#    wal_level = replica
#    archive_mode = on
#    archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
#
# 2. Take a base backup:
pg_basebackup -h localhost -D /var/lib/postgresql/base_backup -Ft -z -P
#
# 3. Restore to specific point in time:
#    recovery_target_time = '2025-07-15 14:30:00'
#    recovery_target_action = 'promote'

-- Streaming Replication (physical)
-- Primary (postgresql.conf):
-- wal_level = replica
-- max_wal_senders = 5
-- wal_keep_size = 1GB
-- hot_standby = on

-- On primary: create replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replica_pass';

-- pg_hba.conf (allow replication connections):
-- host replication replicator 192.168.1.0/24 scram-sha-256

-- On standby: take base backup
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R

-- Check replication status on primary
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  sent_lsn - replay_lsn AS lag_bytes
FROM pg_stat_replication;

-- Check replication lag
SELECT
  client_addr,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS byte_lag,
  NOW() - reply_time AS time_lag
FROM pg_stat_replication;

-- Logical Replication (PostgreSQL 10+)
-- On publisher:
CREATE PUBLICATION my_publication
  FOR TABLE users, orders, products;

-- Add table to existing publication
ALTER PUBLICATION my_publication ADD TABLE audit_logs;

-- On subscriber:
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host dbname=mydb user=replicator password=pass'
  PUBLICATION my_publication
  WITH (copy_data = true, create_slot = true);

-- Check subscription status
SELECT * FROM pg_stat_subscription;

-- Common replication commands
-- Refresh publication
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

-- Skip conflict
SELECT pg_logical_slot_get_binary_changes('my_slot', NULL, 'basebackup');

-- Disable subscription (keep data, stop syncing)
ALTER SUBSCRIPTION my_subscription DISABLE;

# Connect to database
psql -h localhost -p 5432 -U myuser -d mydb
psql postgresql://myuser:pass@localhost:5432/mydb

# Useful psql meta-commands
\l                  # list databases
\c mydb            # connect to database
\dt                # list tables
\dt public.*       # list tables in schema
\d table_name      # describe table
\d+ table_name     # describe table (detailed)
\di                # list indexes
\dv                # list views
\dm                # list materialized views
\df                # list functions
\dn                # list schemas
\du                # list roles/users
\dp                # list table permissions
\x                 # toggle expanded display
\timing            # toggle query timing
\pset pager off    # disable pager
\q                 # quit psql

# Execute SQL from file
\i /path/to/script.sql

# Save query results to file
\o /path/to/output.txt
SELECT * FROM users;
\o                  # stop saving

# Copy table to/from CSV
\copy (SELECT * FROM users) TO 'users.csv' WITH CSV HEADER
\copy users FROM 'users.csv' WITH CSV HEADER

# Describe functions
\df+ function_name

# Show query plan in psql
EXPLAIN ANALYZE SELECT * FROM users LIMIT 10;

# postgresql.conf — Key settings

# Connection settings
listen_addresses = 'localhost'
port = 5432
max_connections = 100

# Memory settings
shared_buffers = '256MB'         # ~25% of total RAM
effective_cache_size = '1GB'     # ~75% of total RAM
work_mem = '4MB'                 # per-operation memory
maintenance_work_mem = '64MB'    # for VACUUM, CREATE INDEX
wal_buffers = '-1'               # auto (1/32 of shared_buffers)

# WAL (Write-Ahead Log)
wal_level = replica
max_wal_size = '1GB'
min_wal_size = '80MB'
checkpoint_completion_target = 0.9

# Query Planner
random_page_cost = 1.1           # SSD (default 4.0 for HDD)
effective_io_concurrency = 200   # SSD concurrent IO

# Logging
log_min_duration_statement = '100ms'  # log slow queries
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p] %u@%d '

# Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = '1min'

# pg_hba.conf — Client authentication
# TYPE  DATABASE   USER      ADDRESS          METHOD

# Local connections
local   all         all                        peer

# IPv4 local connections
host    all         all     127.0.0.1/32      scram-sha-256

# IPv6 local connections
host    all         all     ::1/128           scram-sha-256

# Application connections (from app server)
host    mydb        app_user 10.0.0.0/24      scram-sha-256

# Replication connections
host    replication replicator 10.0.0.0/24    scram-sha-256

# Reload config (no restart needed)
# SELECT pg_reload_conf();
# Or: pg_ctl reload -D /var/lib/postgresql/data

-- Observe system columns (xmin, xmax, ctid)
SELECT xmin, xmax, ctid, *
FROM users
WHERE id = 1;

-- VACUUM reclaims space from dead tuples
VACUUM ANALYZE users;

-- Partial index: only active users
CREATE INDEX idx_active_users_email
  ON users (email) WHERE is_active = TRUE;

-- Partial unique: unique email for non-deleted
CREATE UNIQUE INDEX idx_unique_active_email
  ON users (email) WHERE deleted_at IS NULL;

-- Example with ties in salary
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

-- name  | salary | rn  | rnk | drnk
-- Carol | 120000 |  1  |  1  |  1
-- Alice | 110000 |  2  |  2  |  2
-- Bob   | 110000 |  3  |  2  |  2
-- Dave  |  90000 |  4  |  4  |  3
-- Eve   |  80000 |  5  |  5  |  4

-- ROW_NUMBER: always unique, breaks ties arbitrarily
-- RANK: ties get same rank, next rank has a gap (skip)
-- DENSE_RANK: ties get same rank, no gap after ties

-- Handle deadlock in application (pseudo-code)
-- BEGIN;
--   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
--   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- COMMIT;
-- On deadlock: retry the entire transaction

-- Check current locks
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE NOT granted;

-- View blocked queries
SELECT
  blocked.pid AS blocked_pid,
  blocking.pid AS blocking_pid,
  blocked.query AS blocked_query,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks wl ON wl.locktype = bl.locktype
  AND wl.database IS NOT DISTINCT FROM bl.database
  AND wl.relation IS NOT DISTINCT FROM bl.relation
  AND wl.page IS NOT DISTINCT FROM bl.page
  AND wl.tuple IS NOT DISTINCT FROM bl.tuple
JOIN pg_stat_activity blocking ON blocking.pid = wl.pid
WHERE bl.granted = FALSE;

-- @> : containment (value exists somewhere in document)
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "L"}'::jsonb;

-- ? : key exists (top-level only)
SELECT * FROM products
WHERE attributes ? 'color';

-- ?| : any of these keys exist
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'weight'];

-- ?& : all of these keys exist
SELECT * FROM products
WHERE attributes ?& ARRAY['color', 'size'];

-- Performance: @> with jsonb_path_ops GIN index is the fastest pattern
CREATE INDEX idx_attrs ON products USING GIN (attributes jsonb_path_ops);

-- Step 1: Run EXPLAIN ANALYZE to find the bottleneck
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id;

-- Step 2: Look for these problems:
-- - Sequential Scan on large tables → add index
-- - Nested Loop with huge estimates → analyze table, check statistics
-- - Sort with high cost → add index for ORDER BY
-- - Filter removing many rows → partial index

-- Step 3: Add appropriate indexes
CREATE INDEX CONCURRENTLY idx_users_created
  ON users (created_at);

-- Step 4: Update statistics
ANALYZE users;

-- Step 5: Check pg_stat_statements for patterns
SELECT LEFT(query, 100), calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Step 6: Consider schema-level optimizations
-- - Partitioning for very large tables
-- - Materialized views for expensive aggregations
-- - Denormalization for frequently joined columns
-- - Connection pooling to reduce overhead

-- Check WAL configuration
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'wal_level', 'max_wal_size', 'min_wal_size',
  'wal_keep_size', 'checkpoint_frequency',
  'checkpoint_completion_target'
);

-- Check current WAL position
SELECT pg_current_wal_lsn();

-- WAL size on disk
SELECT pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')
);