We’re managing over 8 million quote records in our CloudSuite instance, and query performance has degraded significantly. Our compliance team needs 7-year retention for audit purposes, but 95% of queries only access quotes from the last 18 months.
I’m exploring a tiered storage architecture with database partitioning by fiscal year. The idea is to keep recent quotes in hot storage with read-write access, while moving older records to cold storage layers. We’re also considering AWS S3 integration for the oldest tiers that only need read-only archive access.
Has anyone implemented a similar archiving strategy? Particularly interested in how you handled compliance audit tables that reference archived quotes, and whether you saw measurable performance improvements after implementing partitioning.
Consider materialized views for your most common audit queries that span multiple fiscal years. We created monthly aggregated views that pre-join quote headers with their audit trail data. These views refresh nightly and cover 90% of our compliance reporting needs without touching the archived partitions. It’s a middle ground that keeps audit queries fast while still maintaining the full detail in cold storage for deep-dive investigations.
From a compliance perspective, make sure your audit tables maintain referential integrity even when quotes move to cold storage. We use a separate audit log database that stores quote snapshots with their full context at archive time. This way auditors can reconstruct the complete quote state without needing to query across storage tiers. The snapshot approach also helps with compliance reporting since you’re not joining across hot and cold storage constantly.
Watch out for partition pruning failures if your WHERE clauses don’t align with partition keys. We saw cases where queries against quote line items weren’t pruning properly because the partition key was on the header table. Had to denormalize the fiscal year into the line item table to get full pruning benefits.