postgres data type binarny object blob lob bytea - ghdrako/doc_snipets GitHub Wiki

Trzeba zadać sobie pytanie, czy pliki binarne mają swoje miejsce w relacyjnej bazie danych. Są one zazwyczaj znacznie większe niż dane tradycyjne. Ich objętość może zatem stać się ogromna, a tym bardziej, jeśli pliki binarne zostaną zmodyfikowane, ponieważ sposób działania PostgreSQL będzie miał tendencję do ich duplikowania. Będzie to miało wpływ na fragmentację, liczbę logów, rozmiar kopii zapasowych i wszelkie operacje konserwacyjne. Interesujące jest przechowywanie w bazie danych danych, które będą wymagały przeszukania, a rzadko przeszukujemy duże pliki binarne. Ogólnie rzecz biorąc, większość danych binarnych, które chcielibyśmy powierzyć bazie danych, można przechowywać w tradycyjny sposób, a PostgreSQL zawiera jedynie ścieżkę lub adres URL do pliku.

PostgreSQL oferuje wybór pomiędzy dwiema metodami zarządzania danymi binarnymi:

  • bytea: typ jak każdy inny;
  • Large Object: osobne obiekty, którymi można zarządzać niezależnie od tabel.

bytea

CREATE TABLE demo_bytea(a bytea);
INSERT INTO demo_bytea VALUES ('bonjour'::bytea);
SELECT * FROM demo_bytea ;
------------------
\x626f6e6a6f7572

Wstawiliśmy ciąg „hello” do pola „bytea”, a właściwie jego binarną reprezentację w obecnym kodowaniu (UTF-8). Po przeszukaniu tabeli zobaczymy tekstową reprezentację pola „bytea”. Zaczyna się ona od „\x”, co oznacza kodowanie szesnastkowe. Następnie każda para wartości szesnastkowych reprezentuje jeden bajt. Dostępny jest drugi format wyświetlania: escape:

SET bytea_output = escape ;
SELECT * FROM demo_bytea ;
--------
bonjour 
INSERT INTO demo_bytea VALUES ('journée'::bytea);
SELECT * FROM demo_bytea ;
----------------
bonjour 
journ\303\251e

Format wyjściowy escape uwzględnia zatem tylko wartości, których nie da się przedstawić w 7-bitowym kodzie ASCII. Ten format może być bardziej zwarty w przypadku danych tekstowych, głównie w alfabecie łacińskim bez akcentów, gdzie największe znaki nie wymagają escape'owania. Jednak format szesnastkowy jest znacznie wydajniejszy w konwersji i jest domyślny od wersji PostgreSQL 9.0.

Uwaga W przypadku starszych aplikacji lub tych, które pozostały z tą konfiguracją, może zaistnieć konieczność wymuszenia wyjścia bytea_output, w przeciwnym razie może dojść do uszkodzenia.)

Aby załadować plik bezpośrednio, możesz skorzystać z funkcji pg_read_binary_file wykonywanej przez serwer PostreSQL:

INSERT INTO demo_bytea (a) SELECT pg_read_binary_file ('/chemin/fichier');

Teoretycznie pole typu bytea może zawierać 1 GB. W praktyce ograniczymy się do znacznie mniejszej ilości, choćby dlatego, że pg_dump popełnia błąd, gdy musi wyeksportować bajty większe niż około 500 MB (dekodowanie podwaja liczbę bajtów i przekracza limit 1 GB). Oficjalna dokumentacja12 zawiera listę funkcji do kodowania, dekodowania, ekstrakcji, haszowania itp. bajtów.

Large Object

Large Object to obiekt całkowicie niezwiązany z tabelami. (W rzeczywistości jest przechowywany w tabeli systemowej pg_largeobject). Kod musi zatem zarządzać tym obiektem osobno:

  • utworzyć duży obiekt i zapisać w nim to, co chcesz;
  • zapisać odwołanie do tego dużego obiektu w tabeli (z typem lob);
  • wykonać zapytanie o obiekt oddzielnie od tabeli;
  • jawnie usunąć go, gdy nie będzie już do niego odwoływać się: nie zniknie on automatycznie!

Duży obiekt wymaga zatem większych nakładów w postaci kodu. W zamian ma on następujące zalety:– rozmiar do 4 TB, co nadal nie jest zalecane;

  • możliwość bezpośredniego dostępu do części (na przykład bajtów od 152 000 do 153 020), co pozwala na przesyłanie jej w częściach bez ładowania jej do pamięci (w szczególności sterownik JDBC PostgreSQL udostępnia klasę LargeObject);
  • możliwość modyfikacji tylko tej części bez przepisywania wszystkiego. Istnieje kilka metod czyszczenia dużych obiektów, które stały się bezużyteczne:
  • wywołanie funkcji lo_unlink w kodzie klienta — ryzykując zapomnienie;
  • użycie funkcji wyzwalacza lo_manage dostarczanej przez moduł contrib lo: (zobacz dokumentację14, jeśli duże obiekty nigdy nie są odwoływane więcej niż raz;
  • regularne wywoływanie programu vacuumlo (również contrib15): wyświetla on wszystkie duże obiekty, do których odwołują się bazy danych, a następnie usuwa pozostałe. To rozwiązanie jest oczywiście nieco obciążające.

Uzycie odnosnikow do plikow

CREATE TABLE fichiers (nom text PRIMARY KEY, data OID); --  tabela plików z tekstem i kolumną umożliwiającą referencję do dużych obiektów

psql -c "\lo_import '/etc/passwd'"  --–  Import lokaln plik za pomocą psql do dużego obiektu.
lo_import 6821285                   -- zwraca OID
INSERT INTO fichiers VALUES ('/etc/passwd',6821285) ; -- wstawic oid do tablicy

INSERT INTO fichiers SELECT 'postgresql.conf',lo_import('/var/lib/pgsql/15/data/postgresql.conf') ; -- automatyzacja obu powyzszych czynnosci

-- Wyświetl zawartość tych różnych plików za pomocą psql.
psql -c "SELECT nom,encode(l.data,'escape') \
FROM fichiers f JOIN pg_largeobject l ON f.data = l.loid;"

-- Zapisanie ich w plikach lokalnych
psql -c "\lo_export loid_retourné '/home/dalibo/passwd_serveur';"