JoelJacobson

In the article, the linked equivalent query is https://github.com/gregrahn/join-order-benchmark/blob/master... which is written using legacy comma-separated joins and a huge WHERE clause:

    SELECT MIN(an.name) AS cool_actor_pseudonym,
           MIN(t.title) AS series_named_after_char
    FROM aka_name AS an,
         cast_info AS ci,
         company_name AS cn,
         keyword AS k,
         movie_companies AS mc,
         movie_keyword AS mk,
         name AS n,
         title AS t
    WHERE cn.country_code ='[us]'
      AND k.keyword ='character-name-in-title'
      AND an.person_id = n.id
      AND n.id = ci.person_id
      AND ci.movie_id = t.id
      AND t.id = mk.movie_id
      AND mk.keyword_id = k.id
      AND t.id = mc.movie_id
      AND mc.company_id = cn.id
      AND an.person_id = ci.person_id
      AND ci.movie_id = mc.movie_id
      AND ci.movie_id = mk.movie_id
      AND mc.movie_id = mk.movie_id;

Cleaned up written as ON joins eliminating redundant quals:

    SELECT MIN(an.name) AS cool_actor_pseudonym,
           MIN(t.title) AS series_named_after_char
    FROM cast_info AS ci
    JOIN name            AS n  ON n.id         = ci.person_id
    JOIN title           AS t  ON t.id         = ci.movie_id
    JOIN aka_name        AS an ON an.person_id = n.id
    JOIN movie_keyword   AS mk ON mk.movie_id  = t.id
    JOIN keyword         AS k  ON k.id         = mk.keyword_id
    JOIN movie_companies AS mc ON mc.movie_id  = t.id
    JOIN company_name    AS cn ON cn.id        = mc.company_id
    WHERE cn.country_code = '[us]'
      AND k.keyword = 'character-name-in-title';

The keyword and company branches only control existence though; their row multiplicities cannot affect MIN. We can therefore optimize this using EXISTS:

   SELECT MIN(an.name) AS cool_actor_pseudonym,
          MIN(t.title) AS series_named_after_char
   FROM cast_info AS ci
   JOIN title AS t ON t.id = ci.movie_id
   JOIN aka_name AS an ON an.person_id = ci.person_id
   WHERE EXISTS
   (
       SELECT 1
       FROM movie_keyword AS mk
       JOIN keyword AS k ON k.id = mk.keyword_id
       WHERE mk.movie_id = t.id
         AND k.keyword = 'character-name-in-title'
   )
   AND EXISTS
   (
       SELECT 1
       FROM movie_companies AS mc
       JOIN company_name AS cn ON cn.id = mc.company_id
       WHERE mc.movie_id = t.id
         AND cn.country_code = '[us]'
   );

Shameless plug: We're working on a new proposed SQL feature to add explicit syntax for key joins: https://keyjoin.org Here is how the query could then be rewritten further:

    SELECT MIN(an.name) AS cool_actor_pseudonym,
           MIN(t.title) AS series_named_after_char
    FROM cast_info AS ci
    JOIN title AS t FOR KEY (id) <- ci (movie_id)
    JOIN aka_name AS an ON an.person_id = ci.person_id
    WHERE EXISTS
    (
        SELECT 1
        FROM movie_keyword AS mk
        JOIN keyword AS k FOR KEY (id) <- mk (keyword_id)
        WHERE mk.movie_id = t.id
          AND k.keyword = 'character-name-in-title'
    )
    AND EXISTS
    (
        SELECT 1
        FROM movie_companies AS mc
        JOIN company_name AS cn FOR KEY (id) <- mc (company_id)
        WHERE mc.movie_id = t.id
          AND cn.country_code = '[us]'
    );

Note: for this to work, I had to add referential constraints (aka "foreign keys") to the join-order-benchmark, which only had PRIMARY KEYs declared.

I wonder what a nontrivial multi-table query with joins look like in Acadia?

I'm not a native English speaker, I know "memory safe" has a precise technical meaning, but the word "safe" still feels a bit strange to me given that a memory-safety bug can make the program crash at run-time.

Sure, Fil-C prevents the bug from possibly being exploited, which is a huge improvement. But crashing can be a DoS attack, and if running a mission-critical system, it might not be an acceptable outcome.

I just feel the already very good presentation could have been made much better if it had put more weight on explaining these trade-offs.

I was amazed by the presentation all the way up until 33:47, where the buggy program compiles and only errors out when the invalid access is executed. So apparently Fil-C enforces memory safety dynamically at run-time, rather than detecting these bugs at compile-time.

A new language designed around memory safety, such as Rust, can reject large and important classes of memory-safety bugs at compile-time. Rust does not catch everything at compile-time, like bounds checks and RefCell, which are checked at run-time(, and the problems due to unsafe and C interop like explained in the presentation.)

Still, IMO the comparison becomes a bit apples vs pears when bragging about how much more memory-safe Fil-C is than Rust. It would have been helpful to explain this important difference about run-time vs compile-time.

Very cool and useful anyway.

It did fix the original "Postgres LISTEN/NOTIFY does not scale" [1] post's problem though, which was mentioned in an update of that article:

    Update: Fixed in Postgres core
    This commit has eliminated the bottleneck in the postgres core.
    Credit to Joel Jacobson and the core postgres contributors for resolving this.

[1] https://www.recall.ai/blog/postgres-listen-notify-does-not-s...

Here is the full pgsql-hackers mailing list thread where you can follow our work from initial idea to commit: https://www.postgresql.org/message-id/flat/9d8a4a42-c354-41f...

Back in 2024, I was trying to optimize PostgreSQL's NUMERIC data type, which is base-10000, using Karatsuba. The problem of finding the optimal threshold of when to switch to Karatsuba turned out to be really hard, since it depends on the size of both factors combined. After some hundreds of hours, I gave up, and started thinking about if there could be a simpler solution. I came to think about another idea I'd had before but abandoned, about 64-bit modernizing the digit base from 10k to 100M, but that would be a challenge due to existing data on disk. Desperate of finding a solution, I wondered if it could be fast enough to do on-the-fly conversion back and forth between base-10k and base-100M, and then realized that, yes, of course, it will be fast already for quite small N (testing shows already between 3-6 base digits). The trick basically reduced the N in O(N^2) into half, i.e. O((N/2)^2), with some O(2*N) cost for the conversion back and forth.

I had a lot of fun hacking on this idea together with the maintainer of the NUMERIC data type, and after two months the patch finally was ready and got committed:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

I was curious what the claim

"10-100x uplift in terms of speed compared to Postgres on things like regexp_matches()"

was about, so I checked, and DuckDB's regexp_matches() is not the same as PostgreSQL's regexp_matches(). DuckDB's version "Returns true if string contains the regexp pattern, false otherwise." [1] while PostgreSQL's "returns a set of text arrays of matching substring(s)" [2].

I think the closest think in PostgreSQL to DuckDB's regexp_matches() is `string ~ pattern` or `regexp_like(string, pattern)`.

[1] https://duckdb.org/docs/lts/sql/functions/regular_expression... [2] https://www.postgresql.org/docs/current/functions-matching.h...

Sorry, should have emphasized that it was the "much easier" part I didn't agree with in that interview.

This article made me think of a strange claim by Elon Musk at 07:08 in this [1] interview:

"Cooling is actually much easier in space than it is on earth. You can just radiate to the vacuum."

I don't think that follows. The radiator is only the final heat sink. You still need to move heat from very dense chips into a deployable, space-rated radiator, and handle pumps, loops, leaks, redundancy, radiation damage, replacement, eclipses, Earth IR/albedo, and launch mass.

[1] https://youtu.be/D_1j5dVWNYI?si=R77VeVKlRXRhaBk5&t=428

I think we can't rule out the explanation that all the ideas of space data centers could be connected to a desire by some of finding additional applications for rockets that can transport stuff to space.

Here is a tl;dr as well: https://keyjoin.org/tldr.html

Shame on The Netherlands: ~89% of homes still use natural gas in some way for heating [1], and their government are now "scrapping the obligation to purchase a heat pump in 2026" [2].

[1] https://www.cbs.nl/en-gb/news/2025/50/ever-more-gas-free-hom... [2] https://www.abnamro.nl/en/personal/specially-for/preferred-b...

Shameless plug: In the upcoming release of PostgreSQL 19, LISTEN/NOTIFY has been optimized to scale much better with selective signaling, i.e. when lots of backends are listening on different channels, patch: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

I agree! It should be very stable, IMO. If not, then please send a bug report and we'll look into it. Also, now it scales well with the number of listening connections (given clients listen on unique channel names): https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Rust without async maybe?

What a really like about concurrent(), is that it improves readability and expressiveness, making it clear when writing and reading that "this code MUST run in parallel".

It's a common misconception that the single queue is a poor design choice. The user reports, of seeing notifications/second severely degrade with lots of backends, cannot be explained by the single-queue design. An efficient implementation of a single-queue, should flatten out as parallellism increases, not degrade and go towards zero.

In the linked "Optimize LISTEN/NOTIFY" pgsql-hackers, I've shared a lot of benchmark results for different workloads, which also include results on how PostgreSQL currently works (this is "master" in the benchmark results), that can help you better understand the expectations for different workloads.

The work-around solution we used at Trustly (a company I co-founded), is a component named `allas` that a colleague of mine at that time, Marko Tikkaja, created to solve our problems, that massively reduced the load on our servers. Marko has open sourced and published this work here: https://github.com/johto/allas

Basically, `allas` opens up a single connection to PostgreSQL, on which it LISTEN on all the channels it needs to listen on. Then clients connect to `allas` over the PostgreSQL protocol, so it's basically faking a PostgreSQL server, and when clients do LISTEN on a channel with allas, allas will then LISTEN on that channel on the real PostgreSQL server on the single connection it needs. Thanks to `allas` being implemented in Go, using Go's efficient goroutines for concurrency, it efficiently scales with lots and lots of connections. I'm not a Go-expert myself, but I've understood Go is quite well suited for this type of application.

This component is still being used at Trustly, and is battle-tested and production grade.

That said, it would of course be much better to avoid the need for a separate component, and fix the scalability issues in core PostgreSQL, so that's what I'm currently working on.

Thanks for the report. For that use-case (if you have a single application using a single connection with a LISTEN) then it's expected that is should perform well, since then there is only a single backend which will be context-switched to when each NOTIFY signals it.

Here is the Commitfest entry if you want to help with reviewing/development/testing of the patch: https://commitfest.postgresql.org/patch/6078/

> It works, but suddenly your query times explode! Instead of doing 1 million transactions per second* you can now do only 3 (*These numbers were exaggerated for dramatic effect)

In general, a single-queue design doesn’t make throughput collapse when you add more parallelism; it just gives you a fixed ceiling. With a well-designed queue, throughput goes up with concurrency, then flattens when the serialized section (the queue) saturates, maybe sagging a bit from context switching.

If instead you see performance severely degrade as you add workers, that typically means there’s an additional problem beyond “we have one queue” — things like broadcast wakeups (“every event wakes every listener”), global scans on each event, or other O(N) work per operation. That’s a very different, and more serious, scalability bug than simply relying on a single queue.

> The problem with Postgres' NOTIFY is that all notifications go through a single queue!

> Even if you have 20 database connections making 20 transactions in parallel, all of them need to wait for their turn to lock the notification queue, add their notification, and unlock the queue again. This creates a bottleneck especially in high-throughput databases.

We're currently working hard on optimizing LISTEN/NOTIFY: https://www.postgresql.org/message-id/flat/6899c044-4a82-49b...

If you have any experiences of actual workload where you are currently experiencing performance/scalability problems, I would be interested in hearing from you, to better understand the actual workload. In some workloads, you might only listen to a single channel. For such single-channel workloads, the current implementation seems hard to tweak further, given the semantics and in-commit-order guarantees. However, for multi-channel workloads, we could do a lot better, which is what the linked patch is about. The main problem with the current implementation for multi-channel workloads, is that we currently signal and wake all listening backends (a backend is the PostgreSQL processes your client is connected to), even if they are not interested in the specific channels being notified in the current commit. This means that if you have 100 connections open in which each connect client has made a LISTEN on a different channel, then when someone does a NOTIFY on one of those channels, instead of just signaling the backend that listen on that channel, all 100 backends will be signaled. For multi-channel workloads, this could mean an enormous extra cost coming from the context-switching due to the signaling.

I would greatly appreciate if you could please reply to this comment and share your different workloads when you've had problems with LISTEN/NOTIFY, to better understand approximately how many listening backends you had, and how many channels you had, and the mix of volume on such channels. Anything that could help us do better realistic simulations of such workloads, to improve the benchmark tests we're working on. Thank you.

> If you call pg_notify or NOTIFY inside a trigger, it will get called 100,000 times and send out 100,000 notifications if you change 100,000 rows in a single transaction which from a performance perspective is ... not ideal.

This is only true if those notifications are different; if they are identical, such as in the same the notification is to alert listeners some table has new data (for cache invalidation), they are sent out as one notification only. See source code comment in async.c:

     *   Duplicate notifications from the same transaction are sent out as one
     *   notification only. This is done to save work when for example a trigger
     *   on a 2 million row table fires a notification for each row that has been
     *   changed. If the application needs to receive every single notification
     *   that has been sent, it can easily add some unique string into the extra
     *   payload parameter.

I think the sweet spot is to use exceptions for bugs. If the error is expected, make it data.

It made me happy to see the pg_get_acl() function that I was involved in adding, is appreciated by users. I think there is still much improvement in the space of querying privileges. I think most users would probably struggle to come up with the query from the article:

    postgres=# SELECT
        (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
        pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl
    FROM pg_catalog.pg_shdepend AS s
    JOIN pg_catalog.pg_database AS d
        ON d.datname = current_database() AND
        d.oid = s.dbid
    JOIN pg_catalog.pg_authid AS a
        ON a.oid = s.refobjid AND
        s.refclassid = 'pg_authid'::regclass
    WHERE s.deptype = 'a';
    -[ RECORD 1 ]-----------------------------------------
    type     | table
    schema   | public
    name     | testtab
    identity | public.testtab
    acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}

What I wanted to really add, was two new system views, pg_ownerships and pg_privileges [1]. The pg_get_acl() was a dependency that we needed to get in place first. In the end, I withdrew the patch trying to add these views. If there is enough interest from users, I might consider picking up the task of trying to work out the remaining obstacles.

Do people here need pg_ownerships and/or pg_privileges?

[1] https://www.postgresql.org/message-id/flat/bbe7d1cb-0435-4ee...

I'll add comments to this thread with Github projects that I can find by searching for "postgres listen/notify" on Github.

https://github.com/graphile/worker

    // Line 535 in src/main.ts
    client.query('LISTEN "jobs:insert"; LISTEN "worker:migrate";')

Every worker pool seems to listen on the same shared channels: jobs:insert - all workers get notified when new jobs are added worker:migrate - all workers get notified about database migrations

Thanks! I found a link to a GitHub Issue where they described their experienced problems: https://github.com/pulp/pulpcore/issues/6805