2

I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few months isn't really interesting.

My idea was to move that data to a cheap SATA hard drive instead of buying more expensive SSD's. Is that a good idea, and is there some good practice for implementing?

My naive implementation would be:
Keep two databases (or create a tablespace on the cheap HDD). Fetch every few hours data from "fast" (SSD) database to the "slow" database (HDD). Every few days, delete data from the slow database. Is this a good idea? I am happy to hear some feedback and better suggestions.

Improve this question

2 Answers 2

Reset to default
4

Here is a better architecture:

  • Create a new tablespace on the slow drive.

  • Set the storage parameters seq_page_cost and random_page_cost higher on that new tablespace so that the PostgreSQL optimizer knows that the disks are slower.

  • Partition the big time series tables by time ranges (use the same boundaries for all affected tables) so that you end up with a couple of dozen partitions for each.

  • Move the old partitions to the slow tablespace.

Then you still have all the data accessible.

Use PostgreSQL v11 or better for partitioning.

Improve this answer
6
  • Thanks a lot for the fast answer. Does that mean that I would have to do the Partition all the time? Do you know is it possible to move the timescaledb partitions that way. As I understand the architecture, timescale already handles the partition part. That would be optimal. Then I could just move the old partitions to the slow and cheap HDD. Actually perfect solution - if that is possible.
    – MichaelRazum
    Commented Jul 18, 2019 at 22:22
  • I don't know the details of timescaledb, but normally you can simply move partitions with ALTER TABLE table_partition SET TABLESPACE newtbsp;.
    – Laurenz Albe
    Commented Jul 18, 2019 at 23:08
  • Thanks a lot will try that out :)!
    – MichaelRazum
    Commented Jul 18, 2019 at 23:08
  • @MichaelRazum I would love to know if you had success with moving timescale chunks to a new tablespace in this manner? We have some space issues now, and I want to move some data around until I can sort out an archiving process in a week or two.
    – Krummelz
    Commented Mar 13, 2020 at 14:06
  • I have never tried it, but if it is a PostgreSQL table, you can put it on a different tablespace. Storage is independent from behavior in PostgreSQL.
    – Laurenz Albe
    Commented Mar 13, 2020 at 14:33
0

PM from Timescale here. Our next release is scheduled to introduce compression, so that will help with things. Tablespaces are a good idea, and we are planning on eventually allowing you to move older chunks to different tablespaces. Please feel free to reach out to us for further info on that functionality, since it's still being developed.

PS, make sure to use drop_chunks when you delete data so as not to create too many tombstones that end up having to be vacuumed.

Improve this answer
3
  • I like the automatic rollups of old rrd-style databases. You resample historic time with average/min/max/quantiles with coarse resolution which greatly reduce storage need but still provides enough data (transparently) to see long term trends and patterns.
    – eckes
    Commented Jul 20, 2019 at 4:42
  • Hey Diana. If you plan to be a regular you may like to have a think about this dba.meta.stackexchange.com/questions/2795/…
    – Michael Green
    Commented Jul 20, 2019 at 10:29
  • I've just upgraded from 1.4 to 1.6 and from what I understand, compression came in with 1.5. Will the mere upgrade of timescale, compress all my old data? I'm sitting with a space issue right now, and I'm actually wondering if I can move timescale chunks with ALTER TABLE table_partition SET TABLESPACE newtbsp; as mentioned above, instead of move_chunk. We're running community edition. Is this safe to do, or should I wait for the compression to kick in? The database is several hundred GB in size at this point.
    – Krummelz
    Commented Mar 13, 2020 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.