Making large datasets usable.

01 / Context

The problem

An industry-message page averaged 32.2 seconds to load: its query combined and sorted all matching inbound and outbound messages before applying pagination. Separately, counting approximately 26 million settlement records caused another view to time out, while redundant indexes consumed production storage.

02 / Responsibility

My contribution

I bounded expensive message queries, replaced full-table counts with lazy pagination, and removed redundant indexes across two production estates.

03 / Approach

Key decisions

For message browsing, I limited each ordered input to enough rows for the requested page before combining results, and skipped the union for single-direction searches. For settlement browsing, I fetched a page plus one row to detect whether another page existed, giving up an exact total count to avoid scanning the table on every visit. I added indexes for common filters and reviewed existing indexes against query patterns, usage and uniqueness requirements before removing redundant ones.

1. 01

   Bound the query

   Limit each ordered message source before combining results for the requested page.

2. 02

   Count only what is needed

   Fetch a page plus one row instead of counting the full settlement table.

3. 03

   Review the indexes

   Add useful filter indexes and remove redundant ones while preserving constraints and query coverage.

04 / Result

The result

The recorded message-query benchmark reduced execution time from about 63 seconds to 16.5 milliseconds. Lazy pagination eliminated the settlement-view timeouts. Separately, removing 11 redundant indexes reclaimed approximately 1.43 TB across two production PostgreSQL estates.

Related work

• Staging a uniqueness changeTo support active and reactive settlement records on the same date, I added the new uniqueness constraint, updated application lookups, then removed the old constraint. The three-stage rollout kept the schema compatible while the application changed.