在PostgreSQL中存储文件是否存在性能问题？

Question

在PostgreSQL中将像HTML页面、图片、PDF等文件存储到表格中，会导致速度变慢吗？我看过一些文章说这样做不被推荐，但我不知道其中是否属实。我考虑使用的列类型是BLOB（据我所知，它会存储成一个文件）或bytea类型，也可以使用其他类型。

- Renato Dinhani

---

投票以重新开放。问题询问在PostgreSQL中存储大文件可能存在的潜在缺点，比这更具体很困难。我已编辑它，避免询问存储大文件的最佳方法。 - johndodo

Answer 1

您基本上有两种选择。您可以直接将数据存储在行中，也可以使用大对象设施。由于PostgreSQL现在使用称为TOAST的东西将大字段移出表，因此在行中直接存储大量数据不应该有性能损失。字段大小仍然存在1 GB的限制。如果这太受限制，或者如果您需要流式API，则可以使用大对象设施，它提供了类似于数据库中文件描述符的东西。您在列中存储LO ID，可以从该ID读取和写入。

我个人建议您避免使用大对象设施，除非绝对需要。通过TOAST，大多数用例可以通过正常使用数据库来覆盖。但是，使用大对象会增加额外的维护负担，因为您必须跟踪已使用的LO ID，并确保在不再使用它们时取消链接它们（但不是先链接），否则它们将永久占用您的数据目录空间。还有很多设施其异常行为的详细信息我并不清楚，因为我从未使用过它们。

对于大多数人而言，在数据库中存储大量数据的主要性能问题是，ORM软件会在每次查询时提取大量数据，除非您明确指示它不这样做。您应该注意告诉Hibernate或您正在使用的任何其他软件将这些列视为大列，并且仅在特定请求它们时才获取它们。

- Daniel Lyons

---

很好的回答，非常感谢。bytea类型用于在表中存储内容吗？ - Renato Dinhani

如果bytea数据很小，它们将默认存储在表中，并针对较大的值移至辅助（“toast”）表并进行压缩。请参阅：http://www.postgresql.org/docs/9.1/static/storage-toast.html 了解介绍。您可以禁用辅助存储的压缩功能，这将提高提取部分值的性能。 - araqnid

bytea是二进制数据的好选择。如果数据是文本形式且与数据库具有相同的编码，您也可以使用text或varchar。 - Daniel Lyons

在这篇文章中加入一些关于 PSQL 备份所需时间的内容会很好。它会让备份花费更长时间吗？似乎不是。 - Nicholas DiPiazza

Answer 2

The BLOB (LO)类型将数据存储在标准的PostgreSQL堆页面内的2KB块中，默认大小为8KB。它们不作为独立的、连贯的文件存储在文件系统中——例如，您不能定位文件，进行逐字节比较并期望它与加载到数据库中的原始文件数据相同，因为还有Postgres堆页面头和结构来划分这些块。

如果您的应用程序需要频繁更新二进制数据，特别是涉及大量小型随机访问写操作，应避免使用大对象（LO）接口。由于PostgreSQL实现并发控制（MVCC）的方式，这可能会导致使用的磁盘空间急剧增加，直到对数据库进行VACUUM。相同的结果可能也适用于以bytea类型或TOAST存储的列中存储的数据。

但是，如果您的数据遵循“写一次，多次读取”的模式（例如上传PNG图像后从未修改过），从磁盘使用的角度来看，这应该是可以的。

请参阅此 pgsql-general 邮件列表线程以获取进一步讨论。

- singularsyntax