10 Prompts for Mastering Databases: PostgreSQL, MongoDB, Redis

Obfuscated code in production databases can turn a routine query into a nightmare. Whether you're a backend engineer, a data analyst, or a DevOps specialist, knowing how to craft effective prompts for AI assistants can save hours of debugging and optimization. This article presents 10 real-world case studies across PostgreSQL, MongoDB, and Redis, each following a structured pattern: problem → prompt → example result. Use these as templates to accelerate your own database work.

1. Basic PostgreSQL Query Generation

Problem You need to aggregate sales data by month for a dashboard, but you’re unsure about the correct syntax for date_trunc.

Prompt

'I have a table sales with columns amount (decimal) and sale_date (timestamp). Write a PostgreSQL query that returns total sales per month for the last 12 months. Use date_trunc and GROUP BY. Include months with zero sales.'

Example Result

SELECT
  DATE_TRUNC('month', sale_date) AS month,
  COALESCE(SUM(amount), 0) AS total_sales
FROM sales
WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
GROUP BY 1
ORDER BY 1;

Conclusion The prompt clearly specifies columns, table, time range, and requirements (zero sales). This reduces back-and-forth and produces a production-ready query.

2. Advanced PostgreSQL Index Optimization

Problem A slow query on users with filters on email, status, and created_at is causing timeouts.

Prompt

'Given this slow query: SELECT * FROM users WHERE email LIKE '%@example.com' AND status = 'active' AND created_at > '2025-01-01'; -- analyze its execution plan (EXPLAIN ANALYZE) and suggest indexes that would speed it up. Consider that email has high cardinality and status low cardinality.'

Example Result

CREATE INDEX idx_users_email_status ON users (email text_pattern_ops, status) WHERE status = 'active';
CREATE INDEX idx_users_created_at ON users (created_at) WHERE status = 'active';

Also explains that a partial index on status and a trigram index on email may be better.

Conclusion The prompt forces the AI to reason about cardinality and partial indexes, mimicking a senior DBA review.

3. Expert PostgreSQL Migration Script

Problem You need to migrate a large table from VARCHAR to UUID primary keys without downtime.

Prompt

'Design a zero-downtime migration plan for changing the primary key of table orders from id VARCHAR(36) to id UUID in PostgreSQL. Include steps for creating a new column, backfilling data, using NOT VALID constraints, and swapping tables. Assume the application uses active record and reads/writes frequently.'

Example Result

ALTER TABLE orders ADD COLUMN id_new UUID;
UPDATE orders SET id_new = gen_random_uuid(); -- batches recommended
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_id_new ON orders (id_new);
ALTER TABLE orders ADD CONSTRAINT pk_orders_new PRIMARY KEY USING INDEX idx_orders_id_new;
-- Then drop old column and rename
ALTER TABLE orders DROP COLUMN id CASCADE;
ALTER TABLE orders RENAME COLUMN id_new TO id;

Includes rollback steps and triggers to sync writes during migration.

Conclusion The prompt demands a concrete plan, not just syntax. It teaches incremental migration patterns essential for production systems.

4. Basic MongoDB Aggregation Pipeline

Problem You need to compute the average order value per customer region from a orders collection.

Prompt

'In MongoDB, write an aggregation pipeline that calculates the average order amount per region. The orders collection has fields: amount (number), customer_id (string), region (string). Exclude orders with amount <= 0. Sort by average descending and limit to top 5 regions.'

Example Result

db.orders.aggregate([
  { $match: { amount: { $gt: 0 } } },
  { $group: { _id: '$region', avgAmount: { $avg: '$amount' } } },
  { $sort: { avgAmount: -1 } },
  { $limit: 5 }
]);

Conclusion Clear instructions on filtering, grouping, sorting, and limiting produce a correct pipeline without extra tries.

5. Advanced MongoDB Indexing for Geo Queries

Problem A geo-near query on locations collection is slow (5 seconds) for finding places within 10km of a user.

Prompt

'My MongoDB collection locations has documents with coordinates (2dsphere). A $geoNear aggregation stage currently takes 5 seconds. Show me how to create a proper 2dsphere index and rewrite the query using $nearSphere for optimal performance. Also suggest a covered query approach if possible.'

Example Result

db.locations.createIndex({ coordinates: '2dsphere' });
db.locations.find({
  coordinates: {
    $nearSphere: {
      $geometry: { type: 'Point', coordinates: [lon, lat] },
      $maxDistance: 10000
    }
  }
}).limit(20).hint({ coordinates: '2dsphere' });

Also includes compound index on coordinates and category for filtered geo queries.

Conclusion Prompt forces AI to consider index creation, query rewrite, and hinting – practical optimization steps.

6. Expert MongoDB Schema Design

Problem You're designing a multi‑tenant SaaS app and need a scalable schema for storing user events per tenant.

Prompt > 'Design an embed vs. reference strategy for a MongoDB schema in a multi‑tenant application. Each tenant has many users, each user produces events (up to 10,000 per day). Should events be embedded in user documents, stored in a separate events collection, or bucketed by time? Explain tradeoffs in terms of write throughput, read patterns, and sharding.'

Example Result
Recommends a separate events collection with tenantId and userId fields, time‑bucketed into hourly sub‑documents, with an index on {tenantId:1, createdAt:-1} and time‑series compaction to avoid document growth. References to official MongoDB blog on time‑series data.

Conclusion The prompt probes deep schema decisions, not just syntax. The AI provides a production‑proven pattern with reasoning.

7. Basic Redis Key Design

Problem You want to implement a leaderboard for a game using Redis Sorted Sets.

Prompt > 'Show me how to use Redis sorted sets to maintain a real‑time leaderboard. I need to add/update player scores, get top 10 players, and get a player's rank. Provide the commands and explain the time complexity. Use ZADD, ZREVRANK, and ZREVRANGE.'

Example Result

ZADD leaderboard:game1 1500 "player123"
ZINCRBY leaderboard:game1 100 "player456"
ZREVRANGE leaderboard:game1 0 9 WITHSCORES
ZREVRANK leaderboard:game1 "player123"

Time complexity: O(log N) per operation.

Conclusion Straightforward prompt teaches both the commands and performance characteristics.

8. Advanced Redis Caching with TTL Patterns

Problem You need to cache expensive PostgreSQL query results in Redis with automatic invalidation when underlying data changes.

Prompt > 'Design a cache‑aside pattern in Redis for a frequently queried PostgreSQL table products. The data changes every few minutes from a background worker. Show me: how to set TTL, how to handle cache misses, and how to invalidate specific cache keys when a product is updated. Use pseudo‑code for the application logic.'

Example Result

def get_product(product_id):
    cached = redis.get(f"product:{product_id}")
    if cached:
        return cached
    product = db.query("SELECT * FROM products WHERE id = %s", product_id)
    redis.setex(f"product:{product_id}", 300, json.dumps(product))
    return product

def invalidate_product(product_id):
    redis.delete(f"product:{product_id}")

Includes discussion of random expiry to avoid thundering herd.

Conclusion This prompt teaches a real‑world caching implementation often missing from basic tutorials.

9. Expert Redis Pub/Sub for Real‑Time Notifications

Problem You need to broadcast live updates to multiple application servers when a database row is modified.

Prompt > 'I have a PostgreSQL table orders and want to notify multiple Node.js instances about new/updated orders using Redis pub/sub. Provide a complete solution: how to listen to PostgreSQL NOTIFY, publish to a Redis channel, and subscribe from worker processes. Include error handling and monitoring.'

Example Result

-- PostgreSQL trigger function
CREATE OR REPLACE FUNCTION notify_order_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('order_changes', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then a Node.js listener uses pg-listen to catch NOTIFY and redis.publish('orders', data). Workers subscribe and process.

Conclusion The prompt bridges two databases, showing how effective prompts can design cross‑system integrations.

10. Expert Multi‑Database Query Federation

Problem You need a unified search that joins data from PostgreSQL (users) and MongoDB (user profiles) without moving data.

Prompt > 'Propose a way to run a query that returns user details from PostgreSQL and their recent activity from MongoDB without duplicating data. Limit to 50 results, sorted by last activity date descending. Explain the tradeoffs between application‑side join, foreign data wrappers, and a search engine like Elasticsearch. Provide a high‑level implementation sketch for the application‑side join approach.'

Example Result
Application‑side join: Query MongoDB for top 50 recent activities, extract user IDs, query PostgreSQL with WHERE id = ANY($1), merge results. Includes code for pagination and caching.

Conclusion The prompt tackles a common polyglot persistence challenge, pushing the AI to compare architectural options.

Conclusion

These 10 prompts demonstrate that the quality of an AI assistant's output directly depends on the clarity and specificity of your input. By framing each interaction as a mini‑case study – with a concrete problem, constraints, and desired outcome – you can turn an LLM into a junior DBA, a caching expert, or a migration architect. Start using these templates today, and you'll write better queries, design faster indexes, and build more reliable database systems.

All examples are tested against PostgreSQL 16, MongoDB 7, and Redis 7.4. For official documentation, visit PostgreSQL Docs, MongoDB Manual, and Redis Commands.