Hacker News Reader

samokhvalovon PGSimCity - How PostgreSQL Works

it was silly, quick check out of curiosity what Opus 5 can do – and the first thing I saw was so impressive that it made me spend hours during weekend polishing it

here it is from history, without edits

> you're a veteran Postgres hacker and expert. I want 3D visualization of how Postgres works, for browser -- all its major components, presented as a 3d model, complex, with all parts zoomable, and animated, with controls -- checkpointer, bgwriter, autovacuum, walwriter, backends, walsender, etc etc etc. Imagine we need to build a 3d model of whole city. That's same level. We need this so engineers who are non-DB-experts would easily understand how it works. Design must be cool, modern, super cool, running all in browser, with controls, with camera position flying with arrow keys and mouse, zoomable, etc. Think deep how to implement it, which modern cool tech to use, and use ultracode to implement in a new directory (we'll commit it later). OK to use the most cool and most modern stuff. Think deep choose tools wisely and let's build an awesome in-browser 3d model of Postgres engine

(later I came with lots of materials about internals and behavior and we started to polish / improve)

samokhvalovon PGSimCity - How PostgreSQL Works

correct on both counts

samokhvalovon PGSimCity - How PostgreSQL Works

yeah, it was there before your comment; maybe not easy to find (help is on "?", which is also maybe not easy to find)

samokhvalovon PGSimCity - How PostgreSQL Works

yep (via pressing T)

but I agree, could be better – thinking

samokhvalovon PGSimCity - How PostgreSQL Works

try pressing T

samokhvalovon PGSimCity - How PostgreSQL Works

Correct -- z-fighting on coplanar ground surfaces. Fixing with explicit offsets.

thanks

samokhvalovon PGSimCity - How PostgreSQL Works

I hear you. WIP!

thanks for all the feedback. It's amazing for me too. It all started yesterday with a single prompt and curiosity what Opus 5 can do.

~3.86B tokens used so far (not counting GPT 5.6 Sol that at some point started to do a lot of legwork for coding)

samokhvalovon Launch HN: Ardent (YC P26) – Postgres sandboxes in seconds with zero migration

Hey, PostgresAI founder here.

thank you for using DBLab

Can you DM me, please? Really curious about your experience

samokhvalovon PgQue: Zero-Bloat Postgres Queue

thank you!

samokhvalovon PgQue: Zero-Bloat Postgres Queue

thanks for pushing back, by the way – I'm thinking this thru, and will likely rename

fun fact: I now think, "River" (Go project) is also a misleading name for a task queue system :)

samokhvalovon PgQue: Zero-Bloat Postgres Queue

1. partitions are never dropped – they got TRUNCATEd (gracefully) during rotation

2. INSERT-only. Each consumer remembers its position – ID of the last event consumed. This pointer shifts independently for each consumer. It's much closer to Kafka than to task queue systems like ActiveMQ or RabbitMQ.

When you run long-running tx with real XID or read-only in REPEATABLE READ (e.g., pg_dump for long time), or logical slot is unused/lagging, this affects performance badly if you have dead tuples accumulated from DELETEs/UPDATEs, but not promptly vacuumed.

PgQue event tables are append-only, and consumers know how to find next batch of events to consume – so xmin horizon block is not affecting, by design.

samokhvalovon Show HN: Postgres extension for BM25 relevance-ranked full-text search

you need to explain claude code that PG18 is out already ;)

samokhvalovon PgQue: Zero-Bloat Postgres Queue

Fair. I had an attempt to clarify it in README that PgQue is "closer to Kafka topics than to a job queue" -- per-subscription cursor on a shared event log, no ACK-delete, no visibility timeout.

That makes PgQue an event-streaming tool, not an MQ. For SKIP LOCKED systems like PGMQ, PgQue can still be a replacement in certain cases – similarly to how Kafka can be a replacement for RabbitMQ or ActiveMQ in certain cases.

Agreed the "queue" naming is historical and a bit loose -- https://github.com/NikolayS/pgque/issues/70

samokhvalovon PgQue: Zero-Bloat Postgres Queue

correct

it's explained in README:

> Category: River, Que, and pg-boss (and Oban, graphile-worker, solid_queue, good_job) are job queue frameworks. PgQue is an event/message queue optimized for high-throughput streaming with fan-out.

samokhvalovon I dug into the Postgres sources to write my own WAL receiver

nice work

I wonder if you considered WAL-G, which is also written in Go

and has this: https://github.com/wal-g/wal-g/blob/master/docs/PostgreSQL.m...

samokhvalovon PgQue: Zero-Bloat Postgres Queue

Taxonomy is correct. But the benefit isn't "table grows indefinitely vs. vacuum-starved death spiral"

in all three approaches, if the consumer falls behind, events accumulate

The real distinction is cost per event under MVCC pressure. Under held xmin (idle-in-transaction, long-running writer, lagging logical slot, physical standby with hot_standby_feedback=on):

1. SKIP LOCKED systems: every DELETE or UPDATE creates a dead tuple that autovacuum can't reclaim (xmin is frozen). Indexes bloat. Each subsequent FOR UPDATE SKIP LOCKED scans don't help.

2. Partition + DROP (some SKIP LOCKED systems already support it, e.g. PGMQ): old partitions drop cleanly, but the active partition is still DELETE-based and accumulates dead tuples — same pathology within the active window, just bounded by retention. Another thing is that DROPping and attaching/detaching partitions is more painful than working with a few existing ones and using TRUNCATE.

3. PgQue / PgQ: active event table is INSERT-only. Each consumer remembers its own pointer (ID of last event processed) independently. CPU stays flat under xmin pressure.

I posted a few more benchmark charts on my LinkedIn and Twitter, and plan to post an article explaining all this with examples. Among them was a demo where 30-min-held-xmin bench at 2000 ev/s: PgQue sustains full producer rate at ~14% CPU; SKIP LOCKED queues pinned at 55-87% CPU with throughput dropping 20-80% and what's even worse, after xmin horizon gets unblocked, not all of them recovered / caught up consuming withing next 30 min.

samokhvalovon PgQue: Zero-Bloat Postgres Queue

(PgQue author here)

I didn't understand nuances in the beginning myself

We have 3 kinds of latencies when dealing with event messages:

1. producer latency – how long does it take to insert an event message?

2. subscriber latency – how long does it take to get a message? (or a batch of all new messages, like in this case)

3. end-to-end event delivery time – how long does it take for a message to go from producer to consumer?

In case of PgQ/PgQue, the 3rd one is limited by "tick" frequency – by default, it's once per second (I'm thinking how to simplify more frequent configs, pg_cron is limited by 1/s).

While 1 and 2 are both sub-ms for PgQue. Consumers just don't see fresh messages until tick happens. Meanwhile, consuming queries is fast.

Hope this helps. Thanks for the question. Will this to README.

samokhvalovon Show HN: Managed Postgres with native ClickHouse integration

congrats! the more postgres everywhere, the better

samokhvalovon Snowflake to buy Crunchy Data for $250M

1) built using an open source kubernetes operator, as I understand 2) Crunchy provides true superuser access and access to physical backups – that's huge

samokhvalovon Xata: Postgres at scale, with copy-on-write branching and anonymization

I also don't see any good reasons for this.

samokhvalovon Xata: Postgres at scale, with copy-on-write branching and anonymization

> we deploy the Postgres instances on Kubernetes via the CloudNativePG operator.

I'm curious if split brain cases already experienced. At scale, it should be so https://github.com/cloudnative-pg/cloudnative-pg/issues/7407

samokhvalovon Optimizing Postgres table layout for maximum efficiency

Thanks for mentioning!

samokhvalovon Ask HN: Where are you hosting your Postgres database in 2024?

have you had minor and major upgrades? with really 0 downtime?

samokhvalovon PostgreSQL and UUID as Primary Key

there is also a problem of data locality and blocks present in caches (page cache, buffer pool) at any given time, in general -- UUIDv4 is losing to bigint and UUIDv7 in this area

samokhvalovon PostgreSQL and UUID as Primary Key

some related stuff:

- https://commitfest.postgresql.org/48/4388/ (original patch created live https://www.youtube.com/watch?v=YPq_hiOE-N8)

- https://postgres.fm/episodes/uuid

- https://postgres.fm/episodes/partitioning-by-ulid

- https://gitlab.com/postgres-ai/postgresql-consulting/postgre...

samokhvalovon Ask HN: Best way to set up self-managed Postgres clusters?

Yes

But why? Patroni is great for HA and it doesn't require k8s.

samokhvalovon Ask HN: Best way to set up self-managed Postgres clusters?

Clusters with HA, backups and so on

samokhvalovon Ask HN: Best way to set up self-managed Postgres clusters?

Why, indeed?

samokhvalovon Ask HN: Best way to set up self-managed Postgres clusters?

Context: https://twitter.com/samokhvalov/status/1771573110858269014

~1000 votes in just one day – obviously, this is an attractive topic to discuss, so wanted to have a thoughtful conversation here on HN.

samokhvalovon Demand the impossible: Rigorous database benchmarking

very good example

for each setting, we need to remember trade-offs

and in this particular case, the trade-off is increased recovery time after potential crashes

it is possible to conduct another benchmark that will measure this time, showing how it increases (in "avg" case -- "normal" TPS, and in "worst"* case -- increased TPS, e.g. massive UPDATE with random IO/access), and collect another set of interesting data and then combine two sets to support decisions

in result, we will choose something like: we decided to use 32 GiB for max_wal_size and 15 min checkpoint_timeout and we know that if we crash in the worst* case, DB will need up to 10 minutes to recover; but benefit is that we have much less disk IO stress during massive writes.

___

*) "worst" here has two levels that show off when writes are massive with random IO pattern of block writes:

- excessive writes from frequent checkpoints

- additionally, more WAL needs to be written due to (if) full_page_writes=on