Astro Hacker News - The Internals of PostgreSQL

dang |next [-]

The Internals of PostgreSQL - https://news.ycombinator.com/item?id=30372750 - Feb 2022 (19 comments)

The Internals of PostgreSQL - https://news.ycombinator.com/item?id=30086374 - Jan 2022 (39 comments)

Internals of PostgreSQL - https://news.ycombinator.com/item?id=18950460 - Jan 2019 (25 comments)

The internals of PostgreSQL - https://news.ycombinator.com/item?id=13488315 - Jan 2017 (53 comments)

The Internals of PostgreSQL - https://news.ycombinator.com/item?id=12142364 - July 2016 (1 comment)

(Reposts are fine after a year or so; links to past threads are just to satisfy extra-curious readers)

gopalv |next |previous [-]

The latest Jepsen report on particledb triggered a new found interest in the SSI implementation in postgres.

If you are just skimming this doc, The HoT update path is the section I would jump to.

Understand HoT and then read the zHeap implementation notes (also of TOAST) puts a good mental model of what postgres does and what it doesn't do.

refset |root |parent [-]

Incidentally, the 2020 Jepsen report on Postgres found a long-standing bug in the SSI implementation [0]

[0] https://news.ycombinator.com/item?id=41560556

hmartin |root |parent [-]

The had a safe super intelligence bug dating back to 2011?

Wow so ahead of their time.

(And since I'm bad at sarcasm and don't wish to mislead, SSI = Serializable Snapshot Isolation in this context.)

jon128 |next |previous [-]

Didn't find the new MVCC in there.

  https://www.orioledb.com/

amw-zero |root |parent [-]

Because Postgres hasn't adopted that yet, and I'm not aware of any actual plan to adopt it? Tom Lane just did an interview where he said he doesn't see any world where they move away from dead tuples and vacuuming.

jon128 |root |parent [-]

Users of software often do not blindly do, or not do, whatever the upstream open source project does.

OrioleDB is friendlier to SSDs and is de facto "the other" MVCC of Postgres. OrioleDB may be still be "beta" but that hasn't stopped operators/users.

MariaDB has some alternative MVCC, one of them is particularly outstanding.

SSD endurance at scale has on and off been a major concern for me, so I empathize with that. Others deserve to know of OrioleDB, and Postgres more broadly is probably better off for it too.

  https://www.uber.com/us/en/blog/postgres-to-mysql-migration/

favori995749721 |next |previous [-]

Chapter 12 (full-text search) quietly changed under this book in Postgres 18: FTS now reads its dictionaries and config through the cluster's default collation provider instead of always libc, so clusters on ICU or the new builtin PG_UNICODE_FAST can see different behavior in FTS and pg_trgm — the release notes explicitly tell you to reindex those indexes after pg_upgrade. Also in 18, LIKE with nondeterministic collations stopped being an error, which was the main reason people avoided case-insensitive ICU collations on searchable columns.

getbrew |next |previous [-]

The HOT chapter is the one with the most direct practical payoff. Two things worth knowing from it: HOT only kicks in when no indexed column changes and the new tuple fits on the same page, so an index on a frequently-updated column (status, updated_at) quietly disables it for every update to that table. And lowering fillfactor (say 80-90) on update-heavy tables leaves free space on each page so HOT can actually happen. Comparing n_tup_hot_upd to n_tup_upd in pg_stat_user_tables is a cheap way to see whether you're getting HOT updates before tuning anything.

ak39 |root |parent |next [-]

Why does this comment sound AI generated?

ComputerGuru |root |parent |next |previous [-]

Depends on how wide your columns are and how often your records are modified. Past a certain width and I’m not sure lowering fillfactor is a good idea.

hmokiguess |root |parent |previous [-]

I’ll be real, I use Postgres every day and I have no clue what you’re talking about

jon128 |root |parent [-]

Damn. That sucks man. Do you have other pants? Do you need to go to the bathroom and fix it?

blenser4 |previous [-]

[dead]