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 1rXIUt-009qyD-EU for pgsql-general@arkaria.postgresql.org; Tue, 06 Feb 2024 10:16:12 +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 1rXIUs-002fmC-CO for pgsql-general@arkaria.postgresql.org; Tue, 06 Feb 2024 10:16:10 +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 1rXIUr-002fm4-Td for pgsql-general@lists.postgresql.org; Tue, 06 Feb 2024 10:16:10 +0000 Received: from mail-ej1-x62a.google.com ([2a00:1450:4864:20::62a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXIUo-005k6E-Od for pgsql-general@lists.postgresql.org; Tue, 06 Feb 2024 10:16:09 +0000 Received: by mail-ej1-x62a.google.com with SMTP id a640c23a62f3a-a36126ee41eso683300966b.2 for ; Tue, 06 Feb 2024 02:16:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1707214565; x=1707819365; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:cc:to:from:subject:message-id:from:to :cc:subject:date:message-id:reply-to; bh=USC6Bnsy+nxr1NhjCQohP8NYz6UDwS+PLbFMkxSGmak=; b=enP059CkPjPYyIfBx0BfXYXM/Mp+P1h18zQn+bmDLG+93PeVtu149YeaIoAj5CqeW1 aGkz0n3aMEdGmA46D2bAmjq35Gll3QTBs/GiWXWMIjD3LLkFBn8+9KgvmoTSyGiqECDV riGx13HGe2gEMe9rRbFU6pVRm4FtCtPP0FvsUtSoqJFSGgrl7TwJyE/geamll2pOu9NY dayADma5ZmtX9o9G34uZP38SZ3AaACoCGbpuvUrUNgLvrPnSm1eqk3LSvs5T7gGtvaWS V7gDVYokR12sM+oqj9P51UmJhEB0p1RKO1o0xZX8qBcBx5zqIQznONF/5zLv0JtjBH/6 2nPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707214565; x=1707819365; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:cc:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=USC6Bnsy+nxr1NhjCQohP8NYz6UDwS+PLbFMkxSGmak=; b=l/YSmcM7/Uip2eAvrnp7fFHY6dw928uRWyV5SqAHQ+rqWJj4Ohu4VWxvOO/2/Oxafr 9Gp5YMhWahLpqxBxhCNv5r8mIGgkfI4UuwwyO9U6Ysgiwv+0NrF5VRXRpOAn5O3AVMi1 UxhV08vuIWJ03UH7upDw+M7HhZ5VKId1RymahLMEyLdP/mlJD3I2rJhJ+aU9SYgNnbEG I+tXrIPYcEkvWyfO2Ku8/hhsrGCTo2UlQFiCeDCPdWiqO6gpqC7ivrdsP2FDFy/HvuUA S+eO8I8vPpQDtq0hQDG3YzMy4QDfre++1wFooa/uwi8D6dFquOa9Q78cKmwIQBNcJea2 sLxQ== X-Gm-Message-State: AOJu0Ywls0yKWwqNx9euNCdEL9l5ce8ROwu2m0IxFALneqJQsWW5XzzT u7dqIo1o+vRcBofwbRiKhzFtO5ULsB2kQG7j9pF5cJpD1QMYqOdnvnVprfCTPvE= X-Google-Smtp-Source: AGHT+IE31AyzmRwTfFQGqEVcfav502VOyqnUcYiGw66Yfcp2QqroJurVNfXdTVQlE+0DgSlZKV+gEw== X-Received: by 2002:a17:907:7676:b0:a35:e22a:9d15 with SMTP id kk22-20020a170907767600b00a35e22a9d15mr1146567ejc.8.1707214565614; Tue, 06 Feb 2024 02:16:05 -0800 (PST) Received: from localhost.localdomain ([2001:871:5e:5287:f30:c9d9:c69e:124e]) by smtp.gmail.com with ESMTPSA id vo14-20020a170907a80e00b00a3700c7a8d2sm982864ejc.94.2024.02.06.02.16.05 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 06 Feb 2024 02:16:05 -0800 (PST) Message-ID: Subject: Re: Question on partitioning From: Laurenz Albe To: veem v Cc: pgsql-general Date: Tue, 06 Feb 2024 11:16:04 +0100 In-Reply-To: References: Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.3 (3.50.3-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 2024-02-06 at 00:26 +0530, veem v wrote: > On Mon, 5 Feb 2024 at 17:52, Laurenz Albe wrot= e: > > On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > > > In postgresql, Is it possible to partition an existing nonpartitioned= table having data > > > already residing in it and indexes and constraints defined in it, wit= hout the need=C2=A0of > > > manually moving the data around, to make it=C2=A0faster? > > > > There is no way to do that. >=20 > Which means for any such operation=C2=A0we need to create a new partition= table with that > structure and load that with the data from the existing=C2=A0partition ta= ble. You could use logical replication to keep the downtime short. > Additionally I see a lot of other restrictions=C2=A0like=C2=A0 > 1)When creating indexes on the partition table "concurrently" keywords ar= e not allowed. That is no problem. You create the index on ONLY the partitioned table, then you create indexes CONCURRENTLY on the partitions, then you attach these indexes as partitions to the index you created in the= beginning. As soon as the last index is attached, the index on the partitioned table b= ecomes valid. > 2)While creating foreign=C2=A0key ,=C2=A0 it does not allow a "not valid"= clause=C2=A0if the table > is partitioned. Create the foreign key on the partitions; the effect is the same. > 3) While creating indexes on this table or running any ALTER command, the= SELECT > queries running=C2=A0from other processes run longer. Does it take any= lock while > doing DDL on the base table in postgres? I am not sure what the "base table" is. Most DDL always locks the table in ACCESS EXCLUSIVE mode; that is not specific to partitioned tables. Yours, Laurenz Albe