BYTEA sloupce

No, první řešení se kterým asi přijdete je použití sloupců typu bytea. Konec konců, tyto sloupce byly navrženy právě pro ukládání binárních dat, ne? Ano, a ve většině případů je to velmi dobře fungující řešení - prostě vytvořte tabulku, například

CREATE TABLE Files (
    id       SERIAL      PRIMARY KEY,
    filename VARCHAR(64) NOT NULL,
    filesize INT         NOT NULL,
    data     BYTEA       NOT NULL,
    created  TIMESTAMP   NOT NULL,
    ... další metadata ...
);

a potom vložte soubor se všemi daty:

INSERT INTO Files VALUES (1, 'test_file.txt', 10, 'abcdefghij', ...);

Jistě, toto funguje ... pokud nepotřebujete ukládat velké soubory, kde "velký" znamená překračující množství dostupné paměti. To je důsledkem skutečnosti že je třeba sestavit celý SQL INSERT příkaz se všemi daty, což může spotřebovat spoustu paměti (a potřeba escapovat binární data vkládaná do BYTEA sloupců to ještě zhoršuje).

Například v PHP existuje nastavení memoty_limit, obvykle nastavená na 8MB (což je celkem rozumná velikost), a tento limit lze překročit již při insertu 2MB souboru (kvůli nutnosti escapování dat pro použití v INSERTu). Můžete sice zvýšit hodnotu memory_limit, ale na sdíleném serveru nebude admin asi nadšený (a já ho naprosto chápu).

Poněkud prasácký způsob obejití omezení paměti spočívá v doplňování dat po "kusech" - při prvním INSERTu použít například pouze prvních 256kB, a potom doplňovat zbývající data pomocí UPDATE opět po 256kB úsecích - zhruba takto:

INSERT INTO Files VALUES (1, 'test_file.txt', 10, 256kB of data, ...);

FOR c IN chunks LOOP
    UPDATE FILES SET data = data || 256kB of data WHERE id = 1;
END LOOP;

Nicméně zatímco toto řešení skutečně eliminuje problém s vyčerpáním paměti, dochází v databázi ke vzniku mnoha mrtvých řádků (tzv. dead tuples) které bude muset autovacuum demon všechny vyčistit.

Při načítání souboru z databáze (např. v případě downloadu) musíte být na množství potřebné paměti také opatrní - pokud je soubor malý můžete ho načíst přímo (a odescapovat ho), ale pokud je soubor velký, musíte ho načítat po kouscích (pomocí funkce substr).

Závěr: Pokud potřebujete ukládat pouze malé soubory (a víte že ani později nebudou žádné velké soubory), můžete bezpečně použít BYTEA sloupec. Pokud většina souborů bude malých ale čas od času budete potřebovat uložit velký soubor, můžete i tak použít BYTEA sloupec ve spojení s UPDATE hackem popsaným výše. Ale pokud budete potřebovat ukládat mnoho velkých souborů, tak BYTEA sloupce asi nejsou tím nejlepším řešením.

BLOBy

Další PostgreSQL vlastnost používaná pro ukládání binárních dat je BLOB - binary large object. BLOB není typ sloupce - tyto objekty jsou ukládány mimo tabulku (ačkoliv v systémové tabulce) a jsou odkazovány OID hodnotou. PostgreSQL obsahuje funkce pro manipulaci s BLOBy - lo_create, lo_open, lo_close, lo_import, lo_export atd. (detaily najdete v PostgreSQL dokumentaci).

Navíc PHP obsahuje PDO s podporou pro PostgreSQL LOB support, takže je možné vytvářet BLOBy přímo z PHP - viz. příklady zde. To znamená že můžete vytvořit BLOB (a zapamatovat si jeho OID), nahrát do něj potřebná binární rada, a následně použít OID v tabulce jako odkaz na BLOB. Ve skutečnosti můžete BLOBY vytvářet automaticky při insertu do hlavní tabulky.

Co je na BLOBech tak pěkného? Neexistují žádné problémy s paměťovými limity, protože data jsou streamována na server (všimněte si funkce stream_copy_to_stream v příkladech) - pouze soubor otevřete jako stream, a nechte systém překopírovat data na server.

Co na BLOBech není pěkného? Neexistuje referenční integrita - můžete klidně smazat BLOB který je stále odkazován z tabulky. Můžete sice vytvořit AFTER UPDATE a AFTER DELETE triggery pro odstranění osiřelých BLOBů, nebo můžete použít  "lo" contrib balíček. Každopádně žádné z těchto řešení nevynucuje referenční integritu takže vám nějaký blbeček klidně může umazat BLOBy přímo - a proto jsem nakonec přišel s následujícím řešením.

moje řešení ...

Při návrhu "perfektního" řešení pro ukládání souborů jsem si definoval následující žádoucí vlastnosti:

• transakčnost
• korektně vynucovaná referenční integrita
• nízké nároky na paměť (žádné problémy s paměťovými limity)
• podpora pro streamování (v ideálním případě)

A přišel jsem s řešením založeným na dvou tabulkách - "Files" a "File_data."

Tabulka "Files" obsahuje informace o souborech (metadata) ale žádná data nebo odkazy na souborový systém nebo BLOB:

CREATE TABLE Files (
    id       SERIAL      PRIMARY KEY,
    filename VARCHAR(64) NOT NULL,
    filesize INT         NOT NULL,
    created  TIMESTAMP   NOT NULL,
    ... other metadata ...
);

Data souboru jsou uložena v tabulce "File_data", rozdělená do segmentů volitelné délky:

CREATE TABLE File_data (
    file_id  INT,
    segment  INT,
    data     BYTEA,
    PRIMARY KEY (file_id, segment)
);

ALTER TABLE File_data ADD FOREIGN KEY (file_id) REFERENCES Files(id);

Ohledně požadovaných vlastností definovaných výše - první dvě (transakčnost, referenční integrita) jsou evidentně splněny. Třetí (nízké paměťové nároky) záleží pouze na velikosti segmentů na které jsou rozděleny soubory v tabulce "File_data" protože velikost segmentu přímo ovlivňuje potřebné množství paměti - 8kB segment potřebuje zhruba 32kB paměti.

Poslední požadavek (podpora pro streamování) splňen není - alespoň ne doslova, protože není možné otevřít soubor jako stream (jako je to možné s BLOBy). Nicméně použitím dostatečně malých segmentů lze streamování dostatečně věrně nasimulovat.