Contrary to popular belief - the answer to everything is NOT 42 - it’s PostgreSQL. (ok. It might also be Postgres).与普遍看法相反，万物的答案不是 42，而是 PostgreSQL。（好吧，可能也叫 Postgres。）
Table Of Contents目录
Intro简介
I started using PostgreSQL roughly in 2003 for a research project called ColumbaDB. Columba is no more, but PostgreSQL is still alive and kicking more than ever.我大约在 2003 年开始为一个名为 ColumbaDB 的研究项目使用 PostgreSQL。Columba 虽然已成往事，但 PostgreSQL 至今依然充满活力，且比以往任何时候都更强大。
In 2003, MySQL was much more widely used than PostgreSQL. MySQL was also potentially faster as it did not implement all features of the SQL standard. At the same time MySQL was lacking many features that we needed (full-text search, powerful indexes, SQL standard compliance etc). PostgreSQL felt more like a “real” database in comparison to MySQL - like a tiny version of Oracle - but in open source clothes.2003 年时，MySQL 的使用率远高于 PostgreSQL。MySQL 当时可能更快，因为它并未实现 SQL 标准的所有功能。与此同时，MySQL 缺少许多我们需要的功能（如全文搜索、强大的索引、SQL 标准合规性等）。相比之下，PostgreSQL 感觉更像是一个“真正”的数据库——就像是披着开源外衣的精简版 Oracle。
During that research project I learned a lot about databases, indexes and the power of PostgreSQL. One important use-case was full-text search. We could have used MySQL in conjunction with another system like Lucene / Solr to make our database searchable. But that would have meant running and maintaining two such systems. Complicated.在那个研究项目中，我学到了很多关于数据库、索引以及 PostgreSQL 强大功能的知识。一个重要的用例是全文搜索。我们本可以结合 Lucene/Solr 等其他系统来实现数据库搜索，但这意味着需要运行和维护两个系统。太复杂了。
PostgreSQL allowed us to use a fulltext search plugin to do everything in one system. No need to sync any data. No need to maintain and run two systems. It just worked and made us smile (after some tweaks of course). Simplicity.PostgreSQL 允许我们使用全文搜索插件在同一个系统中完成所有工作。无需同步任何数据，也无需维护和运行两个系统。它运行良好，让我们感到非常满意（当然是在进行了一些调整之后）。这就是简洁的力量。
Since then I used PostgreSQL for many use-cases throughout my career as CTO / Interim Manager. Most recently I used PostgreSQL to store very high volume web analytics time series data via its TimescaleDB plugin. Check out Privatracker - the best way to do web analytics and respect the privacy of your visitors - to see it in action.从那时起，在担任 CTO/临时管理者的职业生涯中，我多次使用 PostgreSQL 处理各种用例。最近，我利用其 TimescaleDB 插件存储了海量的网站分析时间序列数据。欢迎查看 Privatracker——这是一种既能进行网站分析又能尊重访客隐私的最佳方式，你可以亲身体验它的实际效果。
Many others discussed the topic from different angles. And each article is really worth your time (SQL is Agile, Stephan Schmidt on Using SQL for Everything). Also check out my Linkedin post.许多人从不同角度讨论过这个话题。每一篇文章都非常值得一读（例如《SQL is Agile》以及 Stephan Schmidt 关于《Using SQL for Everything》的文章）。也请查看我的 Linkedin 帖子。
And if you are using PostgreSQL I can highly recommend reading Hazel Bachrach’s nice post on “What I Wish Someone Told Me About Postgres”.如果你正在使用 PostgreSQL，我强烈推荐阅读 Hazel Bachrach 的精彩文章《What I Wish Someone Told Me About Postgres》。
In my humble opinion the power of PostgreSQL comes from three sources:在我看来，PostgreSQL 的强大源于三个方面：
- It is rock-solid and stable.它坚如磐石且非常稳定。
- It is easy to run, install and scale.它易于运行、安装和扩展。
- It massively simplifies your IT setup by being not only a RDBMS, but also a full-text search engine, a document storage and much much more…它不仅是一个关系型数据库管理系统 (RDBMS)，还兼具全文搜索引擎、文档存储等多种功能，极大地简化了你的 IT 架构……
Let’s have a closer look…让我们深入了解一下……
Rock Solid and Stable坚如磐石且稳定
PostgreSQL is boring old technology. The first PostgreSQL release dates back to 1996. PostgreSQL is also very widely used - for a very long amount of time. Ironing out bugs - especially in database systems - takes time. PostgreSQL had that time.PostgreSQL 是一项古老而“枯燥”的技术。它的第一个版本发布于 1996 年。它被广泛使用且历史悠久。修复漏洞——尤其是数据库系统中的漏洞——需要时间，而 PostgreSQL 拥有足够的时间。
It also has a very active community that diligently adds more and more features without breaking any old parts of it. In recent years PostgreSQL got many amazing features like json document storage, partitioning support, common table expressions and much much more. Each new release of PostgreSQL is exciting and brings new nice features.它还有一个非常活跃的社区，在不破坏现有功能的前提下，勤奋地添加越来越多的特性。近年来，PostgreSQL 获得了许多令人惊叹的功能，如 JSON 文档存储、分区支持、公用表表达式 (CTE) 等等。每一个新版本的 PostgreSQL 都令人兴奋，并带来了实用的新功能。
True - PostgreSQL is old - but the features are very very modern - and PostgreSQL becomes better with every release.没错，PostgreSQL 很古老，但它的功能非常现代化，而且每个版本都在不断进步。
Easy to Run, Install and Scale易于运行、安装和扩展
PostgreSQL can be installed very easily locally. It is bundled with all major Linux distributions, part of Mac brew, but can also be installed with applications like PostgresApp.PostgreSQL 在本地安装非常简单。它捆绑在所有主流 Linux 发行版中，是 Mac brew 的一部分，也可以通过 PostgresApp 等应用程序安装。
When running tests, it comes in handy using Testcontainers with PostgreSQL. It was never easier running your tests against a real PostgreSQL database that is 100% similar to the production thing.在运行测试时，使用 Testcontainers 配合 PostgreSQL 非常方便。现在，针对与生产环境 100% 相似的真实 PostgreSQL 数据库进行测试从未如此简单。
If you want to run PostgreSQL on a server then you can simply apt-get install it. Or run it in a docker container.如果你想在服务器上运行 PostgreSQL，只需通过 apt-get 安装，或者在 Docker 容器中运行即可。
All cloud providers allow you to run (and scale!) PostgreSQL by clicking a single button. You got ample of choice at your fingertips:所有云服务商都支持通过点击按钮来运行（并扩展！）PostgreSQL。你拥有丰富的选择：
- Amazon AWSAmazon AWS
- Google GCPGoogle GCP
- Microsoft AzureMicrosoft Azure
- ElephantSQLElephantSQL
- CrunchyDataCrunchyData
- TimescaleTimescale
- … and many more ………以及更多……
That makes PostgreSQL one of the most widely supported software systems in the market. And for you this means less maintenance and more time for creating new features for clients.这使得 PostgreSQL 成为市场上支持最广泛的软件系统之一。对你而言，这意味着更少的维护工作，从而腾出更多时间为客户创造新功能。
Simplifies Your IT Setup简化你的 IT 架构
Running PostgreSQL in the cloud is already just one click. But it gets even better. PostgreSQL can replace many systems that youd’d have to run otherwise.在云端运行 PostgreSQL 只需一键部署。不仅如此，PostgreSQL 还可以替代许多原本需要你额外运行的系统。
PostgreSQL Replaces Solr and Elastic: Full-Text SearchPostgreSQL 替代 Solr 和 Elastic：全文搜索
PostgreSQL allows you to turn your text data into user-searchable data. Without a separate system. It’s also language agnostic and you’ll never have any sync problems between your data and your fulltext search system.PostgreSQL 允许你将文本数据转化为用户可搜索的数据，无需独立的系统。它还支持多语言，且你永远不会遇到数据与全文搜索系统之间的同步问题。
The most impressive article on the topic is how Contentful used PostgreSQL to enable fulltext search for their users. It’s a tale in simplicity that enables growth.关于这个话题，最令人印象深刻的文章是 Contentful 如何利用 PostgreSQL 为用户实现全文搜索。这是一个通过简洁实现增长的典型案例。
Instacart did something very similar: They built their modern search infrastructure on Postgres instead of running a separate search cluster. Same story, different company.Instacart 也做了类似的事情：他们将现代搜索基础设施构建在 Postgres 之上，而不是运行单独的搜索集群。同样的故事，不同的公司。
More on the topic: https://www.postgresql.org/docs/current/textsearch.html更多信息请访问：https://www.postgresql.org/docs/current/textsearch.html
PostgreSQL replaces MongoDB: Excellent Json SupportPostgreSQL 替代 MongoDB：出色的 JSON 支持
PostgreSQL has excellent support for storing and querying(!) json. It also features an index type (GIN) that makes these operations blazingly fast. Is there a need for MongoDB any more?.PostgreSQL 对存储和查询 (!) JSON 提供了卓越的支持。它还具有一种索引类型 (GIN)，使这些操作速度极快。还有必要使用 MongoDB 吗？
The Guardian also wrote an excellent article how they switched from Mongo to PostgreSQL. Thanks for sharing Jan-Otto! Hazel also wrote a nice piece on jsonb and what to take into account when using it.The Guardian 也写了一篇精彩的文章，讲述了他们如何从 Mongo 迁移到 PostgreSQL。感谢 Jan-Otto 的分享！Hazel 也写了一篇关于 jsonb 以及使用时注意事项的好文章。
PostgreSQL replaces Kafka and RabbitMQ: PostgreSQL as a queuePostgreSQL 替代 Kafka 和 RabbitMQ：作为队列使用
Events, queues and persistent logs are getting more and more important in today’s software systems. Systems like Kafka, RabbitMQ, SQS and others provide that functionality. But maintaining them is annoying, custom and you need the skillset.在当今的软件系统中，事件、队列和持久化日志变得越来越重要。Kafka、RabbitMQ、SQS 等系统提供了这些功能，但维护它们很麻烦，且需要特定的技能。
The good news: You can just use PostgreSQL. The magic comes from好消息是：你完全可以使用 PostgreSQL。其魔力源于：
- SELECT .. FOR UPDATESELECT .. FOR UPDATE
- SELECT .. SKIP LOCKEDSELECT .. SKIP LOCKED
Using these SQL features you can effectively use a table as queue. Either in a persistent fashion with a cursor and many consumers, or in a read-once fashion.利用这些 SQL 特性，你可以有效地将表用作队列。无论是通过游标和多个消费者实现持久化，还是实现“只读一次”的模式。
The article at crunchydata explains this concept very well.CrunchyData 的这篇文章很好地解释了这个概念。
My tip: Start with PostgreSQL as a queueing system. Only when that does no longer perform well switch to other systems like Kafka, RabbitMQ or SQS. You’ll be surprised how well PostgreSQL works.我的建议：先从使用 PostgreSQL 作为队列系统开始。只有当性能不再满足需求时，再切换到 Kafka、RabbitMQ 或 SQS 等系统。你会惊讶于 PostgreSQL 的表现。
PostgreSQL Replaces Clickhouse: High Volume Time Series DataPostgreSQL 替代 Clickhouse：高频时间序列数据
Time series data is special. Often you get many data points in a very short amount of time. And then you have to aggregate the data frequently, doing some statistics on it and so on.时间序列数据很特殊。通常，你会在极短时间内获得大量数据点，然后必须频繁地对数据进行聚合、统计等处理。
There are specialized software systems like Clickhouse (amazing by the way…). But you can also use a plugin for PostgreSQL that allows you to do (nearly) the same: Timescale.虽然有 Clickhouse 等专业软件系统（顺便说一句，它非常棒……），但你也可以使用 PostgreSQL 的插件来实现（几乎）相同的功能：Timescale。
I’ve used Timescale and can recommend it. The good news is that you can continue using PostgreSQL - even for high volume data easily. No need to learn and maintain something new.我使用过 Timescale 并推荐它。好消息是，你可以继续使用 PostgreSQL，即使是处理海量数据也毫无压力。无需学习和维护新系统。
PostgreSQL as Vector Database for AI WorkflowsPostgreSQL 作为 AI 工作流的向量数据库
Timescale lately released the pgvector extension, that turns your PostgreSQL into a vector database. This allows you to use the tech you already know for indexing and retrieval of relevant data. That’s an essential part of AI LLM workflows.Timescale 最近发布了 pgvector 扩展，将 PostgreSQL 变成了向量数据库。这允许你利用已有的技术知识来索引和检索相关数据。这是 AI LLM 工作流中至关重要的一部分。
Timescale also recently announced pgai that includes pgvector, but also a lot of other nice extensions that make it super simple to index data, call LLM models and retrieve data based on similarity.Timescale 最近还宣布了 pgai，它不仅包含 pgvector，还包含许多其他出色的扩展，使得索引数据、调用 LLM 模型以及基于相似度检索数据变得异常简单。
PostgreSQL Replaces Redis: Non-Persistent High Performance CachingPostgreSQL 替代 Redis：非持久化高性能缓存
Caching is important. Most applications use something like Redis as a cache to get information like sessions and more quickly. A cache can by definition lose data and can be regenerated from the original source.缓存很重要。大多数应用程序使用 Redis 之类的工具作为缓存来快速获取会话等信息。从定义上讲，缓存可以丢失数据，并且可以从原始源重新生成。
But. Why use Redis when PostgreSQL can be tuned to be as fast (in most usecases) as a Redis cache? The secret is using an UNLOGGED table. You can even emulate Redis’ automatic expire by a trigger. A lot has been written about this - I can just recommend trying it out.但是，为什么在 PostgreSQL 可以被调优到（在大多数用例中）与 Redis 缓存一样快的情况下还要使用 Redis 呢？秘诀在于使用 UNLOGGED 表。你甚至可以通过触发器来模拟 Redis 的自动过期功能。关于这一点已经有很多讨论了——我强烈建议你尝试一下。
PostgreSQL Replaces File System: For Raw DataPostgreSQL 替代文件系统：用于原始数据
For one of my clients we had to read and write a huge amount of small pieces of binary encoded information. We initially thought that doing this via the file system was the fastest way to do so.在我的一个客户项目中，我们需要读写大量小的二进制编码片段。我们最初认为通过文件系统来做是最快的方式。
After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system.经过一些性能测试，我们发现对于我们的用例，PostgreSQL 甚至比从文件系统读取更快。PostgreSQL 非常高效地利用文件系统存储数据，并且增加了大量的缓存以及高效的读写策略，其性能可以超过在文件系统上直接读写原始数据。
We used Flatbuffers to store the data in a blob column. Data was then de-serialized on the client. You might want to try that approach as well.我们使用 Flatbuffers 将数据存储在 blob 列中。数据随后在客户端反序列化。你也可以尝试这种方法。
PostgreSQL Replacing Your Graph DatabasePostgreSQL 替代图数据库
Hierarchical data can be managed in SQL via recursive queries. That’s ok, but also super-hard to read, maintain and debug. Not even speaking of performance.层次结构数据可以通过 SQL 中的递归查询进行管理。这没问题，但代码极其难以阅读、维护和调试，更不用说性能问题了。
The better way is the LTREE datatype of PostgreSQL. It helped me not only once to implement hierarchical tag structures. Easy to read, maintain and blazingly fast.更好的方法是使用 PostgreSQL 的 LTREE 数据类型。它不止一次地帮我实现了层次化的标签结构。不仅易于阅读和维护，而且速度极快。
PostgreSQL Replacing Your MicroservicePostgreSQL 替代微服务
Most of the “microservices” these days are only about models, getting data from a database and returning json to the client.如今大多数“微服务”仅仅是关于模型、从数据库获取数据并向客户端返回 JSON。
But you know what? PostgreSQL can turn any query into a Json result. That effectively replaces your server middleware. There are Pros and Cons to this approach, but it shows the capabilities of PostgreSQL. The amazing Lukas Eder wrote about the topic - not PostgreSQL specific - but everything mentioned there is very well doable in PostgreSQL as well但你知道吗？PostgreSQL 可以将任何查询直接转换为 JSON 结果。这有效地替代了你的服务器中间件。这种方法有利有弊，但它展示了 PostgreSQL 的强大能力。出色的 Lukas Eder 写过关于这个话题的文章——虽然不是专门针对 PostgreSQL，但其中提到的所有内容在 PostgreSQL 中也完全可以实现。
PostgreSQL - Replacing your Playstation 5PostgreSQL - 替代你的 Playstation 5
Well. Some enthusiast implemented Tetris as Common Table Expressions in pure SQL. Crazy. And maybe not to be taken too seriously.嗯，有些发烧友用纯 SQL 的公用表表达式 (CTE) 实现了俄罗斯方块。太疯狂了，或许不必太当真。
Conclusion结论
The list above is not very exhaustive. PostgreSQL is a very flexible piece of software. And it can be extended with plugins to do more and more.上面的列表并不详尽。PostgreSQL 是一款非常灵活的软件，并且可以通过插件扩展以实现越来越多的功能。
You need simplicity if you want to move fast. If you come across a new requirement always ask: Can’t PostgreSQL do this? And do we really need that shiny new technology X?如果你想快速前进，就需要简洁。当你遇到新的需求时，请务必问问自己：PostgreSQL 不能完成吗？我们真的需要那个闪亮的新技术 X 吗？
PostgreSQL might not be the answer to everything - but it is the answer to a lot more than you might think!PostgreSQL 可能不是万物的答案，但它能解决的问题远比你想象的要多！
