Re: Tweaking bytea / large object block sizes?

Le dimanche 12 juin 2011 à 18:00 +0200, Hanno Schlichting a écrit :

> I'm looking into storing binary data in Postgres and trying to
> understand how data is actually stored in the database. The dataset
> I'm looking at is images, photos, pdf documents which should commonly
> be at a minimum 100kb, on average 10mb and can scale up to 100mb for
> each document. I want to store this data in the database, as I need
> transactional integrity and want to avoid the extra complexity of
> managing shared filesystems between a number of frontend application
> servers and database backends.
>

I had a similar requirement for the app that's in my sig. It uses a
PostgreSQL database, but the binary content of the documents resides in
an SQLite database.

That way, my PostgreSQL database remains very small, easy to manage and
backup. I have a separate procedure to do incremental backups of the
documents as they are added.

The SQLite db looks like this :

CREATE TABLE tbldocument_content (id_courrier INTEGER NOT NULL PRIMARY
KEY, content blob);
CREATE TABLE tbldocument_state (id_courrier INTEGER NOT NULL, backed_up
integer default 0, date_created date default (date('now')), FOREIGN
KEY(id_courrier) REFERENCES tbldocument_content(id_courrier));
CREATE INDEX tbldocument_state_backed_up_idx ON
tbldocument_state(backed_up);
CREATE INDEX tbldocument_state_id_courrier_idx ON
tbldocument_state(id_courrier);
CREATE TRIGGER create_document_state AFTER INSERT ON
tbldocument_content
BEGIN
INSERT INTO tbldocument_state (id_courrier) VALUES (NEW.id_courrier);
END;
CREATE TRIGGER drop_document_state AFTER DELETE ON tbldocument_content
BEGIN
DELETE FROM tbldocument_state WHERE id_courrier=OLD.id_courrier;
END;

id_courrier is generated by the PostgreSQL db.

Works great. You can't see it on in action on the web site with the demo
account, though.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique