I have a table that increases by several million rows per day. It holds over a year's worth of data.

The table is regularly SELECTed from, but the older the data is the less frequently it is accessed. The current data (i.e. data added in the last hour) is very frequently queried, but as the records get older they are less likely to be queried.

The database is currently on a traditional (magnetic) hard disk. I also have a much smaller solid state disk that is very fast, but not large enough to hold more than few days' worth of data.

I’d like to somehow spread the table across the two disks. I’d like new data to be INSERTed into the small, high performance disk, and then invisibly migrated to the slower but larger disk after a few days (when it’s less likely to be needed).

I know I could split the data into two tables (let's call then TABLE_SLOW and TABLE_FAST). I could insert into TABLE_FAST and then run an archiving process to transfer the oldest records into TABLE_SLOW every night, and then create a view based on a union of TABLE_SLOW and TABLE_FAST to use for queries – but I’m looking for a solution that wouldn’t require any changes to the application, only the database.

The data includes a field called ‘loadedTime’ so there’s no problem working out how old the data is.

  • Did you ever solve this?
    – Carlos
    Mar 12, 2023 at 9:49

1 Answer 1


How to put data on a different disk: (using tablespaces)

CREATE TABLESPACE fastspace LOCATION '/srv/fast_disk/postgresql/data';



How to make two tables behave like one: (SQL inheritance)


You will also need to add constraints and indices as apropriate to this new table.


Now selects from TABLE_SLOW will also see the data in TABLE_FAST

you may also benefit from implementing constraint exclusion (also described on that page), but the index on Loaded_Time (which I am assuming you have) should give a good speed increase itself


How to move data data: (CTE delete returning)

eg: 3 days old

with deleted as ( 
     delete from only TABLE_FAST 
     where Loaded_Time < 'now()' - '3 days'::interval
     returning * )
insert into slow_table select * from deleted;
  • Thanks Jansen, but I'm not sure that's 100% what I want. Unless I've mis-understood your suggestions I would need to manually run a delete/insert process to move data from one table to another, I would also end up with two tables (with different names). I was looking for a method where I could stick with a single named table and no manual processes. Apr 16, 2018 at 7:42
  • more recent versions of postgresql offer better partitioning, but I don't know if they can distribute a partitioned table across many tablespaces.
    – Jasen
    Feb 24, 2021 at 3:58

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.