SQL

https://www.postgresql.org/docs/current/sql-commands.html
https://www.postgresql.org/docs/current/queries.html
https://www.postgresql.org/docs/current/ddl-schemas.html
https://www.postgresql.org/docs/current/catalog-pg-namespace.html
https://www.postgresql.org/docs/current/infoschema-schemata.html
https://www.postgresql.org/docs/current/functions-info.html
https://www.postgresql.org/docs/current/monitoring-stats.html

scope:
    this page focuses on common SQL used by developers / DBA / SRE
    psql meta commands such as \l / \dt are useful, but they are not SQL
    examples use database orders and schema app

safe habit:
    always qualify production objects with schema name:
        app.orders

    use transaction for multi-step DML:
        BEGIN;
        ...
        COMMIT;

    preview before destructive SQL:
        SELECT ... WHERE ...
        then UPDATE / DELETE with the same WHERE

    avoid in production without review:
        DROP ... CASCADE
        TRUNCATE
        UPDATE without WHERE
        DELETE without WHERE
        long transaction

common naming:
    database:
        orders

    schema / namespace:
        app

    table:
        app.orders
        app.order_items

    roles:
        dba_order
        order_migrator
        order_app
        order_readonly

SELECT current_database();

SELECT
  datname,
  pg_get_userbyid(datdba) AS owner,
  pg_encoding_to_char(encoding) AS encoding,
  datcollate,
  datctype,
  datallowconn,
  datconnlimit
FROM pg_database
ORDER BY datname;

CREATE DATABASE orders
  OWNER dba_order
  TEMPLATE template0
  ENCODING 'UTF8'
  LC_COLLATE 'C'
  LC_CTYPE 'C'
  TABLESPACE pg_default
  CONNECTION LIMIT 200;

SELECT 1
FROM pg_database
WHERE datname = 'orders';

CREATE DATABASE orders OWNER dba_order;

ALTER DATABASE orders OWNER TO dba_order;

ALTER DATABASE orders
  CONNECTION LIMIT 300;

ALTER DATABASE orders
  SET search_path TO app, public;

ALTER DATABASE orders
  RESET search_path;

ALTER DATABASE orders RENAME TO orders_old;

DROP DATABASE orders;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'orders'
  AND pid <> pg_backend_pid();

DROP DATABASE orders;

DROP DATABASE orders WITH (FORCE);

warning:
    DROP DATABASE removes the whole database
    run from another database
    cannot be rolled back after commit

SELECT current_schema();

SHOW search_path;

SELECT current_schemas(true);

SELECT
  schema_name,
  schema_owner
FROM information_schema.schemata
ORDER BY schema_name;

SELECT
  n.oid,
  n.nspname AS schema_name,
  r.rolname AS owner
FROM pg_namespace n
JOIN pg_roles r ON r.oid = n.nspowner
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
ORDER BY n.nspname;

CREATE SCHEMA app AUTHORIZATION order_migrator;

CREATE SCHEMA app AUTHORIZATION order_migrator
  CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now()
  );

CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION order_migrator;

ALTER SCHEMA app OWNER TO order_migrator;

ALTER SCHEMA app RENAME TO app_old;

DROP SCHEMA app;

DROP SCHEMA IF EXISTS app;

DROP SCHEMA IF EXISTS app CASCADE;

production rule:
    use RESTRICT/default first
    only use CASCADE after listing dependent objects

SELECT
  table_schema,
  table_name,
  table_type
FROM information_schema.tables
WHERE table_schema = 'app'
ORDER BY table_name;

SELECT
  schemaname,
  tablename,
  tableowner
FROM pg_tables
WHERE schemaname = 'app'
ORDER BY tablename;

CREATE TABLE app.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no text NOT NULL UNIQUE,
  customer_id bigint NOT NULL,
  status text NOT NULL DEFAULT 'created',
  amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS app.order_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES app.orders(id) ON DELETE CASCADE,
  sku text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE app.big_orders AS
SELECT *
FROM app.orders
WHERE amount >= 1000;

CREATE TEMP TABLE tmp_order_ids (
  order_id bigint PRIMARY KEY
) ON COMMIT DROP;

CREATE UNLOGGED TABLE app.import_orders (
  order_no text,
  payload jsonb,
  imported_at timestamptz DEFAULT now()
);

unlogged table:
    faster writes
    not WAL-logged
    not crash-safe like regular table
    usually for staging / import / cache

COMMENT ON TABLE app.orders IS 'Business orders';
COMMENT ON COLUMN app.orders.order_no IS 'External order number';

ALTER TABLE app.orders
  ADD COLUMN paid_at timestamptz;

ALTER TABLE app.orders
  ADD COLUMN source text NOT NULL DEFAULT 'web';

ALTER TABLE app.orders
  RENAME COLUMN source TO channel;

ALTER TABLE app.orders
  ALTER COLUMN amount TYPE numeric(14, 2);

ALTER TABLE app.orders
  ALTER COLUMN order_no TYPE text USING order_no::text;

ALTER TABLE app.orders
  ALTER COLUMN status SET DEFAULT 'created';

ALTER TABLE app.orders
  ALTER COLUMN status DROP DEFAULT;

ALTER TABLE app.orders
  ALTER COLUMN customer_id SET NOT NULL;

ALTER TABLE app.orders
  ALTER COLUMN paid_at DROP NOT NULL;

ALTER TABLE app.orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('created', 'paid', 'cancelled'));

ALTER TABLE app.order_items
  ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id)
  REFERENCES app.orders(id)
  ON DELETE CASCADE;

ALTER TABLE app.orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('created', 'paid', 'cancelled')) NOT VALID;

ALTER TABLE app.orders
  VALIDATE CONSTRAINT orders_status_check;

ALTER TABLE app.orders
  DROP CONSTRAINT IF EXISTS orders_status_check;

ALTER TABLE app.orders
  RENAME TO orders_old;

ALTER TABLE app.orders
  SET SCHEMA archive;

TRUNCATE TABLE app.order_items;

TRUNCATE TABLE app.order_items RESTART IDENTITY;

TRUNCATE TABLE app.orders CASCADE;

DROP TABLE app.order_items;

DROP TABLE IF EXISTS app.order_items;

DROP TABLE IF EXISTS app.orders CASCADE;

drop vs truncate vs delete:
    DELETE:
        removes selected rows
        can use WHERE
        fires row-level delete triggers

    TRUNCATE:
        removes all rows quickly
        no WHERE
        stronger lock

    DROP:
        removes table definition and data

CREATE INDEX orders_customer_id_idx
ON app.orders (customer_id);

CREATE UNIQUE INDEX orders_order_no_uidx
ON app.orders (order_no);

CREATE INDEX orders_customer_status_created_idx
ON app.orders (customer_id, status, created_at DESC);

index order:
    equality columns first
    then range/sort columns
    match common query patterns

CREATE INDEX orders_unpaid_idx
ON app.orders (created_at)
WHERE status = 'created';

CREATE INDEX orders_lower_order_no_idx
ON app.orders (lower(order_no));

CREATE INDEX orders_metadata_gin_idx
ON app.orders USING gin (metadata);

CREATE INDEX CONCURRENTLY orders_created_at_idx
ON app.orders (created_at);

note:
    CREATE INDEX CONCURRENTLY reduces write blocking
    it cannot run inside a transaction block

DROP INDEX app.orders_created_at_idx;

DROP INDEX CONCURRENTLY IF EXISTS app.orders_created_at_idx;

REINDEX INDEX app.orders_customer_id_idx;

REINDEX TABLE app.orders;

REINDEX DATABASE orders;

SELECT
  id,
  order_no,
  status,
  amount,
  created_at
FROM app.orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

SELECT *
FROM app.orders
WHERE customer_id = 10001
  AND status IN ('created', 'paid')
  AND amount >= 100
  AND created_at >= now() - interval '7 days';

SELECT *
FROM app.orders
WHERE paid_at IS NULL;

SELECT *
FROM app.orders
WHERE paid_at IS NOT NULL;

SELECT *
FROM app.orders
WHERE order_no LIKE 'ORD-%';

SELECT *
FROM app.orders
WHERE order_no ILIKE 'ord-%';

SELECT
  o.id,
  o.order_no,
  o.status,
  i.sku,
  i.quantity
FROM app.orders o
JOIN app.order_items i ON i.order_id = o.id
WHERE o.id = 1001;

SELECT
  o.id,
  o.order_no,
  count(i.id) AS item_count
FROM app.orders o
LEFT JOIN app.order_items i ON i.order_id = o.id
GROUP BY o.id, o.order_no;

SELECT
  status,
  count(*) AS order_count,
  sum(amount) AS total_amount,
  avg(amount) AS avg_amount
FROM app.orders
GROUP BY status
HAVING count(*) > 10
ORDER BY total_amount DESC;

SELECT DISTINCT status
FROM app.orders
ORDER BY status;

SELECT DISTINCT ON (customer_id)
  customer_id,
  id,
  order_no,
  created_at
FROM app.orders
ORDER BY customer_id, created_at DESC;

WITH recent_orders AS (
  SELECT *
  FROM app.orders
  WHERE created_at >= now() - interval '30 days'
)
SELECT status, count(*)
FROM recent_orders
GROUP BY status;

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM app.categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM app.categories c
  JOIN category_tree t ON t.id = c.parent_id
)
SELECT *
FROM category_tree
ORDER BY depth, id;

SELECT
  customer_id,
  order_no,
  amount,
  created_at,
  row_number() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
  ) AS rn
FROM app.orders;

SELECT *
FROM app.orders
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100;

SELECT *
FROM app.orders
WHERE (created_at, id) < (timestamp '2026-01-01 00:00:00+00', 1000)
ORDER BY created_at DESC, id DESC
LIMIT 50;

production preference:
    use keyset pagination for deep pages
    offset pagination gets slower as offset grows

SELECT *
FROM app.orders
WHERE metadata @> '{"channel": "web"}'::jsonb;

SELECT
  id,
  metadata->>'channel' AS channel,
  metadata->'payment'->>'method' AS payment_method
FROM app.orders;

SELECT *
FROM app.products
WHERE tags @> ARRAY['featured'];

SELECT *
FROM app.orders
WHERE created_at >= date_trunc('day', now())
  AND created_at < date_trunc('day', now()) + interval '1 day';

SELECT *
FROM app.orders o
WHERE EXISTS (
  SELECT 1
  FROM app.order_items i
  WHERE i.order_id = o.id
);

INSERT INTO app.orders (
  order_no,
  customer_id,
  status,
  amount,
  metadata
) VALUES (
  'ORD-1001',
  10001,
  'created',
  99.90,
  '{"channel": "web"}'::jsonb
);

INSERT INTO app.orders (order_no, customer_id, amount)
VALUES ('ORD-1002', 10001, 199.00)
RETURNING id, order_no, created_at;

INSERT INTO app.order_items (order_id, sku, quantity, unit_price)
VALUES
  (1001, 'SKU-001', 2, 10.00),
  (1001, 'SKU-002', 1, 79.90);

UPDATE app.orders
SET
  status = 'paid',
  paid_at = now(),
  updated_at = now()
WHERE id = 1001
  AND status = 'created'
RETURNING id, status, paid_at;

UPDATE app.orders o
SET
  amount = s.amount,
  updated_at = now()
FROM app.order_amount_staging s
WHERE s.order_no = o.order_no;

DELETE FROM app.orders
WHERE id = 1001
RETURNING id, order_no;

DELETE FROM app.order_items i
USING app.orders o
WHERE o.id = i.order_id
  AND o.status = 'cancelled';

INSERT INTO app.orders (order_no, customer_id, status, amount)
VALUES ('ORD-1001', 10001, 'created', 99.90)
ON CONFLICT (order_no)
DO UPDATE SET
  status = EXCLUDED.status,
  amount = EXCLUDED.amount,
  updated_at = now()
RETURNING id, order_no, status;

INSERT INTO app.orders (order_no, customer_id, amount)
VALUES ('ORD-1001', 10001, 99.90)
ON CONFLICT (order_no) DO NOTHING;

MERGE INTO app.orders o
USING app.order_imports s
ON o.order_no = s.order_no
WHEN MATCHED THEN
  UPDATE SET amount = s.amount, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (order_no, customer_id, amount)
  VALUES (s.order_no, s.customer_id, s.amount);

COPY app.orders (order_no, customer_id, status, amount)
FROM '/var/lib/postgresql/import/orders.csv'
WITH (FORMAT csv, HEADER true);

\copy app.orders (order_no, customer_id, status, amount) FROM './orders.csv' WITH (FORMAT csv, HEADER true)

BEGIN;

UPDATE app.orders
SET status = 'paid', updated_at = now()
WHERE id = 1001
  AND status = 'created';

INSERT INTO app.order_events (order_id, event_type, created_at)
VALUES (1001, 'paid', now());

COMMIT;

BEGIN;

UPDATE app.orders
SET status = 'cancelled'
WHERE id = 1001;

ROLLBACK;

BEGIN;

SAVEPOINT before_item_update;

UPDATE app.order_items
SET quantity = 2
WHERE id = 5001;

ROLLBACK TO SAVEPOINT before_item_update;

COMMIT;

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT *
FROM app.orders
WHERE customer_id = 10001;

COMMIT;

SELECT *
FROM app.orders
WHERE id = 1001
FOR UPDATE;

WITH picked AS (
  SELECT id
  FROM app.jobs
  WHERE status = 'ready'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE app.jobs j
SET status = 'running', updated_at = now()
FROM picked
WHERE j.id = picked.id
RETURNING j.*;

SELECT pg_try_advisory_lock(1001);

SELECT pg_advisory_unlock(1001);

CREATE ROLE dba_order NOLOGIN;

CREATE ROLE order_migrator LOGIN PASSWORD 'change-me';

CREATE ROLE order_app LOGIN PASSWORD 'change-me';

CREATE ROLE order_readonly LOGIN PASSWORD 'change-me';

ALTER ROLE order_app PASSWORD 'new-password';

ALTER ROLE order_app SET search_path TO app, public;

ALTER ROLE order_app CONNECTION LIMIT 100;

GRANT CONNECT ON DATABASE orders TO order_app;
GRANT CONNECT ON DATABASE orders TO order_readonly;

GRANT USAGE ON SCHEMA app TO order_app;
GRANT USAGE ON SCHEMA app TO order_readonly;

GRANT CREATE ON SCHEMA app TO order_migrator;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO order_app;

GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO order_readonly;

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA app
TO order_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO order_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT SELECT ON TABLES TO order_readonly;

ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO order_app;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON DATABASE orders FROM PUBLIC;

REVOKE SELECT ON app.orders FROM order_readonly;

SELECT has_database_privilege('order_app', 'orders', 'CONNECT');

SELECT has_schema_privilege('order_app', 'app', 'USAGE');

SELECT has_table_privilege('order_app', 'app.orders', 'SELECT');

CREATE OR REPLACE VIEW app.paid_orders AS
SELECT
  id,
  order_no,
  customer_id,
  amount,
  paid_at
FROM app.orders
WHERE status = 'paid';

DROP VIEW IF EXISTS app.paid_orders;

CREATE MATERIALIZED VIEW app.daily_order_summary AS
SELECT
  date_trunc('day', created_at)::date AS order_day,
  status,
  count(*) AS order_count,
  sum(amount) AS total_amount
FROM app.orders
GROUP BY 1, 2;

REFRESH MATERIALIZED VIEW app.daily_order_summary;

CREATE UNIQUE INDEX daily_order_summary_uidx
ON app.daily_order_summary (order_day, status);

REFRESH MATERIALIZED VIEW CONCURRENTLY app.daily_order_summary;

CREATE SEQUENCE app.order_no_seq
  START WITH 1000
  INCREMENT BY 1;

SELECT nextval('app.order_no_seq');

SELECT currval('app.order_no_seq');

SELECT setval('app.order_no_seq', 5000, true);

ALTER TABLE app.orders
  ALTER COLUMN id RESTART WITH 100000;

CREATE TABLE app.order_events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  order_id bigint NOT NULL,
  event_type text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE app.order_events_2026_01
PARTITION OF app.order_events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

ALTER TABLE app.order_events
DETACH PARTITION app.order_events_2026_01;

DROP TABLE app.order_events_2026_01;

CREATE OR REPLACE FUNCTION app.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_set_updated_at
BEFORE UPDATE ON app.orders
FOR EACH ROW
EXECUTE FUNCTION app.set_updated_at();

DROP TRIGGER IF EXISTS orders_set_updated_at ON app.orders;

DROP FUNCTION IF EXISTS app.set_updated_at();

EXPLAIN
SELECT *
FROM app.orders
WHERE customer_id = 10001
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM app.orders
WHERE customer_id = 10001
ORDER BY created_at DESC
LIMIT 50;

warning:
    EXPLAIN ANALYZE executes the query
    do not use it casually for destructive statements

ANALYZE app.orders;

VACUUM app.orders;

VACUUM (ANALYZE) app.orders;

VACUUM (FULL, ANALYZE) app.orders;

VACUUM FULL:
    rewrites table
    takes strong lock
    needs extra disk space
    use only with maintenance window

SELECT
  pg_size_pretty(pg_total_relation_size('app.orders')) AS total_size,
  pg_size_pretty(pg_relation_size('app.orders')) AS table_size,
  pg_size_pretty(pg_indexes_size('app.orders')) AS indexes_size;

SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY n_dead_tup DESC;

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan ASC, indexrelname;

SELECT
  current_database(),
  current_user,
  session_user,
  inet_client_addr(),
  inet_server_addr(),
  pg_backend_pid();

SELECT
  pid,
  usename,
  datname,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  now() - query_start AS query_age,
  left(query, 200) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;

SELECT
  pid,
  usename,
  state,
  now() - query_start AS query_age,
  left(query, 500) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND query_start < now() - interval '5 minutes'
ORDER BY query_start;

SELECT
  pid,
  usename,
  now() - xact_start AS transaction_age,
  state,
  left(query, 300) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

SELECT
  l.locktype,
  l.mode,
  l.granted,
  l.pid,
  a.usename,
  a.state,
  left(a.query, 200) AS query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON a.pid = l.pid
ORDER BY l.granted, l.pid;

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

SELECT pg_cancel_backend(12345);

SELECT pg_terminate_backend(12345);

rule:
    pg_cancel_backend asks the running query to stop
    pg_terminate_backend closes the backend connection
    prefer cancel first unless incident requires termination

SELECT
  table_schema,
  table_name,
  column_name,
  ordinal_position,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_schema = 'app'
ORDER BY table_name, ordinal_position;

SELECT
  tc.table_schema,
  tc.table_name,
  kcu.column_name,
  tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
 AND kcu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_schema = 'app'
ORDER BY tc.table_name, kcu.ordinal_position;

SELECT
  tc.table_schema,
  tc.table_name,
  kcu.column_name,
  ccu.table_schema AS foreign_table_schema,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name,
  tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
 AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'app'
ORDER BY tc.table_name, tc.constraint_name;

SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'app'
ORDER BY tablename, indexname;

SELECT
  schemaname,
  tablename,
  tableowner
FROM pg_tables
WHERE schemaname = 'app'
ORDER BY tablename;

SELECT
  extname,
  extversion
FROM pg_extension
ORDER BY extname;

SHOW server_version;
SHOW data_directory;
SHOW config_file;
SHOW hba_file;
SHOW max_connections;
SHOW shared_buffers;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT gen_random_uuid();

DROP EXTENSION IF EXISTS pgcrypto;

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_customer_policy
ON app.orders
FOR SELECT
USING (customer_id = current_setting('app.customer_id')::bigint);

SET app.customer_id = '10001';

SELECT *
FROM app.orders;

ALTER TABLE app.orders DISABLE ROW LEVEL SECURITY;

before DDL:
    check lock impact
    check table size
    check migration rollback plan
    avoid transaction block for CREATE INDEX CONCURRENTLY

before UPDATE / DELETE:
    run SELECT with same WHERE
    check estimated row count
    use RETURNING for audit when useful
    run in transaction for manual operation

before DROP / TRUNCATE:
    confirm environment and database
    list dependent objects
    confirm backup / restore point
    avoid CASCADE unless reviewed

before production query tuning:
    capture EXPLAIN (ANALYZE, BUFFERS)
    check index usage
    check row estimates
    check dead tuples and autovacuum state