Design a normalized PostgreSQL schema for [describe the application — e.g., a project management tool, an e-commerce platform, a social app]. Produce the full CREATE TABLE DDL with appropriate types — timestamptz (not timestamp) for time, integer minor units or numeric for money (never float), text over varchar(n) unless a real limit exists, and uuid or bigint identity primary keys. Model the relationships correctly: one-to-one, one-to-many, and many-to-many via junction tables with composite keys, each foreign key declaring explicit ON DELETE behavior. Enforce real invariants at the schema level with NOT NULL, UNIQUE, CHECK, and sensible DEFAULTs rather than leaving them to application code. Include created_at/updated_at (with an updated_at trigger), and add the indexes the expected query patterns require — including partial and composite indexes where they pay off. Then explain each normalization decision up to 3NF, and call out where you would DELIBERATELY denormalize for read performance and why. Note any soft-delete, audit, or multi-tenancy columns the domain implies. Done when the DDL runs cleanly on Postgres, every relationship and invariant is enforced by the schema itself, and the indexing demonstrably matches the access patterns you describe.
#29Database
Complex Query Builder
Write an optimized PostgreSQL query that [describe the need — e.g., "the top 10 customers by total order value in the last 90 days, with their most recent order date and order count, excluding cancelled orders"]. Use CTEs for readability and choose the right tools — appropriate aggregations, window functions, and join strategies — avoiding correlated subqueries where a join or window function is faster. Provide the query, then walk through its EXPLAIN (ANALYZE, BUFFERS) plan: identify whether it does a sequential scan, which join algorithm the planner picked, and whether any sort or hash spills to disk, then propose the specific indexes (composite or covering, with column order justified) that would change the plan and the effect you expect. Handle the correctness edge cases explicitly: NULLs in aggregates, ties at the cutoff, time-zone boundaries on the date filter, and an empty result set. If portability matters, note where MySQL differs (window-function and CTE support, LIMIT vs FETCH). Done when the query is correct on those edge cases, the EXPLAIN plan shows index access rather than a full scan on the large tables, and every recommended index is justified by something visible in the plan.
#30Database
Database Migration System
Build a database migration system for a Node + TypeScript app on PostgreSQL — either implement a small runner or wrap a tool like node-pg-migrate or Knex, but explain the mechanics either way. Requirements: timestamp-prefixed, ordered migration files, each exporting up and down; a migrations table tracking applied versions WITH a checksum so a file modified after being applied is detected; each migration runs inside a transaction where possible (and call out the DDL that can't be transactional, like CREATE INDEX CONCURRENTLY); and CLI commands for migrate up, down (one step), status, and create. Stress production safety: forward-only discipline, the expand/contract pattern for breaking changes (add column → backfill → switch reads/writes → drop old) so deploys stay zero-downtime, locking considerations when altering large tables, and a dry-run mode that prints the SQL. Provide example migrations: create a users table, then add a non-blocking index. Done when up/down/status/create all work, a partially failed migration leaves both the database and the tracking table in a consistent state, the checksum catches a tampered migration, and the docs show how to make a breaking schema change without downtime.
#31Database
Query Performance Optimizer
A PostgreSQL query is slow (over 2 seconds). Here is the query: [paste query] and the schema with its current indexes: [paste schema]. Optimize it METHODICALLY. Start from EXPLAIN (ANALYZE, BUFFERS) and show me how to read it to find the ACTUAL cost driver — a sequential scan on a big table, a bad row estimate from stale statistics, an expensive nested-loop join, a sort spilling to disk, or an N+1 pattern in the calling code. Then propose concrete changes ranked by impact: the specific index to add (composite/partial/covering, with the column order justified), query rewrites (CTE vs inline, subquery-to-join, keyset/seek pagination instead of OFFSET on deep pages), avoiding functions wrapped around indexed columns that defeat the index, and refreshing planner statistics. Show the before-and-after plan and the measured timings, and warn me about the write-cost and maintenance trade-offs of each new index. Done when the EXPLAIN plan switches from a full scan to index or index-only access on the hot tables, the measured time drops materially, and the rewritten query is verified to return identical results to the original.
#32Database
Data Seeding Script
Write a DETERMINISTIC database seeding script for a Node + TypeScript app on PostgreSQL. Generate realistic, referentially consistent data: 50 users with varied names, emails, and registration dates spread across the last 6 months; 200 posts distributed across users on a power-law (a few prolific authors, a long tail) with realistic titles and bodies; 500 comments across those posts including threaded replies; plus tags and categories with their many-to-many links. Use faker with a FIXED seed so every run produces identical data for reproducible tests, and insert in dependency order inside a transaction using batched/bulk inserts for speed — not one INSERT per row. Respect every foreign key and constraint, and make it idempotent: a reset command truncates with RESTART IDENTITY CASCADE and re-seeds cleanly. Keep the volume and the seed configurable via flags or env, and guard the script so it can NEVER run against a production database. Done when running it twice yields byte-identical data, all foreign keys and constraints are satisfied, the reset command leaves a clean re-seeded database, and it refuses to run outside a development environment.