Re: BLOBS in DB and MDB

Marko Kaening wrote:

Hi Jeroen,

How do people store their binary data (pdf's, word docs etc) in Postgres? I noticed you can store it in a TEXT field (up to 1GB IIRC), but also using OID's. Which techniques do MDN/DB use?

as far as I know it is recommended never to store binary data like this directly in a database, instead rather store there location in a file system. This speeds everything up dramatically and is safer, assuming the file system is held consistent with the DB. Marko Hi,

At the moment I'm using MySql and I store the documents directly in the DB in seperate table. I'm very happy with that method. Using this method people can upload different documents with the same name, otherwise I would have had to create unique names for each document, and then store them on the FS. Also when storing them in the database I find it easier to manage them. For instance: when I delete a section from the database all the documents in that section are automcatically deleted because of the foreign keys, I don't need to write any additional code for that. Using the FS I would have had to find out which documents are in the section that is to be deleted, find out the FS locations and then unlink them. You also have to watch out with permissions. If everything is stored in the DB it all shares the same access right, which makes things easier. As for speed, I haven't had any issues with speed. We have a fast machine and only very limited traffic because the application runs on a password protected sites with only 100 users or so, but I agree if speed is an issue you should use probably use the FS. Anyway, back to to my question: I saw that Mdb has a special class for handling LOBs, does anyone know how PEAR::DB handles LOBs ? Jeroen