Add pattern matching support for LISTEN/NOTIFY channels
Hi hackers,
I'd like to propose for PG 20 adding glob-style pattern matching support
for LISTEN channel names, allowing clients to subscribe to multiple
channels with a single LISTEN command using wildcards.
Currently, if an application wants to receive notifications from multiple
related channels (e.g., user_1, user_2, ..., user_N), it must issue a
separate LISTEN command for each channel. This becomes cumbersome for
applications that need to monitor a dynamic or large set of channels with
a common naming convention.
With pattern support, a client could simply do LISTEN "user_*" and receive
notifications from any channel matching that pattern such as user_login,
user_logout, user_123, and so on.
The attached patch adds support for glob-style wildcards in channel
names. The * matches zero or more characters, the ? matches exactly one
character, and \ escapes the next character to match literal * or ?.
For example, LISTEN "events_*" matches events_created, events_deleted, etc.
LISTEN "sensor_?" matches sensor_1 or sensor_A but not sensor_12.
LISTEN "app_*_status" matches app_web_status, app_worker_status, and similar.
LISTEN "literal\*star" matches the literal channel name "literal*star".
This is a proof-of-concept implementation using the simplest approach I could
think of to validate the behavior and gather feedback before investing in
optimizations.
Pattern channels are stored in a backend-local list rather than the
global shared hash table, since they cannot be looked up by exact
channel name. A new hasPatterns flag in QueueBackendStatus tracks
whether a backend is listening on any patterns. When signaling backends,
those with hasPatterns=true are always woken for notifications in their
database, so they can check if any notifications match their patterns
locally. The MatchPattern() function implements a backtracking algorithm
to handle the * wildcard matching.
Performance Considerations:
The current implementation has O(PxM) complexity for pattern matching,
where P is the number of patterns and M is the pattern matching cost.
For each notification, backends with patterns must check all their
patterns against the channel name.
I ran some benchmarks comparing exact channel matching vs pattern matching
(Python script created with Claude help attached). Results on my machine:
Test 1: Exact Channel vs Pattern Channel
----------------------------------------------------------------------
Benchmark Notif/sec
Exact channel (LISTEN "user_123") 13176 (baseline)
Pattern channel (LISTEN "user_*") 13153 (-0.2%)
Test 2: Pattern Count Scaling
----------------------------------------------------------------------
Benchmark Notif/sec
1 pattern(s) 13289 (baseline)
5 pattern(s) 13279 (-0.1%)
10 pattern(s) 13268 (-0.2%)
25 pattern(s) 13301 (+0.1%)
50 pattern(s) 13287 (-0.0%)
100 pattern(s) 13211 (-0.6%)
Test 3: Pattern Complexity
----------------------------------------------------------------------
Benchmark Notif/sec
Prefix pattern (user_*) 13446 (baseline)
Single char pattern (user_?) 13179 (-2.0%)
Infix pattern (prefix_*_suffix) 13469 (+0.2%)
Multi-wildcard (a_*_b_*_c) 13200 (-1.8%)
Test 4: Non-Matching Pattern Overhead
----------------------------------------------------------------------
Benchmark Notif/sec
Exact only (baseline) 13141 (baseline)
Exact + 10 non-matching patterns 13098 (-0.3%)
Exact + 50 non-matching patterns 13130 (-0.1%)
Exact + 100 non-matching patterns 12742 (-3.0%)
The benchmark spawns a listener connection and a notifier connection,
then measures how many notifications per second can be delivered. Each
measurement runs for 15 seconds and is repeated 3 times.
The results don't show a huge degradation but perhaps more comprehensive
benchmarks with higher concurrency and more realistic workloads would be
valuable to better understand the performance behavior.
Future Improvements:
If this idea seems reasonable, the pattern match can be optimized using
a more sophisticated data structure or improve the current patch idea.
One option is to follow the current architecture, keeping patterns
separate from exact-match channels, but finding optimizations to make
pattern matching faster. I'm wondering if we could have a sorted list
and try to do some kind of binary search with patterns, but I dind't
explore this idea further.
Another option is a Trie (prefix tree) [1]https://en.wikipedia.org/wiki/Trie. You walk the trie with the
channel name and any node marked as a wildcard indicates a match. For
mid-pattern wildcards like "prefix_*_suffix", when you hit the * node
you search for the remaining suffix in the input. A Trie could
potentially unify exact-match channels and patterns in the same
structure, where exact channels are simply terminal nodes without the
wildcard flag. But memory overhead can be significant for sparse
patterns and also I'm not sure how this would fit using shared memory,
so perhaps a trie for each backend listening on patterns alongside with
the global shared memory for non-pattern channels will be better.
Feedback welcome!
[1]: https://en.wikipedia.org/wiki/Trie
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
Have you read previous threads about $subject? I found a few:
/messages/by-id/flat/CAMpj9JbqhgQ5HjydoP0fovewQdOcu2c4RF5KKkH6J6ZNUjb2Rg@mail.gmail.com
/messages/by-id/flat/CAN_hQmuysJpMzWcyhQwYtHpao8XXMpc48A8F=n-0e6x_z2P_Fw@mail.gmail.com
/messages/by-id/flat/A14CC639-E89D-4367-894D-883DBEC503B1@treysoft.com
/messages/by-id/flat/8d39e9fc-5ab9-40e7-8f94-6dcfe2a57370@yeah.net
It doesn't look like any of these are active, but presumably the discussion
is still relevant.
--
nathan
On Mon Jun 8, 2026 at 6:51 PM -03, Nathan Bossart wrote:
Have you read previous threads about $subject? I found a few:
/messages/by-id/flat/CAMpj9JbqhgQ5HjydoP0fovewQdOcu2c4RF5KKkH6J6ZNUjb2Rg@mail.gmail.com
/messages/by-id/flat/CAN_hQmuysJpMzWcyhQwYtHpao8XXMpc48A8F=n-0e6x_z2P_Fw@mail.gmail.com
/messages/by-id/flat/A14CC639-E89D-4367-894D-883DBEC503B1@treysoft.com
/messages/by-id/flat/8d39e9fc-5ab9-40e7-8f94-6dcfe2a57370@yeah.netIt doesn't look like any of these are active, but presumably the discussion
is still relevant.
Oh, I didn't know about these, I should searched for. Thanks for the
links, I'll read it.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
"Matheus Alcantara" <matheusssilv97@gmail.com> writes:
I'd like to propose for PG 20 adding glob-style pattern matching support
for LISTEN channel names, allowing clients to subscribe to multiple
channels with a single LISTEN command using wildcards.
I'm fairly skeptical that this is worth supporting at the server-code
level. You can already accomplish equivalent things by listening on
a single shared channel and pattern-matching against identifiers
provided in the message payloads. Yes, doing that in C would reduce
the cost of skipping uninteresting messages, but is that enough of a
gain to justify adding this to our support workload?
The proposed implementation doesn't seem that attractive anyway, since
it gives up all the benefit of the targeted wakeups introduced in v19.
If you consider a workload with some high-volume channels that are not
interesting to the wild-card-using listener, it's not hard to believe
that this is a net loss compared to the already-usable way.
(Hmm, I wonder if the feature ought to look like "match a pattern
to the message body" rather than matching to the channel name,
ie push the already-usable filtering design down to the C level.)
regards, tom lane
On Mon Jun 8, 2026 at 7:16 PM -03, Tom Lane wrote:
"Matheus Alcantara" <matheusssilv97@gmail.com> writes:
I'd like to propose for PG 20 adding glob-style pattern matching support
for LISTEN channel names, allowing clients to subscribe to multiple
channels with a single LISTEN command using wildcards.I'm fairly skeptical that this is worth supporting at the server-code
level. You can already accomplish equivalent things by listening on
a single shared channel and pattern-matching against identifiers
provided in the message payloads. Yes, doing that in C would reduce
the cost of skipping uninteresting messages, but is that enough of a
gain to justify adding this to our support workload?The proposed implementation doesn't seem that attractive anyway, since
it gives up all the benefit of the targeted wakeups introduced in v19.
If you consider a workload with some high-volume channels that are not
interesting to the wild-card-using listener, it's not hard to believe
that this is a net loss compared to the already-usable way.
Yeah, I agree with this. I'm currently looking for other possibilities.
(Hmm, I wonder if the feature ought to look like "match a pattern
to the message body" rather than matching to the channel name,
ie push the already-usable filtering design down to the C level.)
It seems like an interesting direction, I'm wondering if something like
this would make sense?
LISTEN events WHERE payload LIKE 'user_%'; -- Pattern match
Or
LISTEN events WHERE payload = 'something'; -- Exclusive match
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
On Mon Jun 8, 2026 at 7:16 PM -03, Tom Lane wrote:
"Matheus Alcantara" <matheusssilv97@gmail.com> writes:
I'd like to propose for PG 20 adding glob-style pattern matching support
for LISTEN channel names, allowing clients to subscribe to multiple
channels with a single LISTEN command using wildcards.I'm fairly skeptical that this is worth supporting at the server-code
level. You can already accomplish equivalent things by listening on
a single shared channel and pattern-matching against identifiers
provided in the message payloads. Yes, doing that in C would reduce
the cost of skipping uninteresting messages, but is that enough of a
gain to justify adding this to our support workload?
Fair point. I think that the main appeal is reducing the need to
maintain dynamic LISTEN lists. For example, a multi-tenant app using
different schemas may want to monitor all tenants and could LISTEN
"tenant_*" instead of issuing separate LISTEN commands as tenants are
created and deleted. Similarly, an app using hierarchical channel names
like orders_created, orders_updated, orders_shipped could have a
reporting service LISTEN "orders_*" rather than explicitly listing each
event type. These are some use cases that I've already miss in the past.
That said, I'm also attracted to the idea of filtering on payload
content, which could achieve the same goal. I'd be interested in
thoughts on whether payload filtering would be a better direction.
The proposed implementation doesn't seem that attractive anyway, since
it gives up all the benefit of the targeted wakeups introduced in v19.
If you consider a workload with some high-volume channels that are not
interesting to the wild-card-using listener, it's not hard to believe
that this is a net loss compared to the already-usable way.
The new attached patch aims to preserves targeted wakeups. Patterns are
stored in two shared memory structures: the globalChannelTable (keyed by
the pattern string itself, storing the listener list just like exact
channels) and a globalPatterns list (an enumerable array of all
registered patterns). When SignalBackends() processes a notification, it
first does an exact match lookup, then iterates globalPatterns testing
each pattern against the notification channel. For matching patterns, it
looks up listeners in globalChannelTable using the pattern as key and
wakes only those backends. A backend listening on "orders_*" won't be
woken for notifications to "user_123".
Of course we may have performance issues if the listen of patterns is
too big, but we may try to apply some optimizations and may try to use
another data structure instead of a list, but I think that the idea
would remain the same.
I've added comments throughout the code explaining the architecture,
particularly in the file header, around GlobalPatternList,
PrepareTableEntriesForListen, and SignalBackends. There are still some
XXX comments marking areas that need more thought, but I believe the
overall architecture remains the same. I'm mainly looking for feedback
on whether this approach makes sense before polishing the remaining
details.
FWIW I'm also studying the previous proposals, still thinking about the
issues already discussed on these.
Thanks.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com