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.
> 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.
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.
CREATE TRIGGER notify_events_trg AFTER INSERT ON xxx.events FOR EACH ROW EXECUTE PROCEDURE public.notify_events();
CREATE FUNCTION public.notify_events() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM pg_notify('events', row_to_json(NEW)::text);
RETURN NEW;
END;
$$;
And then we have a bunch of triggers like this on many tables:
CREATE TRIGGER create_category_event_trg AFTER INSERT OR DELETE OR UPDATE ON public.categories FOR EACH ROW EXECUTE PROCEDURE public.create_category_event();
CREATE FUNCTION public.create_category_event() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
category RECORD;
payload JSONB;
BEGIN
category := COALESCE(NEW, OLD);
payload := jsonb_build_object('id', category.id);
IF NEW IS NULL OR NEW.deleted_at IS NOT NULL THEN
payload := jsonb_set(payload, '{deleted}', 'true');
END IF;
INSERT INTO xxx.events (channel, inserted_at, payload)
VALUES ('category', NOW() AT TIME ZONE 'utc', payload);
RETURN NULL;
END;
$$;
We found no notable performance issues. We have a single LISTEN in another application. We did some stress testing and found that it performs way better than we would ever need
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.
Just out of curiosity, could you try to frame in what context this would or would not work? If you have multiple backends with multiple connections for instance? And then if we start with such a "simple" solution and we later need to scale with distributed backends, how should we do this?
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.
We tried to use LISTEN/NOTIFY for notification purposes, or rather, a queue in which the order mattered (well, kind of, we were aiming for the best scenario where a customer would not perform a specific task in a matter of milliseconds, because we could afford this expectation), in a .NET application using Npgsql.
The listeners were scattered between replicas, so we took advantage of Advisory Locks (https://www.postgresql.org/docs/current/explicit-locking.htm...) by choosing a unique key (unique to the data being sent) and before performing any task on the JSON payload, that the notification would send, we would first check the lock and continue the routine.
The NOTIFY routine was triggered after an insert in an Outbox table, so we could replay it if it failed for some reason.
Unfortunately I don't remember the exact reason we didn't use it, but I was bit sceptical for 2 reasons:
- I had a feeling that I was use this feature in a wrong way; and
but to this day I'm still unsure. Anyway, since it was a complementary system, it didn't hurt to leave it out, we had another background job that would process the outbox table regardless, but I felt it could/would give something closer to "real time" in our system.
The post seems to say that NOTIFY is generally not a good idea, then comments here say that NOTIFY can actually work, but it depends on some particular things (which are not easy to know for newcomers to Postgres), makes it a bit complicated to know what is the way to go for a new database
In my case I have an IoT setting, where my devices can change their "DesiredState", and I want to listen on this to push some message to MQTT... but then there might be also other cases where I want to listen to some messages elsewhere (eg do something when there is an alert on a device, or listen to some unrelated object, eg users, etc)
I'm not clear right now what would be the best setting to do this, the tradeoffs, etc
Imagine I have eg 100k to 10M range of devices, that sometimes these are updated in bulks and change their DesiredState 10k at a time, would NOTIFY work in that case? Should I use the WAL/Debezium/etc?
Can you try to "dumb down" in which cases we can use NOTIFY/LISTEN and in which case it's best not to? you're saying something about single-channel/multi-channel/etc but to a newcomer I'm not clear on what all these are
I have listen/notify on most changes in my database. Not sure I've experienced any performance issue though but I can't say I've been putting things through their paces. IMHO listen/notify's simplicity outweighed the perf gains by WAL.
I'm only sharing this should it be helpful:
def up do
whitelist = Enum.join(@user_columns ++ ["tick"], "', '")
execute """
CREATE OR REPLACE FUNCTION notify_phrasing() RETURNS trigger AS $$
DECLARE
notif jsonb;
col_name text;
col_value text;
uuids jsonb := '{}'::jsonb;
user_columns text[] := ARRAY['#{whitelist}'];
BEGIN
-- First, add all UUID columns
FOR col_name IN
SELECT column_name
FROM information_schema.columns
WHERE table_name = TG_TABLE_NAME AND data_type = 'uuid'
LOOP
EXECUTE format('SELECT ($1).%I::text', col_name)
INTO col_value
USING CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
IF col_value IS NOT NULL THEN
uuids := uuids || jsonb_build_object(col_name, col_value);
END IF;
END LOOP;
-- Then, add user columns if they exist in the table
FOREACH col_name IN ARRAY user_columns
LOOP
IF EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_name = TG_TABLE_NAME AND column_name = col_name
) THEN
EXECUTE format('SELECT ($1).%I::text', col_name)
INTO col_value
USING CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
IF col_value IS NOT NULL THEN
uuids := uuids || jsonb_build_object(col_name, col_value);
END IF;
END IF;
END LOOP;
notif = jsonb_build_object(
'table', TG_TABLE_NAME,
'event', TG_OP,
'uuids', uuids
);
PERFORM pg_notify('phrasing', notif::text);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
"""
# Create trigger for each table
Enum.each(@tables, fn table ->
execute """
CREATE TRIGGER notify_phrasing__#{table}
AFTER INSERT OR UPDATE OR DELETE ON #{table}
FOR EACH ROW EXECUTE FUNCTION notify_phrasing();
"""
end)
end
I do react to most (70%) of my database changes in some way shape or form, and post them to a PubSub topic with the uuids. All of my dispatching can be done off of uuids.
> 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.
"Exceptions should be exceptional" gets to the heart of the problem with the entire concept of Exceptions for non-trivial pieces of software where there's more than a single programmer maintaining the complete system.
Now the library programmer has to guess whether when you - the application programmer try to Wibble a Foozle - will have ensured all the Foozles can be Wibbled, and so not being able to Wibble the Foozle is an exceptional condition, or whether you want to just try to Wibble every Foozle and get an error return if it can't be Wibbled...
One option is for every such condition you bifurcate the API. Instead of three types with a total of eight methods, maybe there are six conditions for two of those methods, and so now it is three types with over 100 methods... ouch. Good bye documentation and product quality.
Well, this obviously depends on a given programming language/culture, but in my mind I would say in case of parsing a string to an int it is an expected case that it could fail, so I would model it as a Return type of an Int or a ParsingError, or something like that.
Meanwhile, for a function doing numerous file copies and network calls I would throw an exception, as the number of possible failure cases are almost limitless. (Like you surely not want to have an ADT that models FileSystemFullErrors and whatnot).
It so happens that in this particular example one is pure and the other is side-effecting, but I'm not convinced that's a hard rule here.
In a good effect system exceptions as effects are isomorphic to error conditions as data, so the choice comes down to what is more ergonomic for your use case, just like the choice between these three isomorphic functions should is down to ergonomics:
frob1 :: Foo -> Bar -> R
frob2 :: (Foo, Bar) -> R
frob3 :: FooBar -> R
data FooBar = FooBar { foo :: Foo, bar :: Bar }
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?
Absolutely. A lot of data security risk is gauged by who has access to what, and the sad fact is that many teams don’t use row or column level security for ergonomic reasons. Features like this would do a lot to make these features easier to reason about, understand, and verify.
// 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
Yep; we have a dedicated listener client per pool (and normally something like 1-20 pools, each with 1-100 workers); the workers themselves don’t listen. We also notify on every job or batch job insert, though we would love to rate limit this so we notify at most once per 2 milliseconds.
reply