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 1qs2F7-006a2O-BH for pgsql-admin@arkaria.postgresql.org; Sun, 15 Oct 2023 14:37:21 +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 1qs2F4-00BJwU-KV for pgsql-admin@arkaria.postgresql.org; Sun, 15 Oct 2023 14:37:19 +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 1qs2F4-00BJwM-7j for pgsql-admin@lists.postgresql.org; Sun, 15 Oct 2023 14:37:18 +0000 Received: from mail1.dalibo.net ([51.159.93.128] helo=mail.dalibo.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qs2Ew-000nlQ-Nx for pgsql-admin@lists.postgresql.org; Sun, 15 Oct 2023 14:37:18 +0000 Received: from [192.168.74.160] (82-64-99-9.subs.proxad.net [82.64.99.9]) by mail.dalibo.com (Postfix) with ESMTPSA id 5B9D71F845; Sun, 15 Oct 2023 16:37:09 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1697380629; bh=s9GX8tQbgi0MGyRiL2mksUeI0L3DhuRoKvqWg5i2xco=; h=Date:Subject:To:References:From:In-Reply-To:From; b=h3+Ej7IW3ZRK//ByR1mWxlt5QYueXYRU0RIUhRmA25QASo1DypGNXys8C5YdTelGG 6pg2vlNsK6QRXXB3O0/uOyQf5uqL+bCYb4HsFgrFkQ1l7YK1HjCR4q/IQSomOc0x9E L6U+KW4NZpyzOO3GyxxxlzKLfMeb28Q44lZyZbJU= Message-ID: Date: Sun, 15 Oct 2023 16:37:08 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Big table and partition Content-Language: en-US, fr-FR To: Rajesh Kumar , Pgsql-admin References: From: Christophe Courtois Autocrypt: addr=christophe.courtois@dalibo.com; keydata= xsFNBFffvRkBEACz9SwjHcwUpg3Eg+UpuNeuxugWyg555J45Osi7J7m45qubSgxsK41HIgTt +bnfcpmi2x4EsygiHUB3sx5eVjr1xVL/nFECBK6uNaKmhckWuTPqiQErbo8UGQw3O3cj0sQc oO+OjPzwejh0q9zVHfJ0GcaYHx+RtwMnlQlXYHjalg6OU0YOtgfgiV96pR+ulKjsQR4PNuac ShTHwGPceBZn4/HOU5d4HI6swOBeomL3xGpkam9i/8v6rdUNfW39o4lk5NuNkuHIDyc241D0 MCEVDsjOxJUs0Z+E0Ulth4wdBnyCeH3yX1wfG5IGTZj0qazpte6zVfE97/uea0cMtShOCkyI c2faqegQokxPcWCGnIL/QSVI7fdgrQMZmHuDFI+nD7fBf+rPEuYlj+nHBZNJbzJNWVi/J7FK y/9ml9bhy9C8eaL5BEw9pxB5gLxKnMabGO1oImr5eUoaLMdDg1bhN6b135+a3DDRNK8D/DBx eTjmyv7rrVs7AC6hLGMrF4/K3uYNEsTC7Ord/xZNcUD5SQHqJYyM6ovHlbSrPL+eWIv2AYcS lTj2HOULcqyJ8KoExY8y+JnPRxll2qu2intel3F1qhnoZTG1ihUN2f4ywxAS0jHtfAIXcQMy idZBKipxOzGx/qUZMHNmmV5KPYPfo431qEM1lRMpZiSPmaMKrwARAQABzT1DaHJpc3RvcGhl IENvdXJ0b2lzIChEYWxpYm8pIDxjaHJpc3RvcGhlLmNvdXJ0b2lzQGRhbGliby5jb20+wsF4 BBMBAgAiBQJX370ZAhsDBgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgAAKCRB80jF0dHO1EkU+ D/9GQlmxrawbtt2Xw7D27BOsLJIp8Ei0u9HlkgSpSf8PdJ4Vj+46zT/GpS3C1aiDr/MZeh9I q+P4dtI6BzOYpK76YS3GKzVOLrLAHTOZUZaC8uDg6SPaaAsJ696og1fc50A2vbyn0+bSNqot 20yCSg1+FaFaRc63Ln2Ya0Us/E+JWLFJocRHmrek6gDFB6N8LOq5usrC4GTapV8zSssnDt5u P0DM0lbbjedumhN5pUxJYS4Yvionf3PqtEwyHRoUleDw4QYs/u2pSiFWGSzwNcqQwaPcvUTs tbsdV81RbQoFiSgG8P2SbXkhAAiv2i6I/AtSssjTFZy37JTx8o3R0/V+CDW5UD8vjgzllSmo CP+WGW4Ailn8GhB3zzJ0iM8uXYJRXn8G695e7vJDu5Vv7Xl5B9BjGtGRiIR4ZzO1EISAoBJM V2QWPHRFKzqKAWH55iqWJh8nL6xK+KLJsRE3MvRrNcIlJ/7XFT8eipPIRVXSBHdLfRnbaHTb PRxjhArXgydEM2Iskm/m41EB8oGKd0awHUM4dZXqkXpwL94wnSJ20Hb1fjbns/drIrylcyIz EfcQsxtms5pb0onEoQCAcKsaH7uimOYWGmbRbMowJBAZbKxVJTrR11iQXBN5K/3Rm8+lYc5w Cdn+y26Tq3ud/z4n0cbngRHQYCuTnvpnRIieQ87BTQRX370ZARAA0Vb302mKW8UkVWXhoosu /SQ6rnCvpjpZJuKJXbpjKKwYBORSq0SdobK796ooPGn97RDBp0boaTScp0WPnkNaJgUhtd3D 8FLa+Itzpgsed9LZcMvH9tR6qTQ0syGSyMSt5Z02Uum2pzd4t62eWApE7gvCStxkSbaI9nhO yKoSUWSrbmNBJNyqAKmxUrmB0gOfbVJ93RO3g+vziE9FlaBwoYPCxnAIZ61/tV2Fk2TeUotE IHp6SN3Ab6J/mzCM9PRzxQDDc07Aq23puZSPJvhxqHnx4HItxZhg/p1VfnvqZ4J1iBGpu/Hl RULurFxCX52dy8PYOdo5HA9Nc0icjJqb0qDQVK+/Nb4LPHxvOOW/koaOdzEjaV9t9M1EoZGm KA8gd1CQgd1g0oq7nJoYtBAHjg2mcjo9Zi9xuvdUI2biXrxN0eVoKFn8egOBFB0lleclUeR+ ARE/n/Dpyhf7wA4lKJ4u1wtGbfIx3gyQbJyiU1l6rTDbgUiNryOVYFfJSWPSz+ftBSqdnVxj MCrju0ilDGZo6ziBmggwO/zgoxuXXqJ94qPE3NJGxl8ZuvLMMy4OAUpQg3ZFBkAVJmAmwda6 1d1XGaDTxhRRvpYyNjO3PmD5QVDn76FHoHxxw8ZehqRXC8Dz7BVhoIGACVEM1jkDWGKEESNh hr2JY5/4lFWoqgcAEQEAAcLBXwQYAQIACQUCV9+9GQIbDAAKCRB80jF0dHO1EjQpD/wM5A2P LSshOUADSCYqzxiroZ/SgB1SkkZjQQb3NBZSDZeN7+NBzdvSxvajmGYOKOtzFYneIJm+l0Gv n6fE7KF5FO4kUW7P2wZBVQSPr2sGLJ+R3F0iIjCpuzeSP6dDy5rS9gCltAgBkhEDymztR+C5 SqhcMMxiLtgYlRBtpZVxs4hCtvu9XaNv0BfdgO8fN4wlyjPmR/HlsEOw1Pyig+5f41FUDn3O dYx2oWbPFHuyIORNiDjaLGI2lgaUdPjXbnAKtNJiovNHz+KL6VLbK2YpY/gSW+e1dy+8f1qa WCI8l0v0T3XwxePkaS4WVcQABltEkXwhYX6FWWJUYo/AlkbPZVmLae8voOZEvjMQzaBbC8Yp UIJI8hK3T3tor+dnPlyTnbfGSdpmDaVA1b37J78RqPbOAFCGfvZ6KfEkYoIF6U4RD8KaEP9P BwgF5pldL6y0p2X6JJZc324vITmymdIjitMkjhtobNNaYcSKnpOXC7nimb5ZcYEm2B6LEaDz GC1uKYQZH/g1JCtJ7bZppRE293k5E81JPdQ41+pDJneLo1HhYb+pY9ae4vSatsAeADFG2mgE P4IUMjM/HHgQCQhcO2O4r3stYmg2hMXPIYl2lGl5OIH+BfFAiTS5DuLx/B0UMXg3iHzBCuKF m89092/TMyaRS7j59rFeYxzhLdaHXA== In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, Le 14/10/2023 à 22:46, Rajesh Kumar a écrit : > In my env, I have a table 30GB where data is getting inserted directly > whenever the new block is created in block chain... > I have been asked to do partition, i said manager since we are not at > all using the past data, partition is not required. Instead we can stop > the sync (downtime not required), change the name of table , create a > new table with same structure with original name and keep only tis month > data. Then sync the blockchain so that new data will come to newly > created table and old data will be there simply as a storage as we r not > selecting any query from old data... In fact, you want to do manual partitioning. Why reinvent the wheel? > Note: on a longer run, data will keep on coming in whenever new block is > created. > Is that right ? Any suggestions ? Better options ? What problem do you have to solve? 30 GB is not so big, but since when do you store data? Will you purge the table? What is the main access key of your queries (critical for the partitioning key)? Without other information I'd say that if you never read the former months again, partition by month, perhaps by year. -- Christophe Courtois Consultant DALIBO