50 AI Prompts for PostgreSQL: Queries, Indexes, and Schema Design

Act as a PostgreSQL schema design expert.

Task:
Design the database schema for a multi-tenant SaaS billing system.

Entities needed:
- tenants: id, name, slug (unique), plan (free/pro/enterprise),
  created_at, is_active
- users: id, tenant_id (FK), email (unique per tenant), role,
  created_at, last_login_at
- subscriptions: id, tenant_id (FK), plan_name, status
  (active/cancelled/past_due), current_period_start, current_period_end,
  stripe_subscription_id (unique)
- invoices: id, tenant_id (FK), subscription_id (FK), amount_cents,
  currency, status (paid/unpaid/void), due_date, paid_at

Requirements:
- Use UUID primary keys with gen_random_uuid() default
- All timestamps must be timestamptz
- Add CHECK constraints for enum-like columns (plan, status, role)
- Add appropriate foreign key ON DELETE behavior
- Add indexes for all FK columns and frequently queried columns
- Include a unique constraint for email scoped to tenant_id

Output: complete SQL migration file

Act as a PostgreSQL expert specializing in analytical queries.

Schema:
[paste your CREATE TABLE statements here]

Task:
Write a PostgreSQL query for this dashboard metric:

Monthly Revenue Report:
- Group invoices by year and month
- Show: year_month, total_revenue_cents, invoice_count,
  paying_tenant_count, avg_invoice_value_cents
- Include only invoices with status = 'paid'
- Add a running_total_cents column (cumulative revenue month over month)
- Add a mom_growth_pct column (month-over-month percentage growth)
- Order by year_month ascending
- Show the last 12 months only

Use: CTEs for readability, window functions for running total and MoM growth
Explain each CTE step in a comment above it

Act as a PostgreSQL performance expert.

Task:
Recommend indexes for these frequently-run queries on our database.

Schema:
[paste your CREATE TABLE statements here]

Queries that are slow (each runs thousands of times per day):
1. SELECT * FROM orders WHERE tenant_id = $1 AND status = 'pending'
   ORDER BY created_at DESC LIMIT 50;

2. SELECT user_id, COUNT(*) as order_count, SUM(total_cents) as revenue
   FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
   GROUP BY user_id ORDER BY revenue DESC;

3. SELECT * FROM products WHERE metadata @> '{"category": "electronics"}'
   AND is_active = true;

For each index:
- Write the CREATE INDEX statement
- Explain which query it helps and why
- Estimate the index type (B-tree, GIN, partial)
- Note any trade-offs (index size, write overhead)

Act as a PostgreSQL JSONB and full-text search expert.

Schema:
CREATE TABLE articles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  search_vector TSVECTOR GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || body)
  ) STORED,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Sample metadata structure:
{"tags": ["tech", "ai"], "author_id": "uuid", "reading_time": 5, "published": true}

Tasks:
1. Find all published articles tagged with 'ai' OR 'ml', ordered by relevance to
   the search term 'neural network'

2. Return the top 5 tags by article count across all published articles
   (tags is a JSONB array)

3. Create the optimal GIN index for full-text search on search_vector
   and another GIN index for JSONB metadata queries

Use: PostgreSQL 16, explain the tsvector and tsquery operators used

Act as a PostgreSQL production operations expert.

Task:
Write a zero-downtime migration to add a new column to a large table.

Table: orders (50 million rows, high write volume, cannot be locked)
Change: add column fulfillment_status TEXT NOT NULL DEFAULT 'pending'
        with a CHECK constraint: ('pending', 'shipped', 'delivered', 'cancelled')

Constraints:
- The migration must not lock the table for more than a few milliseconds
- The application must continue writing to the table during migration
- The column must have a NOT NULL constraint after migration is complete
- PostgreSQL version: 15

Provide:
1. The safe migration steps in order with explanation for each
2. The SQL for each step
3. How to verify each step completed successfully
4. How to roll back if something goes wrong