My first post and communication with the PostgreSQL user community. A monumental occassion....
Anyway, the reason I'm posting is to get some feedback on your experiences with PostgreSQL, good and bad. Those of you have converted to/from or use MySQL and PostgreSQL would be a great help (was it worth the switch? is it easy to administer?)
We are an open-source shop, but are coming to the ever-so evident shortcomings of MySQL (though, I must say, my experience with MySQL has been good - just time to move on). Specifically, I like the idea that I'll be able to use triggers, stored procedures, views, referential integrity and transactions....
Definately interested in your general comments, but I'll end with two specifc questions...
1.) How well does PostgreSQL handle BLOB data? Can it handle any multimedia form? Any problems?
2.) How big can you grow PostgreSQL? I've read many different reports...what are your experiences?
Well, I'm off to play around on my laptop implementation of PostgreSQL...I look forward to your comments. Many thanks in advance!
For storing binary data, I usually store it as text in the database, by first base64 encoding the data. This is a piece of cake if you're using PHP, as there is a base64_encode() and base64_decode();
The reason for this is that BLOB's in postgres are stored in the file system. The pg_dump utility used for backing up postgres databases will backup/restore the DB's link to the filesystem storage of BLOB's, but won't actually backup/restore the files themselves. So what you're left with is having to backup not only the pg_dump output, but the filesystem itself. And it can be tricky to do restores.
So like I said, I usually base64 encode my binary data and dump it into a text field. This is backed up with pg_dump, and a simple base64_decode on the returned data will give you a fully-functional binary.
... I hope you don't have any 1GB files you need to store (Oh, and if you do, you can split it up into multiple fields on the same row, and store 1.6TB files ... you can only store 20 1.6TB files in that table though before you have a 32TB table, and then need to create a new one ... did I mention there are unlimited # of tables??? )
True, you can encode binary data as text, but why not just use the binary string (BYTEA) data type? It works the same way as text columns, but is already intended for binary data. Most PostgreSQL client libraries already have functions to escape data for bytea, such as pg_escape_bytea() in PHP.