15 Prompts for Working with Databases: PostgreSQL, MongoDB, Redis

Working with databases efficiently is a skill that separates great developers from average ones. Whether you're querying millions of rows, designing indexes, or migrating schemas, the right prompts can save hours of debugging. This collection of 15 prompts covers PostgreSQL, MongoDB, and Redis — the three most popular databases for modern applications. Each prompt includes a clear task, the exact prompt to use, and a realistic example result, drawn from official documentation (PostgreSQL Docs, MongoDB Manual, Redis Docs).

Let’s dive in.

1. Basic: Explain a Query Execution Plan (PostgreSQL)

Task: Understand how PostgreSQL executes a query to spot full table scans or missing indexes.

Prompt: Using the pg_stat_user_tables and pg_stat_statements (if enabled), explain how to read the output of EXPLAIN ANALYZE for a query that joins 'orders' and 'customers' on customer_id. Provide two common red flags.

Example Result:
- The planner may show a Seq Scan on orders — that’s a red flag for large tables.
- Another red flag: Sort Method: external merge — indicates insufficient work_mem.
- Use EXPLAIN (ANALYZE, BUFFERS) to see buffer usage.

2. Basic: Write a MongoDB Aggregation Pipeline for Customer Lifetime Value

Task: Calculate the total revenue per customer from an orders collection.

Prompt: Write a MongoDB aggregation pipeline that groups orders by 'customer_id', sums the 'total' field, sorts descending, and limits to top 10 customers. Use stages $group, $sort, $limit.

Example Result:

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

3. Basic: Use Redis as a Simple Cache (TTL Pattern)

Task: Implement a cache‑aside pattern with 5‑minute expiry for user profiles.

Prompt: Show Redis commands to check if a key 'user:123:profile' exists, fetch it, and if missing, set a serialized JSON with EX 300 (5 minutes). Write a Lua script that atomically does GET and conditional SET.

Example Result:

local val = redis.call('GET', KEYS[1])
if not val then
  val = ARGV[1]
  redis.call('SETEX', KEYS[1], 300, val)
end
return val

4. Intermediate: Optimize a PostgreSQL Query with Indexes

Task: Speed up a query filtering by created_at and status on a table with 10M rows.

Prompt: Given table 'events' with columns id, status (enum), created_at (timestamp), and a frequent query: SELECT * FROM events WHERE status = 'active' AND created_at > NOW() - INTERVAL '7 days'. What index would you create? Show the CREATE INDEX command and explain why a BRIN index might be better for large append‑only tables.

Example Result:
- A B‑tree index on (status, created_at) works well for equality + range.
- For append‑only logs, BRIN index on (created_at) is much smaller: CREATE INDEX events_brin_created ON events USING BRIN (created_at) WITH (pages_per_range = 32);
- Test with EXPLAIN (ANALYZE) to see index scan.

5. Intermediate: Migrate a PostgreSQL Schema with Zero Downtime

Task: Add a NOT NULL column to a large table without blocking writes.

Prompt: Explain the steps: add the column as nullable, backfill in batches, then alter to NOT NULL. Provide a batch backfill PostgreSQL function using a cursor with 1000 rows per batch and pg_sleep(0.1) to reduce load.

Example Result:

CREATE OR REPLACE FUNCTION backfill_column() RETURNS void AS $$
DECLARE
  batch_size INT := 1000;
  affected INT;
BEGIN
  LOOP
    UPDATE my_table SET new_col = 'default'
    WHERE id IN (SELECT id FROM my_table WHERE new_col IS NULL LIMIT batch_size FOR UPDATE SKIP LOCKED);
    GET DIAGNOSTICS affected = ROW_COUNT;
    IF affected = 0 THEN EXIT; END IF;
    COMMIT;
    PERFORM pg_sleep(0.1);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

6. Intermediate: Create a MongoDB Compound Index for a Reporting Query

Task: Speed up aggregation that filters by date and groups by category.

Prompt: For an aggregation pipeline that starts with $match on date range and then $group by category, what index would help? Provide the command to create a compound index on (date, category) and explain why index intersection might not be as efficient.

Example Result:

db.sales.createIndex({ date: -1, category: 1 })

• MongoDB uses the index for the $match and can then sort by $group key.
• Avoid separate indexes: MongoDB can intersect but often chooses a single index.

7. Intermediate: Use Redis Streams for a Message Queue

Task: Build a reliable task queue with at‑least‑once delivery using Redis Streams.

Prompt: Show commands to XADD a task, create a consumer group, XREADGROUP to read pending tasks, and XACK after processing. Explain how to handle dead letters by inspecting XPENDING.

Example Result:

XADD tasks * type email payload "{\"to\":\"user@example.com\"}"
XGROUP CREATE tasks email-workers $
XREADGROUP GROUP email-workers consumer1 COUNT 1 STREAMS tasks >
# process...
XACK tasks email-workers <message-id>

8. Advanced: PostgreSQL Full‑Text Search vs. GIN Index

Task: Optimize a LIKE '%keyword%' query on a text column for a search feature.

Prompt: Compare GIN index on tsvector vs. pg_trgm for wildcard searches. For a table with 1M product descriptions, write two indexes and test with EXPLAIN. Which is better for partial matches?

Example Result:
- GIN on to_tsvector('english', description) — great for whole words.
- CREATE INDEX trgm_idx ON products USING GIN (description gin_trgm_ops); — supports LIKE '%keyword%'.
- pg_trgm uses trigrams; it’s slower to build but faster for substring search.

9. Advanced: MongoDB Schema Design for Time‑Series Data (Bucket Pattern)

Task: Store sensor readings efficiently (millions per hour).

Prompt: Explain the bucket pattern: group readings by hour into one document. Provide a schema example with a pre‑allocated array of 60 readings per minute. Show a query to get last hour average temperature using $unwind and $group.

Example Result:

{
  sensor_id: 123,
  hour: ISODate("2026-07-28T10:00:00Z"),
  readings: [
    { min: 0, temp: 22.1 },
    // ... up to 60 entries
  ]
}

• Query: db.sensors.aggregate([{ $match: { sensor_id: 123, hour: { $gte: ... } } }, { $unwind: "$readings" }, { $group: { _id: null, avg: { $avg: "$readings.temp" } } }])

10. Advanced: Redis Cluster Data Distribution and Tagging

Task: Ensure related keys land on the same Redis node in a cluster.

Prompt: Explain Redis Cluster hash slots and hash tags. Show how to force keys {user:123}:profile and {user:123}:cart to be in the same slot by using curly braces {user:123}.

Example Result:

SET {user:123}:profile "..."
SET {user:123}:cart "..."
# Both keys hash to same slot because the tag {user:123} is identical.

11. Advanced: Transactional Multi‑Insert with Error Handling (PostgreSQL)

Task: Insert 100k rows safely with batch commit and rollback on error.

Prompt: Write a PL/pgSQL block that inserts rows in a LOOP with SAVEPOINT per batch of 1000. If a duplicate key violates a unique constraint, log the error and continue.

Example Result:

DO $$
DECLARE
  i INT;
  batch_count INT := 0;
BEGIN
  FOR i IN 1..100000 LOOP
    SAVEPOINT batch_sp;
    BEGIN
      INSERT INTO target (id, val) VALUES (i, 'data');
    EXCEPTION WHEN unique_violation THEN
      ROLLBACK TO batch_sp;
      RAISE NOTICE 'Duplicate id %', i;
    END;
    batch_count := batch_count + 1;
    IF batch_count % 1000 = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END $$;

12. Advanced: MongoDB $lookup Performance Tuning

Task: Join two collections (orders and customers) efficiently.

Prompt: Explain why $lookup can be slow and how to improve it: ensure a foreign field index on the 'from' collection, use $match before $lookup, and consider embedding for small subdocuments. Show a pipeline that reduces documents early.

Example Result:

db.orders.aggregate([
  { $match: { status: "completed", date: { $gte: startDate } } },
  { $lookup: { from: "customers", localField: "customer_id", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" },
  { $project: { "customer.name": 1, total: 1 } }
])

• Index on customers._id is automatic; but also index on orders.customer_id if used elsewhere.

13. Expert: PostgreSQL Table Partitioning and Pruning

Task: Partition a 100M‑row table by month and verify partition pruning.

Prompt: Show CREATE TABLE with RANGE partitioning on created_at. Then run a query with a filter on created_at and use EXPLAIN (VERBOSE) to confirm only relevant partitions are scanned.

Example Result:

CREATE TABLE orders (
  id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  ...
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_07 PARTITION OF orders FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

• Query: EXPLAIN (VERBOSE) SELECT * FROM orders WHERE created_at >= '2026-07-01' AND created_at < '2026-07-15';
• Output: Only orders_2026_07 scanned (Seq Scan on orders_2026_07).

14. Expert: MongoDB Change Streams for Real‑Time Sync

Task: React to inserts/updates in real time using Change Streams.

Prompt: Write a Node.js script using the MongoDB driver that listens to a collection 'orders' and logs any new or updated order. Include resume after failure using resumeToken.

Example Result:

const pipeline = [];
const changeStream = collection.watch(pipeline, { fullDocument: 'updateLookup' });
changeStream.on('change', (change) => {
  if (change.operationType === 'insert') {
    console.log('New order:', change.fullDocument);
  }
});

15. Expert: Redis Lua Scripting for Atomic Inventory Deduction

Task: Deduct stock only if sufficient, in one atomic operation.

Prompt: Write a Lua script that takes key (product:stock), decrement value, and returns 0 if insufficient or 1 if successful. Explain why using Lua avoids race conditions compared to WATCH.

Example Result:

local stock = redis.call('GET', KEYS[1])
if not stock or tonumber(stock) < tonumber(ARGV[1]) then
  return 0
else
  redis.call('DECRBY', KEYS[1], ARGV[1])
  return 1
end

• Call: EVAL script 1 product:stock 1

Conclusion

Mastering these prompts will help you write faster queries, design scalable schemas, and handle migrations with confidence. Start by trying the basic prompts in your local development environment, then gradually adopt the advanced techniques as your data grows. For official documentation, always refer to PostgreSQL docs, MongoDB manual, and Redis docs. Practice each prompt with your own datasets — the best learning comes from real debugging sessions.