Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qHQT5-0003hb-J4 for pgsql-general@arkaria.postgresql.org; Thu, 06 Jul 2023 15:00:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qHQT4-0005nZ-I1 for pgsql-general@arkaria.postgresql.org; Thu, 06 Jul 2023 15:00:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qHQT4-0005nE-6E for pgsql-general@lists.postgresql.org; Thu, 06 Jul 2023 15:00:26 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qHQT1-002YlE-J9 for pgsql-general@lists.postgresql.org; Thu, 06 Jul 2023 15:00:25 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1688655622; x=1689260422; i=jimis@gmx.net; bh=ar150xvjKzmYrPDqnCTJ7+AaFQD721Nf+xlRq8zAJjA=; h=X-UI-Sender-Class:Date:From:To:Subject; b=G9OxZ+p/B9jaGF2ABV9exAkl5de4gqL/xdTMA9SQZPNdZsGPpJG49E5jin4wKjW0EM9ymOU x6jUMXjkB3DZtjXvSjwm3p/lrwcCuAUQY8Hwm0cw+A3CbpCWgfMEZORdjx5h5TzqhCKzQmnuX nVhmZSRytXYXb1CzOdpUCdDd1HtlFNNvaO3XlpghVCzamFfp4ihzP5cYpoMcpsfS+KzVMfonX 1opeHgdLxJ8Nk6cJbMkiCmDC7xNzflsUCx3ont8WI6ncZXN1EriZxhGNdSOALAlFwWM47x2i6 mUaUcZpyM1AaguEQDjqQiKYUZZ/jSIAlGd2x1ZXEz+obtZoGeu/w== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.65] ([185.55.107.82]) by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MFsZ3-1qDryc3LfL-00HR1D for ; Thu, 06 Jul 2023 17:00:21 +0200 Date: Thu, 6 Jul 2023 17:00:19 +0200 (CEST) From: Dimitrios Apostolou To: pgsql-general@lists.postgresql.org Subject: Moving data from huge table slow, min() query on indexed column taking 38s Message-ID: <096aa4b3-892e-7fa5-7370-3a1895e7d6a0@gmx.net> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=US-ASCII X-Provags-ID: V03:K1:3A5Cuc7nbX1LZGOYCRErIFXJDqjmD/YNt9r7c6HTURZnmUCUyXM muOF+PRBEL0Wkm8l2/Bqh/Y/6TN3BLj65GDjg8xicX7SOD08laMYk9F3USppvuQP5E5rNZN TAHlQxFnL6k1NIxWKzIUCH/gi8r5IR5HQG3n65fKB3xTcS0KZxiBhQJMTLnRd+FPWBaW5ez F1m1OvoP8MxmprCx1zFSw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:8YmB6Qky0os=;OyAiyQ+EnT0fgYS5n2OPQ1oLz9e 6Bg5bM5nWSQbKSPDec6tVV7/6zY85ixs3H0EeytnGoXkLilphS46KGeTlYcEl+2untrP6jZhq p/VfSvGbNayDQ6LOpOzXCCy+WRhka7MOdnJIyw0RAwfcOaNju5r1hZiu+BuniaSs1HSWURQ3q 7ftHOJ8uR4MwqtxgNndJyl8shO2BCIlCqIzbYCe9wUa2fQvOxBmjuT8ozHANKb9HgKUUvf8hR 6lQPD/GrYrFs6cpA4Q8XLTAWyPz2f3qkSj3TPA7mGoNJ2j4GNspCzGTojyo10no4j4EiSGh9X 9u1ChE0Xiyyx9LL0fWP2Mt8O9uNlZFJgH/qw0foIe888Qlgn3Vx0dlNlmnX6HfKWk8gOjK87w 0LpyuRyaY6NxZZ+MP9TcHI0qzGlP3CZQlPq7GrjndXDIguFkZfVs/8Bl6LWnVrmOL0Cucc4Kk qNXMljdH9OVG/UJwO3n9a36Nzxtv5EeIAVJYpfF1xej4O7DLI29g48KA9mf8I7Az6IHkqHg1a rX/v+QN3uQa4MsEuXtIwNiybWXKsgfCKHJ+Y9CqDUHsra200sfvHkDxqZNxyZ2HWYO/BW1c5d XBrPg1ry20tTkpWNGaTO5qV+kLNEgmGKwjLydH/t1wuDD6MCXhaA36ZthAHLrh6uGMz0AYFRK hR9U61fIpwSiwawjZJI0yMPXhEzRaHhy1G/j22M2DNdksyfU2OKJXoXzZEn+uthNTITK0ybb+ rbRAogxyVHFd0IJ1//t0JjQMm+K63/VjZ97ArQKhW03tWVSeQX+tgiRy8sAqT4UsBIEt8pU+w WLP4l56vMRo2i9THKvjvCeCGB7QLa4HH7/j/GSd25c/8fV7H2HFxSKKkyaAJ1mNTsU5dVTumw uozIwhkRP3j+S4YIQZuBct8rTaO3jHvWtyRseJ3mwM7XUZXLSJAfynyWg Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 the new partitions will be empty for now). On the topic of bulk-transferring the data from tbl_legacy to tbl: + First I chose the method to DELETE...INSERT everything. I noticed that the postgres process was growing without bounds, up to the point that it consumed almost all of swap space and I had to kill the command. Any idea why that happens? + 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; COMMIT; I increase the parameter $1 and keep going in a loop. At first this goes OK, after one day though I notice that it has slowed down significantly. My investigation shows that the simple query SELECT min(partition_key_column) from tbl_legacy; takes 38s, despite having an index on the column! A VACUUM fixes that, so I guess the index has a ton of dead tuples. I guess autovacuum does not do its job because the table is constantly busy. Unfortunately VACUUM takes long (several hours) on this huge table, so = I can't add in the loop after the DELETE command. Is there a better way to avoid the bloat in the loop above? Why can't the DELETE command update the index by pointing the minimum element beyond the dead tuples? + Finally I resorted to just copying the data: INSERT INTO tbl SELECT * FROM tbl_legacy; This took half a day or so but worked without blowing up the memory usage like the 1st query. Why is that? By the way, I don't consider the last solution ideal, since the data is duplicated between the two tables and it's harder to implement workarounds to do it without taking downtime. But it worked for me for this time. Any other ideas for partitioning an existing huge table? Thanks in advance, Dimitris