Indexing is the single most effective way to speed up a slow database. But more indexes aren't always better—they slow down writes.
1. Covering Indexes
Create an index that includes all the columns you're querying for. This allows the database to answer the query from the index without ever touching the actual table.
2. Partial Indexes
Only index the rows that matter. For example, if you frequently search for active users, only index where status = 'active'. This keeps the index small and fast.
3. Composite Indexes
The order of columns in an index matters. Always put the column with the highest selectivity (most unique values) first.
4. GIN for JSONB
In PostgreSQL, use GIN indexes to make your JSONB fields searchable at lightning speed.
A well-indexed database is the difference between a "laggy" app and a "premium" experience.