PostgreSQL for Everything
Drawing on years as CTO and interim manager, the author argues that PostgreSQL can replace most of your stack: full-text search, document storage, queues, time-series, vector search, cache, even graph databases. He cites Contentful, Instacart and The Guardian as real-world examples, then walks through the mechanisms: tsvector/tsquery, GIN indexes, SELECT ... SKIP LOCKED for queues, UNLOGGED tables for cache-like speed, TimescaleDB for analytics, pgvector for AI retrieval, and Apache AGE for openCypher. The post is a practical starting point for engineers who want to simplify operations, but it is an opinion essay rather than a benchmark-driven study, so the performance claims need validation before adoption.
Contrary to popular belief - the answer to everything is NOT 42 - it’s PostgreSQL. (ok. It might also be Postgres).
This article was discussed on Hacker News. The thread contains a lot of additional links, real-world experience and critical thoughts - well worth reading.
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.
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.
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 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.
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.
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.
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”.
In my humble opinion the power of PostgreSQL comes from three sources:
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…
Let’s have a closer look…
与普遍看法相反，万物的答案不是 42，而是 PostgreSQL。（好吧，也可能叫 Postgres。）
本文在 Hacker News 上引发过讨论。帖子里有大量延伸链接、真实经验与批评性思考，很值得一读。
我大约在 2003 年开始在一个名为 ColumbaDB 的研究项目中使用 PostgreSQL。Columba 早已不在了，但 PostgreSQL 至今依然充满活力。
2003 年时 MySQL 比 PostgreSQL 流行得多。MySQL 可能也更快，因为它没有实现全部 SQL 标准特性。但与此同时，MySQL 缺少我们需要的许多功能（全文搜索、强大的索引、SQL 标准合规等）。相比之下，PostgreSQL 更像一个“真正的”数据库——像是穿上了开源外衣的小型 Oracle。
在那个研究项目中，我学到了很多关于数据库、索引和 PostgreSQL 威力的知识。 其中一个重要场景是全文搜索。我们本可以用 MySQL 搭配 Lucene / Solr 等另一个系统来让数据库可搜索。但那意味着要运行和维护两套系统，很麻烦。
PostgreSQL 让我们通过一个全文搜索插件就在一个系统里完成所有事。无需同步任何数据，也无需维护和运行两套系统。它开箱即用（当然，稍作调整之后），让我们喜笑颜开。这就是简洁的力量。
此后，在我担任 CTO / 临时管理者的职业生涯中，我把 PostgreSQL 用在了许多场景。最近一次，我通过 TimescaleDB 插件用 PostgreSQL 存储海量 Web 分析时间序列数据。你可以看看 Privatracker——既做 Web 分析又尊重访客隐私的最佳方式——来了解实际效果。
还有很多人从不同角度讨论过这个话题，每篇文章都值得一读（SQL is Agile、Stephan Schmidt 的 Using SQL for Everything）。也可以看看我的 LinkedIn 帖子。
如果你正在用 PostgreSQL，我强烈推荐阅读 Hazel Bachrach 的精彩文章“What I Wish Someone Told Me About Postgres”。
在我看来，PostgreSQL 的力量来自三个方面：
- 它坚如磐石、稳定可靠。
- 它易于运行、安装和扩展。
- 它能极大简化你的 IT 架构，因为它不仅是关系型数据库，还是全文检索引擎、文档存储，而且远不止这些……
下面我们仔细看看。
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.
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.
True - PostgreSQL is old - but the features are very very modern - and PostgreSQL becomes better with every release.
PostgreSQL 是无聊的老技术。它的第一个版本可以追溯到 1996 年。PostgreSQL 也被广泛使用了很长时间。消除 bug——尤其是数据库系统中的 bug——需要时间。PostgreSQL 拥有过这些时间。
它还有一个非常活跃的社区，不断添加新功能，同时不破坏任何旧有的部分。近年来，PostgreSQL 获得了许多令人惊叹的特性，比如 JSON 文档存储、分区支持、公共表表达式等等。每一个新版本都令人兴奋，带来新的好东西。
没错，PostgreSQL 很老——但它的特性非常非常现代——而且每发布一个新版本，PostgreSQL 都会变得更好。
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.
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.
If you want to run PostgreSQL on a server then you can simply apt-get install it. Or run it in a docker container.
All cloud providers allow you to run (and scale!) PostgreSQL by clicking a single button. You got ample of choice at your fingertips:
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 本地安装非常容易。它随所有主流 Linux 发行版捆绑，包含在 Mac 的 brew 中，也可以通过 PostgresApp 等应用安装。
运行测试时，使用 Testcontainers + PostgreSQL 非常方便。用与生产环境 100% 相同的真实 PostgreSQL 数据库来跑测试，从未如此简单。
如果要在服务器上运行 PostgreSQL，只需 apt-get install，或在 Docker 容器中运行。
所有云服务商都允许你点击一个按钮就运行（甚至扩展！）PostgreSQL。你面前有大量选择：
Amazon AWS、Google GCP、Microsoft Azure、ElephantSQL、CrunchyData、Timescale……还有很多。
这让 PostgreSQL 成为市场上支持面最广的软件系统之一。对你来说，这意味着更少的维护，更多为客户开发新功能的时间。
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 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.
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.
Instacart did something very similar: They built their modern search infrastructure on Postgres instead of running a separate search cluster. Same story, different company.
The built-in tsvector / tsquery machinery is a very good starting point. It is part of vanilla PostgreSQL, needs no extra moving parts, and works extremely well for the vast majority of use-cases. Start there.
If you outgrow it - typically because you need better relevance ranking (BM25) or more scalability - you don’t have to leave PostgreSQL either. There are extensions that pick up exactly where the built-in search ends:
pg_textsearch by Timescale / TigerData - BM25 ranking as a PostgreSQL extension.
ParadeDB / pg_search - Elasticsearch-grade search inside PostgreSQL, built on Tantivy (docs).
That’s the beauty of it: You can start with plain vanilla PostgreSQL and seamlessly move to more advanced techniques and third-party extensions once - and only if - you actually need them.
The pros and cons of full-text search on PostgreSQL are discussed very nicely in this LinkedIn discussion - recommended reading before you decide.
More on the topic: https://www.postgresql.org/docs/current/textsearch.html
PostgreSQL 可以把你的文本数据变成用户可搜索的数据，而无需额外的系统。它还无关语言，而且你永远不会遇到数据与全文搜索系统之间的同步问题。
这方面最令人印象深刻的是 Contentful 如何用 PostgreSQL 为其用户提供全文搜索。这是一个以简洁支撑增长的故事。
Instacart 也做了非常类似的事：他们没有运行独立的搜索集群，而是在 Postgres 上构建了现代搜索基础设施。同样的故事，不同的公司。
内置的 tsvector / tsquery 机制是一个很好的起点。它是原生 PostgreSQL 的一部分，不需要额外组件，在绝大多数场景下都表现得非常好。从那里开始。
如果你超出了它的能力——通常是因为需要更好的相关度排序（BM25）或更强的扩展性——你也不必离开 PostgreSQL。有一些扩展可以恰好从内置搜索的终点继续：
- Timescale / TigerData 的 pg_textsearch——作为 PostgreSQL 扩展提供 BM25 排序。
- ParadeDB / pg_search——基于 Tantivy 在 PostgreSQL 内部提供 Elasticsearch 级搜索（文档）。
这正是它的美妙之处：你可以从普通 PostgreSQL 开始，一旦——而且只有当你确实需要时——再无缝迁移到更高级的技术和第三方扩展。
关于 PostgreSQL 全文搜索的利弊，这个 LinkedIn 讨论说得很好——做决定前推荐阅读。
更多内容：https://www.postgresql.org/docs/current/textsearch.html
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?.
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.
PostgreSQL 对存储和查询（！）JSON 提供了出色的支持。它还有一种索引类型（GIN），能让这些操作快得惊人。还有必要再用 MongoDB 吗？
《卫报》也写过一篇优秀文章，讲述他们如何从 Mongo 切换到 PostgreSQL。感谢 Jan-Otto 分享！Hazel 也写了一篇关于 jsonb 以及使用注意事项的好文。
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.
The good news: You can just use PostgreSQL. The magic comes from
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.
The article at crunchydata explains this concept very well.
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.
事件、队列和持久化日志在当今软件系统中越来越重要。Kafka、RabbitMQ、SQS 等系统提供了这些功能。但维护它们很烦人、很定制化，而且需要相应的技能。
好消息是：你完全可以用 PostgreSQL。魔法来自
SELECT .. FOR UPDATE
SELECT .. SKIP LOCKED
利用这些 SQL 特性，你可以有效地把一张表当队列用。既可以采用带游标和多消费者的持久化方式，也可以采用只读一次的方式。
CrunchyData 的文章把这个概念解释得非常好。
我的建议是：先用 PostgreSQL 作为队列系统。只有当它确实不再满足性能时，再切换到 Kafka、RabbitMQ 或 SQS 等系统。你会惊讶于 PostgreSQL 的表现。
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.
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.
时序数据很特殊。通常你会在极短时间内获得大量数据点，然后还需要频繁聚合这些数据、做统计等等。
有些专门的软件系统，比如 ClickHouse（顺便说一句，很了不起……）。但你也可以用 PostgreSQL 的一个插件做（几乎）同样的事：Timescale。
我用过 Timescale，可以推荐。好消息是，即使面对高吞吐数据，你也可以继续轻松使用 PostgreSQL，无需学习和维护新东西。
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 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 最近发布了 pgvector 扩展，它能把 PostgreSQL 变成向量数据库。 这让你可以用已经熟悉的技术来索引和检索相关数据，这是 AI 大语言模型工作流中的关键部分。
Timescale 最近还发布了 pgai，其中包含 pgvector，以及许多其他好用的扩展，让索引数据、调用 LLM 模型和按相似度检索数据变得极其简单。
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.
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.
缓存很重要。大多数应用会用 Redis 之类的缓存来快速获取会话等信息。缓存按定义可以丢失数据，并且可以从原始来源重建。
但是，当 PostgreSQL 经过调优后（在大多数用例中）可以像 Redis 缓存一样快时，为什么还要用 Redis？秘诀是使用 UNLOGGED 表。你甚至可以通过触发器模拟 Redis 的自动过期。关于这一点已有大量文章，我只能推荐你亲自试试。
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.
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.
我的一位客户需要我们读写海量小型二进制编码信息。我们最初认为通过文件系统操作是最快的方式。
经过一番性能测试后，我们清楚地看到，在我们的场景下 PostgreSQL 甚至比从文件系统读取更快。PostgreSQL 非常高效地利用文件系统存储数据，而且它还有大量缓存以及高效的读写策略，可以胜过直接在文件系统上读写原始数据。
我们使用 Flatbuffers 将数据存入 blob 列，然后在客户端反序列化。你也可以试试这个方法。
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.
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.
层级数据可以通过递归查询在 SQL 中管理。这还行，但读起来、维护和调试都极其困难，更不用说性能了。
更好的方式是 PostgreSQL 的 LTREE 数据类型。它不只一次帮我实现层级化标签结构，易读、易维护，而且快得惊人。
But what if you need a real graph - not just a tree? Nodes, edges, properties, and queries that traverse relationships in every direction? That’s usually the moment someone suggests adding Neo4j to the stack. And with it: another system to run, another backup strategy, another data sync.
You don’t have to. Apache AGE (“A Graph Extension”) turns PostgreSQL into a graph database. It’s an Apache Software Foundation top-level project and it implements openCypher - the same query language you’d use in Neo4j. The best part: graph queries and plain SQL live in the same database and can be combined in a single statement.
SELECT * FROM cypher('my_graph', $$ MATCH (a:Person)-[:WORKS_AT]->(c:Company) RETURN a.name, c.name $$) AS (person agtype, company agtype);
My tip - the same one as for queueing: Start with PostgreSQL. Model your graph with AGE (or with LTREE if a hierarchy is all you need) and only reach for a dedicated graph database if you really hit the limits. Your data stays in one place, transactional and consistent with the rest of your application.
More on the topic: Apache AGE documentation and the source on GitHub.
但如果你需要的是一张真正的图——不只是树呢？节点、边、属性，以及向各个方向遍历关系的查询呢？通常这时就会有人建议在技术栈中加入 Neo4j。随之而来的是：又一个要运行的系统、又一套备份策略、又一次数据同步。
你不必如此。Apache AGE（“A Graph Extension”）可以把 PostgreSQL 变成图数据库。它是 Apache 软件基金会的顶级项目，实现了 openCypher——也就是你在 Neo4j 中使用的同一种查询语言。最棒的是：图查询和普通 SQL 生活在同一个数据库里，可以在同一条语句中组合使用。
SELECT * FROM cypher('my_graph', $$ MATCH (a:Person)-[:WORKS_AT]->(c:Company) RETURN a.name, c.name $$) AS (person agtype, company agtype);
我的建议和队列那条一样：从 PostgreSQL 开始。用 AGE 对图建模（如果你只需要层级，用 LTREE 就行），只有真的遇到瓶颈时再引入专门的图数据库。你的数据留在同一个地方，具备事务性，并与应用其余部分保持一致。
更多内容：Apache AGE 文档和 GitHub 源码。
Most of the “microservices” these days are only about models, getting data from a database and returning json to the client.
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
如今大多数“微服务”不过是处理模型、从数据库取数据并返回 JSON 给客户端。
你知道吗？PostgreSQL 可以把任何查询变成 JSON 结果。这实际上可以替代你的服务端中间层。这种方法有利有弊，但它展示了 PostgreSQL 的能力。出色的 Lukas Eder 写过相关话题——并非专门针对 PostgreSQL——但其中提到的每一点在 PostgreSQL 中也都完全可行。
Well. Some enthusiast implemented Tetris as Common Table Expressions in pure SQL. Crazy. And maybe not to be taken too seriously.
好吧。有位爱好者用纯 SQL 的公共表表达式实现了俄罗斯方块。很疯狂。也许不必太当真。
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.
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 might not be the answer to everything - but it is the answer to a lot more than you might think!
上面的清单不算很详尽。PostgreSQL 是一个非常灵活的软件，而且可以通过插件不断扩展，做越来越多的事。
想要快速前进，你就需要简洁。遇到新需求时，永远先问：PostgreSQL 做不到吗？我们真的需要那个闪闪发光的新技术 X 吗？
PostgreSQL 也许不是万物的答案——但它能回答的问题，远比你想的要多。