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.