Introduction
If you're a developer or DBA, you know the pain of a slow query. The database is the backbone of most applications, and when it chokes, everything suffers. PostgreSQL and Redis are a powerful duo—PostgreSQL for reliable, transactional data storage, and Redis for high-speed caching and real-time operations. But using them effectively requires more than just knowing SQL and basic commands. It requires a mindset of optimization, and that's where AI prompts come in.
This article is a curated collection of 12 practical prompts designed to help you squeeze every drop of performance from your database stack. Each prompt is a ready-to-use template that you can feed into an AI assistant (like ChatGPT, Claude, or a specialized tool) to get expert-level advice, code, and analysis. We'll cover everything from diagnosing slow queries and designing effective indexes to configuring Redis caching strategies and avoiding common pitfalls. Whether you're a beginner looking to learn best practices or an expert seeking to refine your workflow, these prompts will save you time and headaches.
1. Explain a Slow Query
Task: Analyze a PostgreSQL query and explain why it might be slow.
Prompt:
I have a PostgreSQL query that runs slowly. Here's the query:
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC;
Can you explain potential bottlenecks and suggest improvements?
Example Result:
The AI will likely point out the lack of an index on orders.user_id and users.created_at, and suggest using EXPLAIN ANALYZE to get execution details. It may recommend creating composite indexes or rewriting the query to use a subquery for better performance.
2. Generate an Index Strategy
Task: Given a table schema and common query patterns, generate a set of indexes.
Prompt:
I have a PostgreSQL table `products` with columns: id (PK), name, category_id, price, created_at. My application frequently runs queries like:
- SELECT * FROM products WHERE category_id = ? AND price BETWEEN ? AND ?;
- SELECT * FROM products WHERE name ILIKE '%search%';
- SELECT * FROM products WHERE created_at > ? ORDER BY created_at DESC;
What indexes should I create? Please provide the exact CREATE INDEX statements and explain your reasoning.
Example Result:
The AI will suggest a composite index on (category_id, price) for the range query, a GIN index on name for ILIKE patterns, and a B-tree index on created_at for sorting. It will also discuss index size and maintenance trade-offs.
3. Optimize a Query with EXPLAIN ANALYZE
Task: Interpret the output of EXPLAIN ANALYZE and suggest optimizations.
Prompt:
Here is the EXPLAIN ANALYZE output for a slow query:
Seq Scan on orders (cost=0.00..15000.00 rows=100000 width=8)
Filter: (user_id = 12345)
Planning Time: 0.5 ms
Execution Time: 120.0 ms
What does this tell me, and how can I improve it?
Example Result:
The AI will explain that a sequential scan is occurring, indicating a missing index on user_id. It will suggest creating an index and re-running the query, and also mention the possibility of using a covering index if the query only needs certain columns.
4. Design a Redis Cache Strategy
Task: Design a caching strategy for a specific use case.
Prompt:
I'm building a social media app with PostgreSQL as the main database and Redis for caching. I want to cache user profiles and their recent posts. The profiles don't change often, but posts are updated frequently. What caching strategy should I use? Include cache invalidation and TTL specifics.
Example Result:
The AI will suggest using Redis hashes for user profiles with a TTL of 1 hour, and sorted sets for recent posts with a TTL of 5 minutes. It will also explain cache-aside pattern and how to invalidate cache when a new post is created.
5. Write a Lua Script for Atomic Operations
Task: Create a Lua script for an atomic Redis operation.
Prompt:
I need to atomically increment a counter in Redis, but only if it's below a maximum value. Write a Lua script to do this, and explain how to use it with EVAL.
Example Result:
The AI will provide a Lua script like:
local current = redis.call('GET', KEYS[1])
if current and tonumber(current) >= tonumber(ARGV[1]) then
return 0
else
return redis.call('INCR', KEYS[1])
end
And explain how to call it with EVAL.
6. Configure Redis for Maximum Performance
Task: Recommend Redis configuration settings for a high-throughput scenario.
Prompt:
I'm using Redis as a cache and message queue. My server has 16GB RAM and 8 CPU cores. What are the recommended settings for `maxmemory`, `maxmemory-policy`, `save`, and `appendonly`? Also, any other tuning tips?
Example Result:
The AI will suggest setting maxmemory to 12GB, maxmemory-policy to allkeys-lru, and disabling persistence if it's a pure cache. It will also mention tcp-keepalive, timeout, and using unixsocket for local connections.
7. Debug a Redis Memory Spike
Task: Identify the cause of a memory spike in Redis.
Prompt:
Redis memory usage spiked from 2GB to 8GB overnight. I have `maxmemory-policy` set to `noeviction`. What could be causing this, and how can I debug it? Provide commands to inspect memory usage.
Example Result:
The AI will suggest using redis-cli --bigkeys to find large keys, MEMORY DOCTOR for leak analysis, and INFO memory to see memory breakdown. It will also advise checking for keys with no TTL and setting maxmemory-policy to allkeys-lru if appropriate.
8. Use Redis to Implement Rate Limiting
Task: Implement a rate limiter using Redis.
Prompt:
I need to rate limit API requests to 100 requests per minute per user. Using Redis, what's the best approach? Provide a code example in Python using redis-py.
Example Result:
The AI will suggest using a sliding window algorithm with a sorted set, or a simpler fixed window with INCR and EXPIRE. It will provide Python code for both, explaining the trade-offs.
9. Synchronize PostgreSQL and Redis
Task: Develop a strategy to keep data consistent between PostgreSQL and Redis.
Prompt:
I have a product catalog in PostgreSQL that I want to cache in Redis. How should I keep the cache up-to-date? Options: cache-aside, write-through, or event-driven? Consider consistency and performance.
Example Result:
The AI will compare the three patterns and recommend cache-aside for most cases, with event-driven using PostgreSQL's LISTEN/NOTIFY or a CDC tool like Debezium for real-time updates.
10. Optimize JSONB Queries
Task: Improve performance of queries on JSONB columns.
Prompt:
I have a PostgreSQL table with a JSONB column `data`. Queries like `SELECT * FROM events WHERE data->>'type' = 'click'` are slow. How can I optimize this? Should I use GIN indexes? Are there any pitfalls?
Example Result:
The AI will suggest creating a GIN index on the JSONB column, but also warn about the inefficiency of ->> with a GIN index and suggest using a B-tree index on the specific key expression like (data->>'type'). It will also discuss using jsonb_path_ops for faster operations.
11. Monitor Database Health
Task: Create a set of monitoring queries for PostgreSQL and Redis.
Prompt:
What are the most important metrics to monitor in PostgreSQL and Redis? Provide a list of queries and commands to check health, performance, and potential issues.
Example Result:
The AI will list PostgreSQL metrics like cache hit ratio, index usage, and dead tuples, with queries like SELECT * FROM pg_stat_user_tables and pg_stat_bgwriter. For Redis, it will suggest INFO stats, INFO commandstats, and SLOWLOG GET. It will also mention tools like pg_stat_statements and RedisInsight.
12. Migrate from PostgreSQL to Redis
Task: Plan a migration of certain data from PostgreSQL to Redis for better performance.
Prompt:
I have a table in PostgreSQL that stores user sessions. Access is very frequent, and I want to move it to Redis. What are the steps? How do I handle data migration and application changes?
Example Result:
The AI will outline steps: export data, transform to Redis data structures (e.g., hashes), import, update application code to use Redis, and set up a sync mechanism. It will also discuss considerations like TTL and persistence.
Conclusion
These 12 prompts are your toolkit for mastering PostgreSQL and Redis performance. They're designed to be starting points—you can adapt them to your specific context and ask follow-up questions. The key is to understand the underlying principles: indexing, query planning, caching patterns, and monitoring. With these prompts, you'll be able to diagnose issues, implement solutions, and make your database stack faster and more reliable. So next time you face a slow query or a Redis memory warning, don't panic—ask your AI assistant, and let these prompts guide you to a solution.
Comments