PostgreSQL 通用数据库:一套系统处理一切场景
- Home
- Interim CTO
- Services
- Blog
- About
- 🇩🇪
- Home
- Interim CTO
- Services
- Blog
- About
- 🇩🇪
- 首页
- 临时首席技术官
- 服务
- 博客
- 关于
- 🇩🇪
- 首页
- 临时首席技术官
- 服务
- 博客
- 关于
- 🇩🇪
Menu
菜单
PostgreSQL for Everything.
一切皆可用PostgreSQL。
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年开始使用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广泛。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强大功能的知识。一个重要的用例是全文搜索。我们本可以使用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是敏捷的,Stephan Schmidt关于使用SQL处理一切的文章)。也请查看我的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关于“我希望有人告诉我的关于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…
- 它坚如磐石,稳定可靠。
- 它易于运行、安装和扩展。
- 它不仅是关系型数据库管理系统,还是全文搜索引擎、文档存储等,极大地简化了您的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是古老的技术。首个PostgreSQL版本可追溯至1996年。PostgreSQL也被广泛使用——且历史悠久。消除错误——尤其是在数据库系统中——需要时间。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文档存储、分区支持、公共表表达式等等。每个新版本的PostgreSQL都令人兴奋,并带来新的优秀功能。
True - PostgreSQL is old - but the features are very very modern - and PostgreSQL becomes better with every release.
确实,PostgreSQL很古老——但功能非常非常现代——而且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 AWS
- Google GCP
- Microsoft Azure
- ElephantSQL
- CrunchyData
- Timescale
- … and many more …
- Amazon AWS
- Google GCP
- Microsoft Azure
- ElephantSQL
- CrunchyData
- Timescale
- ……以及更多……
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 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 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 Data
PostgreSQL 取代 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 Workflows
PostgreSQL 作为 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大语言模型工作流中的关键部分。
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 Caching
PostgreSQL替代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 Data
PostgreSQL替代文件系统:用于原始数据
更进一步:量化金融体系
看懂新闻只是起点——沿量化金融路径,把它变成能交付的工程能力