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 1q4M81-0000qG-Id for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 13:44:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q4M80-0005iW-D3 for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 13:44:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q4M7z-0005iL-UI for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 13:44:40 +0000 Received: from smtp70.iad3a.emailsrvr.com ([173.203.187.70]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q4M7x-002tv8-0s for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 13:44:38 +0000 Received: from app48.wa-webapps.iad3a (relay-webapps.rsapps.net [172.27.255.140]) by smtp9.relay.iad3a.emailsrvr.com (SMTP Server) with ESMTP id B9F8E55FE for ; Wed, 31 May 2023 09:44:35 -0400 (EDT) Received: from kartographia.com (localhost.localdomain [127.0.0.1]) by app48.wa-webapps.iad3a (Postfix) with ESMTP id A06FBE1811 for ; Wed, 31 May 2023 09:44:35 -0400 (EDT) Received: by apps.rackspace.com (Authenticated sender: peter.borissow@kartographia.com, from: peter.borissow@kartographia.com) with HTTP; Wed, 31 May 2023 09:44:35 -0400 (EDT) X-Auth-ID: peter.borissow@kartographia.com Date: Wed, 31 May 2023 09:44:35 -0400 (EDT) Subject: Hash Index on Partitioned Table From: "peter.borissow@kartographia.com" To: pgsql-general@lists.postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative;boundary="----=_20230531094435000000_79451" Importance: Normal X-Priority: 3 (Normal) X-Type: html X-Client-IP: 170.250.244.158 Message-ID: <1685540675.65414951@apps.rackspace.com> X-Mailer: webmail/19.0.23-RC X-Classification-ID: b03d17f4-e740-49d7-9f7e-f54ae52668fe-1-1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_20230531094435000000_79451 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =0ADear PostgreSQL Community,=0A =0AI have a rather large database with ~25= 0 billion records in a partitioned table. The database has been running and= ingesting data continuously for about 3 years.=0A =0AI had a "regular" BTr= ee index on one of the fields (a unique bigint column) but it was getting t= oo big for the disk it was on. The index was consuming 6.4 TB of disk space= .=0A =0AI created a new disk with double the size, dropped the original ind= ex, and started to generate a new index.=0A =0AAfter doing some research I = decided to try to create a hash index instead of a BTree. For my purposes, = the index is only used to find specific numbers ("=3D" and "IN" queries). F= rom what I read, the hash index should run a little faster than btree for m= y use case and should use less disk space.=0A =0AAfter 115 hours, the hash = index is still generating and is using significantly more disk space than t= he original BTree index (8.4 TB vs 6.4 TB). I don't know how to check the s= tatus of the index creation task so I can't really estimate how much longer= it will take or how much disk space it will consume.=0A =0AQuestions:=0A = =0A(1) Why is the hash index consuming more disk space than the btree index= ? Is it because the hash of the bigint values larger than the storing the b= igints in the btree?=0A(2) Are there any known issues having a hash index o= n this many records?=0A(3) Are there any known issues having a hash index o= n partitioned tables?=0A(4) Is there any way to estimate when the index pro= cess will complete?=0A =0AServer info:=0A - PostgreSQL 13=0A - Ubuntu 20.04= .6 LTS=0A - 64 cores (only 1 is ever used during index creation)=0A - Memor= y usage is steady at 58GB/188GB=0A - All disks are high speed NVMe drives= =0A - 1,686 tables in the partition=0A =0AThanks in advance,=0APeter ------=_20230531094435000000_79451 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Dear PostgreSQL Commun= ity,

=0A

 

=0A

I have a = rather large database with ~250 billion records in a partitioned table. The= database has been running and ingesting data continuously for about 3 year= s.

=0A

 

=0A

I had a "re= gular" BTree index on one of the fields (a unique bigint column) but it was= getting too big for the disk it was on. The index was consuming 6.4 TB of = disk space.

=0A

 

=0A

I = created a new disk with double the size, dropped the original index, and st= arted to generate a new index.

=0A

 

=0A

After doing some research I decided to try to create a has= h index instead of a BTree. For my purposes, the index is only used to find= specific numbers ("=3D" and "IN" queries). From what I read, the hash inde= x should run a little faster than btree for my use case and should use less= disk space.

=0A

 

=0A

A= fter 115 hours, the hash index is still generating and is using significant= ly more disk space than the original BTree index (8.4 TB vs 6.4 TB). I don'= t know how to check the status of the index creation task so I can't really= estimate how much longer it will take or how much disk space it will consu= me.

=0A

 

=0A

Questions:=

=0A

 

=0A

(1) Why is th= e hash index consuming more disk space than the btree index? Is it because = the hash of the bigint values larger than the storing the bigints in the bt= ree?

=0A

(2) Are there any known issues having a has= h index on this many records?

=0A

(3) Are there any = known issues having a hash index on partitioned tables?

=0A

(4) Is there any way to estimate when the index process will comple= te?

=0A

 

=0A

Server inf= o:

=0A

 - PostgreSQL 13

=0A

 - Ubuntu 20.04.6 LTS

=0A

 - 64 cores = (only 1 is ever used during index creation)

=0A

&nbs= p;- Memory usage is steady at 58GB/188GB

=0A

 -= All disks are high speed NVMe drives

=0A

 - 1,= 686 tables in the partition

=0A

 

=0A

Thanks in advance,

=0A

Peter

------=_20230531094435000000_79451--