TL;DR: Measure actual access frequency per data age band first; then move cold history to cheaper tiers using table partitioning plus a migration path that keeps old data queryable, either natively (partitioned tables to slower tablespaces or object-storage engines) or via an archive service with a documented recall SLA. Never tier anything whose access pattern you have not verified.
How to approach it
Refuse to tier by intuition. Ask what evidence exists about reads by age, whether queries join across the proposed boundary, and what latency the business tolerates for historical lookups. Those three answers decide between native partitioning, an archive database, or object storage with query-on-demand.
A strong answer
Audit before architecture. Pull access statistics: pg_stat_user_tables gives sequential and index scan counts, but per-row-age analysis needs application-side instrumentation or sampling of query logs joined against row timestamps. The typical finding justifies the whole exercise: some large fraction of rows older than a few months receive almost no reads, while a small hot window absorbs nearly all traffic. If the data does not show that shape, stop here; tiering uniformly-accessed data adds complexity and saves little.
Native tiering where supported. PostgreSQL declarative partitioning can keep recent and historical rows behind one logical table. Self-managed deployments can place partitions in tablespaces on different storage, but managed services may restrict that choice. Converting an existing table requires a migration plan, locking analysis and validation of indexes, uniqueness, foreign keys and query plans. It is not automatically a zero-application-change operation. See PostgreSQL partitioning.
Archive services for cold data. Beyond a threshold (years-old records kept for compliance and occasional investigation), a database row costs more than its value. Move those to object storage in columnar format (Parquet) queried on demand with Athena/DuckDB-style engines, or into a cheap archive database. The contract matters more than the technology: define recall latency ("full record available within five minutes"), keep a thin index or ID map in the primary database so applications can discover archived IDs, and version the export format. Compliance requirements decide retention mechanics; engineering decides retrieval ergonomics.
| Tier | Access profile | Home | Recall expectation |
|---|---|---|---|
| Hot | Current workload | Fast NVMe volume | Milliseconds |
| Warm | Months-old, occasional reads | Slower tablespace or secondary | Tens of milliseconds |
| Cold | Rarely read, compliance-bound | Object storage, columnar | Minutes, on demand |
The economics check. Compare actual storage rates, retained copies, retrieval and request charges, migration effort and ongoing query costs over a stated period. There is no universal fraction of data that makes tiering worthwhile. Use measured access by age and test recall latency before moving a cohort.
What interviewers probe next
"What breaks first after tiering?" Cross-tier joins and reports that silently full-scan archives. Mitigate with documentation, query-time hints, and monitoring for queries crossing the boundary unexpectedly.
"How do you keep the archive trustworthy?" Checksummed exports, restore drills into a scratch environment quarterly, and retention policy encoded as code rather than tribal memory.
"Why not just delete old data?" Sometimes correct! But deletion decisions belong to legal and product requirements, not cost pressure. Present it as the cheapest tier of all when retention allows.
Common mistakes
Tiering by age alone without access evidence, then discovering a monthly report reads every old row through the slow path.
Moving data to a format nobody can query back under time pressure. An archive you cannot search is a deletion with extra steps.
Forgetting the indexes follow the data: an unindexed archive forces full scans precisely during the incidents when patience is lowest.