Large datasets punish unclear thinking. Performance requires a disciplined approach, and this case study describes the method Bitnwise uses for it in PostgreSQL and SQL Server environments.
Context
Operational and analytical databases in enterprise settings hold large tables that many processes read and write: overnight batches, reporting procedures, ad hoc analysis and applications, often at the same time. As data grows, queries that were once fast become the bottleneck of the whole schedule.
Challenge
A query can be technically correct and still operationally unacceptable. Enterprise environments need results that are fast, explainable and stable. Slow procedures delay reports and decisions, block other work and cost compute. Quick fixes, such as one more index or a hint, often move the problem elsewhere instead of solving it.
Approach
Optimisation starts with evidence, not guesses:
- Read the execution plan. Find where time is actually spent: scans, joins, sorts, spills to disk.
- Understand cardinality. Check how many rows each step really handles against what the planner expects, and fix the statistics or the query when they disagree.
- Design indexes for access patterns. Index for how data is actually filtered and joined, and remove indexes that only cost writes.
- Rewrite the logic where it matters. Replace row-by-row processing with set-based SQL, break very large statements into steps the planner can handle, and avoid repeating work.
- Keep lineage and documentation. Every optimised procedure stays readable and documented, so the next change does not undo the gain.
- Measure before and after. Compare run times and resource use on realistic data before a change goes live.
Outcome
Better performance, clearer ownership and more reliable analytics workflows. Faster procedures free up the processing window, and documented logic means the team can maintain the result without relying on one person. The same discipline supports the credit risk analytics platform work and Bitnwise's data and backend services.
Working together
If a database or a set of procedures has become the slow part of your process, get in touch.