How do you choose database indexes for a generation portal?

How do you choose database indexes for a generation portal?

Indexes should support the queries the application actually performs. Start with a list of important reads rather than indexing every field.

Write representative queries

Examples include a user’s projects ordered by creation time, active jobs for a worker, and lookup by an external operation identifier. Record filters, sort order, and expected result size. Include ownership in the access pattern so a fast query does not encourage unsafe unscoped reads.

Measure and maintain

Evaluate query plans and representative data volumes with the database’s tools. Extra indexes use storage and add maintenance work to writes. Use uniqueness constraints where they enforce real invariants, such as a scoped idempotency key. Plan index creation and rollout separately from application startup when appropriate. Revisit the design when query behavior changes, and test pagination with equal timestamps rather than relying on accidental row order.