Sample Header Ad - 728x90

Why does moving a postgres table from one tablespace to another take so long

0 votes
1 answer
183 views
Why does moving a postgres table from one tablespace to another take so long (and how can I speed it up) I have a postgres database with two main tablespaces, one on a SDD, the other on magnetic HDD. Occasionally I need to move some very large tables from the SDD to the HDD using ALTER TABLE... SET TABLESPACE... this works ok but can take several hours. I know that I can find the physical location for the table being moved by SELECT pg_relation_filepath('myTableName'); which in this case returns the partial path *pg_tblspc/59626/PG_13_202007201/59627/890785* If I switch from postgres to the operating system I can local several dozen files called *890785.nnn* (where *nnn* is a number) I assume (please correct if I'm wrong) that these files make up the bulk of the table data? I can monitor this directory, and see the files are being copied across as the ALTER TABLE command runs. There are several GBytes of files here, experimentation shows me that I can copy the files (via windows) in about on tenth of the time it takes postgres to move them. My question is, why does postgres take so long? is it doing something more than just a move? if so, why? And most importantly, is there anyway of speeding it up? (BTW - all other users are disconnected before running *ALTER TABLESPACE...*
Asked by ConanTheGerbil (1303 rep)
Jun 29, 2024, 02:19 PM
Last activity: Jul 4, 2024, 06:55 PM