Issue
I'm storing time-series data of some products to my DB server.
- Ubuntu 20.04 LTS
- PostgreSQL 15.x
- SSD: 5TB with RAID 5 (OS and Postgres installed)
- HDD: 6TB with RAID 5 (my target of data migration)
As data comes in, it is stored in the SSD. At this time, the amount of data that comes in is quite large and becomes quite burdensome to keep as time passes, or as there are more products. Due to the nature of time-series data, old data(more than a month old by my standards) become less valuable and less sought after over time.
So I want to transfer old data to HDD as it is.
As I caputured, the actual time-series data is stored in sqlt_data_1_2023_XX table, and as you can see, the table is automatically created as the month changes.
Here's what I want or wonder: Can the table where the data is stored be moved from one disk to another while keeping the logical structure intact? The reason why we want the logical structure(schema, as I caputred) to be the same is that even if the location where the data is stored changes, the 3rd-party application(web monitoring) must refer to the time series data.
Please comment if my explanation is ambiguous or if you need additional information for a solution. Thank you in advance.
Since I am a beginner in PostgreSQL, I saw keywords such as 'table partitioning' and 'tablespace' when I searched in advance. But before I study and understand this term, I want to make sure my requirements are implementable first.
Solution
This is a use case for tablespaces. Create a tablespace on the spinning disk:
CREATE TABLESPACE slow LOCATION '/mountpoint/of/hdd/somedir'
(seq_page_cost = 5.0, random_page_cost = 10.0);
The parameters indicate that that disk is much slower. /mountpoint/of/hdd/somedir must be an existing directory that belongs to the PostgreSQL operating system user.
Whenever one partition/table gets too old, move it to the slow tablespace:
ALTER TABLE some_table SET TABLESPACE slow;
The only restriction is that the table is not accessible while it is being moved. If that is a problem for you, you could choose an alternative course:
make sure that your application does not write to the table in question
create a copy of the table on the slow tablespace
drop the original table and rename the new one to the old name
Answered By - Laurenz Albe Answer Checked By - David Marino (WPSolving Volunteer)
