Consider storing hashes as binary data in SQL database about hashr HOT 2 CLOSED

I have built a somewhat representative database now with ~3M entries so going to try to do some performance comparisons for this.

from hashr.

Ok I did some tests with both replacing the VARCHAR(100) with BYTEA and BIT(256). I also tried to query both using SELECT ... WHERE ... IN ... and using WITH ... SELECT ... JOIN. In all tests I queried for 50 000 samples that did exist in the database and the same number of samples that did not exist in the database. The results are as follows:

VARCHAR + IN: min: 0.66, max: 0.71, avg: 0.68
VARCHAR + WITH: min: 1.08, max: 1.17, avg: 1.12
BYTEA (HEX) + IN: min: 0.84, max: 0.97, avg: 0.89
BYTEA (HEX) + WITH: min: 1.15, max: 1.29, avg: 1.20
BYTEA (RAW) + IN: min: 0.76, max: 0.88, avg: 0.80
BYTEA (RAW) + WITH: min: 1.15, max: 1.34, avg: 1.21
BIT (HEX) + IN: min: 1.00, max: 1.18, avg: 1.03
BIT (HEX) + WITH: min: 3.39, max: 3.56, avg: 3.45

I also compared the sizes of the databases:
using VARCHAR(100): 3999 MB
using BYTEA: 2741 MB
using BIT(256): 2870 MB

So in conclusion, using IN-statements are faster than joining with a temporary table using WITH. Using the BIT(256) type doesn't seem to be any good. It is also very cumbersome to work with so that can pretty much be discarded. BYTEA offers a ~1/3 reduction of the database size at the cost of about 15% lookup time. That is probably not a worthwhile trade-off considering that these databases will probably be in the <100GB range for most use-cases.

from hashr.