Date: Fri, 11 Apr 2008 10:25:44 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jon Stewart <jonathan.l.stewart@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Creating large database of MD5 hash values
Message-ID: <20080411142543.GA6442@alvh.no-ip.org>
References: <c0787d8a0804100748l51b63c7fx34db59e33f13c208@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <c0787d8a0804100748l51b63c7fx34db59e33f13c208@mail.gmail.com>
User-Agent: Mutt/1.5.17+20080114 (2008-01-14)

Jon Stewart escribió:
> Hello,
> 
> I am creating a large database of MD5 hash values. I am a relative
> newb with PostgreSQL (or any database for that matter). The schema and
> operation will be quite simple -- only a few tables, probably no
> stored procedures -- but I may easily end up with several hundred
> million rows of hash values, possible even get into the billions. The
> hash values will be organized into logical sets, with a many-many
> relationship. I have some questions before I set out on this endeavor,
> however, and would appreciate any and all feedback, including SWAGs,
> WAGs, and outright lies. :-) I am trying to batch up operations as
> much as possible, so I will largely be doing comparisons of whole
> sets, with bulk COPY importing. I hope to avoid single hash value
> lookup as much as possible.

If MD5 values will be your primary data and you'll be storing millions
of them, it would be wise to create your own datatype and operators with
the most compact and efficient representation possible.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support