logo标签列表

在PostgreSQL中存储图像

postgresqlimage
148

好的，我正在开发一个应用程序，它将使用运行PostgreSQL的Linux后端向一个Windows框架提供图像服务，而前端是用C#.NET编写的，尽管前端几乎不重要。我的问题是：

  • 在Postgres中存储图像的最佳方法是什么？

这些图像每个大约有4-6百万像素，并且我们正在存储超过3000张图片。另外需要注意的是：这不是一个Web应用程序，最多只会有两个前端同时访问数据库。

8个回答
85

更新到2012年，当我们看到图片尺寸和数量在所有应用程序中不断增长时...

我们需要一些区分“原始图像”和“处理后的图像”，例如缩略图。

正如Jcoby的回答所说，那么有两个选项，我建议：

  • 使用blob (二进制大型对象): 用于原始图像存储，存储在您的表中。请参见伊万的答案（不会出现备份blob的问题！），PostgreSQL附加提供的模块，How-tos等。

  • 使用单独的数据库和DBlink：用于原始图像存储，在另一个（统一/专业）数据库中。在这种情况下，我更喜欢使用bytea，但blob几乎相同。分离数据库是“统一图像网络服务”的最佳方式。

  • 使用bytea（BYTE Array）：用于缓存缩略图像。将小图像缓存以快速发送到Web浏览器（避免渲染问题）并减少服务器处理。缓存还包括必要的元数据，如宽度和高度。数据库缓存是最简单的方法，但请检查您的需求和服务器配置（例如Apache模块）：store thumbnails at file system可能更好，比较性能。请记住，它是一个（统一的）Web服务，可以存储在单独的数据库中（没有备份），为多个表提供服务。请参阅PostgreSQL二进制数据类型手册，tests with bytea column等。

注意1：今天使用“双重解决方案”（数据库+文件系统）已经被弃用。使用“仅数据库”而不是双重解决方案有许多优点。PostgreSQL具有可比较的性能和良好的导出/导入/输入/输出工具。

注意2：请记住，PostgreSQL仅有bytea，没有默认的Oracle的BLOB：“SQL标准定义了……BLOB。输入格式与bytea不同，但提供的函数和运算符大多相同”，手册。

编辑2014年：我今天没有更改上面的原始文本（我的答案是Apr 22'12，现在有14票），我开放答案供您更改（请参阅“Wiki模式”，您可以进行编辑！）proofreading和更新。
问题很稳定（@Ivans的'08答案获得19票），请帮助改进此文本。

5
“...使用“双重解决方案”（数据库+文件系统）已被弃用...”的参考文献是什么？ - dangel
1
一些2019年的消息！自2018年以来，PostgREST支持将bytea直接输出到Web。请参见此NGINX简单配置以使用它。请参见PostgREST二进制输出指南。 - Peter Krauss
58

关于jcoby的回答：

bytea作为“普通”列意味着在提取它时，该值会完全读入内存。相反，对于blob，您可以将其流式传输到标准输出。这有助于减少服务器内存占用。特别是当你存储4-6 MPix图像时。

备份blob没有问题。pg_dump提供“-b”选项将大型对象包含在备份中。

因此，我更喜欢使用pg_lo_ *，你也许可以猜到。

关于Kris Erickson的回答：

我会说相反的观点：）。当图像不是您存储的唯一数据时，请不要存储它们在文件系统中，除非您绝对必须这样做。始终确保数据一致性是一个巨大的好处，并且使数据“完整”（数据库）。顺便说一下，PostgreSQL在保持一致性方面非常出色。

然而，现实往往对性能要求过高，迫使您从文件系统中提供二进制文件。但即使如此，我也倾向于将数据库用作二进制文件的“主”存储，所有其他关系都保持一致，并提供一些基于文件系统的缓存机制以进行性能优化。

21
10年过去了，您认为您的观点仍然有效吗？自那时以来有任何更新吗？ - leventunver
4
不，这些观点是不正确的。例如，第一个关于 BYTEA 是一个“常规”列的说法。多年来，Postgres 已支持对/从 BYTEA 列进行流式传输，这意味着在将其存储到数据库中之前，您不必将内容存储在内存中。 - oligofren
35

在数据库中，有两种选项：

  • bytea。将数据存储在列中，并作为备份的一部分导出。使用标准数据库函数进行保存和检索。推荐您使用。
  • blob。将数据以外部形式存储，通常不作为备份的一部分导出。需要特殊的数据库函数来保存和检索。

我过去曾经非常成功地使用bytea列来存储10多GB的图像并包含数千行。PG的TOAST功能基本上抵消了blobs所具有的任何优势。您需要在任一情况下都包含元数据列，例如文件名、内容类型、尺寸等。

1
10GB不算太多 :-( 我正在寻找TB级别的解决方案。 - Valentin H
2
@ValentinHeinitz 对于TB级别的数据，即使是较小的文本列，普通的Postgres也会遇到困难。 - sudo
26

2015年中期的快速更新:

您可以使用Postgres Foreign Data interface将文件存储在更适合的数据库中。例如，将文件放入MongoDB的一部分GridFS中。然后使用https://github.com/EnterpriseDB/mongo_fdw在Postgres中访问它。

这有以下几个优点：您可以在Postrgres和MongoDB中访问/读取/写入/备份它，具体取决于哪个更灵活。

还有用于文件系统的外部数据封装器：https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_Wrappers

例如，您可以使用此外部数据封装器：https://multicorn.readthedocs.org/en/latest/foreign-data-wrappers/fsfdw.html（请参见此处的简要使用示例）。

这样一来，您就可以获得所有链接文件都确实存在的一致性，并且拥有所有其他ACID，而它们仍位于实际的文件系统中，这意味着您可以使用任何想要使用的文件系统，而Web服务器可以直接提供它们（OS缓存也适用）。

1
谢谢。外部数据包装器（file_fdw）是否提供图像的写入访问权限？我想将图像存储到Postgresql中的文件系统中，并在其中存储元数据，但我也必须保持一致性。您有详细的解决方案吗？是否还有其他可用的扩展程序？Multicorn需要Python，我更喜欢不使用Python。 - Jay Khatwani
2
是的，它们具有写入权限。它们在双向上都是完全一致的。但我不知道是否有一个不使用Python就能实现这个相同功能的解决方案。 - Kenyakorn Ketsombut
25

10年后的更新 在2008年，用于运行数据库的硬盘具有与存储文件的磁盘非常不同的特征和更高的成本。现如今，有比10年前更好的存储文件的解决方案，我会撤回这个建议并建议读者查看该主题中的其他答案。

原始内容

除非你确实必须这样做，否则不要将图像存储在数据库中。虽然我明白这不是一个Web应用程序，但如果没有共享的文件位置可以指向，在数据库中保存文件的位置就没有意义。

//linuxserver/images/imagexxx.jpg

如果您可以快速设置Web服务器并将Web网址以及本地路径存储在数据库中（以及本地路径），那么也许您可以这样做。 虽然数据库可以处理LOB和3000个图像（4-6百万像素，假设每个图像500K），但1.5 GB的空间不算很多，文件系统比数据库更适合存储大型文件。

19
但是你需要想出一种方法将文件分发到多个目录中。文件系统不擅长在一个单独的目录中存储数百万个文件（实际上，即使是数万个文件也是一个问题）。 - user330315
1
没有回答原问题。我个人想要将图像存储在Postgres中，因为我希望SQL作为我的抽象层，并且不想在我的ext4文件系统中管理文件。 - sudo
我感到矛盾，这并没有回答问题，但我还是点了赞，因为它比回答问题更好。 - Andrew Carr
14
如果你的图片很小，考虑将它们作为base64存储在纯文本字段中。原因是虽然base64有33%的开销，但通过压缩可以消除大部分开销(参见base64编码的空间开销是多少？)。虽然数据库会变得更大，但发送到客户端的web服务器包不会变得更大。在html中，您可以在<img src="">标签中内联使用base64，这可能会简化您的应用程序，因为您不必作为单独的浏览器获取以二进制形式提供图像。将图像处理为文本也在您必须发送/接收json时简化了事情，因为json不能很好地处理二进制数据。
是的，我知道您可以将二进制数据存储在数据库中，并在进出数据库时将其转换为/从文本进行操作，但有时ORM使这变得麻烦。与所有其他字段一样，将其视为纯文本可能更简单。
对于处理缩略图来说，这绝对是正确的方法。
(由于OP的图片不小，因此这并不是他问题的真正答案。)
9

2022年答案

现在最常见的模式是仅在数据库中存储图像的引用，并将图像本身存储在文件系统中（即S3桶）。

好处是，您的数据库备份更小，不再存在单点故障，负载现在可以分散到远离数据库的地方，而且云存储桶通常比数据库存储更便宜。

不利之处是，您必须在两个位置管理图像 - 删除一个图像后，您的应用程序需要跟踪并从另一个位置删除它。

1
我可以问一下为什么不再存在单点故障吗？尽管您的解决方案仍需要数据库和 S3 存储，所以难道不是增加了一个故障点吗？ - Waelmio
@Waelmio 是的，有不止一个故障点，这意味着没有单一的故障点。如果您的数据库被破坏，至少您仍然拥有存储在S3上的图像文件。如果您的S3被破坏，那么至少您有元数据和图像ID可以帮助数据恢复。 - Janac Meena
1
但是如果S3或者您的数据库出现问题，您的服务将无法工作，因此我们并没有减少单点故障。我确实是指您有一个更多的单点故障。数据恢复发生在故障之后，这并不意味着没有故障。 - Waelmio
我明白你的意思，但这与原始问题有些超出范围。我同意你的观点，为了真正分散单点故障的风险，应该采取其他措施，比如备份、数据恢复实例、复制等。 - Janac Meena
0
2023答案：使用CDN。
把图片直接放在数据库里可能很诱人，但是设置一个CDN只需要5分钟，而且它们价格便宜，可以自动优化传输，好处还有很多。
你的API响应会显著减小，图片加载速度明显更快，CDN还可以自动生成缩略图、裁剪、转换等功能。
可能有一些特殊情况下，将图像存储在数据库中是有意义的，但请将其视为比使用CDN更复杂的选项，并且必须有充分的理由来支持这种选择。
即使是对于一个玩具应用程序，使用CDN也能为您省去很多麻烦。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接