The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →On a disposable PostgreSQL instance, set max_notify_queue_pages to 64, restart the server, and hold a transaction open in a session that has issued LISTEN. Then commit distinct NOTIFY payloads from another session until a producer transaction fails at commit. With 8 KB database pages, 64 pages configure a 512 KiB queue; that is capacity, not a guaranteed event count.
What the reproduction demonstrates
PostgreSQL retains notification events until listening sessions have processed them. If the notification queue fills, transactions that call NOTIFY fail when they try to commit—not necessarily when the NOTIFY statement is issued. A listener left in a long-running transaction can prevent queue cleanup. PostgreSQL documents that at half-full, log warnings point to the session preventing cleanup. See the PostgreSQL 18 NOTIFY documentation.
As an Amazon Associate I earn from qualifying purchases.
This is a controlled failure demonstration, not a production tuning recommendation. Use an isolated local instance: deliberately limiting the queue can cause notification-producing transactions to fail.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteConfigure a small queue
-
In the disposable server’s startup configuration, add
max_notify_queue_pages = 64. -
Restart PostgreSQL; this parameter can only be set at server start.
-
Connect to the test database and verify the active value with
SHOW max_notify_queue_pages;.
The PostgreSQL 18 resource-consumption configuration documentation gives a default of 1,048,576 pages and describes that as 8 GB when pages are 8 KB. Under the same 8 KB page-size assumption, 64 pages equal 512 KiB (64 × 8 KiB). Recalculate if the installation uses a different database block size.
Hold cleanup back with a listener transaction
Open two sessions to the same database. In session A, register the listener and leave its transaction open:
LISTEN queue_repro;
BEGIN;
-- Leave this transaction open while the producer runs.For this reproduction, run LISTEN before opening the long transaction. Keep session A connected and do not commit or roll it back while producing events; the open transaction is what prevents cleanup from advancing.
Produce distinct notifications and catch the commit error
In session B, send notifications on the subscribed channel in separate committed transactions, incrementing the payload each time. For example:
SELECT pg_notify('queue_repro', 'event-000001');Repeat with payloads such as event-000002, event-000003, and so on. Ensure the client commits each statement as its own transaction and captures errors reported at commit. A client-side loop or script is useful, but the loop should stop and record the first failed transaction rather than treating the pg_notify call alone as proof of success.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use a distinct payload for every transaction. PostgreSQL folds repeated notifications with the same channel and identical payload within a single transaction into one event. Distinct events committed separately make the queue pressure easier to observe and preserve transaction commit ordering.
Monitor queue usage and identify the blocker
From a third session, sample queue occupancy while the producer runs:
SELECT pg_notification_queue_usage();The function reports the fraction of the queue occupied by pending notifications. Record the sampling time if comparing readings. PostgreSQL documents warnings in the server log at half-full, including the session that prevents cleanup; log access and formatting depend on the server’s logging setup.
The queue’s configured 512 KiB capacity does not imply a fixed number of notifications. Entry sizes and queue bookkeeping affect how many events fit, so the failure point is not a dependable event-count benchmark.
Recommended Free Tools
End the blocking transaction and recover
After capturing the failure, or once you have finished the demonstration, release session A’s open transaction:
Best Value
ROLLBACK;Ending the long-running listener transaction allows cleanup to proceed. Confirm that queue usage can fall, then restore the instance’s normal configuration if you intend to keep using it.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Keep payload size separate from queue capacity
The notification payload has its own limit: in the default configuration it must be shorter than 8,000 bytes. That byte limit is distinct from the total queue capacity. For large or binary data, PostgreSQL recommends storing the content in a table and sending a key or identifier in the notification instead. See the NOTIFY command reference.
What LISTEN/NOTIFY is—and is not—suited to
LISTEN/NOTIFY is useful for signaling that database state changed. It should not be treated as a durable general-purpose message broker when consumers may stay stalled long enough to block cleanup. Before relying on it, consider whether every event must be retained, how long listeners may hold transactions open, expected payload size, how consumers recover after being offline, and whether they can reread authoritative state from a table.
For client applications using libpq, notifications are retrieved through the connection’s asynchronous notification handling; see PostgreSQL’s libpq asynchronous notification documentation. Implementation details such as the central queue and listener positions are described in PostgreSQL’s async.c source reference; development-source details can change over time.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
