PostgreSQL for Everything

Contrary to popular belief - the answer to everything is NOT 42 - it’s PostgreSQL. (ok. It might also be Postgres). 与普遍的看法相反，万物的答案不是 42，而是 PostgreSQL。（好吧，也许也可以叫它 Postgres）。

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 年开始使用 PostgreSQL，当时是为了一个名为 ColumbaDB 的研究项目。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。由于没有实现 SQL 标准的所有功能，MySQL 在当时可能更快。但与此同时，MySQL 缺乏我们所需的许多功能（如全文搜索、强大的索引、SQL 标准合规性等）。与 MySQL 相比，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 强大功能的知识。其中一个重要的用例是全文搜索。我们本可以结合 MySQL 和 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 插件使用 PostgreSQL 来存储海量的网络分析时间序列数据。你可以看看 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》的文章）。也欢迎查看我的领英帖子。

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》（我希望有人早点告诉我的关于 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 使用范围极广，且历史悠久。修复 Bug（尤其是在数据库系统中）需要时间，而 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 Homebrew 的一部分，还可以通过 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 install 即可，或者在 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 AWS
Google GCP
Microsoft Azure
ElephantSQL
CrunchyData
Timescale
… 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 you’d have to run otherwise. 在云端运行 PostgreSQL 只需一键点击，但它的优势远不止于此。PostgreSQL 可以替代许多你原本不得不运行的其他系统。

PostgreSQL Replaces Solr and Elastic: Full-Text Search PostgreSQL 替代 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 Support PostgreSQL 替代 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. 《卫报》也写了一篇很棒的文章，讲述了他们如何从 Mongo 迁移到 PostgreSQL。感谢 Jan-Otto 的分享！Hazel 也写了一篇关于 jsonb 的好文章，介绍了使用时需要注意的事项。

PostgreSQL replaces Kafka and RabbitMQ: PostgreSQL as a queue PostgreSQL 替代 Kafka 和 RabbitMQ：将 PostgreSQL 作为队列使用

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 UPDATE SELECT .. 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. 我的建议是：先从 PostgreSQL 作为队列系统开始。只有当它无法满足性能需求时，再考虑切换到其他系统。