Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uMTfd-00Cv5t-Ky for pgsql-performance@arkaria.postgresql.org; Tue, 03 Jun 2025 15:35:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uMTec-001Zh1-Cm for pgsql-performance@arkaria.postgresql.org; Tue, 03 Jun 2025 15:34:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uMTec-001Zgt-0l for pgsql-performance@lists.postgresql.org; Tue, 03 Jun 2025 15:34:18 +0000 Received: from mout.gmx.net ([212.227.17.22]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uMTea-0001WR-10 for pgsql-performance@lists.postgresql.org; Tue, 03 Jun 2025 15:34:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1748964853; x=1749569653; i=jimis@gmx.net; bh=9Qu3E2QqwiM1bWjeFi/RwEl8vjSyLoCb9ZRuCm9ik2o=; h=X-UI-Sender-Class:Date:From:To:Subject:Message-ID:MIME-Version: Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=LQi2zHu3vpVrZR0lXghghdIraW4MaEdqmQLUbhxm/8MUuljeC8cHnWG+CtMpTyCN U3Ica276YOtck8WpO+Y1iWmoOPuLBy7f4ZDdri/ydnTmcgiW3QEFzA0r1vCDlYnpL 3xLM4dpyMq4Njhg7RGYTlpPbIsV5umZ2Lf7XNy8G7AjL1hfL1SM8mxwLVuO3Q9pEN kWE6495EfZuanJ/YrrEuZElfTYPK6kr14GC21voL42Mko04kNr4OClyPRjyi2rbA9 wpQAaT5m+t9ULBawmZhse//VY2gl/MpHnKbACdt8WqMloV81TUg3VtkxwoZE7hNli R+y1DbmQxrcSk9rvZA== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.81] ([185.55.106.54]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1Mf0BG-1uoejP0mvC-00n4yf for ; Tue, 03 Jun 2025 17:34:13 +0200 Date: Tue, 3 Jun 2025 17:34:12 +0200 (CEST) From: Dimitrios Apostolou To: pgsql-performance@lists.postgresql.org Subject: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized Message-ID: <5f002654-92e9-8032-3e1a-134461b4e819@gmx.net> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=US-ASCII X-Provags-ID: V03:K1:syWlN6DsCwnL4m7TyAZl4QZGcB4sso81TLn3qaOo8ng2DfmdTWg FvFaGHrO+Y5Vqp/7zigwnC44XJG7jxRzw6t2CUaSVATHniU9M5VMDsNOw306wQ8lzMmciyz XamJDOOXSjdbmrTuBl3jJubeMGHOw/RwJHoOOuwgyWZWQvnMi501YGY6rGrPjs7oNSpnFfh 57h5bK0zxoGDWO27LMVVA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:2M64GBVlAzE=;4w2YZ2IsBFHBokGwnubcwiJO0Y6 ezeAkcbNi1eSYl/WYc2rO3mvy4fl/6SUJ0FKJNTn+JzRxAY3e5ly5Uen+/Gbd1xlQ/xI3PZjg gL8vDcbWxHF/vl3GhJBG8APuNYWZLZ041kF9X2SLhvnM7cH3waosBItvDeKk3WlICmaq3OhMO +Cliidh83e4tzT8FmW8uF021bA1O8+NuoA082vnt+GLcR8JI3Zenef3WgliHja+e2unn6qEhk lnf8R12SAAC7qsyJS0Q5U/k/9m8mXZhLPTz3nwKDsG5mRe6lvfC9c11OVeSIZI6oBJ5ZMM/mK t4MNAKodQXDKRmh8nUeV4UvDyK2ieIXvToM1hsr/4eGH40XZTD2KBM1DS0g/Q8Oj3f8lyRQl+ KlxckLtLAiyeDwBj8C8pHvks8X21dR5fYZXFZTeimHwMm7Bff7HLyNimVnNrlehJmiwKH1Hc/ wm6i1OWO5T9cSh3JniGMQkp4cTakPR1Brb5ZpKrJvJrN38qXndOzfsrBFUleMUSjdYfyVJRlO 8ODWeUWLTgur4yAyh8frhODzOJuyCwtcnZCoOUhjzbneKvDF7YL5D+gxglgblj1q6rQi3iq5O fVUPG1aUmiAitrCnYkblI9TFrD/UxSaKtE4pafpgi0V4GajpT837RxCR0fA5qRoscNB5GI13S cnX4m+Bj6Yd6/ChY34qps7UL1ZvEsG53/kiU8QdKSCIts15INYdK9QbDcD57FJBCWoSYEACII GbpKn9dy2unPkL1k7VsTdbz71ueT0OLKbSo5LDURDApJZk3KfT7U1z+Dl+Zk6cs+9yA59GKW9 IY3UbQ1DVZVLwHI0oko0XMhT07ZTUlm/o9BDVeDJPPfiWCohDBFWLD8/fByAcyPZRhmsAsgL/ y0GEv+8te52CuyqMRtA8HiIrmsU0P6fuAEyD9fdHUQSVfQpBXn6pgMBSEn0zeuwVwjHjw27Zn oAGihsGFeQMrWrR9uz/COUH5IITd3993nI0PqePZ3Y7O2lXlqzzsuo2Ba5VvOgWAZwLoHNclp OskAt/gsMx6lIqqx7Tm50O8LmtFB47Wa2k2boDyG5+v/OXJA8dib1Tf4MEK4PlezYZPMr+fAE +SgM27uyuXwVoGY1/oc9pihvhgFv7Gv3tGx+BPXzslxV6IkhFEsdBX2EVtfORjV/GUbju8kug cgxxNn/X57z4BgtDgtQcFPWXrt7LXDAxA3YFKWOY4uc8lD46L/zlcM1aMsbSyuKTi5aPUjnkn 9w7B/eXWqLBfhIm+6hF6hyQF0iqKXyXWUAiWrG2vNl3mzdKCs8ExDGgiBqAOt+lcGK3LfPZZd k2oZ/rqWBH4DTSl3QRCpRi+KAN6eP7dJNG5UF/U5dXTRC5RyKGw1C855TpVEWedd0AdqWgA4U ZBUwoOZ7dCrre+xTMwmaD5xE0L4hTbGxjKRwb3nO79RANx+QQktu5V6WHJluqMVodN9TEnQNp QZFDgZ4MmKOalBpfwi4+JNlHUyS66oCvkHedp0RvTlyaTVs1jgNklJhSvIMyQnfEaXH4n+/AQ Qz0zv4pDaF3oAPe4/M5RPlz9vg1BbizEGkCn1tRWYZqsFHbLpFIHZoCGebJw5tdH/S0kWoxy7 rJUFpUTrilHdZkqy/cjejh0vqMcCgwpPJPmMheaPgsQuQ65dT0bsAiJqtehISK9WTaO042vrt oj9Uf8NaETEjCtUl/3gAzZQbrASu1pB7LZ0oN6xl8dxRPFHwYRH6PyFP+jlSQpXvZrRkeZS3g L5rKUOgBVolCZZaM6twvRssLfvxaXl0oJFKdv9g+nxgURdXK5ZGN8MD9Xh0DJlrOEBQBeWeK8 fIYKkE3k03V2cUbjwsX1/JdZ15ZGzWhWqjcAgZAttGDMBRe5jaUs3NH8iEAkVg07UKpX+0pON k4v1lmH3exz/bjLCekCY5f7mzQDvQ+gifnfYBZe6v/YeJeevt662A8JwwndVunM+MxioawE5d KRn61QZ2vg7Lm8pOtiQQF5YlLmUfgt4enEVQBgvTA7/4bb7InY/t4t64D5S0BJUVco/2g6bEV O/Sl3UL2SjuY+ETv3hRGVaK1KlfF+Z3HLtPhXjYAiwG56w2zyWiSpqs4bW/riHxMQoWJXeRZ9 ofhvNE5Bh/J0E1efjxZfaUMclN/iFRK5+mYIVNBfGMHWGosA6FLF7jdetFvDyazF/o5gCXiQF 1vtO1AfaBH7O/eVOLD2uuHCcpAlKSW8cIIb/RGYe91geX41tV0P2TOLYFFqobinLKjP5Za+wq Duv4INYXJNbU+FDx8aA7ocYqXMn/MN1mSM0CCjYx80RcRqGLi+a/lW1NoKPY3XySPn3fjxodB fH0fU/zt//5DTjF0kjLJKWB22y+x94q8+mAqSHz9GEOY8lHgjzT3W1SiyQ08ktKVVJ4HP8VbB Q1fiMTO2P7BhVFzTII5Awt4cpEOlllILiiZlXkWJqnirPGZ7sNxPOfR10Y/0UOnVY5Yyl92OC Wc66ybc6L6q25oVJS5ScqLJU42qZ5INjYD7uy+2YhCwtxKeCzIuSZ6g9/A5vuU3vgOsfzuBa/ s0WHWXY4jC0rujM/h7kvJpikWtgsrT5CsDxHHSX5FKhlN4+UqU5YpbX+SwQEYYHJ3EYhF3Ga5 ZR9o5JcFulAr9SNHSn7ffsdxRKMk/9Cnp/G/cQERgY4Xy+83O9mEgUr+tHoBYHUzTgQYfBG1A s7v4cm45Xo/Z2fn2nKhRsk3KpSn2nQnS3wolCEmGTZ9/wfrs0Kkpb4kRRMdbbK4yAv+aaffhr BBqKkks7E4iYxwC7DHOPqHYWaAOw6a7KliY1Jzg3g2HFsYr+lFdqIkpX6juY4k9QEcmEMvB5H kGvPYdF/k5cKNRn6kSg7ldJ1wrrZZxSBQNPgA3hHs2AqX6YsFIYkBDngrRcroP2cPrFu+Or/e wq/4JHpPaABz5VnqMWC7ZbWzC+ekKeeKY9y5/sOW7KlXfEpfBSt/9dsXeWzCuENOQ1jhdWM7A lJz57Cm3WeHogd7l2vfqV/+NtbMm8++A+mGQNjo4aaUSCeI1Sp/YwRVdHQh6h1spq/8LS3WmO 30ACwhtdskvdMIZ+dk3f/SfPbNWdrgl8eaFtWHzvavy5dFc1GJLAxz7VAKm1HDNELsuFt6SC9 gYljskwKoxuo1HR/5Sv9SL+2naElqlpARVaS8uJwVEoCaj731T/xXR2grRVQXGqOmSwl3PzSV zsiEYxN4KmJTimQDNe9FERxO8KlNmYUnlCLejJ2DhHqsQTw== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello list, I'm debugging the abysmal performance of pg_restoring a huge=20 (10TB) database, which includes a table with more than 1000 partitions. As part of pg_restore -j... --section=3Dpost-data I see *days* being spent in the sequential creation of foreign keys: ALTER TABLE the_master_partitioned_table ADD CONSTRAINT ... FOREIGN KEY (columnX) REFERENCES another_table(columnX) Each of the above queries take 1-2 days, and multiple such queries are=20 issued sequentially. My questions: + This master_partitioned_table contains at least 5 foreign key constraints from different columns to different tables. Despite pg_restore being invoked with -j, they all run sequentially. I guess pg_restore dependency resolution decides so. Is there a reason for that? + The backend process for each of the above ALTER TABLE commands, does not parallelize the foreign key checks for the different partitions. I know, because in the logs I see gigabytes of temporary files being written, with the CONTEXT showing queries issued incrementally on all the different partitions: :LOG: temporary file: path "pg_tblspc/16390/PG_17_202406281/pgsql_= tmp/pgsql_tmp3363462.579", size 1073741824 :CONTEXT: SQL statement "SELECT fk."columnX" FROM ONLY "public"."table= _partition_214" fk LEFT OUTER JOIN ONLY "public"."another_table" pk ON ( pk."columnX" OPERATOR(pg_catalog.=3D) fk."columnX") WHERE pk."columnX" IS NULL AND (fk."columnX" IS NOT NULL)" Why can't the backend issue these queries in parallel workers? + Based on the pg_restore manual, I am experimenting with manually issuing DISABLE TRIGGERS before the restoration, but I can't see a difference when I'm doing the section=3Dpost-data separately. Is it supposed to sp= eed things up? Thanks in advance, Dimitris