postgres-ops — Skillopedia

PostgreSQL Operations Comprehensive PostgreSQL skill covering schema design through production operations. Quick Connection Index Type Selection Quick Index Reference | Index | Best For | Size | Write Cost | |-------|----------|------|------------| | B-tree | Equality, range, sort | Medium | Low | | GIN | Arrays, JSONB, FTS, trigrams | Large | High | | GiST | Geometry, ranges, FTS | Medium | Medium | | BRIN | Correlated data (timestamps) | Tiny | Very low | | Hash | Exact equality only | Medium | Low | Deep dive : Load for composite, partial, expression, and covering index strategies. EXPLAIN…

);\n\n-- Non-negative money (in cents)\nCREATE DOMAIN positive_cents AS integer\nCHECK (VALUE > 0);\n\n-- Non-empty text\nCREATE DOMAIN nonempty_text AS text\nCHECK (VALUE \u003c> '' AND VALUE IS NOT NULL)\nNOT NULL;\n\n-- Use domains in tables\nCREATE TABLE invoices (\n id serial PRIMARY KEY,\n customer_email email_address NOT NULL,\n amount_cents positive_cents NOT NULL,\n description nonempty_text\n);\n\n-- Domain constraints can be altered without modifying tables\nALTER DOMAIN positive_cents ADD CONSTRAINT allow_zero CHECK (VALUE >= 0);\n```\n\n---\n\n## Constraints\n\n### CHECK Constraints\n\n```sql\n-- Column-level\nCREATE TABLE products (\n id serial PRIMARY KEY,\n price numeric CHECK (price >= 0),\n status text CHECK (status IN ('active', 'inactive', 'archived'))\n);\n\n-- Table-level (can reference multiple columns)\nCREATE TABLE discounts (\n id serial PRIMARY KEY,\n discount_pct numeric,\n discount_flat numeric,\n CONSTRAINT one_discount_type CHECK (\n (discount_pct IS NULL) != (discount_flat IS NULL)\n )\n);\n\n-- Named constraint for clearer error messages\nALTER TABLE orders ADD CONSTRAINT chk_positive_total\nCHECK (total_cents > 0);\n```\n\n### UNIQUE Constraints\n\n```sql\n-- Single column\nCREATE TABLE users (\n id serial PRIMARY KEY,\n email text UNIQUE NOT NULL\n);\n\n-- Composite unique\nCREATE TABLE team_members (\n team_id integer,\n user_id integer,\n UNIQUE (team_id, user_id)\n);\n\n-- Partial unique (unique only within a condition)\nCREATE UNIQUE INDEX idx_users_active_email\nON users (email) WHERE deleted_at IS NULL;\n```\n\n### EXCLUDE Constraints\n\nExclusion constraints generalize UNIQUE by allowing any operator, not just equality. Require a GiST or SP-GiST index.\n\n```sql\n-- No two bookings for the same room may overlap\nCREATE EXTENSION btree_gist;\n\nCREATE TABLE bookings (\n id serial PRIMARY KEY,\n room text,\n during tsrange,\n EXCLUDE USING gist (room WITH =, during WITH &&)\n);\n```\n\n### Foreign Key Options\n\n```sql\nCREATE TABLE orders (\n id serial PRIMARY KEY,\n customer_id integer,\n\n -- ON DELETE options:\n -- CASCADE - delete order when customer deleted\n -- SET NULL - set customer_id to NULL\n -- SET DEFAULT - set to column default\n -- RESTRICT - error if customer has orders (default behavior)\n -- NO ACTION - like RESTRICT but deferred-constraint-friendly\n\n CONSTRAINT fk_orders_customer\n FOREIGN KEY (customer_id)\n REFERENCES customers(id)\n ON DELETE RESTRICT\n ON UPDATE CASCADE\n);\n```\n\n### Deferrable Constraints\n\nDeferrable constraints are checked at transaction commit instead of statement time, enabling circular references and bulk data loading.\n\n```sql\n-- Define as deferrable\nALTER TABLE employees ADD CONSTRAINT fk_manager\nFOREIGN KEY (manager_id) REFERENCES employees(id)\nDEFERRABLE INITIALLY DEFERRED;\n\n-- Or defer within a transaction\nBEGIN;\nSET CONSTRAINTS fk_manager DEFERRED;\n-- Insert records that temporarily violate the constraint\nINSERT INTO employees (id, manager_id, name) VALUES (1, 2, 'Alice');\nINSERT INTO employees (id, manager_id, name) VALUES (2, 1, 'Bob');\nCOMMIT; -- constraint checked here, both records now exist\n```\n\n---\n\n## Generated Columns\n\nGenerated columns compute their value automatically from other columns. PG12+ supports STORED (persisted to disk). PG16+ added experimental VIRTUAL (computed on read, not stored).\n\n```sql\n-- STORED generated column\nCREATE TABLE measurements (\n id serial PRIMARY KEY,\n value_celsius numeric NOT NULL,\n -- Automatically computed and stored\n value_fahrenheit numeric GENERATED ALWAYS AS (value_celsius * 9/5 + 32) STORED\n);\n\nINSERT INTO measurements (value_celsius) VALUES (100);\nSELECT value_celsius, value_fahrenheit FROM measurements;\n-- Returns: 100, 212\n\n-- Full name from parts\nCREATE TABLE persons (\n id serial PRIMARY KEY,\n first_name text NOT NULL,\n last_name text NOT NULL,\n full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED\n);\n\n-- Searchable slug from title\nCREATE TABLE posts (\n id serial PRIMARY KEY,\n title text NOT NULL,\n slug text GENERATED ALWAYS AS (\n lower(regexp_replace(trim(title), '[^a-zA-Z0-9]+', '-', 'g'))\n ) STORED\n);\n\nCREATE INDEX idx_posts_slug ON posts(slug);\n```\n\nRestrictions: generation expression cannot reference other generated columns, user-defined functions must be IMMUTABLE, cannot have a DEFAULT, cannot be written to directly.\n\n---\n\n## Table Inheritance and Partitioning\n\n### Traditional Inheritance (pre-PG10)\n\n```sql\nCREATE TABLE events (\n id bigserial PRIMARY KEY,\n occurred_at timestamptz NOT NULL,\n payload jsonb\n);\n\nCREATE TABLE click_events (\n element_id text NOT NULL\n) INHERITS (events);\n\n-- Queries on parent include child rows\nSELECT count(*) FROM events; -- includes click_events rows\nSELECT count(*) FROM ONLY events; -- excludes child tables\n```\n\nTraditional inheritance is largely superseded by declarative partitioning for the partition use case.\n\n### Declarative Partitioning (PG10+)\n\n#### Range Partitioning\n\n```sql\nCREATE TABLE events (\n id bigint NOT NULL,\n occurred_at timestamptz NOT NULL,\n payload jsonb\n) PARTITION BY RANGE (occurred_at);\n\nCREATE TABLE events_2024_q1 PARTITION OF events\nFOR VALUES FROM ('2024-01-01') TO ('2024-04-01');\n\nCREATE TABLE events_2024_q2 PARTITION OF events\nFOR VALUES FROM ('2024-04-01') TO ('2024-07-01');\n\n-- Default partition catches unmatched rows\nCREATE TABLE events_default PARTITION OF events DEFAULT;\n\n-- Index on partition key (propagates to all partitions)\nCREATE INDEX ON events (occurred_at);\n```\n\n#### List Partitioning\n\n```sql\nCREATE TABLE orders (\n id bigint NOT NULL,\n region text NOT NULL,\n total numeric\n) PARTITION BY LIST (region);\n\nCREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US', 'CA');\nCREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('DE', 'FR', 'GB');\nCREATE TABLE orders_other PARTITION OF orders DEFAULT;\n```\n\n#### Hash Partitioning\n\n```sql\nCREATE TABLE user_events (\n user_id bigint NOT NULL,\n event text\n) PARTITION BY HASH (user_id);\n\nCREATE TABLE user_events_0 PARTITION OF user_events FOR VALUES WITH (MODULUS 4, REMAINDER 0);\nCREATE TABLE user_events_1 PARTITION OF user_events FOR VALUES WITH (MODULUS 4, REMAINDER 1);\nCREATE TABLE user_events_2 PARTITION OF user_events FOR VALUES WITH (MODULUS 4, REMAINDER 2);\nCREATE TABLE user_events_3 PARTITION OF user_events FOR VALUES WITH (MODULUS 4, REMAINDER 3);\n```\n\n#### Sub-partitioning\n\n```sql\nCREATE TABLE metrics (\n tenant_id integer NOT NULL,\n recorded_at date NOT NULL,\n value numeric\n) PARTITION BY LIST (tenant_id);\n\nCREATE TABLE metrics_tenant1 PARTITION OF metrics\nFOR VALUES IN (1) PARTITION BY RANGE (recorded_at);\n\nCREATE TABLE metrics_tenant1_2024 PARTITION OF metrics_tenant1\nFOR VALUES FROM ('2024-01-01') TO ('2025-01-01');\n```\n\n---\n\n## Row-Level Security\n\nRLS restricts which rows a user can see or modify. Enabled per table; policies define the filter predicate.\n\n### Enabling RLS\n\n```sql\nALTER TABLE documents ENABLE ROW LEVEL SECURITY;\n\n-- Without this, the table owner bypasses all policies!\nALTER TABLE documents FORCE ROW LEVEL SECURITY;\n```\n\n### Policy Types\n\n```sql\n-- PERMISSIVE (default): policies are OR'd together; user sees rows matching ANY policy\n-- RESTRICTIVE: policies are AND'd; user must match ALL restrictive policies\n\n-- Allow users to see only their own rows\nCREATE POLICY user_isolation ON documents\nAS PERMISSIVE\nFOR ALL\nTO application_role\nUSING (owner_id = current_setting('app.user_id')::integer);\n\n-- Separate read and write policies\nCREATE POLICY documents_select ON documents\nFOR SELECT\nTO application_role\nUSING (owner_id = current_setting('app.user_id')::integer OR is_public = true);\n\nCREATE POLICY documents_insert ON documents\nFOR INSERT\nTO application_role\nWITH CHECK (owner_id = current_setting('app.user_id')::integer);\n\nCREATE POLICY documents_update ON documents\nFOR UPDATE\nTO application_role\nUSING (owner_id = current_setting('app.user_id')::integer)\nWITH CHECK (owner_id = current_setting('app.user_id')::integer);\n\nCREATE POLICY documents_delete ON documents\nFOR DELETE\nTO application_role\nUSING (owner_id = current_setting('app.user_id')::integer);\n```\n\n### Multi-Tenant Pattern\n\n```sql\n-- Set tenant context at session start (via connection pooler or app middleware)\nSET app.tenant_id = '42';\n\n-- RLS policy using session variable\nCREATE POLICY tenant_isolation ON orders\nUSING (tenant_id = current_setting('app.tenant_id')::integer);\n\n-- Superuser bypass: use a dedicated non-superuser role for the app\nCREATE ROLE app_user NOLOGIN;\nGRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;\n\n-- Service role that bypasses RLS (for admin tasks)\nCREATE ROLE service_role BYPASSRLS LOGIN;\n```\n\n### RESTRICTIVE Policies\n\n```sql\n-- Combine PERMISSIVE (what user owns) AND RESTRICTIVE (not deleted)\nCREATE POLICY only_active ON documents\nAS RESTRICTIVE\nFOR ALL\nUSING (deleted_at IS NULL);\n\nCREATE POLICY owner_access ON documents\nAS PERMISSIVE\nFOR ALL\nUSING (owner_id = current_setting('app.user_id')::integer);\n\n-- Result: user sees rows where deleted_at IS NULL AND owner_id matches\n```\n\n### Common Pitfalls\n\n| Pitfall | Fix |\n|---------|-----|\n| Table owner bypasses RLS silently | Add `FORCE ROW LEVEL SECURITY` to the table |\n| No policy defined means no rows visible | Always define at least one PERMISSIVE policy per operation |\n| Superuser always bypasses RLS | Use a non-superuser application role |\n| `current_user` vs session variable | Use `current_setting()` for app-set context; `current_user` reflects DB login role |\n| Performance: predicate not pushed down | Create index on the tenant/owner column used in policy USING clause |\n\n```sql\n-- Verify your policies are working\nSET ROLE app_user;\nSET app.user_id = '1';\nSELECT count(*) FROM documents; -- should only return user 1's documents\nRESET ROLE;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":20613,"content_sha256":"6cb41201f8bebf28fc1ddd072a092555abd60c2d14ae77aa015252dde307b5fa"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"PostgreSQL Operations","type":"text"}]},{"type":"paragraph","content":[{"text":"Comprehensive PostgreSQL skill covering schema design through production operations.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Connection","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Standard connection\npsql \"postgresql://user:pass@localhost:5432/dbname\"\n\n# With SSL\npsql \"postgresql://user:pass@host:5432/dbname?sslmode=require\"\n\n# Environment variables (libpq)\nexport PGHOST=localhost PGPORT=5432 PGDATABASE=mydb PGUSER=myuser PGPASSWORD=secret\npsql\n\n# Connection pooling (pgBouncer default)\npsql \"postgresql://user:pass@localhost:6432/dbname\"","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Check current connection\nSELECT current_database(), current_user, inet_server_addr(), inet_server_port();\n\n-- Active connections\nSELECT count(*) FROM pg_stat_activity WHERE state = 'active';","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Index Type Selection","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"What query pattern are you optimizing?\n│\n├─ Equality (WHERE col = val)\n│ └─ B-tree (default, almost always right)\n│\n├─ Range (WHERE col > val, ORDER BY, BETWEEN)\n│ └─ B-tree\n│\n├─ Array/JSONB containment (@>, ?, ?|, ?&)\n│ └─ GIN\n│\n├─ Full-text search (@@)\n│ └─ GIN with tsvector\n│\n├─ Geometric/range overlap (&&, \u003c->)\n│ └─ GiST\n│\n├─ Pattern matching (LIKE '%text%', similarity)\n│ └─ GIN with pg_trgm (gin_trgm_ops)\n│\n├─ Large table, few distinct values, append-only\n│ └─ BRIN (tiny index, good for timestamps)\n│\n└─ Exact equality only, no range/sort needed\n └─ Hash (rare - B-tree usually better)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Quick Index Reference","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Best For","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Size","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Write Cost","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"B-tree","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Equality, range, sort","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Medium","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Low","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"GIN","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Arrays, JSONB, FTS, trigrams","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Large","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"High","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"GiST","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Geometry, ranges, FTS","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Medium","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Medium","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"BRIN","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Correlated data (timestamps)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Tiny","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Very low","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Hash","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Exact equality only","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Medium","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Low","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"Deep dive","type":"text","marks":[{"type":"strong"}]},{"text":": Load ","type":"text"},{"text":"./references/indexing.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for composite, partial, expression, and covering index strategies.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"EXPLAIN ANALYZE Workflow","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Step 1: Run with ANALYZE and BUFFERS\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;\n\n-- Step 2: Read bottom-up. Find the slowest node.\n-- Step 3: Check estimates vs actuals\n-- actual rows=10000, rows=100 -> bad estimate, run ANALYZE\n-- Step 4: Look for these red flags:","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Red Flag","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Meaning","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fix","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Seq Scan","type":"text","marks":[{"type":"code_inline"}]},{"text":" on large table","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"No usable index","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add index matching WHERE/JOIN","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"actual rows","type":"text","marks":[{"type":"code_inline"}]},{"text":" >> ","type":"text"},{"text":"estimated rows","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Stale statistics","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ANALYZE tablename","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Nested Loop","type":"text","marks":[{"type":"code_inline"}]},{"text":" with high rows","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"O(n*m) join","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Check join conditions, add index","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Sort","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"external merge","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"work_mem too small","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Increase ","type":"text"},{"text":"work_mem","type":"text","marks":[{"type":"code_inline"}]},{"text":" for session","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Buffers: shared read","type":"text","marks":[{"type":"code_inline"}]},{"text":" >> ","type":"text"},{"text":"hit","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Cold cache or table too large","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Check ","type":"text"},{"text":"shared_buffers","type":"text","marks":[{"type":"code_inline"}]},{"text":", add covering index","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Hash Batch","type":"text","marks":[{"type":"code_inline"}]},{"text":" > 1","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Hash join spilling to disk","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Increase ","type":"text"},{"text":"work_mem","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"Deep dive","type":"text","marks":[{"type":"strong"}]},{"text":": Load ","type":"text"},{"text":"./references/query-tuning.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for plan node reference and optimization patterns.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Workload Profiles","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Setting","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"OLTP","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"OLAP","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Notes","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"shared_buffers","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"25% RAM","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"25% RAM","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Same baseline","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"work_mem","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"4-16 MB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"256 MB-1 GB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"OLAP needs big sorts","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"effective_cache_size","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"75% RAM","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"75% RAM","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Planner hint","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"random_page_cost","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1.1 (SSD)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1.1 (SSD)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Lower for SSD","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"max_parallel_workers_per_gather","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"2","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"4-8","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"OLAP benefits more","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"checkpoint_completion_target","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"0.9","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"0.9","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Spread checkpoint I/O","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"wal_buffers","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"64 MB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"64 MB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"-1 for auto","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"maintenance_work_mem","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"512 MB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1-2 GB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"For VACUUM, CREATE INDEX","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"Deep dive","type":"text","marks":[{"type":"strong"}]},{"text":": Load ","type":"text"},{"text":"./references/config-tuning.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for full postgresql.conf walkthrough and extension setup.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Operations","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Backup & Restore","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Logical backup (single database)\npg_dump -Fc dbname > backup.dump\n\n# Restore\npg_restore -d dbname backup.dump\n\n# Parallel backup (faster for large DBs)\npg_dump -Fc -j4 dbname > backup.dump\n\n# Base backup for PITR\npg_basebackup -D /backup/base -Ft -Xs -P","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Vacuum & Maintenance","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Manual vacuum (reclaim space, update stats)\nVACUUM (VERBOSE, ANALYZE) tablename;\n\n-- Full vacuum (rewrites table, exclusive lock)\nVACUUM FULL tablename; -- CAUTION: locks table\n\n-- Reindex without downtime\nREINDEX INDEX CONCURRENTLY idx_name;\n\n-- Update statistics only\nANALYZE tablename;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Monitor Key Metrics","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Slow queries (requires pg_stat_statements)\nSELECT query, calls, mean_exec_time, total_exec_time\nFROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;\n\n-- Table bloat indicator\nSELECT schemaname, relname, n_dead_tup, n_live_tup,\n round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct\nFROM pg_stat_user_tables WHERE n_dead_tup > 1000\nORDER BY n_dead_tup DESC;\n\n-- Lock contention\nSELECT pid, relation::regclass, mode, granted, query\nFROM pg_locks JOIN pg_stat_activity USING (pid)\nWHERE NOT granted;\n\n-- Cache hit ratio (should be > 99%)\nSELECT sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio\nFROM pg_statio_user_tables;","type":"text"}]},{"type":"paragraph","content":[{"text":"Deep dive","type":"text","marks":[{"type":"strong"}]},{"text":": Load ","type":"text"},{"text":"./references/operations.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for WAL archiving, PITR, autovacuum tuning, connection pooling.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Data Types Quick Reference","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use When","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Example","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"JSONB","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Semi-structured data, flexible schema","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'{\"tags\": [\"a\",\"b\"]}'::jsonb","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ARRAY","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fixed-type lists","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ARRAY['a','b','c']","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"tsrange","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Time periods, scheduling","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'[2024-01-01, 2024-12-31)'::tsrange","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"tsvector","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Full-text search","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"to_tsvector('english', body)","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"uuid","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Distributed IDs","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"gen_random_uuid()","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"inet","type":"text","marks":[{"type":"code_inline"}]},{"text":"/","type":"text"},{"text":"cidr","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"IP addresses, networks","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'192.168.1.0/24'::cidr","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"Deep dive","type":"text","marks":[{"type":"strong"}]},{"text":": Load ","type":"text"},{"text":"./references/schema-design.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for normalization, constraints, RLS, generated columns, table inheritance.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Gotchas & Anti-Patterns","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Mistake","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Why It's Bad","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fix","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT *","type":"text","marks":[{"type":"code_inline"}]},{"text":" in production","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Wastes bandwidth, blocks covering index scans","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"List columns explicitly","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Function on indexed column (","type":"text"},{"text":"WHERE UPPER(email) = ...","type":"text","marks":[{"type":"code_inline"}]},{"text":")","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Prevents index use","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Expression index: ","type":"text"},{"text":"CREATE INDEX ... ON (UPPER(email))","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"NOT IN (subquery)","type":"text","marks":[{"type":"code_inline"}]},{"text":" with NULLs","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Returns no rows if subquery has NULL","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"NOT EXISTS","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Missing ","type":"text"},{"text":"ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" after bulk load","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Planner uses stale row estimates","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Run ","type":"text"},{"text":"ANALYZE tablename","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"VACUUM FULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" in production","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Exclusive lock on entire table","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Regular ","type":"text"},{"text":"VACUUM","type":"text","marks":[{"type":"code_inline"}]},{"text":" + ","type":"text"},{"text":"pg_repack","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"LIMIT","type":"text","marks":[{"type":"code_inline"}]},{"text":" without ","type":"text"},{"text":"ORDER BY","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Non-deterministic results","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Always pair with ","type":"text"},{"text":"ORDER BY","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Offset pagination on large tables","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Scans and discards rows","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Keyset pagination: ","type":"text"},{"text":"WHERE id > last_id","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Too many indexes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Slows writes, wastes space","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Audit with ","type":"text"},{"text":"pg_stat_user_indexes","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Single shared connection pool","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Contention across services","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Per-service pools via pgBouncer","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"default_transaction_isolation = serializable","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Excessive serialization failures","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Keep ","type":"text"},{"text":"read committed","type":"text","marks":[{"type":"code_inline"}]},{"text":", use explicit ","type":"text"},{"text":"SERIALIZABLE","type":"text","marks":[{"type":"code_inline"}]},{"text":" where needed","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Row-Level Security (RLS) Quick Start","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Enable RLS on table\nALTER TABLE documents ENABLE ROW LEVEL SECURITY;\n\n-- Policy: users see only their own rows\nCREATE POLICY user_isolation ON documents\n USING (owner_id = current_setting('app.current_user_id')::int);\n\n-- Policy: admins see everything\nCREATE POLICY admin_access ON documents\n USING (current_setting('app.role') = 'admin');\n\n-- Set context per request (from app layer)\nSET app.current_user_id = '42';\nSET app.role = 'user';","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Full-Text Search Quick Start","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Add search column\nALTER TABLE articles ADD COLUMN search_vector tsvector\n GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;\n\n-- Index it\nCREATE INDEX idx_articles_fts ON articles USING gin(search_vector);\n\n-- Search with ranking\nSELECT title, ts_rank(search_vector, query) AS rank\nFROM articles, to_tsquery('english', 'database & optimization') AS query\nWHERE search_vector @@ query\nORDER BY rank DESC;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"LISTEN/NOTIFY","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Publisher\nNOTIFY order_events, '{\"order_id\": 123, \"status\": \"shipped\"}';\n\n-- Subscriber (in psql or app)\nLISTEN order_events;\n\n-- Check for notifications (app code)\n-- Python: conn.poll(); conn.notifies\n-- Node: client.on('notification', callback)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Files","type":"text"}]},{"type":"paragraph","content":[{"text":"Load these for deep-dive topics. Each is self-contained.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Reference","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"When to Load","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./references/schema-design.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Designing tables, choosing types, constraints, RLS policies, JSONB modeling","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./references/indexing.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Choosing index types, composite/partial/expression indexes, index maintenance","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./references/query-tuning.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Reading EXPLAIN plans, pg_stat_statements, optimizing specific query patterns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./references/operations.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Backup/restore, WAL/PITR, vacuum tuning, monitoring, connection pooling","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./references/replication.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Streaming/logical replication, failover, partitioning, FDW","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./references/config-tuning.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"postgresql.conf settings, OLTP/OLAP profiles, extension setup","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"See Also","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"sql-ops","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Vendor-neutral SQL patterns (CTEs, window functions, JOINs)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"sqlite-ops","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLite-specific patterns and operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"python-database-ops","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLAlchemy ORM and async database patterns","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"postgres-ops","author":"@skillopedia","source":{"stars":21,"repo_name":"claude-mods","origin_url":"https://github.com/0xdarkmatter/claude-mods/blob/HEAD/skills/postgres-ops/SKILL.md","repo_owner":"0xdarkmatter","body_sha256":"e89eb3a4851b556b0d8af19f7c50651434aa48733e6410eb1a3a9324945cca83","cluster_key":"607e090160d60c2f71fc839d17833a2ff6c2003a11ba8492987015d9e696153a","clean_bundle":{"format":"clean-skill-bundle-v1","source":"0xdarkmatter/claude-mods/skills/postgres-ops/SKILL.md","attachments":[{"id":"934423db-ef6c-5307-96f2-897ee34214e6","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/934423db-ef6c-5307-96f2-897ee34214e6/attachment","path":"assets/.gitkeep","size":0,"sha256":"e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855","contentType":"text/plain; charset=utf-8"},{"id":"bfd25a0d-2731-5695-8265-7d572b82fa0d","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/bfd25a0d-2731-5695-8265-7d572b82fa0d/attachment.md","path":"references/config-tuning.md","size":21825,"sha256":"05992fb1eb230a56d9203b51472dd6657ccccf71e8bf808d122e5030a5f2f0d5","contentType":"text/markdown; charset=utf-8"},{"id":"9c5b48a5-2efa-5388-9eff-4068c391bbdd","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/9c5b48a5-2efa-5388-9eff-4068c391bbdd/attachment.md","path":"references/indexing.md","size":24322,"sha256":"e9190d299d3ebc0d076fa3b36e64dace5a08067b9870892ac891bf6605dfaca4","contentType":"text/markdown; charset=utf-8"},{"id":"a9fc7627-1beb-5ab7-8db1-58139dc69361","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/a9fc7627-1beb-5ab7-8db1-58139dc69361/attachment.md","path":"references/operations.md","size":25189,"sha256":"d95c97b307b655e3c18787df511e7efab9dc9f88695daa33d7d679dfe223a249","contentType":"text/markdown; charset=utf-8"},{"id":"ec0d76f3-940f-57f8-ad0f-07b80907f067","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ec0d76f3-940f-57f8-ad0f-07b80907f067/attachment.md","path":"references/query-tuning.md","size":22250,"sha256":"1a617858c9db703239f4e2bce97172c75fbe9a5900ef18ab99d29600ae148481","contentType":"text/markdown; charset=utf-8"},{"id":"7e99abd9-c45e-58c5-9af9-0ce635d24b88","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/7e99abd9-c45e-58c5-9af9-0ce635d24b88/attachment.md","path":"references/replication.md","size":17319,"sha256":"0f23cd755b1450aaf28204b5a6836cfa7bd12a92368a841d950b4acca8fdac41","contentType":"text/markdown; charset=utf-8"},{"id":"0d265def-31f4-5809-8ef2-7473b14820e9","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/0d265def-31f4-5809-8ef2-7473b14820e9/attachment.md","path":"references/schema-design.md","size":20613,"sha256":"6cb41201f8bebf28fc1ddd072a092555abd60c2d14ae77aa015252dde307b5fa","contentType":"text/markdown; charset=utf-8"},{"id":"ad660d92-7aaa-5006-9b04-3552e03a6171","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ad660d92-7aaa-5006-9b04-3552e03a6171/attachment","path":"scripts/.gitkeep","size":0,"sha256":"e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855","contentType":"text/plain; charset=utf-8"}],"bundle_sha256":"7ab5e43d09cdfbbde5680a34140ba14221451465a74737bf4ba59431c4d7ffae","attachment_count":8,"text_attachments":6,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":2,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/postgres-ops/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"security","category_label":"Security"},"exact_dupes_collapsed_into_this":0},"license":"MIT","version":"v1","category":"security","metadata":{"author":"claude-mods","related-skills":"sql-ops, sqlite-ops, python-database-ops"},"import_tag":"clean-skills-v1","description":"PostgreSQL operations, optimization, and administration. Use for: schema design, index selection, query tuning with EXPLAIN ANALYZE, postgresql.conf configuration, backup and restore (pg_dump, pg_basebackup, WAL, PITR), vacuum and autovacuum tuning, connection pooling (pgBouncer, pgPool), replication (streaming, logical), partitioning, monitoring (pg_stat_statements, pg_stat_activity), JSONB operations, full-text search (tsvector, tsquery), row-level security (RLS), extensions (PostGIS, pg_trgm, timescaledb), GiST/GIN/BRIN indexes, materialized views, foreign data wrappers, LISTEN/NOTIFY.","allowed-tools":"Read Write Bash"}},"renderedAt":1782979294069}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.