在数据库中存储二进制文件
在数据库中存储二进制文件
PostgreSQL 的新用户经常在 PostgreSQL 邮件列表中询问如何（或是否）在数据库中存储二进制文件，例如图像、Word 文档、电子表格、HTML、PDF、压缩档案，甚至代码。关于这个问题，有两个主流观点，每个观点都有优缺点，下面将详细介绍。
- 将二进制文件保存到常规文件系统，然后将元数据和某种符号链接写入数据库以指向其位置。
- 将大型非结构化数据的二进制文件直接存储在数据库字段中。
注意：许多人访问此页面是为了寻找 PostgreSQL 中等效于 MySQL 的 “BLOB*” 数据类型。Postgres 中的等效类型是 BYTEA。有关 PostgreSQL 的“二进制大型对象”（它们与 MySQL BLOB 不同，并且提供随机查找等）的详细信息，请参见下文。
方法
在数据库中存储大型二进制文件，也称为非结构化数据流。
Postgres 与其他数据库一样，提供了类似的基本结构。
- 使用 bytea 或 text 数据类型存储二进制数据
- BLOB 二进制大型对象，请参见 大型对象支持
- 优点
- 每个条目限制为 4TB（PostgreSQL 9.3+），每个数据库限制为 40 亿个
- 可以对条目进行流式处理和查找（可以减少数据库服务器和客户端的内存要求）
- 不需要编码或转义。
- 缺点
- 必须使用与通常用于访问 BLOB 不同的接口。
- 需要跟踪
OID。通常使用一个包含其他元数据的单独表来描述每个 OID 是什么。 - （8.4 和 < 8.4）数据库中没有访问控制。
- 有时 不建议（基本上，只有在你的条目非常大，你需要/想要逐段查找和读取它时才需要它们）。
- 优点
无论选择哪种特定的存储方式，数据库存储都有一些共同的特征
- 总体优点
- 安全性和访问控制变得简单
- 版本控制更容易
- ACID
- 保存到主数据库中的文件将自动流式传输到备用副本
- 备份更容易，无需跟踪外部文件
- 总体缺点
- 在数据库中存储文件会导致性能下降。
- 数据库的内存要求更高
- 备份可能需要更长的时间
- 外部应用程序访问文件很复杂。通常需要将临时文件复制到客户端以访问和修改文件，然后需要将其复制回来。
在数据库中存储元数据和二进制文件位置的符号链接
- 优点
- 访问二进制文件时，性能跳过数据库访问层。
- 文件数量仅受文件系统限制
- 大小受文件系统限制
- 缺点
- 数据库用户需要权限才能写入数据库外部。在托管的 PostgreSQL 环境中，这通常不是一种选择。
- 需要开发一个接口来跟踪外部附加的文件
- 外部文件和数据库可能不同步
- 二进制文件在数据库中可能缺少条目
- 数据库包含指向外部文件的条目，这些条目已被用户或其他事件删除或移动，这些事件不受数据库控制。
- 文件系统和数据库之间的安全设置相互独立。应用程序可能需要多个安全登录才能访问所有资源。这取决于应用程序的安全需求和用于访问控制的方法。有多种方法可以创建单点登录基础设施。
- 取决于网络的复杂性，存在多个故障点。
何时应将文件存储在数据库中？
这里常见的建议是，当文件必须 ACID 时。
例如：质量检验过程中拍摄的照片
数据库中的一个普通记录将包括检验员、检验时间戳、用于检验零件的过程、零件是否被接受或拒绝，以及零件的序列号。在这种情况下，还会拍摄显示被检验零件的照片。这些照片是检验记录保存流程中不可或缺的一部分。
作为记录/检验报告的关键部分，照片不应该单独存储。考虑一下，如果存储在文件服务器上的照片被移动到新位置作为升级的一部分，如果链接到记录没有更新，那么链接就会失效。因此，检验记录本身毫无价值，因为它丢失了它描述的部分，而没有记录描述的照片也毫无价值。因此，这两个部分必须保持在一起才能作为检验报告具有任何价值。
当将照片存储在数据库外部时，没有简单的方法可以保证照片的原子性、一致性、隔离性和持久性。将照片存储在数据库中是唯一实用的解决方案，程序员可以保证将来遵循这些规则。文件数据将自动复制并流入数据库备份。由于写入数据库服务器上的任意文件需要额外的信任，因此将数据保存到数据库可能是唯一可用的选项，或者被你的 PostgreSQL 管理员认为是安全的。
在数据库中存储文件的最佳方法是什么？
在 PostgreSQL 中，有三种方法可供选择：BLOB、bytea 或 text 数据类型。
本文的主要作者现在已经完全转向使用 bytea，因为 Python 和 pl/Python 使 bytea 比 text 类型更容易使用。
bytea
- 数据存储要求与在文件系统中存储的对象相同
- 使用标准 SQL 语句来
INSERT、UDPATE、DELETE和SELECT对象 - 数据的输入和输出难易程度取决于编程工具。Python 和 .Net 拥有最简单的自动语言类型到 postgres 类型的转换（取决于数据库驱动程序）。其他语言的数据库访问层需要在进行其他处理之前执行额外的转义或转换步骤。
- 服务器端工具用于编码或解码到其他 格式
text
- 数据存储要求可能比在文件系统中存储文件高出 30%
- 使用标准 SQL 语句来
INSERT、UDPATE、DELETE和SELECT对象 - 总是必须编码和解码为某种格式，例如（并非详尽列表）：
- Base64 可能由于电子邮件而成为最常见的二进制文件编码方法。
- uuencoding
- Quoted Printable
- ASCII85
这会增加相当大的开销。
- 服务器端工具用于编码或解码到其他 格式
BLOB，也称为大型对象
- 必须使用完全不同的接口，请阅读 大型对象 文档以充分了解。
- 确实具有在数据上查找和流式传输数据输出的优势。
- 数据存储要求与保存在文件系统中时相同
- 服务器端工具用于操作对象数量有限，请参见 服务器端工具。
应该在数据库中存储哪些元数据？
以下是一个基本列表，列出了应该与二进制文件一起记录在数据库中的内容
- 原始文件名
- 数据类型
- 这需要告诉我们数据库中存储的是什么类型的二进制文件
- 作者建议遵循 Internet Media Type，这使得更容易在网站或电子邮件附件中使用相同的信息。
- 详细描述
- 添加日期
什么时候在数据库中存储二进制文件是一个坏主意？
对于非常大的文件（100MB+），性能对应用程序至关重要。数据库层会增加许多不必要的开销和复杂性。
