Postgres - Preferable to store byte array as BYTEA in Postgres or base64 encode the values and store them as TEXT

Question

I'm currently working to move some plaintext data in Postgres to an encrypted format. I'm using Go and most cryptographic functions intake and output byte arrays. I also need to support key rotation and will generally by using the byte format to pack additional data at the front of values for metadata.

The columns are currently TEXT in Postgres 12.

Would there be any advantage to storing the output directly as BYTEA (in a new column) or would base64 encoding the values and storing them back into the same column provide better performance (faster reads/writes).

Both approaches would require an equal amount of refactoring to support other SELECT statements in the code.

I don't have a specific answer for you, but base64-encoding the bytes increases the required storage space by around 25%, and of course requires an encoding/decoding step in each direction. From those facts alone I would expect a BYTEA column to be more performant. — larsks
– larsks, Commented Sep 9, 2020 at 0:33
@larsks, 33%. 8 bits of output (one ASCII character) for every 6 bits of input (2**6 = 64). — Peter
– Peter, Commented Sep 9, 2020 at 8:00

Laurenz Albe · Accepted Answer · 2020-09-09 06:06:57Z

5

Definitely go with bytea. Converting to and from Base-64 is just an unnecessary waste of CPU time, and the result will waste storage space.

answered Sep 9, 2020 at 6:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - Preferable to store byte array as BYTEA in Postgres or base64 encode the values and store them as TEXT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related