UPDATE orders SET status = 'archived' WHERE created_at < '2022-01-01';

  CREATE INDEX idx_orders_created_at ON orders(created_at);
  

  SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
  

  VACUUM ANALYZE orders;
  

Seq Scan on users  (cost=0.00..44231.00 rows=1000000 width=64)
  Filter: (status = 'active')

Index Scan using idx_users_status on users
  Index Cond: (status = 'active')

SELECT 
    o.OrderID,
    p.ProductName,
    p.Amount
FROM Orders o
CROSS APPLY (
    SELECT TOP 1 *
    FROM Products p
    WHERE p.OrderID = o.OrderID
    ORDER BY p.Amount DESC
) p;

SELECT 
    o.order_id,
    p.product_name,
    p.amount
FROM orders o,
LATERAL (
    SELECT *
    FROM products p
    WHERE p.order_id = o.order_id
    ORDER BY p.amount DESC
    LIMIT 1
) p;

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

SELECT 
  calls, 
  total_time, 
  mean_time, 
  query 
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

ANALYZE my_big_table;

VACUUM ANALYZE my_big_table;

pg_basebackup -h replica.host -U repl_user -D /backup/pg -F tar -z -P

SELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

wait_event_type: Lock
wait_event: relation

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
  AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
  AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
  AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
  AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
  AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
  AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
  AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;