Home 零对冲(ZeroHedge)每日HackerNews

PostgreSQL 内部原理
The Internals of PostgreSQL

原始链接: https://www.interdb.jp/pg/index.html

Hironobu Suzuki 是一名软件工程师和作者，开放国际 PostgreSQL 机会，包括欧洲、南半球和东南亚。他寻求具有签证赞助或远程、数字游民友好工作的挑战性 PostgreSQL 职位，也寻求在日本居住数年期间从事将机器学习/人工智能应用于数据库管理系统的远程职位。他创建了《PostgreSQL 内幕》，出版了七本数据库书籍，并在日本组织了 PostgreSQL 研讨会和会议。他曾在南美洲居住，有时会返回。可通过电子邮件或私信联系，其内容对教育、非商业和商业用途有具体规则。

Hacker News 正在讨论《PostgreSQL 的内部结构》，用户分享了实用的收获以及相关的数据库工程辩论。要点包括仅堆元组（HOT）更新：它仅在没有索引列变化且新元组能放入同一页时才有效，因此对频繁更新的列建索引可能禁用 HOT；降低 fillfactor 可能有帮助。列顺序因对齐填充而影响行大小。帖子还重新审视了 MVCC 权衡：Postgres 使用追加式存储和 vacuum，评论者比较了 Oracle 基于 undo 的方法，并提到 OrioleDB 作为替代的 MVCC 层。其他内容包括 PostgreSQL 18 全文搜索/排序规则变化，以及一个宣传的基于 SSD 的缓存 Lavik，声称比 Redis DRAM 更快。总体而言，该帖子混合了深入的 Postgres 内部机制与运维性能和存储引擎比较。
相关文章
原文

I am officially open to new international opportunities! 🌍

After a temporary stay in Japan to focus on my health and personal responsibilities, I am fully recovered, ready, and excited to work abroad again.

I am highly interested in returning to Europe, as well as opportunities in the Southern Hemisphere and Southeast Asia.

Looking for:

  • Challenging tasks in PostgreSQL.
  • Roles offering international visa sponsorship OR Remote work that supports a digital nomad lifestyle.

If your team is hiring, let’s connect! Please feel free to reach out via e-Mail(hironobu @ interdb.jp or interdb.mx @ gmail.com) or DM.

What’s New

The Internals of PostgreSQL

PostgreSQL is a well-designed, open-source multi-purpose relational database system which is widely used throughout the world.

It is one huge system with the integrated subsystems, each of which has a particular complex feature and works cooperatively with each other. Although understanding of the internal mechanism is crucial for both administration and integration using PostgreSQL, its hugeness and complexity make it difficult.

The main purposes of this documentation are to explain how each subsystem works, and to provide the whole picture of PostgreSQL.

This documentation covers versions 19-beta (excluding the new buffer page lock mechanism) and earlier.

Some academic papers have referred to this documentation. The Chinese version of this documentation was published in June 2019.

This documentation was first published on 26 September 2015.

Author

Hironobu SUZUKI

I am a software programmer/engineer, the author of:

I graduated from graduate school in information engineering (M.S. in Information Engineering), have worked for several companies as a software developer and technical manager/director. I published seven books in the fields of database and system integration (4 PostgreSQL books and 3 MySQL books). In June 2019, the Chinese book of this documentation was published.

As a director of the Japan PostgreSQL Users Group (2010-2016), I organized the largest (non-commercial) technical seminar/lecture on PostgreSQL in Japan for more than six years, and also served as the program committee chair of the Japan PostgreSQL Conference in 2013 and as a member in 2008 and 2009.

In June 2022, my interview article was published in “PostgreSQL person of the week”.

Cuando era joven, vivió en Sudamérica por unos años. Recientemente, a veces vuelve a allí.

I am seeking a remote position focused on the application of ML and AI technologies to DBMS. For personal reasons, I will be living in Japan for several years.

      Blog

Contact

Please read the following FAQ before sending messages.

Since publishing my content, I’ve been fortunate to receive a lot of positive feedback, which is truly gratifying. However, unfortunately, I’ve encountered individuals in the past who tried to misuse my content for self-promotion .

These experiences have shaped the approach I’ve outlined below.

  1. Who can use this documentation freely?
    If you are a teacher or a student belonging to an educational organization, you can freely use this documentation and figures in your study. Anyone can use this documentation and figures with noncommercial meetings and lectures, if you state the link to this site and the copyright; otherwise, contact me.
  2. Is it available for commercial contents?
    This content can be used under two options:
    There was a comment on Hacker News that took this seriously, but of course, it’s a joke. To be clear, I have no intention of having any commercial ties to this. Regardless, you can imagine the kind of requests I get on a daily basis.
    • Revenue Share: You can leverage this content after a revenue share agreement is signed. Under this agreement, you’ll share 20% of the sales generated from using this content.
    • Full Buyout: In very rare cases, I consider requests for full commercial use of all content on this site. For a complete buyout of all content rights, the cost is €5,000,000.
  3. Does the author need any help to write?
    No. I am writing for my own enjoyment. Even if you send me your article, I will never include it in my documentation.
  4. Why doesn’t the author use the GitBook or other e-book formats/sites?
    I am using the best tools I need and I will decide what I use. I am always trying a lot of tools for better explanations.
    Simply put, this documentation is optimized to read on html file and it is hard to convert to other formats.
    I do not have any plan to make PDF version and Smartphone versions because of same reason.
  5. Why doesn’t the author use RSS to notify the update?
    I keep my domain and my server to provide my documentation; you can read when you want to read. What do you want more?
  6. Why doesn’t the author waive the copyright of this documentation or use the creative commons license?
    I’d like to ask you what problems you have by that I keep on having the copyright of my documentation.

After reading, send a message to my twitter in public.

or  

If you use email, please provide at least two SNS addresses (e.g. LinkedIn, Twitter) for verification purposes. Due to the XZ backdoor incident, I no longer accept contact from anonymous individuals.

© Copyright ALL Right Reserved, Hironobu SUZUKI.

For any inquiries regarding the use of this documentation or any of its figures, please contact me.

Exception Educational institutions can use this documentation freely.

联系我们 contact @ memedata.com