DEV Community: Ariel Bodan

PostgreSQL LISTEN/NOTIFY Queue Full: A 512 KiB Reproduction

Ariel Bodan — Sat, 19 Sep 2026 01:46:01 +0000

What causes PostgreSQL to report:

ERROR: too many notifications in the NOTIFY queue

I reproduced it with PostgreSQL 17, Docker, and two psql sessions.

Instead of trying to fill PostgreSQL's default multi-gigabyte queue, I started the server with:

max_notify_queue_pages=64

With 8 KiB pages, that gave the experiment a queue capacity of only 512 KiB.

The listener did this:

LISTEN demo;
BEGIN;

and remained idle in transaction.

From another connection, I generated 100 large pg_notify() calls.

The result was easy to observe:

Successful sends: 64
Failed sends:     36
Queue usage:      100%

PostgreSQL reported:

ERROR: too many notifications in the NOTIFY queue

Then I returned to the listener and executed:

COMMIT;

Pending notifications were delivered and:

SELECT pg_notification_queue_usage();

returned to 0.

The useful takeaway is that increasing max_notify_queue_pages only gives the system more space. It does not remove a transaction that is preventing notification cleanup.

The full experiment includes the Docker Compose setup, SQL commands, queue measurements, inspection of pg_notify/, and recovery verification:

https://bodanthebackend.com/articles/en/postgresql-listen-notify-queue-full/

How I Turned 350+ Programming Books Into 24 Learning Roadmaps With AI

Ariel Bodan — Fri, 18 Sep 2026 06:10:45 +0000

I had more than 350 programming books and resources sitting on my computer.

Some algorithm books were loose in the root folder. A JavaScript book somehow ended up inside Docker. Arduino material was mixed with SEO resources. I also had duplicate copies of books spread across different folders.

It was a mess.

But the bigger problem wasn't the folder structure.

I knew there were plenty of good books in there. I just had no idea what I should read first.

Backend? Data Engineering? Advanced Python? C?

Everything was mixed together.

So instead of spending an entire Saturday moving files around by hand, I decided to see how far I could get with opencode.

I started with a simple request

I asked it to scan the whole folder recursively, group the files by topic, and create a learning roadmap for each topic.

My original prompt was basically:

Review the folder recursively, organize the files by topic, and create a roadmap for each topic — C Programming, Backend Engineering, Data Engineering, Data Analytics, and so on — in a Markdown file.

I expected a cleaned-up list of books.

What I got was much more useful.

It created 24 different learning areas, each with its own roadmap.

Instead of just grouping the books into folders, it organized them into stages:

Foundations → Intermediate → Advanced

Each book was placed where it made the most sense in the learning path.

So if I want to learn C from scratch, I can see which book to start with.

If I want to focus on Data Engineering, there's a progression for that too.

If I'm preparing for technical interviews, I have a separate path.

That was the point where the project stopped being about file organization.

It became a way to answer a much better question:

What should I study next?

Then I let it reorganize the actual files

Once the roadmaps looked good, I asked it to make the real folder structure match them.

This was the part where I wanted to be more careful.

Moving hundreds of files is one thing. Deleting the wrong file is another.

So I had it:

create new folders where needed;
move books into the right topic;
find duplicate files;
compare duplicates by their actual contents, not just their filenames;
verify the total file count before and after;
update the roadmap with the new file paths.

It found four exact duplicates I didn't even know I had.

After everything was reorganized, I ended up with:

348 resources organized across 24 topics.

And, more importantly, nothing got lost.

The prompt I'd use today

The first prompt worked, but I'd make it more specific now:

Scan my programming library recursively, including subfolders and PDF/EPUB files. Classify every file by technical topic, such as C Programming, Backend Engineering, Data Engineering, Data Analytics, Frontend, DevOps, AI, and others.

For each topic, create a learning roadmap divided into stages: foundations, intermediate, and advanced. Place each resource in the stage where it's most useful.

Save everything in a ROADMAPS_BY_TOPIC.md file and include the goal of each stage. At the end, tell me what percentage of the files were successfully categorized.

For the actual reorganization, I'd also add:

Before moving anything, show me the planned file changes in a table.

Only remove exact duplicates after comparing their contents. Never assume two files are duplicates just because their names are similar.

When you're done, verify that the file count only changed because exact duplicates were removed, and update all paths in the roadmap.

That extra approval step is worth having whenever an AI agent is going to make real changes to your files.

What I'm doing next

My next step is to create a checklist for each topic with:

books I've finished;
books I still need to read;
notes;
progress through the roadmap.

That would turn the whole library into something closer to a personal learning system instead of a folder full of things I might read someday.

I wrote up the full process, including the original prompts and the improved versions, here:

https://bodanthebackend.com/articles/en/organizar-biblioteca-programacion-ia-opencode/

I'm curious if anyone else has tried using an AI agent for something similar — not just coding, but organizing the resources you use to learn.

Why Adding an Index Won't Fix Your Slow COUNT(*) in PostgreSQL

Ariel Bodan — Mon, 07 Sep 2026 03:15:00 +0000

COUNT(*) looks like a trivial operation:

SELECT COUNT(*)
FROM orders;

The query asks for a single number, but that doesn't mean PostgreSQL can produce it with a constant-time read from some internal counter.

When we need an exact count, PostgreSQL has to determine how many rows are actually part of the visible result set for that query. On large tables, that work can become a meaningful chunk of total execution time.

And the problem doesn't just go away by throwing an index at it.

The useful question isn't "do I have an index?" It's:

How many rows does PostgreSQL actually need to examine to compute this count — and can that work be reduced?

Why COUNT(*) Can Be Expensive in PostgreSQL

PostgreSQL uses MVCC — Multi-Version Concurrency Control — to manage concurrent access to data.

That's what lets multiple transactions work at the same time while each sees a consistent view of the database. But it also means row visibility depends on the snapshot the query is running under.

That's why PostgreSQL can't answer:

SELECT COUNT(*)
FROM orders;

by simply reading an exact counter stored somewhere in the table's metadata.

To return an exact result, it has to process the rows — or an index structure representing those rows — and determine which ones are part of the visible result.

On a small table, that cost is invisible.

On a table with millions of rows, the amount of work starts to matter.

Which leads to an important distinction:

returning a single row from COUNT(*) does not mean processing a single row.

How to Analyze a COUNT with EXPLAIN ANALYZE

Before reaching for an index, it's worth looking at what PostgreSQL is actually doing.

Say we have this query:

SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

We can analyze it with:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

The goal isn't to hunt for an Index Scan by default. Worth checking instead:

the scan type
estimated rows vs. actual rows processed
rows discarded by the filter
buffer activity
total execution time

PostgreSQL might well choose:

Seq Scan on orders

if it estimates that a sequential read is cheaper than using an index. That doesn't mean the planner made a bad call — it depends entirely on how many rows match the condition.

When an Index Can Actually Improve a COUNT

Say we have 10 million orders, but only 30,000 are pending:

SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

Here, the filter shrinks the working set meaningfully. An index on status could let PostgreSQL skip most of the table:

CREATE INDEX idx_orders_status
ON orders(status);

Depending on statistics, value distribution, and page state, PostgreSQL might use an index-based access path — and under the right conditions, an Index Only Scan.

But it's important not to treat that as a guarantee:

having an index does not guarantee an Index Only Scan.

The planner picks whatever it estimates as the cheapest plan.

Index Only Scan and the Visibility Map

An Index Only Scan can skip a lot of table visits because the values needed to answer the query already live in the index itself.

For a count like:

SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

an index on status holds what's needed to locate the relevant entries. But PostgreSQL still has to respect MVCC's visibility rules — and that's where the visibility map comes in.

PostgreSQL tracks which pages contain only rows that are visible to every relevant transaction. When a page is marked all-visible, an Index Only Scan can skip visiting the heap to check row visibility individually.

This is why it's wrong to think VACUUM "updates the index." The index already stays current as the table changes. What VACUUM actually helps maintain is the visibility information that lets PostgreSQL avoid certain heap visits.

You can check this directly in the plan by looking at:

Heap Fetches: 0

A high Heap Fetches count means PostgreSQL had to check table pages to confirm visibility, even while using an Index Only Scan.

The Real Problem with COUNT and Filters: Selectivity

Now flip the scenario. Say 90% of orders are completed:

SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

In that case, an index on status buys you a lot less. Why? Because even if PostgreSQL can quickly locate every entry where status = completed, those entries make up almost the entire table.

The index isn't eliminating enough work.

That's why PostgreSQL might still prefer a Seq Scan even when there's an index that appears to match the WHERE clause.

The right question was never:

Does an index exist?

It's:

How much does that index actually shrink the set of data PostgreSQL has to process?

Selectivity matters more than the mere existence of an index.

When a Partial Index Makes Sense

Partial indexes shine when you're frequently querying a small, well-defined subset of data.

Say only a small fraction of orders are pending:

SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

We could create:

CREATE INDEX idx_orders_pending
ON orders(id)
WHERE status = 'pending';

That index doesn't contain every order — only the ones matching status = 'pending'. If pending is a small subset, this index can end up considerably smaller than one covering the whole table, and for queries using exactly that predicate, PostgreSQL works with a much leaner structure.

The math changes completely if you build a partial index around a value that represents 90% of the table — you'd still be maintaining an index covering nearly every row, so the potential savings shrink accordingly.

Partial indexes pay off when they mirror a real, sufficiently selective access pattern.

COUNT with Multiple Filters

Counts get more interesting once they combine several conditions:

SELECT COUNT(*)
FROM orders
WHERE account_id = 42
  AND status = 'pending'
  AND created_at >= DATE '2026-09-01';

A single-column index may not be enough here. Worth exploring a composite index:

CREATE INDEX idx_orders_account_status_created
ON orders(account_id, status, created_at);

But don't just copy that pattern without measuring. Column order should reflect how the data is actually filtered, its distribution, and the app's real query patterns.

The right workflow is still:

query
↓
EXPLAIN ANALYZE
↓
rows processed
↓
selectivity
↓
index design
↓
new EXPLAIN ANALYZE

Not:

slow query
↓
create index
↓
hope

When an Index Won't Fix the COUNT

There's a hard limit here. If you genuinely need to count a large chunk of a huge table, PostgreSQL has to process a substantial amount of data to produce an exact result.

An index can change how that data is accessed. It can't make the rows that belong in the count disappear.

If an application is constantly running:

SELECT COUNT(*)
FROM orders;

against a very large table and expects a near-instant response, it's worth asking whether running an exact count on every single request is even the right model. That's where other strategies come in — precomputed counters, approximate counts via pg_class.reltuples, and a practical checklist for diagnosing any slow COUNT.

I go through all of that — plus the full 10-step diagnostic checklist — in the complete write-up on my site.

Curious how others handle this in production: do you maintain precomputed counters, or just accept the cost of an exact COUNT when it matters? Would love to hear how you've dealt with this in your own stack.