I have some byte data(millions of rows), and currently, I am converting to base64 first, and storing it as TEXT. The data is indexed on a row that contains base64 data. I assume Postgres does the conversion to base64 itself.
Will it be faster if I store using BYTEA data type instead?
How will the indexed queries be affected on two data types?
Add a comment
|
1 Answer
Converting bytes to text using Base64 will consume 33% more space than bytes. Even this would be faster, you will use quite more space on disk. Loading and storing data should be slower as well. I see no advantage in doing that.
Postgres supports indices on BYTEA columns. Since the bytes are shorter than the text, byte columns with indexes should be faster than text columns with indices as well.
8 Comments
AuthorProxy
bytea is slower than text, engineering.pivotal.io/post/bytea_versus_text_in_postgresql
clemens
@AuthorProxy: Unfortunately, the test is not very meaningful because it does not select according to the column. The test also includes implicit encoding and decoding operations that can produce completely different timings with other programming languages or adapters.
Ahmad Ferdous
@ciemens, Incorrect info of how many times more space base64 encoding uses. It's 1.33%: stackoverflow.com/a/13378842/2821632
MKesper
No, it's about 133% (so 33% more), in worst case (1 single byte) even 4 times as much (400%, or 300% more).
clemens
@AhmadFerdous: Thanks, you‘re right. I‘ve updated my post.
Nick T
@uberrebu "1.33 more space" to me implies 2.33x total space. Could also phrase it "1.33 times the original space"
|
