Re:Squeeze the hell out of the system you have
by wizofaus on Saturday August 12, 2023 @04:23PM
(#37103917)
If your primary issue is read/query performance, then sure, well-designed indexes, partitioning and, as you say, carefully constructed WHERE clauses are often enough to maintain decent performance even with millions of records. But if you then to do deletions or bulk updates or schema transformations you're in for some serious downtime.
I'd amend that to "don't let your scan coverage get too big". Understanding how much data must be loaded in memory and compared is essential to writing performant database applications. And yes, those characteristics change over time as the data grows, so there may be a one-size-fits-all solution. But "table too large" can pretty much always be solved by adding better indexes or by partitioning the table, and making sure common queries (query's?) hit only one partition.
As a simple example: a lot of queries can be optimized to include "WHERE fiscal_year = $current". But you need to design your database and application up front to make use of such filtered indexes.