Hacker News new | ask | show | jobs
by pilif 3478 days ago
If you combine the partitions with tablespaces, you can put tables on multiple disks. Let's say you keep a record of all orders you have processed. During the day-to-day operation, you need, say, the last 2 months of data all the time, but the older data you only need for reporting here and then.

By partitioning, you can keep the recent data on a fast disk and the older data on slower disks while still being able to run reports over the whole dataset.

And once you really don't need the old data any more, you can just bulk-remove partitions which will get rid of everything in that partition without touching anything else.

Even then you don't split over tablespaces: By keeping the data that's changing often separate from the data that's static and is only read, then you gain some advantages in index management and disk load when vacuum runs as it mostly wouldn't have to touch the archive partitions.