From: Tom Lane <tgl@sss.pgh.pa.us>
To: peter.borissow@kartographia.com <peter.borissow@kartographia.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Hash Index on Partitioned Table
Date: Wed, 31 May 2023 10:07:50 -0400
Message-ID: <2657.1685542070@sss.pgh.pa.us> (raw)
In-Reply-To: <1685540675.65414951@apps.rackspace.com>
References: <1685540675.65414951@apps.rackspace.com>
"peter.borissow@kartographia.com" <peter.borissow@kartographia.com> writes:
> 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 years.
> I had a "regular" 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.
That's ... really about par for the course. Each row requires an 8-byte
index entry, plus 12 bytes index overhead. If I'm doing the math right
then the index is physically about 78% full which is typical to good for
a b-tree. Reindexing would remove most of the extra space, but only
temporarily.
> After 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 ("=" and "IN" queries). From what I read, the hash index should run a little faster than btree for my use case and should use less disk space.
I'm skeptical. The thing to bear in mind is that btree is the mainstream
use-case and has been refined and optimized far more than the hash index
logic.
> (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 bigints in the btree?
From memory, the index entries will be the same size in this case,
but hash might have more wasted space.
> (4) Is there any way to estimate when the index process will complete?
An index on a partitioned table isn't a single object, it's one index per
partition. So you should be able to look at how many partitions have
indexes so far. You might have to drill down to the point of counting how
many files in the database's directory, if the individual indexes aren't
showing up as committed catalog entries yet.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter.borissow@kartographia.com
Subject: Re: Hash Index on Partitioned Table
In-Reply-To: <2657.1685542070@sss.pgh.pa.us>
References: <1685540675.65414951@apps.rackspace.com> <2657.1685542070@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox