1

I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).

Initial thought was to enforce a valid hex string storing it as BYTEA will be good idea, but impact I evaluated was the use of ENCODE on select queries.

I did some performance benchmarks, considering both tables have few million rows;

# A file query_with_char.sql
SELECT "key" FROM table_varchar;;

# A file query_with_binary.sql
SELECT ENCODE("key", 'hex') FROM table_binary;

pgbench -c 30 -T 120 -n -f ./query_with_binary.sql -f ./query_with_char.sql -P 5 -S my_db

SQL script 1: ./query_with_binary.sql
 - weight: 1 (targets 33.3% of total)
 - 236 transactions (34.6% of total, tps = 1.876072)
 - latency average = 8896.888 ms
 - latency stddev = 2548.701 ms
SQL script 2: ./query_with_varchar.sql
 - weight: 1 (targets 33.3% of total)
 - 225 transactions (33.0% of total, tps = 1.788628)
 - latency average = 7164.604 ms
 - latency stddev = 2209.866 ms

I am unable to understand why the performance of query ENCODE is faster compared to normal string. How is PostgreSQL able to do encoding on million rows faster than just fetching the string columns?

Can someone explain what might be wrong in the above test?

  • 1
    The cost of encoding 32 bytes into hex is probably very small compared to the total cost of running the query. – Daniel Vérité Nov 2 '18 at 13:44
  • To make sense of your test I would like to see the table definition for table_varchar and table_binary, and how you inserted test values. And your version of Postgres, obviously. – Erwin Brandstetter Nov 2 '18 at 22:15
  • @ErwinBrandstetter Consider it a table with only field key. In one table its BYTEA type and in other its CHAR(64). My PostgreSQL is 9.6.9, running in a docker container. I used real data and kept the only key column. – Nazar Hussain Nov 2 '18 at 23:23
2

encode() is a very cheap function. I would not expect any measurable impact at all in your test.

The difference is almost certainly due to the much smaller storage size of bytea as compared to char(64). Consider:

SELECT pg_column_size('90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271'::char(64)) AS size_char64
     , pg_column_size(decode(text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271', 'hex')) AS size_bytea;

 size_char64 | size_bytea
-------------+------------
          68 |         36

The dominant factor for performance of simple SELECT queries is the number of data pages that have be read.

"Best" data type?

Obviously the choices are BYTEA and CHAR(64).

If your goal is to optimize performance, consider a third option:
2 uuid columns

To understand, first read:

And:

Then consider this demo (executed on pg 11, but true for all modern versions):

RAM size:

SELECT pg_column_size(t64)                   AS c_text
     , pg_column_size(t64::char(64))         AS c_char64
     , pg_column_size(decode(t64, 'hex'))    AS c_bytea
     , pg_column_size( left(t64, 32)::uuid)
     + pg_column_size(right(t64, 32)::uuid)  AS c_2x_uuid
FROM  (SELECT text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271') t(t64);

 c_text | c_char64 | c_bytea | c_2x_uuid 
--------+----------+---------+-----------
     68 |       68 |      36 |        32

Disk size (compressed format):

CREATE TEMP TABLE c64 AS 
SELECT t64                    AS c_text
     , t64::char(64)          AS c_char64
     , decode(t64, 'hex')     AS c_bytea
     , left (t64, 32)::uuid   AS c_uuid1
     , right(t64, 32)::uuid   AS c_uuid2
FROM  (SELECT text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271') t(t64);

SELECT pg_column_size(c_text)    AS c_text
     , pg_column_size(c_char64)  AS c_char64
     , pg_column_size(c_bytea)   AS c_bytea
     , pg_column_size(c_uuid1)
     + pg_column_size(c_uuid2)   AS c_2x_uuid
FROM   c64;

 c_text | c_char64 | c_bytea | c_2x_uuid 
--------+----------+---------+-----------
     65 |       65 |      33 |        32

db<>fiddle here

The seemingly minor difference between 33 and 32 bits can actually make a difference of 8 bytes, because several storage mechanisms require padding at multiples of 8 bytes.

Repeat your test with 2 UUIDs. I am confident it will come out on top.

  • I would have chosen uuid data type, but data in my scenario is not restricted to this format. Normally its 64 character long hexadecimal strings and sometimes 128. I was surprised in start but later convinced that encode is really a cheap method which don't have any impact on fetching data. – Nazar Hussain Nov 2 '18 at 23:22
  • If it's either 64 or 128 hex characters, you can still make it work with 4 uuid columns. Two additional columns with NULL, typically cost nothing or very little extra. See: stackoverflow.com/a/12147130/939860 – Erwin Brandstetter Nov 2 '18 at 23:25
  • Yes but then concatting those columns together will cause extra query everywhere in the application. Instead of getting one single column. – Nazar Hussain Nov 2 '18 at 23:27
  • 1
    You can have a view or function wrapping this and send all queries to the view or use the function. (Be sure to handle nulls properly.) Just saying. If you want top performance, that might be the way. bytea will be much better than char(64) already. – Erwin Brandstetter Nov 2 '18 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.