PostgreSQL vs MySQL for handling inserting/deleteing large quanties of blobs

Question

I have an application that I will soon be doing a significant rewrite of that stores a significant quantity of transient blobs in a database. The application will insert and delete a large number of blobs (up to 5mb in size each) during the course of each day. Currently the application uses a version of PostgreSQL that is very old (7.3.x). With this version of PostgreSQL we had to routinely run the external vacuum process to keep the database size under control, additionally this process required the application to be shutdown to function correctly.

We were looking at either upgrading to the newest PostgreSQL or migrating to another database. Specifically we were interested in moving to MySQL. I was wondering if anyone here was familiar with the blob handling support of the newest versions of these servers, and provide any suggestions on which one might perform best for an application that will constantly be inserting and removing blobs. Other feature differences between the two servers aren't an issue for us.

I did some research and found plenty of feature comparisons between MySQL and PostgreSQL, but nothing that really addressed this issue. I'm hoping someone here might have some experience with this aspect of one or both database systems.

Thanks

Answer 1

Postgres 7.x indeed was a major PITA when it comes to vacuuming. 9.0 is a lot better in this area. The autovacuum daemon can be configured on a per-table level since I think 8.3 and for the described scenario you would probably make it very aggressive for that table (or tables if more than one is involved).

I don't think it matters whether you delete rows with BLOBs (i.e bytea) column or not. Especially because the blobs are stored out-of-line anyway (you might need to configure the auto-vaccuum daemon for the so called TOAST table as well, but I'm not sure)

The question is rather how many rows (in percent of the total rows) you delete/update in the table, rather than how big each blob is.

As much as I like PostgreSQL I do have to admit that the whole vacuum topic (even though getting easier and easier which each release) is still one of its weakest points (and the source of a lot of trouble).

I can't say anything about MySQL as I have never used in a production environment. In contrast to you the other features (beside blobs) are important enough for me to stay away from MySQL - and if it's only for the license.