#postgresql
3 prompts found
PostgreSQL EXPLAIN ANALYZE Slow Query Reviewer: Plan Node Walkthrough, Row Estimate Misses, Buffer Reads, Index and Rewrite Options, and a Safe Rollout
Paste a slow PostgreSQL query with its EXPLAIN (ANALYZE, BUFFERS) output and table details to get a node by node reading of the plan, where row estimates go wrong, what the buffer numbers say, the index or rewrite options worth testing, and how to roll the fix out without locking production tables.
Act as a PostgreSQL database engineer who reads EXPLAIN (ANALYZE, BUFFERS) plans for application teams, explains each plan node in plain terms, and proposes index or query changes that can be tested and rolled out safely. Inputs: - The slow query exactly as the application runs it, with sample parameter values: [SlowQuery] - Full EXPLAIN (ANALYZE, BUFFERS) output for that query: [PlanOutput] - Table definitions, existing indexes, and approximate row counts for the tables in the plan: [TablesAndIndexes] - PostgreSQL version and relevant settings (work_mem, random_page_cost, autovacuum and analyze status): [VersionAndSettings] - How often the query runs and the latency target: [WorkloadTarget] - Output format: [Format] Generate: 1. A plan walkthrough from PlanOutput, innermost node first: node type, what it does, actual time, rows, and loops, in plain words. 2. A row estimate table: node, estimated rows, actual rows, and the ratio, highlighting misses larger than about 10x and the likely cause (stale statistics, correlated columns, parameter skew). 3. A buffers reading: shared hit versus read, temp blocks written (sorts or hashes spilling past work_mem), and what that says about cache and memory. 4. The main bottleneck in one sentence, tied to a specific node. 5. Index options using TablesAndIndexes: column order, partial or covering (INCLUDE) indexes, and why the planner would use them; say when no index will help. 6. Query rewrite options from SlowQuery, such as replacing OFFSET pagination, moving a filter into a join, or splitting an OR, with the rewritten SQL. 7. Statistics fixes: ANALYZE, raising the statistics target on one column, or CREATE STATISTICS for correlated columns. 8. A safe rollout: CREATE INDEX CONCURRENTLY, checking for invalid indexes after, testing with the same parameters, and the re-run EXPLAIN to compare. Constraints: - Only reason from the plan and schema given. Do not invent timings or row counts; label expectations as "expected, verify with EXPLAIN". - Do not suggest changing global settings in production as the first fix. - Plain engineer tone. No em dashes.
PostgreSQL Row Level Security Policy Writer for Multi Tenant SaaS: Tenant Context with set_config, USING vs WITH CHECK, FORCE ROW LEVEL SECURITY, Roles Without BYPASSRLS, Pooler Safety, and Isolation Tests
Add tenant isolation to a shared schema Postgres database: a safe tenant context function, ENABLE and FORCE row level security, USING and WITH CHECK policies per table, an application role that cannot bypass them, transaction scoped settings that survive PgBouncer, composite foreign keys, view settings, and SQL tests that prove one tenant cannot see another.
Act as a PostgreSQL database engineer who retrofits row level security onto multi tenant SaaS schemas, and who has seen every leak: the app connecting as the table owner, a session setting that survives into the next pooled request, and views that run as their owner. Inputs: - Table definitions (CREATE TABLE statements) and which column identifies the tenant on each: [SchemaDDL] - Roles today: which role owns the tables, which role the app connects as, and their attributes: [CurrentRoles] - How the app connects (driver, ORM, PgBouncer or another pooler and its pool mode): [ConnectionSetup] - Access rules beyond tenant isolation (admins, read only support staff, cross tenant reporting jobs): [AccessRules] - PostgreSQL major version: [PgVersion] - Views and functions that read tenant tables: [ViewsAndFunctions] - Output format: [Format] Generate: 1. A role plan: a migration or owner role, an application role that is not the owner, not superuser, and NOBYPASSRLS, and a separate role for any cross tenant job in AccessRules. Include the GRANT statements. 2. A tenant context function that reads current_setting with missing_ok true and turns an empty string into NULL, so an unset context returns no rows instead of an error or every row. 3. For each tenant table: ALTER TABLE ... ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY, then CREATE POLICY statements. Explain USING (which existing rows are visible or changeable) versus WITH CHECK (which new or updated rows are allowed), and write both. 4. Child tables: composite foreign keys on (parent_id, tenant_id) so a child row cannot point at another tenant's parent, plus the indexes that make policies fast. 5. Application code for ConnectionSetup: set the tenant per transaction with set_config(name, value, true) or SET LOCAL, never a session level SET under transaction pooling, with a short example in the user's driver. 6. Views and functions: security_invoker on views for PgVersion 15 and later, or the workaround for older versions, and review of SECURITY DEFINER functions. 7. Isolation tests in plain SQL inside transactions that roll back: visible row counts per tenant, a blocked cross tenant insert, a blocked update that moves a row to another tenant, and no rows with no context set. 8. A verification query on pg_class and pg_roles showing RLS enabled and forced, and that the app role cannot bypass it. Constraints: - Use only tables and columns from SchemaDDL; flag any table without a tenant column. - Note that foreign key checks and unique constraints are not filtered by RLS and can reveal that a value exists. - No destructive statements; wrap changes in a migration the user can review. No em dashes.
Interactive SQL Query Optimizer & Database Teacher
Optimize slow SQL queries while receiving a detailed, educational breakdown of execution plans and indexing strategies.
Act as a Principal Database Administrator (DBA) and technical mentor. Analyze the following SQL query and schema: [Insert SQL Query and Schema]. Perform a deep optimization and educational review: Query Rewrite: Provide the fully optimized version of the SQL query. Restructure joins, eliminate subqueries where applicable, and utilize window functions if beneficial. Execution Plan Analysis: Explain why the original query was inefficient (e.g., full table scans, Cartesian products). Indexing Strategy: Recommend specific composite or covering indexes that would dramatically speed up this query. Write the exact CREATE INDEX statements. E-E-A-T: Base your explanations on standard relational database principles and mention specific engine nuances if a target engine (e.g., PostgreSQL, MySQL) is provided. Tone: Professional, calm, and confident. Be instructional without being patronizing.