Database45-75 min

PostgreSQL Indexing Basics

Use `EXPLAIN ANALYZE`, add targeted indexes, and verify that query plans improve for real application queries.

PostgreSQLSQLPrisma

Prerequisites

A slow query or endpoint to investigate.
Access to run SQL against staging or local data.
Enough sample data to make query plans realistic.

Plan the implementation

Start by choosing the exact page, route, API, or deployment surface you want to improve. A narrow target makes the implementation measurable and easier to verify.

Write down the current behavior and the user-facing problem it creates.
Pick one measurable success signal such as bundle size, latency, error rate, security coverage, or UI responsiveness.
Identify the files, routes, providers, and environment variables involved.
Create a rollback note before changing production-sensitive configuration.

Set up the required tools

Install or configure only the tools needed for this implementation. Keep config close to the feature so future developers can find the moving parts quickly.

Implementation snippet

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = '...' ORDER BY created_at DESC;

Checklist

Dependencies are added to the correct workspace package.
Environment variables are documented in `.env.example` when needed.
Local development still starts without production-only secrets.
The change is small enough to review in one pull request.

Implement the core pattern

Capture the exact SQL query from logs or ORM debugging.
Run `EXPLAIN ANALYZE` before adding an index.
Create an index that matches filters and sort order.
Use concurrent index creation for large production tables when possible.
Run the same query plan again and compare actual time and scan type.