1

I've seen this on one of the production databases I've come across and these images apparently cover a large portion of their DB. After researching a lot I couldn't really find a lot of good answers pertaining as to why it is actually bad in terms of performance.

Yes, it is not stream friendly and takes up ~33% more space: https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data

Sure, it gives wrong hints to DB: https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data

And other answers like: storing files on mongodb don't really explain why this is problem

But, what is actually going on that might cause databases that store images as base64 encoded string to lose performance? Why would a DB lose performance on selects(or any other aspect) due to such practices?

I would also like an answer that specifies a NoSQL(MongoDB) vs RDBMS(PostgreSQL) performance in this regard. Does the nature of having things in memory like MongoDB make the performance hit even worse? And if so, why? How is this image saved as a base64 string behaving on both systems?

  • 4
    Avoiding to read/write 33% of additional data is a sufficient reason. – Daniel Vérité May 8 at 12:29
  • PostgreSQL can run databases in hundreds of GB and even TB and it still wouldn't perform as bad as a smaller DB with base64 encoded strings for images inside. Sure the extra size is a bad enough reason to not do it... But it doesn't explain why it degrades performance considering there are huge databases out there that still perform well. – Chessbrain May 8 at 12:33
2

The one reason I can see is that you are doing unnecessary work that way. PostgreSQL will compress and toast the base64 string, so you have to pay the price of compression and decompression, unless you set the column to EXTERNAL, then you don't compress, but you waste storage space and I/O bandwidth.

Hint: if you store compressed binary data in PostgreSQL, set the bytea column to EXTERNAL storage. Otherwise you waste CPU in a futile attempt to compress the data.

| improve this answer | |

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.