20 Prompts for Database Work: PostgreSQL, MongoDB, and Redis Optimization

## Introduction Databases are the backbone of modern applications. Whether you're running a high-traffic e-commerce platform, a real-time analytics dashboard, or a simple blog, the way you interact with your data store can make or break performance. Over the past decade, three database systems have emerged as dominant forces: PostgreSQL (the relational heavyweight), MongoDB (the NoSQL document store), and Redis (the in-memory speed demon). Each has its own query language, indexing strategies, and optimization techniques. But here's the challenge: many developers treat database interaction as a necessary evil — they write basic CRUD queries and move on. In reality, mastering database prompts (queries, commands, and configuration snippets) can drastically reduce latency, cut storage costs, and simplify migrations. This article provides 20 expert-level prompts organized by category: basic, advanced, and expert. Each prompt includes a task description, the exact command or query, and a realistic example result. These prompts are not theoretical. They are battle-tested in production environments handling millions of requests per day. By the end, you'll have a reusable toolkit for PostgreSQL, MongoDB, and Redis that covers everything from slow query analysis to zero-downtime schema migrations. ## Basic Prompts ### 1. PostgreSQL: Find Missing Indexes **Task:** Identify tables with sequential scans (Seq Scan) that indicate missing indexes. **Prompt (SQL):** ```sql SELECT schemaname, relname AS table_name, seq_scan, seq_tup_read, idx_scan, seq_tup_read / NULLIF(seq_scan, 0) AS avg_tuples_per_seq_scan FROM pg_stat_user_tables WHERE seq_scan > 1000 ORDER BY seq_tup_read DESC LIMIT 10; ``` **Example Result:** | schemaname | table_name | seq_scan | seq_tup_read | idx_scan | avg_tuples_per_seq_scan | |------------|-------------|----------|--------------|----------|-------------------------| | public | orders | 4500 | 9000000 | 120 | 2000 | | public | products | 3200 | 6400000 | 85 | 2000 | **Explanation:** This query checks system statistics (`pg_stat_user_tables`) for tables that have been scanned sequentially many times. A high `avg_tuples_per_seq_scan` combined with low `idx_scan` suggests you need an index on frequently filtered columns. For example, if `orders` is often queried by `customer_id`, adding an index like `CREATE INDEX idx_orders_customer_id ON orders(customer_id);` will switch scans to index lookups. ### 2. MongoDB: List Slow Queries **Task:** Retrieve the slowest operations from the profiler to identify performance bottlenecks. **Prompt (MongoDB shell):** ```javascript db.system.profile.find({ millis: { $gte: 100 } }).sort({ millis: -1 }).limit(10).pretty(); ``` **Example Result:** ```json { "op" : "query", "ns" : "ecommerce.orders", "command" : { "find" : "orders", "filter" : { "status" : "pending" } }, "millis" : 450, "planSummary" : "COLLSCAN", "ts" : ISODate("2026-06-30T10:15:00Z") } ``` **Explanation:** MongoDB's system profiler logs operations slower than a threshold (here 100ms). The `COLLSCAN` plan summary indicates a full collection scan — a classic sign of missing indexes. To fix this, create an index on the `status` field: `db.orders.createIndex({ status: 1 })`. Always run the profiler sparingly in production (sample rate 0.1–0.5). ### 3. Redis: Check Memory Usage by Key **Task:** Find keys consuming the most memory. **Prompt (redis-cli):** ```bash redis-cli --bigkeys ``` **Example Result:** ``` # Scanning the entire keyspace to find biggest keys as well as # average sizes per key type. You can use -i 0.1 to sleep 0.1 sec # per 100 SCAN commands (not usually needed). [00.00%] Biggest string found so far '"user:1234:session"' with 10240 bytes [05.00%] Biggest hash found so far '"product:5678:metadata"' with 500 fields [25.00%] Biggest set found so far '"active_tokens"' with 15000 members ... ``` **Explanation:** `redis-cli --bigkeys` scans the entire keyspace (using the `SCAN` command) and reports the largest keys by type. This is essential for capacity planning and identifying memory leaks. For example, if a hash has 500 fields, consider splitting it or using a smaller data structure. Note: running this on a large dataset (millions of keys) can briefly spike CPU — use `--sleep 0.1` to throttle. ### 4. PostgreSQL: Find Unused Indexes **Task:** Detect indexes that are never used in queries. **Prompt (SQL):** ```sql SELECT indexrelid::regclass AS index_name, relid::regclass AS table_name, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relid; ``` **Example Result:** | index_name | table_name | idx_scan | idx_tup_read | idx_tup_fetch | |-------------------------|------------|----------|--------------|---------------| | idx_orders_created_at | orders | 0 | 0 | 0 | | idx_products_deleted_at | products | 0 | 0 | 0 | **Explanation:** Indexes that have never been scanned (`idx_scan = 0`) waste disk space and slow down writes. Before dropping them, double-check that they aren't used in unique constraints or foreign keys. Use `DROP INDEX CONCURRENTLY` to avoid locking production tables. ## Advanced Prompts ### 5. PostgreSQL: Analyze Query Execution Plan **Task:** Break down a slow query to see cost, rows, and join strategies. **Prompt (SQL):** ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT o.id, o.total, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'shipped' AND o.created_at > '2026-01-01'; ``` **Example Result (abbreviated):** ```json [ { "Plan": { "Node Type": "Hash Join", "Parallel Aware": false, "Startup Cost": 1000.0, "Total Cost": 5000.0, "Plan Rows": 10000, "Plan Width": 24, "Actual Startup Time": 0.5, "Actual Total Time": 2.3, "Actual Rows": 9500, "Shared Hit Blocks": 500, "Shared Read Blocks": 20, "Plans": [ { "Node Type": "Seq Scan", "Relation Name": "orders", "Filter": "((status = 'shipped'::text) AND (created_at > '2026-01-01'::date))", "Actual Rows": 9500, "Actual Total Time": 1.8 }, { "Node Type": "Index Scan", "Index Name": "users_pkey", "Relation Name": "users", "Actual Rows": 1, "Actual Total Time": 0.02 } ] } } ] ``` **Explanation:** The `EXPLAIN ANALYZE` output shows that `orders` is being sequentially scanned (Seq Scan) even though we have a filter. This is because there's no composite index on `(status, created_at)`. Adding `CREATE INDEX idx_orders_status_created ON orders(status, created_at)` would replace the Seq Scan with an Index Scan, reducing actual time from 1.8ms to under 0.1ms. ### 6. MongoDB: Create a Compound Index for Sorting **Task:** Speed up a query that filters and sorts. **Prompt (MongoDB shell):** ```javascript db.orders.createIndex({ status: 1, created_at: -1 }, { name: "idx_status_created" }); ``` **Example Result:** ```json { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } ``` **Explanation:** Before this index, a query like `db.orders.find({ status: "shipped" }).sort({ created_at: -1 })` would perform a full collection scan and then sort in memory (which can exceed the 32MB memory limit). The compound index covers both the filter (equality on `status`) and the sort (descending on `created_at`), allowing MongoDB to return results directly from the index — a covered query. ### 7. Redis: Use Lua Scripting for Atomic Operations **Task:** Decrement a product stock but only if sufficient quantity exists. **Prompt (redis-cli):** ```lua -- Script: atomic_stock_decrement.lua local key = KEYS[1] local qty = tonumber(ARGV[1]) local current = redis.call('GET', key) if not current then return {err = "Key not found"} end current = tonumber(current) if current < qty then return {err = "Insufficient stock"} end redis.call('DECRBY', key, qty) return {ok = current - qty} ``` **Example Execution:** ```bash redis-cli --eval atomic_stock_decrement.lua product:100:stock , 2 ``` **Example Result:** ```json { "ok": 48 } ``` **Explanation:** Without Lua, you'd need a `WATCH`/`MULTI`/`EXEC` transaction, which can fail under high contention. Lua scripts run atomically on the Redis server, ensuring no race conditions. This is perfect for inventory management, rate limiting, or leaderboard updates. ### 8. PostgreSQL: Migrate Schema Without Downtime (Online Migration) **Task:** Add a NOT NULL column with a default value to a large table. **Prompt (SQL — step by step):** ```sql -- Step 1: Add the column as nullable (instant, no table rewrite) ALTER TABLE users ADD COLUMN email_verified boolean DEFAULT false; -- Step 2: Backfill existing rows in batches to avoid long locks WITH batch AS ( SELECT ctid FROM users WHERE email_verified IS NULL LIMIT 10000 ) UPDATE users SET email_verified = false WHERE ctid IN (SELECT ctid FROM batch); -- Step 3: After backfill, add NOT NULL constraint (validated in background) ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL; ``` **Example Result:** ```sql ALTER TABLE Time: 0.002s -- (step 1) UPDATE 10000 Time: 0.450s -- (step 2, repeated until 0 rows affected) ALTER TABLE Time: 0.001s -- (step 3, since all rows already have values) ``` **Explanation:** Adding a NOT NULL column with a default in PostgreSQL 11+ is instant (metadata-only). However, adding a NOT NULL constraint on an existing column requires a full table scan to verify no NULLs exist. By backfilling in small batches (using `ctid` for speed), you avoid locking the table for minutes or hours. This pattern is widely used in zero-downtime deployments. ## Expert Prompts ### 9. PostgreSQL: Parallel Query Tuning **Task:** Force a query to use parallel workers for large aggregations. **Prompt (SQL):** ```sql SET max_parallel_workers_per_gather = 4; SET parallel_tuple_cost = 0.001; SET parallel_setup_cost = 100; EXPLAIN (ANALYZE, BUFFERS) SELECT department_id, COUNT(*), AVG(salary) FROM employees WHERE salary > 50000 GROUP BY department_id; ``` **Example Result (abbreviated):** ```json { "Plan": { "Node Type": "Finalize Aggregate", "Plans": [ { "Node Type": "Gather", "Workers Planned": 4, "Workers Launched": 4, "Workers": [ { "Node Type": "Partial Aggregate" }, { "Node Type": "Parallel Seq Scan", "Filter": "(salary > 50000)" } ] } ] } } ``` **Explanation:** PostgreSQL can parallelize sequential scans and aggregates. The key parameters: `max_parallel_workers_per_gather` limits workers per query, `parallel_tuple_cost` and `parallel_setup_cost` tune the optimizer's decision to use parallelism. In this example, 4 workers each scan a portion of the `employees` table, perform partial aggregation, and the gather node merges results. This can speed up a query from 10 seconds to 2.5 seconds on a 16-core server. ### 10. MongoDB: Aggregation Pipeline Optimization **Task:** Rewrite a slow aggregation to use indexes and reduce stage order. **Prompt (MongoDB shell — before):** ```javascript db.orders.aggregate([ { $match: { status: "shipped" } }, { $sort: { total: -1 } }, { $group: { _id: "$customer_id", totalSpent: { $sum: "$total" } } }, { $sort: { totalSpent: -1 } }, { $limit: 10 } ]); ``` **Optimized Prompt:** ```javascript db.orders.aggregate([ { $match: { status: "shipped" } }, { $group: { _id: "$customer_id", totalSpent: { $sum: "$total" } } }, { $sort: { totalSpent: -1 } }, { $limit: 10 } ]); ``` **Example Result:** ```json [ { "_id": 123, "totalSpent": 45000 }, { "_id": 456, "totalSpent": 32000 }, ... ] ``` **Explanation:** The original pipeline sorts all documents by `total` before grouping, which is unnecessary and expensive (requires a large in-memory sort). By removing the first `$sort`, we save memory and time. The `$group` stage can use an index on `status` (for `$match`) and then perform the group in memory. For even better performance, use `$sortByCount` if applicable. ### 11. Redis: Stream Processing with Consumer Groups **Task:** Implement a reliable task queue with exactly-once semantics. **Prompt (redis-cli):** ```bash # Create a consumer group XGROUP CREATE task_queue my_group $ MKSTREAM # Producer adds a task XADD task_queue * task_id 123 payload '{"type":"email"}' # Consumer reads new messages XREADGROUP GROUP my_group worker1 COUNT 1 BLOCK 5000 STREAMS task_queue > ``` **Example Result:** ```json 1) 1) "task_queue" 2) 1) 1) "1719876543210-0" 2) 1) "task_id" 2) "123" 3) "payload" 4) "{\"type\":\"email\"}" ``` **Explanation:** Redis Streams with consumer groups provide at-least-once delivery (by default). To achieve exactly-once, you must store processed message IDs in a separate set and check before processing. The `>` symbol tells Redis to deliver only new, unread messages. If a worker crashes, pending messages remain and can be claimed by another worker using `XAUTOCLAIM`. ### 12. PostgreSQL: Partial Index for Active Records **Task:** Index only rows that are frequently queried (e.g., active users). **Prompt (SQL):** ```sql CREATE INDEX idx_users_active ON users(email) WHERE status = 'active' AND deleted_at IS NULL; ``` **Example Result:** ```sql CREATE INDEX Time: 0.150s ``` **Explanation:** A partial index includes only rows matching a condition. If 90% of your queries target active users, this index is much smaller (and faster) than a full index on `email`. The optimizer automatically uses it when the WHERE clause matches the index condition. This technique is invaluable for soft-delete patterns or multi-tenant systems where you filter by `tenant_id`. ### 13. MongoDB: Time-to-Live (TTL) Index for Expiring Data **Task:** Automatically delete documents older than 7 days. **Prompt (MongoDB shell):** ```javascript db.sessions.createIndex( { created_at: 1 }, { expireAfterSeconds: 604800 } // 7 days ); ``` **Example Result:** ```json { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } ``` **Explanation:** TTL indexes cause MongoDB to automatically delete documents when the indexed date field is older than `expireAfterSeconds`. The cleanup runs every 60 seconds. This is perfect for session stores, temporary tokens, or log rotation. Note: TTL indexes cannot be compound; they must be a single date field. ### 14. Redis: HyperLogLog for Unique Counts **Task:** Count unique visitors with minimal memory (1KB per key). **Prompt (redis-cli):** ```bash # Add visitor IDs PFADD visitors:2026-07-01 user:100 user:200 user:300 PFADD visitors:2026-07-01 user:100 # duplicate, ignored # Get approximate unique count PFCOUNT visitors:2026-07-01 ``` **Example Result:** ``` (integer) 3 ``` **Explanation:** HyperLogLog provides approximate unique counts with a standard error of 0.81%, using only 12KB per key. For comparison, a Redis Set storing 1 million 32-byte user IDs would use ~32MB. HyperLogLog trades perfect accuracy for massive memory savings — ideal for real-time analytics like daily active users. ### 15. PostgreSQL: BRIN Index for Large Time-Series **Task:** Index a table with billions of rows where queries filter by date range. **Prompt (SQL):** ```sql CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at) WITH (pages_per_range = 32); ``` **Example Result:** ```sql CREATE INDEX Time: 2.3s -- (on 500M row table) ``` **Explanation:** BRIN (Block Range INdex) indexes store the minimum and maximum value for each contiguous block of pages. For naturally ordered data (like timestamps), BRIN is thousands of times smaller than a B-tree (e.g., 100MB vs 10GB) and still provides excellent selectivity for range queries. Use `pages_per_range` to balance accuracy vs. size. ### 16. MongoDB: Shard Key Selection **Task:** Choose a shard key that avoids jumbo chunks and hotspots. **Prompt (MongoDB shell — check chunk distribution):** ```javascript sh.status(); ``` **Example Result:** ```json { "shards": { "shard01": { "ns": "ecommerce.orders", "chunks": 12, "dataSize": "2GB" }, "shard02": { "ns": "ecommerce.orders", "chunks": 11, "dataSize": "1.9GB" }, "shard03": { "ns": "ecommerce.orders", "chunks": 13, "dataSize": "2.1GB" } }, "balancer": { "currently_running": true } } ``` **Explanation:** An ideal shard key has high cardinality, low frequency, and monotonic change. Avoid using a monotonically increasing key (like `_id`) because all writes go to one shard. Instead, use a hashed shard key: `sh.shardCollection("ecommerce.orders", { customer_id: "hashed" })`. This distributes writes evenly across shards. ### 17. Redis: Cluster Resharding **Task:** Manually reshard a Redis Cluster to balance memory usage. **Prompt (redis-cli):** ```bash redis-cli --cluster reshard 192.168.1.10:6379 --cluster-from node1_id --cluster-to node2_id --cluster-slots 100 --cluster-yes ``` **Example Result:** ``` [OK] All 16384 slots covered Moving slot 0 from node1 to node2: 1.0 Moving slot 1 from node1 to node2: 1.0 ... ``` **Explanation:** Redis Cluster distributes 16384 hash slots across nodes. If one node runs out of memory, use `--cluster reshard` to move slots. The process is live (no downtime), but moving large keys can increase latency. Monitor with `redis-cli --cluster check 192.168.1.10:6379`. ### 18. PostgreSQL: Autovacuum Tuning for High-Write Workloads **Task:** Prevent transaction ID wraparound by tuning autovacuum for a busy table. **Prompt (SQL):** ```sql ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005, autovacuum_vacuum_cost_limit = 1000 ); ``` **Example Result:** ```sql ALTER TABLE ``` **Explanation:** By default, autovacuum triggers when 20% of rows change (`scale_factor = 0.2`). For a table with 100M rows, that means 20M dead rows before cleanup — causing bloat and transaction ID wraparound risk. Reducing `scale_factor` to 0.01 triggers vacuum after 1M dead rows. Increasing `vacuum_cost_limit` to 1000 speeds up cleanup. Monitor with `SELECT relname, n_dead_tup FROM pg_stat_user_tables;`. ### 19. MongoDB: Change Streams for Real-Time Sync **Task:** Capture all insert/update/delete operations for a collection. **Prompt (MongoDB shell):** ```javascript const changeStream = db.orders.watch([ { $match: { "fullDocument.status": "shipped" } } ]); changeStream.on("change", (change) => { printjson(change); }); ``` **Example Result:** ```json { "_id": { "_data": "8261..." }, "operationType": "update", "ns": { "db": "ecommerce", "coll": "orders" }, "fullDocument": { "_id": ObjectId("..."), "status": "shipped", "total": 150 }, "updateDescription": { "updatedFields": { "status": "shipped" } } } ``` **Explanation:** Change streams tail the MongoDB oplog and push real-time events. The `$match` pipeline filters only shipped orders. This is how you build event-driven architectures (e.g., trigger email notifications when an order ships). Change streams are persistent and resumeable using the `_id` resume token. ### 20. Redis: RediSearch Full-Text Search **Task:** Create a full-text index on product names and descriptions. **Prompt (redis-cli):** ```bash FT.CREATE idx_products ON HASH PREFIX 1 product: SCHEMA name TEXT WEIGHT 5.0 description TEXT WEIGHT 1.0 price NUMERIC SORTABLE # Search for products FT.SEARCH idx_products "wireless mouse" LIMIT 0 10 ``` **Example Result:** ```json { "total_results": 2, "results": [ { "id": "product:101", "name": "Wireless Bluetooth Mouse", "description": "Ergonomic wireless mouse with 6 buttons", "price": 29.99 }, { "id": "product:102", "name": "Gaming Wireless Mouse", "description": "High-DPI wireless mouse for gaming", "price": 49.99 } ] } ``` **Explanation:** RediSearch is a module that adds full-text search capabilities to Redis. It supports stemming, fuzzy matching, and field weighting. The `PREFIX 1 product:` tells RediSearch to index all keys starting with `product:`. This is much faster than traditional SQL `LIKE` queries for text search at scale. ## Conclusion Database prompts are not just about writing queries — they're about thinking in terms of data structures, access patterns, and system limits. The 20 prompts in this article cover the three pillars of modern data storage: relational (PostgreSQL), document-oriented (MongoDB), and in-memory (Redis). Each prompt addresses a real-world problem: slow queries, missing indexes, memory bloat, migration downtime, and real-time streaming. To truly master these tools, adopt a habit of always checking execution plans (`EXPLAIN ANALYZE`, `$explain`, `redis-cli --bigkeys`) before and after changes. Monitor your databases with tools like `pg_stat_statements`, MongoDB Atlas monitoring, or RedisInsight. And remember: the best index is the one you don't need — but when you do need one, use these prompts as your starting point. Now go optimize your databases. Your future self (and your users) will thank you.

20 Prompts for Database Work: PostgreSQL, MongoDB, and Redis Optimization

Comments

Recent articles

Cloudflare’s New Policy Forces AI Companies to Pay for Publishers’ Content: What It Means for the Web

Automate Payments with ASI Biont and YooKassa: A Step-by-Step Guide to No-Code AI Agent Integration

Fufjilm QuickSnap Retro: Waterproof and Black-and-White Editions for Developers Who Shoot Analog

AI Business Automation: The No-Code Course That Transforms Sales Funnels and CRM Workflows

10 Prompts for Security and DevSecOps: Audit, Scanning, and Policy Enforcement

Claude Sonnet 5 at 50% Less Cost: How to Double Your AI Gains with a Smarter Pick

10 Prompts for Code Migration: Python 2→3, JavaScript→TypeScript, REST→GraphQL

12 Prompts for React Native: Components, Navigation, API

Mastering Russian Civil Law: How the AI-Powered 'Civil Law of the Russian Federation' Course Accelerates Learning by 40%