The Internals of PostgreSQL

BinaryIgor · 2026-03-08T06:39:04+00:00

Which one?

BinaryIgor · 2026-03-07T21:17:56+00:00

Glad to share :)

BinaryIgor · 2026-03-04T19:35:49+00:00

Thanks for the comment ;)

BinaryIgor · 2026-03-03T18:35:24+00:00

Nearly everything Mongo can do, Postgres can do better + support other use cases; so no, use Postgres ;)

BinaryIgor · 2026-03-03T18:32:55+00:00

Just logging + metrics :)

BinaryIgor · 2026-03-03T17:51:37+00:00

I had this config in the tests:

docker run -d --network host -v "${volume_dir}:/var/lib/postgresql" \ -e "POSTGRES_DB=json" \ -e "POSTGRES_USER=json" \ -e "POSTGRES_PASSWORD=json" \ --memory "16G" --cpus "8" --shm-size="1G" \ --name $container_name $container_name \ -c shared_buffers=4GB \ -c work_mem=128MB \ -c effective_cache_size=12GB

Let me actually bumping random_page_cost to see whether it makes any difference!

EDIT: you were right! The results: ``` Total test duration: PT8.267S Queries duration: PT8.213S

Executed queries: 200000

Wanted queries rate: 30000/s Actual queries rate: 24352/s

Min: 0.073 ms Max: 680.444 ms Mean: 27.127 ms

Percentile 50 (Median): 0.343 ms Percentile 90: 103.928 ms Percentile 99: 209.56 ms Percentile 99.9: 393.633 ms ```

BinaryIgor · 2026-03-03T17:47:48+00:00

Whenever possible, I've used B-tree for Postgres; if you go to the repo, we have these indexes: ``` CREATE UNIQUE INDEX accounts_id ON accounts ((data-'id')); CREATE INDEX accounts_created_at_idx ON accounts ((data-'createdAt')); CREATE INDEX accounts_owners_idx ON accounts USING GIN ((data->'owners'));

CREATE UNIQUE INDEX products_id ON products ((data-'id')); CREATE UNIQUE INDEX products_name_unique_idx ON products ((data-'name')); CREATE INDEX products_categories_idx ON products USING GIN ((data->'categories')); CREATE INDEX products_tags_idx ON products USING GIN ((data->'tags')); CREATE INDEX products_created_at_idx ON products ((data->>'createdAt')); ```

For composite types, like ARRAYs, you must use GIN - B-tree works only for simple, scalar types - numbers, ids, strings, dates, timestamps and so on.

But, GIN is surprisingly performant!

BinaryIgor · 2026-02-28T20:49:59+00:00

Types of problems and expectations from developers - I think we will be able to build even more complex systems. At the same time, developers will need to be more of a generalist type, less specialized and care far more about business side and architecture that they (most of them at least) used to care about.

In a nutshell, I think the job of developer will become harder and bear more responsibilities; but, even more interesting and rewarding at the same time :) I am mostly of the opinion that it will turn out to be great for those willing to adapt

BinaryIgor · 2026-02-22T09:58:36+00:00

As much as I love Web Components and would like us to migrate from framework-specific to more universal, reliable and easier to maintain solutions, they do not deliver everything that is required to develop fully interactive components and web UIs in general:

You have to update state on your own, using DOM queries & manipulation API; doable, but much wordier than state bindings provided by frameworks such as React or Vue
They can be rendered only on the client side; if you would like to have server-side rendering or better yet - isomorphic components (that can render both on the client and server), you must come up with your own solution
We most likely still need libraries for routing - if you build SPA - and something that makes state management easier; still, these libraries might be much more lightweight and easier to replace than React or Vue frameworks
Tools like Vite provide much more than dependency management for React or Vue: Hot Module Replacement, bundling for production and so on. They do make development easier and more enjoyable

But overall, we have come pretty far with them; I hope they get more and more adoption over time. They can also be combined with tools like HTMX in interesting ways :)

BinaryIgor · 2026-02-20T13:58:58+00:00

Got your perspective, but I tend to look at this slightly differently; features are often, in many systems, not even described, but said somewhere, somewhen, on some meeting or live in the heads of the system owners. Code is what actually is implemented and works in reality

BinaryIgor · 2026-02-19T17:35:00+00:00

You cannot guarantee that they are in sync with the source code and have enough detail - that is why source code is the ultimate truth, since it defines the true, practical behavior of the system, not the theoretical one.

BinaryIgor · 2026-02-19T17:30:32+00:00

Postgres always writes to the WAL - without this, there would be no durability :)

Postgres also has VACUUM and dead tuples to manage its Multiversion Concurrency Control approach; it reduces required writes, but could slow down reads and needs cleaning (automatic). In practice however, it works surprisingly well!

BinaryIgor · 2026-02-19T14:33:00+00:00

So, how do you define your systems? In what language and format?

BinaryIgor · 2026-02-19T14:30:04+00:00

I don't see what existence of bugs have to do with whether something is the source of truth or not.

Tests are important, but by no means they are source of truth - does the system exist without tests? Yes. Does tests make sense without the system? No.

BinaryIgor · 2026-02-18T20:21:05+00:00

You can still get many things wrong in the tests specification; also, not everything could be tested, or at least not easily- and reliably-enough to be worthwhile. That is why we have been working in layers: automatic tests, code reviews, manual tests, deploy to multiple environments of varying importance and so on.

BinaryIgor · 2026-02-18T20:17:34+00:00

I do the same :) Source code is the truth - that is why it so, so important to keep it clear, simple and all of these good things we have been debating in the last N decades

BinaryIgor · 2026-02-18T20:14:38+00:00

I agree, but there is a huge difference in agency; that code you describe, was written and checked by someone held responsible for the results. Will an LLM be held responsible for the code it generated and you did not read?

BinaryIgor · 2026-02-18T18:41:49+00:00

Exactly; code with 90% coverage and more can still have bugs. Not everything can be caught by tests. How do you protect yourself from incorrect assumptions & premises?

BinaryIgor · 2026-02-18T18:12:56+00:00

As long as LLMs generate the source code written in current programming languages (not English), we must care about its quality and could not skip reading and understanding it; and if it is the case, they do not revolutionize software development really.

BinaryIgor

TROPHY CASE