Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths.

The upper limit for the insertion rate is tens of billions of rows per year.

For my implementation, the hash never needs to leave the database, but the hashed data must be compared with external data for existence frequently.

In my limited experience with optimization for these purposes, I assume that the best data type for the hash would be bytea. The alternative is of course a much longer hex string.

Is it correct that bytea is the optimal data type for these hashes?

If bytea is not optimal, what is?

How should my intent be implemented?


I'm using the hash of the text per Erwin Brandstetter's recommendation to be able to ensure that large text is unique. It is my limited understanding that raw binary data is always the most performant especially when compared to strings.

The hash only needs to be compared for existence to preempt uniqueness violations, so the hash happily never needs to leave the database. Because of libpqxx's incredible design, it appears as if the data can simply be input through a prepared statement and converted with (decode(md5($1::text), 'hex')). When I become more familiar with bytea insertion via libpqxx 3.1, I'll move it to c++.

For this implementation, collisions are acceptable because data can be reconstructed to conform without breaking the system.

If the maximum throughput is happily reached then it should be expected that the economic resources will be available to accommodate it; therefore, the primary concern will always be performance, so if my understanding of the capabilities of Postgres and its ability for partitioned tables to handle these amounts of rows is accurate, it will hopefully be the right tool for the job for a long while. Luckily, data older than a few seconds will never change, and it is my understanding that Postgres partitioned tables can make minced meat out of such data. If not, this will be one of those good problems.

share|improve this question
Better to link to the post than the username, so others who find this one can see what you're talking about. I think you probably mean dba.stackexchange.com/q/69161/7788 . –  Craig Ringer Jun 27 at 2:18
@CraigRinger Argh! Sorry Craig Ringer! Omitted totally by accident. Will edit... –  user32234 Jun 27 at 2:19

1 Answer 1

up vote 1 down vote accepted

bytea will be optimal for storing the hash.

It'll be transferred in/out of the database as a hex string anyway, unless you use PostgreSQL's binary wire protocol (supported by libpq and partly by PgJDBC) to transfer them.

For best results, store as bytea and have the client application use a PQexecParams call that requests binary results.

Though on re-reading, this is confusing:

For my implementation, the hash never needs to leave the database, but the hashed data must be compared with external data for existence frequently

Do you mean that the hash isn't transferred for comparison, the original unhashed text data is? If so, the above is irrelevant, as the binary protocol offers no benefits for text-form data.

Also: "tens of billions" of rows is a lot. PostgreSQL has quite a large per-row overhead at 28 bytes, so you're going to be losing a lot of space. Especially once you factor in index overheads too. Is PostgreSQL the right tool for this job?

A final thought: With that many rows, you're getting up into hash-collision territory. Do you care if it's possible - though unlikely - for two different strings to have the same hash, so an incorrect unique violation is reported? If that's a problem then a unique b-tree index on the hash probably isn't the right tool for the job.

share|improve this answer
Thank you for your excellently thorough edit Craig Ringer! I have hopefully clarified per your concerns. –  user32234 Jun 27 at 3:15
Also, please post a base64 encoded ed25519 public key...and please don't forget the signing key! ;) –  user32234 Jun 27 at 13:07
On a side note, I've gone bytea-crazy for other columns: why do I need to use PQexecParams? I'm parsing the text result in c++ to binary. Will I get a true performance boost by employing that method? If so, would you mind linking to a libpqxx example? Thank you so much in advance! –  user32234 Jun 30 at 8:04
I don't really work with libpqxx so I can't tell you off the top of my head. It might well be using binary transfers for bytea behind the scenes automatically. If in doubt, look at the on-the-wire data with Wireshark, or dig with a debugger. –  Craig Ringer Jun 30 at 8:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.