Re: Moving data from huge table slow, min() query on indexed column taking 38s

On Thu, 6 Jul 2023, Dimitrios Apostolou wrote: + Then I broke the command above in many small chunks WITH rows AS ( DELETE FROM tbl_legacy AS t WHERE (partition_key_column < $1) RETURNING t.* ) INSERT INTO tbl SELECT * FROM rows; CO

Re: Moving data from huge table slow, min() query on indexed column taking 38s

Hello Dimitry, at the end, a table is a file with many other functionalities, these functionalities consume resources. If the DBMS (like oracle or db2) allow us to disable the functionalities so we can perform a copy between tables, otherwise (and often also for oracle and db2) the best approach i

Re: Moving data from huge table slow, min() query on indexed column taking 38s

Thanks! I have completed the transfer by taking down the database and working exclusively on it, but I still wonder how one could do it in production without exclusive locks. The loop with small DELETE...INSERT transactions I posted on the parent post bloats the table fast. The link you posted co

Re: Moving data from huge table slow, min() query on indexed column taking 38s

Hello, this is a standard problem during bulk copy. here some suggestions; for example disable indexes. The main issue is related to index, lock escalation and log writing. In other dbms you should set log off on the table, but postgresql does no

Re: Moving data from huge table slow, min() query on indexed column taking 38s

On Thu, 6 Jul 2023, Dimitrios Apostolou wrote: + First I chose the method to DELETE...INSERT everything. Just to clarify, the query looked more like WITH rows AS ( DELETE FROM tbl_legacy RETURNING * ) INSERT INTO tbl SELECT * FROM rows; I noticed that the postgres process was

Moving data from huge table slow, min() query on indexed column taking 38s

I have a huge (10G rows) table "tbl_legacy" named test_runs that I want to PARTITION BY RANGE on an indexed integer column. I have created the new partitioned table "tbl" and 1000 partitions in it, so that it can take my existing data and have the partitions ready for the next year (so most of th