在PostgreSQL中存储图像 [英] Storing Images in PostgreSQL

问题描述

好吧，所以我正在开发一个应用程序，它将使用运行PostgreSQL的Linux后端将图像提供给Windows框，其前端用C＃.NET编写，尽管前端应该不重要。我的问题是：

Alright, so I'm working on an application which will use a Linux back-end running PostgreSQL to serve up images to a Windows box with the front end written in C#.NET, though the front-end should hardly matter. My question is:

• 在Postgres中处理图像处理的最佳方法是什么？

• What is the best way to deal with storing images in Postgres?

图像大约为4-6百万像素，我们存储的数量超过3000.这也可能是值得注意的：这不是一个Web应用程序，最多会有两个前端一次访问数据库。

The images are around 4-6 megapixels each, and we're storing upwards of 3000. It might also be good to note: this is not a web application, there will at most be about two front-ends accessing the database at once.

推荐答案

Re jcoby的回答：

Re jcoby's answer:

bytea是一个普通列，也意味着当你获取它时，该值被完全读入内存。相比之下，Blob可以流入stdout。这有助于减少服务器内存占用。特别是，当你存储4-6个MPix图像时。

bytea being a "normal" column also means the value being read completely into memory when you fetch it. Blobs, in contrast, you can stream into stdout. That helps in reducing the server memory footprint. Especially, when you store 4-6 MPix images.

备份blob没问题。 pg_dump提供了-b选项，可以将大对象包含在备份中。

No problem with backing up blobs. pg_dump provides "-b" option to include the large objects into the backup.

所以，我更喜欢使用pg_lo_ *，你可能会猜到。

So, I prefer using pg_lo_*, you may guess.

Re Kris Erickson的答案：

Re Kris Erickson's answer:

我会说相反:)。当图像不是您存储的唯一数据时，请不要将它们存储在文件系统中，除非您绝对必须这样做。确保数据一致性并将数据整体化（数据库）是一件好事。顺便说一句，PostgreSQL在保持一致性方面非常出色。

I'd say the opposite :). When images are not the only data you store, don't store them on the file system unless you absolutely have to. It's such a benefit to be always sure about your data consistency, and to have the data "in one piece" (the DB). BTW, PostgreSQL is great in preserving consistency.

但是，真实的，现实往往性能要求太高;-)，它会促使你从服务器中提供二进制文件文件系统。但即便如此，我倾向于使用DB作为二进制文件的主存储，所有其他关系一致地链接，同时提供一些基于文件系统的缓存机制以进行性能优化。

However, true, reality is often too performance-demanding ;-), and it pushes you to serve the binary files from the file system. But even then I tend to use the DB as the "master" storage for binaries, with all the other relations consistently linked, while providing some file system-based caching mechanism for performance optimization.

这篇关于在PostgreSQL中存储图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！