Turning PostgreSQL Tuning into a Game: 12 Prompts to Automate Database Optimization

Turning PostgreSQL Tuning into a Game: 12 Prompts to Automate Database Optimization

Database optimization can feel like a chore—endless hours spent analyzing EXPLAIN output, tweaking work_mem, and praying that the next migration won't break production. But what if you could delegate the mundane parts to an AI assistant and focus on the strategic decisions? In this article, I share 12 practical prompts that turn PostgreSQL optimization into an interactive, even enjoyable, process. Each prompt is designed to be used with an AI-powered tool like ASI Biont, which can execute SQL and analyze the results in a sandbox environment. You'll see how to move from reactive firefighting to proactive, automated tuning.

Why Prompts for PostgreSQL?

PostgreSQL is a powerful, feature-rich database, but its flexibility comes at a cost: complexity. According to the official PostgreSQL documentation, the server has over 200 configuration parameters, and query plans can be influenced by dozens of factors. Even experienced DBAs rely on experience and intuition to find bottlenecks. AI prompts can help by:

• Automating repetitive analysis: Instead of manually running pg_stat_statements queries, you can ask the AI to identify top N slow queries and suggest indexes.
• Providing context-aware recommendations: The AI can explain why a particular index helps, referencing the PostgreSQL official docs.
• Simulating scenarios: You can test EXPLAIN variations without touching production, thanks to the sandbox.

Important: While AI is a great assistant, it's not a replacement for understanding the fundamentals. Always verify suggestions against your workload and the official documentation.

The Prompts: From Basics to Expert

Below, you'll find 12 prompts divided into three skill levels. Each prompt includes the exact text to use, an explanation of what it does, and a sample output (based on a typical e-commerce database).

Level 1: Basic Prompts — Quick Wins for Beginners

If you're new to PostgreSQL tuning, these prompts will help you identify obvious issues and learn best practices without getting lost in technical details.

1. Find the Slow Queries

Prompt:

Analyze the pg_stat_statements view and list the top 10 queries by total execution time. For each, provide the query text, the number of calls, and the average execution time. Suggest indexes that could speed them up.

Why it works: pg_stat_statements is an extension that tracks query execution statistics. It's a goldmine for finding performance bottlenecks. The AI will parse the view and give you a clear action list.

Example output:

2. Identify Missing Indexes

Prompt:

Using the pg_stat_user_tables and pg_stat_user_indexes views, identify tables that have high sequential scan counts but low index usage. Propose specific indexes for these tables.

Why it works: Sequential scans (SEQ SCAN) often indicate missing indexes. This prompt helps you find tables where full scans are frequent, suggesting where indexes would be most beneficial.

Example output:

• Table order_items: 12,000 seq scans, 0 index scans. Consider idx_order_items_order_id ON order_items(order_id).
• Table products: 8,500 seq scans, only 200 index scans. Add idx_products_category ON products(category_id).

3. Explain a Query Plan in Plain English

Prompt:

Take the following SQL query and run EXPLAIN ANALYZE on it. Then explain the plan step by step, pointing out any potential bottlenecks and suggesting how to improve it.

SELECT c.name, o.total FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.created_at > '2026-01-01' ORDER BY o.total DESC LIMIT 50;

Why it works: Understanding EXPLAIN output is crucial. This prompt forces the AI to act as a tutor, breaking down each node and what it means.

Example output:

• Sort (cost=high) — Sorting 10k rows to find top 50. Consider an index on orders(total DESC).
• Hash Join — Building a hash table for 5k customers. Consider increasing work_mem if this causes disk spills.
• Seq Scan on orders — Scanning 100k rows. Add an index on created_at to narrow the data.

Level 2: Advanced Prompts — For the Practiced DBA

Now you're comfortable with the basics. These prompts tackle more complex scenarios like concurrency, vacuum, and specific performance issues.

4. Diagnose Lock Contention

Prompt:

Query pg_stat_activity and pg_locks to find any blocking sessions. For each blocking chain, show the query of the blocking session and the blocked session. Suggest ways to reduce contention (e.g., using advisory locks or modifying the schema).

Why it works: Lock contention can bring your app to a crawl. This prompt helps you visualize who is blocking whom and get advice on resolving it.

Example output:

• Session A (pid 123) holds AccessExclusiveLock on table orders; Session B (pid 456) is waiting for AccessShareLock. The blocking query is ALTER TABLE orders ADD COLUMN discount numeric;. Suggestion: Use pg_repack or perform the change during a maintenance window.

5. Tune VACUUM and Autovacuum

Prompt:

Analyze the autovacuum settings for tables in my database. Using pg_stat_user_tables and pg_settings, list tables that haven't been vacuumed recently and suggest optimal autovacuum parameters for high-update tables.

Why it works: Autovacuum is critical for MVCC. This prompt helps you fine-tune thresholds to prevent bloat.

Example output:

• Table sessions has 30% dead tuples, but autovacuum hasn't run for 2 days. Set autovacuum_vacuum_scale_factor = 0.05 and autovacuum_vacuum_threshold = 1000 for this table using ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.05);.

6. Optimize a Complex JOIN

Prompt:

Here's a complex query with multiple joins and subqueries. Run EXPLAIN and suggest query rewrites, index strategies, or configuration changes that could improve its performance. Consider leveraging PostGIS features if applicable.

SELECT u.username, COUNT(o.id) as order_count, SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.created_at > NOW() - INTERVAL '1 year'
LEFT JOIN (SELECT user_id, MAX(created_at) as last_order FROM orders GROUP BY user_id) lo ON lo.user_id = u.id
WHERE u.active = true
GROUP BY u.username, lo.last_order
ORDER BY total_spent DESC NULLS LAST;

Why it works: Complex queries often have hidden inefficiencies. The AI can suggest using CTEs, window functions, or partial indexes.

Example output:

• The subquery for last_order scans the entire orders table. Use a lateral join or a window function to compute this more efficiently.
• Add an index on orders(user_id, created_at) to speed up the join and the subquery.
• Consider a partial index on orders(user_id) WHERE created_at > NOW() - INTERVAL '1 year'.

Level 3: Expert Prompts — For the Performance Guru

You're comfortable with tuning. These prompts push the boundaries, exploring advanced features like partitioning, logical replication, and even custom C functions.

7. Design a Partitioning Strategy

Prompt:

I have a table `events` that stores 5 million rows per month. Design a partitioning strategy using range partitioning on the `created_at` column. Provide the DDL for creating partitions for the next 6 months, and explain how to automate partition creation using pg_partman.

Why it works: Partitioning is key for managing large tables. This prompt tests your ability to plan and automate.

Example output:

CREATE TABLE events (
  id bigserial,
  created_at timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
-- ... repeat for 6 months

For automation, use pg_partman with a monthly interval: SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');

8. Tune Workload-Specific Parameters

Prompt:

For my PostgreSQL instance that runs a high-volume OLTP workload with many concurrent transactions, suggest specific values for work_mem, shared_buffers, effective_cache_size, and max_connections, based on my system's available memory (32GB). Provide the rationale for each setting.

Why it works: Configuration tuning is an art. This prompt forces you to reason about memory allocation.

Example output:

• shared_buffers = 8GB (25% of RAM, as per PostgreSQL wiki)
• effective_cache_size = 24GB (75% of RAM)
• work_mem = 64MB (allows complex sorts without disk spills, but be careful with total memory)
• max_connections = 200 (reduce if you have many idle connections, use a connection pooler like PgBouncer)

9. Analyze and Improve a Slow Window Function

Prompt:

I have a query that uses a window function to calculate running totals. It's slow on large datasets. Rewrite it using a lateral join or other techniques to improve performance. Show EXPLAIN before and after.

SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total
FROM sales
ORDER BY date;

Why it works: Window functions are often unavoidable, but they can be optimized.

Example output:

• Original plan: WindowAgg over a full sort. Use a self-join with a lateral to compute running total incrementally, especially if you only need the last N days.

SELECT s.date, s.amount, SUM(s2.amount) as running_total
FROM sales s
CROSS JOIN LATERAL (
  SELECT amount FROM sales s2 WHERE s2.date <= s.date
) s2
GROUP BY s.date, s.amount;

But this might be even slower. In practice, consider using OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) with appropriate indexes.

10. Use Explain (analyze, buffers, format json) for Deep Dive

Prompt:

Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on the following query. Summarize the key metrics (e.g., execution time, buffer usage, rows removed by filter) and identify where the most time is spent.

SELECT * FROM products WHERE category_id = 5 AND price < 100 ORDER BY name;

Why it works: JSON output gives you machine-readable detail. The AI can parse and interpret it.

Example output:

• Execution time: 150 ms.
• Buffer usage: 3000 shared hits, 500 local hits.
• Rows removed by filter: 2000 (category_id mismatch). Suggest a composite index on (category_id, price).

11. Optimize for Bulk Inserts

Prompt:

I need to load 10 million rows into a table logs as quickly as possible. Compare the performance of COPY vs. multi-row INSERT, and suggest settings like wal_level, fsync, and synchronous_commit that can be temporarily changed for faster loading. Also recommend using pg_bulkload if appropriate.

Why it works: Bulk loading is a common task. This prompt explores trade-offs between safety and speed.

Example output:

• Use COPY with BINARY format for best performance.
• Set synchronous_commit = off and fsync = off during the load (with backup plan), then revert.
• Consider pg_bulkload for even faster loading, but it requires more setup.

12. Create a Self-Tuning Query

Prompt:

Design a PL/pgSQL function that automatically analyzes a table and suggests indexes based on query patterns from pg_stat_statements. The function should output a report of suggested indexes.

Why it works: This is the ultimate automation—teaching the database to tune itself.

Example output:

CREATE OR REPLACE FUNCTION suggest_indexes() RETURNS TABLE(table_name text, suggested_index text) AS $$
DECLARE
  q record;
BEGIN
  FOR q IN SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 10 LOOP
    -- Heuristic: if query contains WHERE on column, suggest index
    RETURN QUERY SELECT 'orders', 'CREATE INDEX ON orders (status)' WHERE q.query LIKE '%status%';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Conclusion

These 12 prompts are just the beginning. By integrating AI into your PostgreSQL workflow, you can reduce the time spent on routine tuning and focus on architecture and innovation. Remember to always verify AI suggestions with benchmarks and the official documentation. For a hands-on experience, try these prompts with ASI Biont, which can safely execute SQL in a sandbox and provide real-time feedback. Start with the basics, and soon you'll be optimizing like a pro—and maybe even having fun.

Ready to automate your database? Explore ASI Biont's capabilities and take your PostgreSQL tuning to the next level.