PostgreSQL bytea vs. smallint []

Chcę zaimportować duże (100Mb - 1 GB) dane szeregów czasowych do bazy danych PostgreSQL. Dane pochodzą z plików w formacie EDF , które dzielą dane na „rekordy” lub „epoki” trwające zwykle kilka sekund. Rekord każdej epoki przechowuje sygnały dla każdego kanału danych jako sekwencyjne tablice krótkich liczb całkowitych.

Jestem zobowiązany do przechowywania plików w bazie danych, w najgorszym przypadku jako BLOB. Biorąc to pod uwagę, chciałbym zbadać opcje, które pozwoliłyby mi zrobić coś więcej z danymi w bazie danych, na przykład ułatwić zapytania na podstawie danych sygnałowych.

Mój pierwotny plan polega na przechowywaniu danych jako jednego wiersza na rekord epoki. To, co próbuję rozważyć, to to, czy zapisać rzeczywiste dane sygnału jako typy bajtowe, czy małe [[] (a nawet małe [] []). Czy ktoś mógłby polecić jedno nad drugim? Interesują mnie koszty przechowywania i dostępu. Użycie zostanie wstawione raz, od czasu do czasu czytaj, nigdy nie aktualizuj. Gdyby jeden był łatwiej zapakowany jako typ niestandardowy, tak że mogłem dodać funkcje do analizy porównywania rekordów, tym lepiej.

Bez wątpienia brakuje mi szczegółów, więc możesz dodawać komentarze do tego, co chcesz, żebym wyjaśnił.

Wobec braku odpowiedzi sam zgłębiłem ten problem.

Wygląda na to, że funkcje zdefiniowane przez użytkownika mogą obsłużyć wszystkie typy bazowe, w tym bytea i smallint[], więc nie ma to większego wpływu na wybór reprezentacji.

Wypróbowałem kilka różnych reprezentacji na serwerze PostgreSQL 9.4 działającym lokalnie na laptopie z systemem Windows 7 z konfiguracją waniliową. Relacje do przechowywania danych rzeczywistych sygnałów były następujące.

Duży obiekt dla całego pliku

CREATE TABLE BlobFile (
    eeg_id INTEGER PRIMARY KEY,
    eeg_oid OID NOT NULL
);

Tablica SMALLINT na kanał

CREATE TABLE EpochChannelArray (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    channel INT,
    signal SMALLINT[] NOT NULL,
    PRIMARY KEY (eeg_id, epoch, channel)
);

BYTEA na kanał w każdej epoce

CREATE TABLE EpochChannelBytea (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    channel INT,
    signal BYTEA NOT NULL,
    PRIMARY KEY (eeg_id, epoch, channel)
);

Tablica SMALLINT 2D na epokę

CREATE TABLE EpochArray (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    signals SMALLINT[][] NOT NULL,
    PRIMARY KEY (eeg_id, epoch)
);

Tablica BYTEA na epokę

CREATE TABLE EpochBytea (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    signals BYTEA NOT NULL,
    PRIMARY KEY (eeg_id, epoch)
);

Następnie zaimportowałem wybrane pliki EDF do każdej z tych relacji za pomocą Java JDBC i porównałem wzrost wielkości bazy danych po każdym przesłaniu.

Pliki to:

• Plik A: 2706 epok 16 kanałów, każdy kanał 1024 próbki (16385 próbek na epokę), 85 MB
• Plik B: 11897 epok po 18 kanałów, każdy kanał 1024 próbek (18432 próbek na epokę), 418 MB
• Plik C: 11746 epok 20 kanałów, każdy kanał 64 do 1024 próbek (17088 próbek na epokę), 382 MB

Jeśli chodzi o koszt przechowywania, oto rozmiar zajmowany w MB dla każdego przypadku:

W stosunku do oryginalnego rozmiaru pliku duże obiekty były o około 30-35% większe. Natomiast przechowywanie każdej epoki jako BYTEA lub SMALLINT [] [] było mniejsze niż 10%. Przechowywanie każdego kanału jako osobnej krotki daje 40% wzrost, jako BYTEA lub SMALLINT [], więc niewiele gorsze niż przechowywanie jako duży obiekt.

Jedną rzeczą, której początkowo nie doceniałem, jest to, że „Tablice wielowymiarowe muszą mieć pasujące zakresy dla każdego wymiaru” w PostgreSQL . Oznacza to, że SMALLINT[][]reprezentacja działa tylko wtedy, gdy wszystkie kanały w epoce mają tę samą liczbę próbek. Dlatego plik C nie działa z EpochArrayrelacją.

W kategoriach jak koszty dostępu, nie bawił się z tym, ale przynajmniej jeśli chodzi o wprowadzenie danych początkowo najszybsze przedstawienie było EpochByteai BlobFile, z EpochChannelArraynajwolniej, biorąc około 3 razy tak długo, jak dwóch pierwszych.