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 1ujU56-008EMN-UU for pgsql-admin@arkaria.postgresql.org; Wed, 06 Aug 2025 02:40:45 +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 1ujU54-00DBCV-QA for pgsql-admin@arkaria.postgresql.org; Wed, 06 Aug 2025 02:40:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ujU54-00DBCM-9C for pgsql-admin@lists.postgresql.org; Wed, 06 Aug 2025 02:40:42 +0000 Received: from mail-southindiaazon11020115.outbound.protection.outlook.com ([52.101.227.115] helo=MA0PR01CU009.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1ujU51-000yll-29 for pgsql-admin@lists.postgresql.org; Wed, 06 Aug 2025 02:40:41 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=Zi/CPhBvrd0O+1MdwH//6iREtyEYbqOOPhWwFJOJhjhnU/S1JEyg6m4Fyv9O/J7P7SzZD996whaAyXFx24w9Iv5m0kDj1JOVlNXB6XMTr4Y0oZpZMTvxM0TQwwHF1u9/RMiVKs771Bg8uV+1JlLvYIR6ajyiX36kX8gmv8Afh7kjbcaxf5F8SG2Vr5ACH8ONhW+UXCECHiMif8caXrNs3eHSiKi2KQQhZUWwAMG6bnVTlGZsGBxzyczsdOUQLgDOduizRDHpmaQ4XVDCBOKii24AxafS3nPzld9vfoa/aIBg4fJ9vJ5t0DOpDGm5gFGUNPypFkuchzYz40gJGH5LFQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=c0FU1ye8p2puRSxAhnwWqVPRxRTSOlTm3J3RakmcU2E=; b=rRmyGfEiDYRnrlkmDui9lKQCv5YsnZmZkRMecnKl02ZaK+ZEoTT2nv3jZsaIZJLDjTDn0LoM17Twyjznmedn/lzpyK7efcIQa4OpxF6eiwzpwe5i4LdN2Mk921YO9w0tj8lSJbABXjBBFFURo7Z5PuGMvakB6pl1TNOuuKfOyY/EtAJyu8eq1sy1IrgRGQLHTZXT4skUKnumamlFRymkH0r9s1xXgC736GbDGAlrK6Uosje8oYQ0ytkuOB98OOfI1E3G88gcPKipsJxbsXw3tbwMSC37bLgv0CEBVszdgs/txLQGPzOqSnbPVKBiXtzqvJFHto4bM64I3+oWQxf5Rg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=iitmpravartak.net; dmarc=pass action=none header.from=iitmpravartak.net; dkim=pass header.d=iitmpravartak.net; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iitmpravartak.net; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=c0FU1ye8p2puRSxAhnwWqVPRxRTSOlTm3J3RakmcU2E=; b=Mk+cxnw3CGa5sJqDvDZuCpcKwO9ea2MLO3cF4utDa6a4GFER3GvqnU8VCP8mDspce6aD/vzC26odYXGDxu2W5DxrcYeijfO6wfgXb4GNYhH6L3ZuX8pTC+MveIGZM5+cz5lihMZaQZtqmtEs90+L41KOSrPFYf98Ny+NQFKMgIeNinQ+tgORP8o5AD5ZemVPwAhwR/MZzxvg6Di5yxTaDEQUvU0WFd5wLwM9DTJVcvDpWABZVv0rBjkjia1A7nKsISQWkIez+Q/L3VE/DCdZV08Ht2kkfQCaxnfduwrWn6g30zNaN51gOEY76gHs/jxJe/4Dsn7QcvW0RsGvToieFw== Received: from PN4P287MB4381.INDP287.PROD.OUTLOOK.COM (2603:1096:c01:2a2::14) by PN2P287MB0429.INDP287.PROD.OUTLOOK.COM (2603:1096:c01:118::11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9009.14; Wed, 6 Aug 2025 02:40:33 +0000 Received: from PN4P287MB4381.INDP287.PROD.OUTLOOK.COM ([fe80::bc32:f582:cb07:8c0d]) by PN4P287MB4381.INDP287.PROD.OUTLOOK.COM ([fe80::bc32:f582:cb07:8c0d%3]) with mapi id 15.20.9009.013; Wed, 6 Aug 2025 02:40:33 +0000 From: "DINESH NAIR" To: "David G. Johnston" , Mahesh Shetty CC: Pgsql-admin Subject: Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 Thread-Topic: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 Thread-Index: AQHcBQA1ym8jaFe6NEirJiy57CrKBrRR+RmAgAIT2d8= Date: Wed, 6 Aug 2025 02:40:33 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: undefined: 1707084 drawingcanvaselements: [] composetype: reply authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=iitmpravartak.net; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: PN4P287MB4381:EE_|PN2P287MB0429:EE_ x-ms-office365-filtering-correlation-id: 8a8ae511-6132-4437-1d0a-08ddd4929dd0 x-ms-exchange-atpmessageproperties: SA x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|366016|1800799024|376014|31052699007|39142699007|38070700018|8096899003|7053199007; x-microsoft-antispam-message-info: =?Windows-1252?Q?wqyzb/k/dyCX7X3e8862eCQyYf1G6SVsV1jw0i5IklMWQHrtqX1HaXKJ?= =?Windows-1252?Q?OrCBn4zFyN9r8d1EKuNcdONqBpSJkY2qMnm+akDNC0WmqFggOdTLPUu0?= =?Windows-1252?Q?PoXfZFReH22v/znxkGFCFfB3w4ANDSogl8okR2Rp8QwTHpEK/5gQfGIq?= =?Windows-1252?Q?r4azxTeoxiHbALMJ/HF1SRGDEiyStGf5rdx52ApIwcgY42mGPRp/gyZ9?= =?Windows-1252?Q?H0m2yY0KTgi3t8K3122hqhCbdnUqOY0gzQ3xbIACUuaxdnBcvoAN4MRK?= =?Windows-1252?Q?rEHozUB1H3y1VDXo5/xBPlxa77FozTAWNufAB+CdToUK1O8c/YtV2q1x?= =?Windows-1252?Q?xea3Dmk+a3WYf8m9ULpVWr+twGwae2nR4MWqg0rJqkzLT65hN/+M1Jo8?= =?Windows-1252?Q?MRV51xEJpzKind6yYeTJmWJ5pLsbj9Y3jr6kUDgW7BTE/rlmb+UTrskV?= =?Windows-1252?Q?4f31GIf3Md9YAtPeGqO3GlwuH2cxQIAU6yz2fANHyiLzhAa63nie90OU?= =?Windows-1252?Q?2IjV6XGd0n8VF2GvgWeHY1y4yzZlx7YpgGfc2IgPoVtj2n11tuY55tdv?= =?Windows-1252?Q?3IYE29DXbda27R95A+LidA8jehsxXlk7ogk6qVW9AXHH1ZnFcRfTqHnw?= =?Windows-1252?Q?PbbUQOiGlTQD8G1nFBuJetvAAl2PCE22w0kU+1vl0WY6kcOja6rTrgAU?= =?Windows-1252?Q?xVvKRXWFfvSas/5EPBiHQ2I4V61cM/fSsyYaTqgiOMiKl6BvnjNaOIgE?= =?Windows-1252?Q?xU3PBySFj5jRs9xI7vm5EqXQ71CUjhQXz5D4BBFysOFcVJ3arOFxyHIe?= =?Windows-1252?Q?dPzsun/BaIFH4sIz29jO4h7C+Ybh4MNbbIA1VEYDX9yaxMiCMJqMhFub?= =?Windows-1252?Q?WIMkTcXsv2Q7+ysa6ww/nLDEAK636sQl83OpDqt45Zmc/PXsAsQ6TuOc?= =?Windows-1252?Q?peYbb3uHYLsufYgbffnNn6iG6I52Hf37/roo2LyZXDKItNkNildhfWJ1?= =?Windows-1252?Q?Q2QP79SeI+FwNvCdgsvlioUC8cj8dO1T1N1WRrHynRO9clFLFuKXkRp1?= =?Windows-1252?Q?wlb4//k59vd+LNj7HM/3JiNKDcJOKn1y/hJZpsKOmQs3jd1vtjaLvEdz?= =?Windows-1252?Q?5OVYHbrrtOGyJ6joNoEqAjPb9k42g+mB1o+hYl++IQBmFbbjOqj4au8F?= =?Windows-1252?Q?uqg1tX/h1lruZBTMww1Ma7hjrgOMjVsgNHH3sWYGVCP3R+dymId1SkwX?= =?Windows-1252?Q?3nlj4RkQyT1EmNNhjWr0tMJeBNFH47TmTtvidw7WeM4UvuddT9PIg6Fh?= =?Windows-1252?Q?THBpnaAXuP5K8N50+8DQOnFQTFiwg0uSI7Wgu4c3PAhI5f/uyJL2aPRm?= =?Windows-1252?Q?EiEGvb+NAqTxZZ9cd+0KrBGdjiyQRp9Qh0rL/wLOfV3j2UzjwTNvJbRt?= =?Windows-1252?Q?0zu/sm9+mNymAxvayfmn0WVywkuJDZfwv/RCYESIs+540naNyEY1WCKS?= =?Windows-1252?Q?tJVFVJOXTboRTs9Fg5VwOjhYBM7T6QYlwtN+ACG0Jy6qFNME+lTx/K6H?= =?Windows-1252?Q?aX0fMTHqVHptfJB8fwj5kSyCNkvr3dIo5mlU0g=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:PN4P287MB4381.INDP287.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(366016)(1800799024)(376014)(31052699007)(39142699007)(38070700018)(8096899003)(7053199007);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?XkHhGWTcZ6Hm3lyAe3yWO0+p/OW+mvJ59jJJZuEX77gwzoZMI/nAaB+A?= =?Windows-1252?Q?x6lvyAc3V2vCQkZUS1UecE98/vtGC+BmoHiETbGbScpG/P64yp80rhTb?= =?Windows-1252?Q?5Q1SnH6iXxiSwaXXN22F2iDOgONBaKrgkLL2CsA0BYWm3flNP+kfhqcG?= =?Windows-1252?Q?ETnwyOMsK7TyLIIEZ52GKkUa63i/MluT1EAy6GVPMGBU7/oO/Cn6Ve62?= =?Windows-1252?Q?lB0jrtlM//ak3y2usRHHVVDAvkatFUYxnI9gT+iqNNtT6FY6wjszJOSy?= =?Windows-1252?Q?+Xz/lkDgZcrRjFJNdBBgpk+VJAb6jLiUqho7POQrmeNYEcql2NgvfthH?= =?Windows-1252?Q?mHWLbBBeZPAvysNzkeos9b+/1XAdTwk5YwcTpb8kaoqg/M/ub4crZvK4?= =?Windows-1252?Q?9F5JiBIfFZGOPgWbxLHtBO3P6dyiqblI9W9R8jQT7w8bCZZCO+y6JDq+?= =?Windows-1252?Q?JhQlQ34dwHHfWfeGpriBeidlgwmgFWQkqHh+3etEolJQQ7MhqpPaHOV6?= =?Windows-1252?Q?Ew5HIHEGlVJ40v6Sm23jajs5/psivgh2P4KxUs2iE314Y45weQ62Y8+t?= =?Windows-1252?Q?BTIQuokyRSFU9sjCNpK64O7PJDOzUC6mF5F4JkwG3/dBLWPenFI8Q5cF?= =?Windows-1252?Q?h1xF5bbIjjFmLlC1YqpajIRdtYEjD8yWLWM7embbj2bPSDKpCuCYKR2e?= =?Windows-1252?Q?c1n54ViNmTQY52/eATD2j9QDUgZ8/ZTafMYoaNklJG/LZcHBtRAx79ry?= =?Windows-1252?Q?HOhws/qXI3y4JX5Ew6ykcD9TV/xAMii4bmfgDbvnySouAMiip45OiufB?= =?Windows-1252?Q?h3D1BF1YvfqvzXGfGzPBtN1ADYurxP3kxM+ZjDzfAZEoHHcBiNgZTmPB?= =?Windows-1252?Q?R0890EJ/6ZrcFSzboEzTrnz0NEFYUlOiQ0LR9bWgCts0b/DwApMPbaOR?= =?Windows-1252?Q?807Xm/Q050GxWpO21Sqn2Vuwu9GXEmK3E9Bav2sD8lB08vXJLh7RZmAB?= =?Windows-1252?Q?+7tWpl+OUcvczDwBLc34yLexMlpVS67x/AJJgv+bam5z5jKbv8lqhzCH?= =?Windows-1252?Q?KWKhm9Pbr+w6dIqMLwOM/MhJ5gh22GbTQlD+lV3HkAv2telNGM3QwC3W?= =?Windows-1252?Q?+Z+gRl1axyyNNlcScPpctbORslOZokcY1gmZ45Squ85h8IYRRTHhh4ps?= =?Windows-1252?Q?+SjuEHWHw5aupWkUQTMKKYuuZOHnaO/R3Nwq1H6i2mHp8C7C9n+d5/Xc?= =?Windows-1252?Q?v8TlfOG5lYELEGh2hNEo7PexE+QN0NIpDT3S5o33E5/uCbZaks2TF6Go?= =?Windows-1252?Q?R2qfdpfdim12MeYm8pvGchBJoHQl5EKVzN2hMHCE0vbcvqtn4ChCUHb/?= =?Windows-1252?Q?lWPVmcB84BtwXpEZvTbloqArVBWmelddFElay7SCln2MPJLcEMpZr7ks?= =?Windows-1252?Q?mzBFvg69Uk8k+3OdDbmntDrlxQo1YqGjPTVri/kWTPHK+VoFNHHfsRJ7?= =?Windows-1252?Q?MRNbVCAX6A30EJj2PJnJQWFCqCFPHpr8E62TRMdfd6VsZvqFhDRq2BZB?= =?Windows-1252?Q?ONFj8azeN0zTq8vDqS8zD5uLGdU9s6aQfn7lMwcOhLMjOs/CfyEsjwCt?= =?Windows-1252?Q?rBN8xt7gbzMwq8QiK9AgfAmlG696KHQIx7v4VYQF7RN52yt30DTDj/lf?= =?Windows-1252?Q?yNkFcQZj9k5oE+gkF2UE++WtzTD/pUBhIwUQgy0O4G98H1o/qSyqBw?= =?Windows-1252?Q?=3D=3D?= Content-Type: multipart/alternative; boundary="_000_PN4P287MB4381D8F12608B6572F14A5C89C22APN4P287MB4381INDP_" MIME-Version: 1.0 X-OriginatorOrg: iitmpravartak.net X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: PN4P287MB4381.INDP287.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 8a8ae511-6132-4437-1d0a-08ddd4929dd0 X-MS-Exchange-CrossTenant-originalarrivaltime: 06 Aug 2025 02:40:33.4426 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 3e964837-c238-4683-9155-49f4ec04f8e9 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: YByaVEkjPdWSlNtnDsU2FvlC4pJ5W1Kj6n6c5bLSX8o3QUOuTSHsiS4sQOcy2TJVxnfVHV2rGsg5b4iPW5+RwDLBNb0uyWQhB42OJitbrGA= X-MS-Exchange-Transport-CrossTenantHeadersStamped: PN2P287MB0429 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_PN4P287MB4381D8F12608B6572F14A5C89C22APN4P287MB4381INDP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi, For partition tables concurrent indexes are not supported(checked in postg= res 17.5 version ) Work around will be to create normal indexes on the main table. Thanks Dinesh Nair ________________________________ From: David G. Johnston Sent: Monday, August 4, 2025 11:05 AM To: Mahesh Shetty Cc: pgsql-admin@lists.postgresql.org Subject: Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 Caution: This email was sent from an external source. Please verify the sen= der=92s identity before clicking links or opening attachments. On Sunday, August 3, 2025, Mahesh Shetty > wrote: I have a large partitioned table with around 100 partitions, and we're plan= ning to add an index to it. I'm a bit concerned about the potential impact = and duration of the indexing process, It will end very quickly=85 and I=92d appreciate your inputs on the following: 1. If I run CREATE INDEX CONCURRENTLY on the parent table, will it autom= atically create indexes concurrently on all its partitions? Sometimes it=92s best to just try (I didn=92t though)=85but the answer is e= xplicitly documented (see create index) Concurrent builds for indexes on partitioned tables are currently not suppo= rted. However, you may concurrently build the index on each partition indiv= idually and then finally create the partitioned index non-concurrently in o= rder to reduce the time where writes to the partitioned table will be locke= d out. In this case, building the partitioned index is a metadata only oper= ation. David J. --_000_PN4P287MB4381D8F12608B6572F14A5C89C22APN4P287MB4381INDP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Hi,

For partition tables concurrent indexes are not supported(checked in  = postgres 17.5 version )
Work around will be to create normal indexes on the main table.   = ;

Thanks 

Dinesh Nair


From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Monday, August 4, 2025 11:05 AM
To: Mahesh Shetty <maheshetty20@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.post= gresql.org>
Subject: Re: Indexing Strategy for Partitioned Table in Postgre= SQL 15.13
 
Caution: This e= mail was sent from an external source. Please verify the sender=92s identit= y before clicking links or opening attachments.
On Sunday, August 3, 2025, Mahesh Shetty <= ;maheshetty20@gmail.com> wrote:<= /div>

I have a large partitioned table with around 1= 00 partitions, and we're planning to add an index to it. I'm a bit concerne= d about the potential impact and duration of the indexing process,


It will end very quickly=85 

and I=92d appreciate your inputs on the follow= ing:

  1. If I run CREATE IN= DEX CONCURRENTLY on the parent table, will it automatically create indexes concurrently on a= ll its partitions?

Sometimes it=92s best to just try (I didn=92= t though)=85but the answer is explicitly documented (see create index)

Concurrent builds for indexes on partitioned tables are currently not suppo= rted. However, you may concurrently build the index on each partition indiv= idually and then finally create the partitioned index non-concurrently in o= rder to reduce the time where writes to the partitioned table will be locked out. In this case, building the pa= rtitioned index is a metadata only operation.

David J.

--_000_PN4P287MB4381D8F12608B6572F14A5C89C22APN4P287MB4381INDP_--