主页 / dba / 问题 / 102456
Accepted
beldaz
Asked: 2015-05-26 21:49:55 +0800 CST

PostgreSQL bytea 与 smallint[]

  • 772

我希望将大型（100Mb -- 1 GB）多通道时间序列数据导入 PostgreSQL 数据库。数据来自EDF 格式文件，这些文件将数据分块为“记录”或“纪元”，每个记录通常为几秒。每个纪元的记录将每个数据通道的信号保存为短整数的顺序数组。

我被要求将文件存储在数据库中，在最坏的情况下作为 BLOB。鉴于此，我想研究允许我对数据库中的数据做更多事情的选项，例如促进基于信号数据的查询。

我最初的计划是将数据存储为每个纪元记录一行。我要权衡的是将实际信号数据存储为 bytea 还是 smallint[]（甚至是 smallint[][]）类型。任何人都可以推荐一个吗？我对存储和访问成本感兴趣。用法很可能是插入一次，偶尔读取，从不更新。如果一个更容易包装为自定义类型，以便我可以添加用于分析比较记录的函数，那就更好了。

毫无疑问，我缺乏细节，所以请随时对您希望我澄清的内容发表评论。

postgresql datatypes

1 个回答

  1. Best Answer

    在没有任何答案的情况下，我自己进一步探讨了这个问题。

    看起来用户定义的函数可以处理所有基本类型，包括 bytea和smallint[]，因此这不会对表示形式的选择产生太大影响。

    我在 PostgreSQL 9.4 服务器上尝试了几种不同的表示形式，该服务器在具有原始配置的 Windows 7 笔记本电脑上本地运行。存储实际信号数据的关系如下。

    整个文件的大对象

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

    每个通道的 SMALLINT 数组

    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

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

    每个时期的 SMALLINT 二维数组

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

    每个时期的 BYTEA 数组

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

    然后，我通过 Java JDBC 将选择的 EDF 文件导入到每个关系中，并比较每次上传后数据库大小的增长情况。

    这些文件是：

    • 文件 A：16 个通道的 2706 个 epoch，每个通道 1024 个样本（每个 epoch 16385 个样本），85 MB
    • 文件 B：18 个通道的 11897 个 epoch，每个通道 1024 个样本（每个 epoch 18432 个样本），418 MB
    • 文件 C：20 个通道的 11746 个 epoch，每个通道 64 到 1024 个样本（每个 epoch 17088 个样本），382 MB

    在存储成本方面，以下是每种情况占用的大小（以 MB 为单位）： 以 MB 为单位的存储成本

    相对于原始文件大小，大对象大约大 30-35%。相比之下，将每个时期存储为 BYTEA 或 SMALLINT[][] 的时间要大不到 10%。将每个通道存储为单独的元组可以增加 40%，无论是 BYTEA 还是 SMALLINT[]，因此并不比存储为大对象差多少。

    我最初没有意识到的一件事是PostgreSQL中的“多维数组必须具有每个维度的匹配范围” 。这意味着SMALLINT[][]只有当一个时期中的所有通道都具有相同数量的样本时，该表示才有效。因此，文件 C 无法处理该EpochArray关系。

    就访问成本而言，我没有玩过这个，但至少就最初插入数据而言，最快的表示是EpochBytea和BlobFile，EpochChannelArray最慢的表示大约是前两个的 3 倍。

    • 11

相关问题