We dropped 8 indexes and added 3 without a single production statistic
A round of database work in our app fixed a handful of genuinely expensive reads and rationalised the indexes on the three hottest tables. Every finding came from reading application code and the SQL it generates, because pg_stat_statements was not enabled and there were no production query statistics to read at all. That worked better than it should have, and it is still the wrong way round. So…
A recent database optimization effort in the application resulted in the removal of eight indexes and the addition of three, with no impact on production statistics. The changes were primarily driven by analyzing the application code and SQL queries, as no production query statistics were available. The primary goal was to identify and remove expensive reads and redundant indexes that were not being utilized by any queries in the codebase.
The migration process dropped indexes that were either prefix-redundant, duplicates of implicit unique indexes, or unused by any query. The added indexes were designed to support ascending order for better performance when querying the most frequently accessed data. Additionally, the implementation addressed a Drizzle-specific issue where the $count function was emitting an unfiltered count(*) over the entire table without a filter, leading to slower performance.
The changes also included updating the dashboard layout to use the id-keyed cached row instead of the email-keyed one, reducing the number of reads of the same row per render. Lastly, the analytics engine was enhanced with an optional preloadedSessions argument to fetch the necessary rows alongside its own response, eliminating the need for separate sequential fetches.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
