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):

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:

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):

db.system.profile.find({
  millis: { $gte: 100 }
}).sort({ millis: -1 }).limit(10).pretty();

Example Result:

{
  "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):

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):

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:

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):

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):

[
  {
    "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):

db.orders.createIndex({
  status: 1,
  created_at: -1
}, {
  name: "idx_status_created"
});

Example Result:

{
  "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):

-- 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:

redis-cli --eval atomic_stock_decrement.lua product:100:stock , 2

Example Result:

{
  "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):

-- 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:

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):

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):

{
  "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):

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $sort: { total: -1 } },
  { $group: { _id: "$customer_id", totalSpent: { $sum: "$total" } } },
  { $sort: { totalSpent: -1 } },
  { $limit: 10 }
]);

Optimized Prompt:

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $group: { _id: "$customer_id", totalSpent: { $sum: "$total" } } },
  { $sort: { totalSpent: -1 } },
  { $limit: 10 }
]);

Example Result:

[
  { "_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):

# 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:

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):

CREATE INDEX idx_users_active
ON users(email)
WHERE status = 'active' AND deleted_at IS NULL;

Example Result:

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):

db.sessions.createIndex(
  { created_at: 1 },
  { expireAfterSeconds: 604800 }  // 7 days
);

Example Result:

{
  "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):

# 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):

CREATE INDEX idx_logs_created_brin
ON logs USING BRIN(created_at)
WITH (pages_per_range = 32);

Example Result:

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):

sh.status();

Example Result:

{
  "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):

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):

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_limit = 1000
);

Example Result:

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):

const changeStream = db.orders.watch([
  { $match: { "fullDocument.status": "shipped" } }
]);

changeStream.on("change", (change) => {
  printjson(change);
});

Example Result:

{
  "_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):

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:

{
  "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.