TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 02

Basic Queries: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

SETUP & POSTGRESQL ARCHITECTURE - CONCEPTUAL

  1. Q1Your friend asks "is PostgreSQL the same as pgAdmin?" - explain the difference.
  2. Q2Explain what psql is in one sentence.
  3. Q3In the client-server model, what runs where: PostgreSQL, pgAdmin, your queries?
  4. Q4What is the default port PostgreSQL listens on, and why does it matter?
  5. Q5Why do you need both PostgreSQL AND pgAdmin? Can't you use just one?
  6. Q6A teammate types psql in terminal and gets "command not found". Name 2 likely causes.
  7. Q7Explain why a fresh PostgreSQL installation usually has a 'postgres' superuser.
  8. Q8What is the 'public' schema in PostgreSQL - is it the same as RetailMart's schemas?
  9. Q9Name 3 SQL clients besides pgAdmin you could use (any platform).
  10. Q10Why do we use VS Code AND pgAdmin in this course - what does each do better?
  11. Q11Your study buddy asks "what does \dt do in psql?" - answer.
  12. Q12Explain what \l lists in psql.
  13. Q13Explain what \dn lists in psql.
  14. Q14What does \c some_db do in psql?
  15. Q15Your friend says "Git is for code, not databases" - counter-argue why we use Git in this course.
  16. Q16Explain what setup_retailmart.sql does in one sentence.
  17. Q17Why does the setup script use \copy instead of COPY for loading CSVs?
  18. Q18What command in psql exits the session cleanly?
  19. Q19A junior asks "do I need to run psql with sudo?" - answer with the trade-off.
  20. Q20Why is PostgreSQL preferred over MySQL for this analytics course? Name two reasons.
  21. Q21In an interview, you're asked "what's the difference between TCP and Unix-socket connections in PostgreSQL?" - short answer.
  22. Q22What is a "connection string" - give the typical Postgres format.
  23. Q23Your teammate forgot the postgres password. Name one safe way to reset it on macOS.
  24. Q24Why does this course use a SEPARATE practice DB (practice) AND the RetailMart V3 DB?
  25. Q25After installation, what THREE smoke-test queries should you always run to verify everything works?

SETUP VERIFICATION QUERIES (25) Topics: version, identity, basic system info

  1. Q26Return the PostgreSQL server version (single column).
  2. Q27Return the name of the database you're currently connected to.
  3. Q28Return your current login user.
  4. Q29Return the current date (server-side).
  5. Q30Return the current timestamp (server-side, with timezone).
  6. Q31Return the IP address of the server you're connected to (use inet_server_addr()).
  7. Q32Return the current schema (search_path top entry).
  8. Q33Return all schemas the user has access to from information_schema.schemata.
  9. Q34Count how many schemas exist in the database.
  10. Q35List all table names in the 'sales' schema using information_schema.tables.
  11. Q36List all table names in the 'customers' schema.
  12. Q37List all table names in the 'core' schema.
  13. Q38Count how many tables exist in the 'sales' schema.
  14. Q39Count how many tables exist across all schemas in this database.
  15. Q40Return the size of the database in human-readable form (use pg_size_pretty + pg_database_size).
  16. Q41List columns of customers.customers (use information_schema.columns).
  17. Q42List columns of sales.orders.
  18. Q43List columns of products.products.
  19. Q44Return the data type of every column in stores.employees from information_schema.
  20. Q45Return the count of distinct schemas owned by the postgres user.
  21. Q46Return a list of all table names that start with 'order' across any schema.
  22. Q47Return a list of all table names that contain 'employee' across any schema.
  23. Q48Return all schema names alphabetically sorted.
  24. Q49Show the search_path setting for the current session.
  25. Q50Return the server's timezone setting.

SCHEMA TOUR - ROW COUNTS & PEEKS (25) Topics: SELECT count(*), SELECT * LIMIT N for every major table

  1. Q51Count rows in customers.customers - how many customers does RetailMart have?
  2. Q52Count rows in sales.orders - total order volume.
  3. Q53Count rows in sales.order_items - line items across all orders.
  4. Q54Count rows in products.products - product catalog size.
  5. Q55Count rows in stores.stores - number of retail outlets.
  6. Q56Count rows in stores.employees - total headcount.
  7. Q57Count rows in core.dim_brand - number of brands.
  8. Q58Count rows in core.dim_category - number of categories.
  9. Q59Count rows in core.dim_region - number of regions.
  10. Q60Count rows in core.dim_department - number of departments.
  11. Q61Count rows in support.tickets - total support cases ever.
  12. Q62Count rows in marketing.campaigns - campaigns run.
  13. Q63Count rows in hr.attendance - attendance records.
  14. Q64Count rows in finance.expenses - expense entries.
  15. Q65Count rows in payroll.pay_slips - pay slips issued.
  16. Q66Count rows in loyalty.members - loyalty program enrollment.
  17. Q67Count rows in audit.application_logs - system logs captured.
  18. Q68Count rows in audit.api_requests - API hits logged.
  19. Q69Count rows in web_events.page_views - total page-view events.
  20. Q70Count rows in call_center.calls - calls handled.
  21. Q71Count rows in manufacture.work_orders - production work orders.
  22. Q72Count rows in supply_chain.warehouses - number of warehouses.
  23. Q73Count rows in sales.returns - return records.
  24. Q74Count rows in sales.shipments - shipment records.
  25. Q75Count rows in customers.reviews - product reviews submitted.

FIRST PEEK AT THE DATA - SELECT * LIMIT (25) Topics: SELECT * LIMIT 5 to feel each table's shape

  1. Q76The DBA wants you to "peek" at customers.customers - show the first 5 rows of every column.
  2. Q77Peek at the first 5 rows of sales.orders.
  3. Q78Peek at the first 5 rows of products.products.
  4. Q79Peek at the first 5 rows of stores.employees.
  5. Q80Peek at the first 5 rows of stores.stores.
  6. Q81Peek at the first 5 rows of core.dim_brand.
  7. Q82Peek at the first 5 rows of core.dim_category.
  8. Q83Peek at the first 5 rows of core.dim_region.
  9. Q84Peek at the first 5 rows of support.tickets.
  10. Q85Peek at the first 5 rows of marketing.campaigns.
  11. Q86Peek at the first 5 rows of hr.attendance.
  12. Q87Peek at the first 5 rows of finance.expenses.
  13. Q88Peek at the first 5 rows of payroll.pay_slips.
  14. Q89Peek at the first 5 rows of loyalty.members.
  15. Q90Peek at the first 5 rows of loyalty.tiers.
  16. Q91Peek at the first 5 rows of audit.application_logs.
  17. Q92Peek at the first 5 rows of audit.api_requests.
  18. Q93Peek at the first 5 rows of web_events.page_views.
  19. Q94Peek at the first 5 rows of call_center.calls.
  20. Q95Peek at the first 5 rows of manufacture.work_orders.
  21. Q96Peek at the first 5 rows of supply_chain.warehouses.
  22. Q97Peek at the first 5 rows of supply_chain.shipments.
  23. Q98Peek at the first 5 rows of customers.addresses.
  24. Q99Peek at the first 5 rows of customers.reviews.
  25. Q100Peek at the first 5 rows of customers.wallets.

Combined ideas, multi-step thinking

SETUP & PSQL POWER-USE

  1. Q1In psql, what does \dt+ show that \dt does not?
  2. Q2How would you list ALL tables across ALL schemas in psql in one command?
  3. Q3In psql, how do you set your output format to expanded (\x) - and when is it useful?
  4. Q4How do you turn ON timing in psql to see query execution time?
  5. Q5How do you redirect psql output to a file (one command)?
  6. Q6Your pgAdmin server connection fails with "could not connect" - list 3 things you'd check.
  7. Q7In psql, how do you load a SQL file from disk?
  8. Q8What does \df show?
  9. Q9How do you describe a specific table's columns in psql?
  10. Q10How do you exit psql cleanly?
  11. Q11In VS Code with SQLTools, you can't see RetailMart V3 schemas. List 3 likely causes.
  12. Q12Your psql prompt shows 'practice=#' - what does the '#' mean?
  13. Q13How do you change passwords for the postgres user via psql?
  14. Q14What does the .psql_history file contain and where is it?
  15. Q15How do you run a single SQL command from the shell WITHOUT entering an interactive psql session?
  16. Q16Your study buddy has PostgreSQL but no psql command. What likely went wrong with install?
  17. Q17How do you check the disk size of EACH database on the server?
  18. Q18How do you find which user owns the customers.customers table?
  19. Q19Inside psql, how would you view the actual DDL of the sales.orders table?
  20. Q20How do you view PostgreSQL's configured shared_buffers value via psql?

SCHEMA / CATALOG EXPLORATION

  1. Q21Count how many tables exist per schema (top 10) using information_schema.tables.
  2. Q22List all schemas WITH count of tables in each, sorted descending.
  3. Q23List all columns of type 'numeric' across the entire database.
  4. Q24List all columns of type 'date' across all schemas.
  5. Q25List all columns of type 'jsonb' across all schemas.
  6. Q26Find every table that has a column literally named 'cust_id'.
  7. Q27Find every table that has a column literally named 'customer_id'.
  8. Q28Find every table whose name contains 'log'.
  9. Q29Find every table whose name contains 'order'.
  10. Q30Show columns of customers.customers with their data type AND whether nullable.
  11. Q31Show columns of sales.orders with their data type AND character_maximum_length where applicable.
  12. Q32List all PRIMARY KEY constraints across the database from information_schema.table_constraints.
  13. Q33List all FOREIGN KEY constraints.
  14. Q34List all CHECK constraints.
  15. Q35List all UNIQUE constraints.
  16. Q36Show tables in 'sales' schema and the number of columns in each.
  17. Q37Find every table that has a column named 'email'.
  18. Q38Find every column whose name contains 'date' across all schemas.
  19. Q39Find columns whose name contains 'amount'.
  20. Q40Show all sequences in the database (information_schema.sequences).
  21. Q41Show all VIEWS in the database.
  22. Q42Show the data type of customers.customers.tier specifically.
  23. Q43Show the longest VARCHAR column in the database (highest character_maximum_length).
  24. Q44List tables in the 'core' schema sorted by table_name.
  25. Q45List tables in the 'audit' schema.
  26. Q46Show server version + database name + current user in one row.
  27. Q47Show the database size for practice + retailmart_v3 (if both exist) in human-readable form.
  28. Q48Show how many connections are currently open to this DB (pg_stat_activity).
  29. Q49Show the OS user the database server is running as (use current_setting('cluster_name') or similar).
  30. Q50List all installed extensions (pg_extension).

FILTERED COUNTS / DISTINCT EXPLORATION

  1. Q51How many customers are in tier 'Gold'?
  2. Q52How many customers are in tier 'Platinum'?
  3. Q53How many products have a price greater than 5000?
  4. Q54How many orders are 'Cancelled'?
  5. Q55How many tickets have priority 'Critical'?
  6. Q56How many tickets are currently 'Open'?
  7. Q57How many tickets are 'In Progress'?
  8. Q58How many tickets have NEVER been resolved (resolved_date IS NULL)?
  9. Q59How many sales.shipments are still in transit (delivered_date IS NULL)?
  10. Q60How many web_events.page_views are from anonymous users (customer_id IS NULL)?
  11. Q61How many orders did RetailMart place in calendar year 2025?
  12. Q62How many orders happened in Q1 2025 (Jan-Mar)?
  13. Q63How many customers registered in 2024?
  14. Q64How many distinct cities are in customers.addresses?
  15. Q65How many distinct store cities are there?
  16. Q66How many distinct courier_name values in sales.shipments?
  17. Q67How many distinct call_reason values in call_center.calls?
  18. Q68How many distinct categories in support.tickets?
  19. Q69How many distinct brands have at least one product?
  20. Q70How many distinct payment_mode values are in sales.payments?
  21. Q71How many ads_spend rows are for platform = 'Google'?
  22. Q72How many ads_spend rows are for platform = 'Meta' (returns 0 - value isn't in V3 data; verify by checking distinct values).
  23. Q73How many employees work as 'Store Manager'?
  24. Q74How many employees work as 'Cashier'?
  25. Q75How many api_requests had status_code = 500?
  26. Q76How many api_requests had status_code = 200?
  27. Q77How many application_logs are at level 'ERROR'?
  28. Q78How many application_logs are at level 'FATAL'?
  29. Q79How many page_views are from 'Mobile' devices?
  30. Q80How many calls have call_duration_seconds = exactly 60 (probable timeout)?

DIAGNOSTIC / MULTI-STEP QUERIES

  1. Q81Are there any orders with NULL net_total? Find them.
  2. Q82Are there any customers with NULL first_name?
  3. Q83Are there any products with NULL brand_id?
  4. Q84Are there any employees with NULL dept_id?
  5. Q85Are there any reviews with NULL rating?
  6. Q86Are there orders with order_date in the FUTURE (after CURRENT_DATE)?
  7. Q87Are there shipments with delivered_date EARLIER than shipped_date (data quality bug)?
  8. Q88Are there tickets with resolved_date EARLIER than created_date?
  9. Q89Are there products with price = 0 or negative?
  10. Q90Are there employees with salary = 0 or negative?
  11. Q91Show the date range of sales.orders (MIN and MAX order_date).
  12. Q92Show the date range of hr.attendance.
  13. Q93Show the date range of web_events.page_views (view_timestamp).
  14. Q94Show the date range of marketing.campaigns (start_date min/max).
  15. Q95Show how many DISTINCT customer_ids appear in sales.orders.
  16. Q96Show how many DISTINCT product_ids appear in sales.order_items.
  17. Q97Show how many DISTINCT employee_ids appear in hr.attendance.
  18. Q98Show how many DISTINCT brand_ids appear in products.products.
  19. Q99Show how many DISTINCT cust_ids appear in support.tickets.
  20. Q100Show how many DISTINCT campaign_ids appear in marketing.ads_spend.

Interview grade, edge cases

CATALOG INTROSPECTION DEEP-DIVE

  1. Q1What's the difference between pg_class.relkind values 'r', 'i', 'S', 'v', 'm', 'p', 't', 'f'?
  2. Q2Explain why pg_class.relnamespace is an OID - and how to resolve it to a schema name.
  3. Q3How would you list every table in RetailMart V3 with its schema name (skip system schemas)?
  4. Q4Why does pg_attribute.attnum < 0 for system columns?
  5. Q5What is pg_index.indkey - and how do you decode a "1 3 2" key list?
  6. Q6How do you tell from pg_constraint whether a constraint is PK, FK, UNIQUE, CHECK, or EXCLUSION?
  7. Q7Explain pg_depend - what is it used for during DROP CASCADE?
  8. Q8Why is pg_proc joined to pg_namespace to find functions in a specific schema?
  9. Q9What's stored in pg_type - and how does it model composite/array/range/domain types?
  10. Q10How would you find all extensions installed and their version (pg_extension)?
  11. Q11Explain pg_authid vs pg_roles - why are they different views?
  12. Q12How do you list every sequence in RetailMart V3 with its last_value?
  13. Q13Walk through how an IDENTITY column appears in pg_attribute (attidentity column).
  14. Q14Compare information_schema.tables vs pg_class for portability.
  15. Q15What's in pg_stat_user_tables that pg_stat_all_tables hides?
  16. Q16How would you find all foreign-key relationships across all RetailMart schemas?
  17. Q17What does pg_stat_user_indexes.idx_scan = 0 indicate - and how do you act on it?
  18. Q18Explain pg_locks columns: locktype, mode, granted - what does waiting look like?
  19. Q19How would you find the longest-running transaction right now (pg_stat_activity)?
  20. Q20Explain pg_stat_database - what's the cache hit ratio formula?
  21. Q21What does pg_stat_bgwriter tell you about checkpoint pressure?
  22. Q22Walk through pg_settings - how do you find which GUC values were set by the user vs default?
  23. Q23What does pg_tablespace store - and what's the default tablespace OID?
  24. Q24How would you list all roles and their attributes (CREATEDB, SUPERUSER, etc.)?
  25. Q25What is pg_publication / pg_subscription - when do you query them?

SCHEMA AUDIT QUERIES

  1. Q26List every table in RetailMart V3 with row count estimate (pg_class.reltuples).
  2. Q27List every table with PK column name(s) (join pg_constraint + pg_attribute).
  3. Q28List every foreign key with source/target tables and columns.
  4. Q29Find tables WITHOUT a primary key.
  5. Q30Find every column with a NOT NULL constraint in customers schema.
  6. Q31List every CHECK constraint in sales schema and its expression text.
  7. Q32List every UNIQUE constraint (not PK) across all schemas.
  8. Q33Find tables with > 5 indexes (potential over-indexing).
  9. Q34List every index along with the columns indexed (use pg_get_indexdef).
  10. Q35Find duplicate indexes (same columns, same order, on the same table).
  11. Q36List columns whose data type is TEXT but could be CHAR/VARCHAR with a length limit.
  12. Q37Find every table with a column named 'id' that is NOT a primary key.
  13. Q38Find every table that has both 'created_at' and 'updated_at' timestamp columns.
  14. Q39List every NUMERIC/DECIMAL column with its precision and scale.
  15. Q40Find columns of type JSON (legacy) vs JSONB - should any be migrated?
  16. Q41List every column with a DEFAULT value across the products schema.
  17. Q42List every generated column (GENERATED ALWAYS AS ...) in V3.
  18. Q43Find tables that have no foreign key references pointing INTO them (isolated tables).
  19. Q44Find tables referenced by 5+ other tables (highly central tables).
  20. Q45List every view across V3 with its underlying SELECT (pg_views).
  21. Q46List every materialized view with its definition + ispopulated flag.
  22. Q47Find every column in V3 named like '%email%' across all schemas.
  23. Q48List every trigger across V3 with target table and trigger function.
  24. Q49List every function in RetailMart V3 with its return type and argument types.
  25. Q50List every schema in V3 with the count of tables, views, and functions.

STORAGE, SIZE & PARTITION AUDIT

  1. Q51List every table with its total size (heap + indexes + TOAST) using pg_total_relation_size.
  2. Q52List every table with heap size, index size, TOAST size separately.
  3. Q53List the top 10 largest tables in RetailMart V3 by total size.
  4. Q54List the top 10 largest indexes in RetailMart V3.
  5. Q55Compute the index-to-heap ratio per table and flag ratios > 1.0.
  6. Q56Find tables with significant n_dead_tup (potential bloat).
  7. Q57Compare reltuples (catalog estimate) vs actual COUNT(*) for sales.orders - how big is the drift?
  8. Q58List every table with its last_vacuum, last_autovacuum, last_analyze timestamp.
  9. Q59Find tables that have NEVER been analyzed (last_analyze IS NULL).
  10. Q60List every partitioned table in V3 with its partition strategy (LIST/RANGE/HASH).
  11. Q61List the child partitions of any partitioned table in V3 (pg_inherits + pg_class).
  12. Q62For each partition, show its partition bound expression (pg_get_expr).
  13. Q63Compute total size of each partition group (parent + children sum).
  14. Q64Find every table with a TOAST table (pg_class.reltoastrelid <> 0) and its TOAST size.
  15. Q65Find columns with EXTENDED storage that might benefit from EXTERNAL (already-compressed content).
  16. Q66List every tablespace with the count of objects stored in it.
  17. Q67Find the relation with the highest pg_relation_filenode churn (proxy: many VACUUM FULL runs).
  18. Q68Compute total V3 database size - sum of all schemas + per-schema breakdown.
  19. Q69Find every table with FILLFACTOR != 100 (custom storage parameter).
  20. Q70List every index that is invalid (pg_index.indisvalid = false).
  21. Q71List every index that is unique (pg_index.indisunique = true) - and confirm count matches PK + UNIQUE constraints.
  22. Q72Find every duplicate row-count estimate (reltuples) > 1M across V3 - production-scale tables.
  23. Q73List every sequence in V3 with its last_value and is_cycled.
  24. Q74Find tables where reltuples is more than 20% off from actual count(*) - stats stale.
  25. Q75List every relation with an unusual relpersistence (TEMP or UNLOGGED).

DIAGNOSTIC & HEALTH-CHECK QUERIES

  1. Q76Show cache hit ratio for the entire database (heap_blks_hit / heap_blks_hit + heap_blks_read).
  2. Q77Show per-table cache hit ratio - rank worst 10 tables.
  3. Q78Show per-index cache hit ratio - find indexes living entirely on disk.
  4. Q79List unused indexes (idx_scan = 0 AND not part of unique constraint) - candidates to drop.
  5. Q80List most-scanned indexes (top 10 by idx_scan).
  6. Q81List most-accessed tables (top 10 by seq_scan + idx_scan combined).
  7. Q82List tables with high seq_scan + large size (missing index suspects).
  8. Q83Show current activity: pid, user, state, query - sorted by query_start (oldest first).
  9. Q84Show backends in 'idle in transaction' state - and how long they've been idle.
  10. Q85Show current locks with blocking pid (use pg_blocking_pids).
  11. Q86Compute index hit rate per index from pg_statio_user_indexes.
  12. Q87Show TOP 10 queries by total_exec_time from pg_stat_statements.
  13. Q88Show TOP 10 queries by mean_exec_time from pg_stat_statements.
  14. Q89Show TOP 10 queries by calls (most frequently executed).
  15. Q90Show queries that have spilled to disk (temp_blks_read > 0) - work_mem may be too low.
  16. Q91List all replication slots and their lag (pg_replication_slots).
  17. Q92Show pg_stat_replication - current state of all connected replicas.
  18. Q93Compute checkpoint statistics: checkpoints_timed vs checkpoints_req from pg_stat_bgwriter.
  19. Q94Find the most bloated index by approximate bloat ratio (pgstattuple if installed).
  20. Q95List databases ranked by size (pg_database_size).
  21. Q96Find the count of WAL files on disk (pg_ls_waldir if PG10+).
  22. Q97Show user privileges on schemas (has_schema_privilege checks across roles).
  23. Q98Find every role that has SUPERUSER and warn (security audit).
  24. Q99List every table where REFERENCES grant has been issued - and to whom.
  25. Q100Build a "health summary" single-row query: total tables, total indexes, DB size, cache hit ratio, longest active query duration.

Production scenarios, optimisation

PG_STAT_STATEMENTS DEEP

  1. Q1Top 10 by total_exec_time with normalized queries.
  2. Q2Top 10 by mean_exec_time (with min calls threshold).
  3. Q3Queries with the worst p95 latency.
  4. Q4Queries with the largest temp file IO.
  5. Q5Queries with the highest shared_blks_read (cache miss).
  6. Q6Queries doing the most WAL bytes.
  7. Q7Queries with the worst rows_per_call (overflow).
  8. Q8Queries spawned by autovacuum (filter by userid).
  9. Q9Compare current run vs last reset baseline.
  10. Q10Build a "slow query bulletin": top 20 with summary per app.
  11. Q11Find a query that suddenly slowed down (planid change).
  12. Q12Track query frequency per hour via pg_stat_statements snapshots.
  13. Q13Identify "N+1" patterns: query with calls > 1M and tiny rows.
  14. Q14Identify "kitchen sink" patterns: query with rows_per_call > 1M.
  15. Q15Find queries with high jit_functions but no benefit.
  16. Q16Reset pg_stat_statements; rerun 24h later for a clean window.
  17. Q17Compare CPU vs IO bound queries by ratio.
  18. Q18Diagnose a "cliff" - sudden 10x slowdown.
  19. Q19Walk through tracking "regression after deploy" via the stats.
  20. Q20Find queries that always use temp files.
  21. Q21Find queries with very different mean vs max (variance flag).
  22. Q22Aggregate by application_name.
  23. Q23Find connection-pool-killer queries.
  24. Q24Find dead-code queries: in code but never called.
  25. Q25Top 5 queries per role/user.

LOCKING & CONTENTION

  1. Q26List the longest-running transactions right now.
  2. Q27List who holds AccessExclusiveLock.
  3. Q28Build a blocking chain (root blocker -> cascade).
  4. Q29Detect long-waiting backends (wait_event_type = 'Lock').
  5. Q30Detect 'idle in transaction' >5 min.
  6. Q31Find tables most contended (lock waits).
  7. Q32Find advisory locks held longer than 1 hour.
  8. Q33Find autovacuum that's been running >10 min.
  9. Q34Detect deadlocks since cluster start.
  10. Q35Detect WAL writer falling behind.
  11. Q36Find connections by application_name + idle duration.
  12. Q37Track active SLA breaches (query >30s).
  13. Q38pg_terminate_backend the offender - safer wrapper script.
  14. Q39Use pg_signal_backend grants for safer terminate.
  15. Q40Lock conflict matrix: which lock type blocks which.
  16. Q41Find SubtransControlLock contention (subtransaction storm).
  17. Q42Tune deadlock_timeout vs lock_timeout.
  18. Q43Find row-level locks holding back vacuum.
  19. Q44Detect xmin horizon stuck (long-running transaction).
  20. Q45Calculate transaction age (txid_current - txid_started).
  21. Q46Build a "stuck VACUUM" report.
  22. Q47Detect SELECT FOR UPDATE traffic jams (queue table pattern).
  23. Q48Show the longest backend by transaction_age.
  24. Q49Find tables that frequently appear in pg_locks (hotspots).
  25. Q50Show parallel worker count per active query.

INDEX FORENSICS

  1. Q51Index size per table - ratio to heap.
  2. Q52Duplicate indexes - same columns on same table.
  3. Q53Redundant indexes - covered by another.
  4. Q54Unused indexes (no scans since reset).
  5. Q55Rarely-used indexes (idx_scan / idx_tup_fetch low).
  6. Q56Index bloat estimate (using pgstattuple or pgstattuple_approx).
  7. Q57Find INVALID indexes (CONCURRENTLY failed).
  8. Q58Find FOR-only indexes that should be partial (most rows match).
  9. Q59Find BTREE indexes that should be GIST/GIN.
  10. Q60Index columns with very low cardinality (Boolean index = waste).
  11. Q61Find tables where every query does seq scan despite indexes.
  12. Q62Reorder index columns for better hits (cardinality analysis).
  13. Q63Build a "best index for this query" suggestion.
  14. Q64List partition-local vs partition-global indexes.
  15. Q65Schedule REINDEX CONCURRENTLY for top-10 bloated indexes.
  16. Q66Find indexes never written to (unused).
  17. Q67Find indexes on FK columns that are missing.
  18. Q68Index health overall - average scan rate.
  19. Q69Top-N indexes by total IO.
  20. Q70Drift between pg_stat_user_indexes and pgstattuple_approx.
  21. Q71Build a "candidate index" report from missed index opportunities.
  22. Q72Find HASH indexes (legacy, often unused).
  23. Q73Find BRIN indexes - confirm they're matched to time-correlated cols.
  24. Q74Compare statistics target per indexed column.
  25. Q75Build an "index lineage" report - when each was last rebuilt.

AUTO-AUDIT SCRIPTS

  1. Q76Auto-audit: tables without PK.
  2. Q77Auto-audit: columns referenced by FK that lack an index.
  3. Q78Auto-audit: tables not vacuumed in >30 days.
  4. Q79Auto-audit: stats stale (last_analyze > 7 days).
  5. Q80Auto-audit: top 20 largest tables.
  6. Q81Auto-audit: tables with > 30% bloat (pgstattuple).
  7. Q82Auto-audit: indexes > 1 GB never used.
  8. Q83Auto-audit: tables with very high seq_scan ratio.
  9. Q84Auto-audit: tables with autovacuum frequently triggered.
  10. Q85Auto-audit: tables with high churn (n_tup_upd + n_tup_del).
  11. Q86Auto-audit: connections per application_name.
  12. Q87Auto-audit: slow queries (mean_exec_time > 1s).
  13. Q88Auto-audit: queries spilling to disk.
  14. Q89Auto-audit: replication slot lag.
  15. Q90Auto-audit: cache hit ratio per table - flag <0.95.
  16. Q91Auto-audit: xid age per table.
  17. Q92Auto-audit: wraparound risk (txid_age > 1B).
  18. Q93Auto-audit: WAL volume per hour.
  19. Q94Auto-audit: checkpoint pressure (forced vs timed ratio).
  20. Q95Auto-audit: SUPERUSER count + warning.
  21. Q96Auto-audit: tables granted to PUBLIC (security flag).
  22. Q97Auto-audit: NOT NULL violations in audit views.
  23. Q98Auto-audit: orphan FK rows (data integrity).
  24. Q99Auto-audit: extensions installed + version mismatch.
  25. Q100Build a single "DB health dashboard" query - 20 KPIs in one row.