For years, pg_buffercache has been the DBA’s window into shared_buffers: which relations are cached, how hot they are, and which pages are dirty. Earlier releases introduced pg_buffercache_evict() for testing, and PostgreSQL 18 added pg_buffercache_evict_relation() and pg_buffercache_evict_all(). In PostgreSQL 19, the module gains the mirror image: the ability to mark buffers dirty on demand.
This post covers the three new functions, what they return, and why they exist. It also shows where they fit in the wider pg_buffercache toolkit, walks through a hands-on demo on PostgreSQL 19beta4, and ends with troubleshooting tips.
Related reading: PG19 Hacktober: pg_buffercache Under the Hood: Shared Memory Mapping Refactored covers the new pg_buffercache_os_pages view, so it is not repeated here.
What Is a Dirty Buffer?
When a backend modifies a page, it does so in a shared buffer first, not directly on disk. The buffer is flagged as dirty, meaning the in-memory copy is newer than the copy on disk. A dirty buffer is eventually written out by one of three actors:
- The checkpointer, which flushes dirty buffers at each checkpoint.
- The background writer, which trickles dirty buffers out between checkpoints.
- A backend, when it needs a free buffer and the victim it picks is dirty. It must write the page out before reusing the slot.
The number of dirty buffers, and how quickly they are flushed, influences checkpoint duration, I/O spikes, and tail latency. That is why a controlled way to create dirty state is useful for testing.
Anatomy of a Buffer: What pg_buffercache Shows You
Every row in the pg_buffercache view describes one slot of the shared buffer pool. There is exactly one row per buffer, and unused buffers show all fields as null except bufferid.
| Column | Type | What it tells you |
|---|---|---|
| bufferid | integer | Slot number, from 1 to shared_buffers |
| relfilenode | oid | Filenode of the relation stored in this slot |
| reltablespace | oid | Tablespace of that relation |
| reldatabase | oid | Database of that relation (0 for shared system catalogs) |
| relforknumber | smallint | Which fork: main data, free space map, visibility map, and so on |
| relblocknumber | bigint | Page number within the relation |
| isdirty | boolean | Whether the in-memory page is newer than the on-disk copy |
| usagecount | smallint | Clock-sweep access count, used to pick eviction victims |
| pinning_backends | integer | How many backends currently hold a pin on this buffer |
Two of these columns matter most for this post. isdirty is the flag the new functions set. pinning_backends explains why some buffers cannot be dirtied or evicted: a buffer that another backend has pinned is skipped.
Because all databases share the pool, you will also see pages from relations outside your current database. When you join to pg_class, restrict the join to reldatabase equal to the current database’s OID or zero, as the documentation recommends.
What’s New in PostgreSQL 19
The PostgreSQL 19 documentation for pg_buffercache lists three new functions:
| Function | Scope | Argument | Returns |
|---|---|---|---|
| pg_buffercache_mark_dirty() | One buffer | A buffer id (bufferid from pg_buffercache) | buffer_dirtied, buffer_already_dirty |
| pg_buffercache_mark_dirty_relation() | All unpinned buffers of a relation, across all forks | A relation identifier | buffers_dirtied, buffers_already_dirty, buffers_skipped |
| pg_buffercache_mark_dirty_all() | All unpinned buffers in the pool | None | buffers_dirtied, buffers_already_dirty, buffers_skipped |
They mirror the existing eviction functions, so the module now offers a complete toolkit: dirty a buffer, inspect it, then flush or evict it.
Rules and Caveats
- Superuser only. The read-only views are available to pg_monitor members, but the mark-dirty functions are restricted to superusers.
- Developer testing only. These functions are intended for developer testing.
- Unpinned buffers only. Buffers pinned by other backends are skipped.
- Results go stale immediately. Concurrent activity can change buffer state the moment a function returns.
- Inspection is lock-light. pg_buffercache and pg_buffercache_summary() do not take buffer manager locks, so counts under concurrent activity can be slightly inaccurate.
Where the New Functions Fit: The Full PG19 Toolkit
The mark-dirty functions are one part of a larger module. In PostgreSQL 19, pg_buffercache falls into five groups, and knowing which tool belongs to which job saves you from reaching for the heaviest one.
| Group | Objects | Purpose | Who can run it |
|---|---|---|---|
| Inspect | pg_buffercache view | One row per buffer, full detail | Superusers and pg_monitor members |
| Inspect (cheap) | pg_buffercache_summary(), pg_buffercache_usage_counts() | Aggregates: used, unused, dirty, pinned, usage count histogram | Superusers and pg_monitor members |
| Map to OS memory | pg_buffercache_os_pages view, pg_buffercache_numa view | OS page of each buffer, and optionally its NUMA node | Superusers and pg_monitor members |
| Evict | pg_buffercache_evict(), pg_buffercache_evict_relation(), pg_buffercache_evict_all() | Push unpinned buffers out of the pool | Superusers only |
| Mark dirty | pg_buffercache_mark_dirty(), pg_buffercache_mark_dirty_relation(), pg_buffercache_mark_dirty_all() | Set the dirty flag on unpinned buffers | Superusers only |
Access to the read-only functions can also be granted to other roles with GRANT, but the evict and mark-dirty functions are documented as superuser-only developer tools.
Choosing the Right Inspection Tool
The aggregate functions are significantly cheaper than the full view. A practical rule of thumb:
- Use pg_buffercache_summary() for a quick “how many buffers are dirty right now” check. It returns a single row with buffers_used, buffers_unused, buffers_dirty, buffers_pinned and usagecount_avg.
- Use pg_buffercache_usage_counts() when you want the distribution of usage counts, including how many buffers at each count are dirty or pinned.
- Use the pg_buffercache view only when you need to know which relation or block is involved. It does not give a consistent snapshot across all buffers, although the information for each individual buffer is self-consistent.
- Use pg_buffercache_os_pages when you care about how buffers line up with OS memory pages. The previous post in this series covers it in depth, so it is not repeated here.
Hands-On: Marking Buffers Dirty
This demo was run on PostgreSQL 19beta4 with pg_buffercache 1.7 and the default shared_buffers of 128 MB, which is 16,384 buffers. You need a test instance and a superuser psql session.
Step 1: Install the Extension and Create Test Data
CREATE EXTENSION IF NOT EXISTS pg_buffercache;Confirm the extension version:
postgres=# \dx
List of installed extensions
Name | Version | Default version | Schema | Description
----------------+---------+-----------------+------------+---------------------------------
pg_buffercache | 1.7 | 1.7 | public | examine the shared buffer cache
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)Create a table with 100,000 rows, then checkpoint so we start from a clean state:
CREATE TABLE demo_dirty (
id int PRIMARY KEY,
payload text
);
INSERT INTO demo_dirty
SELECT g, repeat('x', 100)
FROM generate_series(1, 100000) g;
CHECKPOINT;
CREATE TABLE
INSERT 0 100000
CHECKPOINTStep 2: Take a Baseline
SELECT * FROM pg_buffercache_summary();
buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
--------------+----------------+---------------+----------------+-------------------
2619 | 13765 | 0 | 0 | 4.639557082856052
(1 row)buffers_dirty is zero. Right after a checkpoint it should be at or near zero. Background activity can dirty a few buffers, so if you see a small number, run CHECKPOINT again.
Now look at just our table:
SELECT count(*) AS cached_buffers,
count(*) FILTER (WHERE isdirty) AS dirty_buffers
FROM pg_buffercache
WHERE relfilenode = pg_relation_filenode('demo_dirty')
AND reldatabase = (SELECT oid FROM pg_database
WHERE datname = current_database());
cached_buffers | dirty_buffers
----------------+---------------
1729 | 0
(1 row)The table has 1,729 buffers in the pool and none of them is dirty.
Step 3: Dirty a Single Buffer
Find the buffer holding block 0 of the table’s main fork and store its id in a psql variable. Run this on its own:
SELECT bufferid
FROM pg_buffercache
WHERE relfilenode = pg_relation_filenode('demo_dirty')
AND reldatabase = (SELECT oid FROM pg_database
WHERE datname = current_database())
AND relforknumber = 0
AND relblocknumber = 0 \gset
Then mark that buffer dirty:
SELECT * FROM pg_buffercache_mark_dirty(:bufferid);
buffer_dirtied | buffer_already_dirty
----------------+----------------------
t | f
(1 row)buffer_dirtied is true. Call it a second time:
SELECT * FROM pg_buffercache_mark_dirty(:bufferid);
buffer_dirtied | buffer_already_dirty
----------------+----------------------
f | t
(1 row)This time the buffer was already dirty, so buffer_already_dirty is true. Confirm in the view:
SELECT bufferid, relblocknumber, isdirty
FROM pg_buffercache
WHERE bufferid = :bufferid;
bufferid | relblocknumber | isdirty
----------+----------------+---------
613 | 0 | t
(1 row)Step 4: Dirty a Whole Relation
SELECT * FROM pg_buffercache_mark_dirty_relation('demo_dirty'::regclass);
buffers_dirtied | buffers_already_dirty | buffers_skipped
-----------------+-----------------------+-----------------
1728 | 1 | 0
(1 row)The function dirtied 1,728 buffers. One was already dirty (the buffer from Step 3), and none were skipped. Together, that is 1,729, exactly the number of cached buffers we saw for the table in Step 2. The function covers all forks of the relation, so cached free space map or visibility map buffers are included.
Step 5: Check the Dirty Buffer Count
Before running a checkpoint, record the checkpointer’s counter and the dirty buffer count:
SELECT buffers_written FROM pg_stat_checkpointer;
SELECT * FROM pg_buffercache_summary();
buffers_written
-----------------
47935
(1 row)
buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
--------------+----------------+---------------+----------------+--------------------
2631 | 13753 | 1730 | 0 | 4.6484226529836565
(1 row)There are 1,730 dirty buffers: the 1,729 from our table plus one more from other activity. Run CHECKPOINT and query both again, and buffers_dirty drops back to about zero while buffers_written rises. The background writer can contribute a few buffers, so treat the difference as approximate. If log_checkpoints is on, the server log also shows a checkpoint complete line with the number of buffers written.
Step 6: Go Big with pg_buffercache_mark_dirty_all()
Turn on timing, then mark every buffer in the pool dirty and checkpoint. Put \timing on on its own line:
\timing on
SELECT * FROM pg_buffercache_summary();
SELECT * FROM pg_buffercache_mark_dirty_all();
SELECT * FROM pg_buffercache_summary();
CHECKPOINT;
buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
--------------+----------------+---------------+----------------+--------------------
2631 | 13753 | 1730 | 0 | 4.6484226529836565
(1 row)
buffers_dirtied | buffers_already_dirty | buffers_skipped
-----------------+-----------------------+-----------------
901 | 1730 | 0
(1 row)
buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
--------------+----------------+---------------+----------------+--------------------
2631 | 13753 | 2631 | 0 | 4.6484226529836565
(1 row)
CHECKPOINT
Time: 74.063 mspg_buffercache_mark_dirty_all() dirtied 901 more buffers on top of the 1,730 that were already dirty. Now buffers_dirty (2,631) equals buffers_used: every valid, unpinned buffer in the pool is dirty, whichever relation or database it belongs to. The checkpoint that had to write them all took 74 ms on this instance.
Step 7: Combine with Eviction
The mark-dirty and evict functions work well together. Dirty a buffer, then evict it:
SELECT * FROM pg_buffercache_mark_dirty(:bufferid);
SELECT * FROM pg_buffercache_evict(:bufferid);
buffer_dirtied | buffer_already_dirty
----------------+----------------------
t | f
buffer_evicted | buffer_flushed
----------------+----------------
t | tbuffer_evicted is true when the buffer was removed from the pool. It is false if the buffer was not valid, was pinned, or became dirty again after the attempt to write it out. buffer_flushed is true if the buffer was written to disk, although not necessarily by your own session. Here both are true, which shows that a dirty buffer has to be written out before its slot can be reused.
If concurrent activity reuses the buffer id between the two calls, the result can differ. That is why the documentation describes these results as immediately out of date.
Step 8: Clean Up
DROP TABLE demo_dirty;Reading the Results: What Every Return Value Means
When a buffer cannot be dirtied, the behavior is to report that in the result, so reading the result correctly matters.
| Function | Result | Meaning |
|---|---|---|
| pg_buffercache_mark_dirty(bufferid) | buffer_dirtied = true | The buffer was valid, unpinned and clean, and is now dirty |
| pg_buffercache_mark_dirty(bufferid) | buffer_already_dirty = true | Nothing changed because the buffer was already dirty |
| pg_buffercache_mark_dirty(bufferid) | both false | The buffer was not valid, or it was pinned and could not be dirtied |
| pg_buffercache_mark_dirty_relation(rel) | three counts | buffers_dirtied, buffers_already_dirty, and buffers_skipped (pinned or invalid) |
| pg_buffercache_mark_dirty_all() | three counts | The same three counts, across the whole pool |
A few edge cases are worth knowing before you build a test on top of these functions:
- Counts cover only cached buffers. A relation that is not fully in shared_buffers, or one that was never read, will only partly appear.
- Pinned buffers are skipped, not waited for. On a busy system, a few skipped buffers are normal.
- The answer is stale the moment you read it. Do not use a returned count as a guarantee about what the pool looks like a second later.
- Unused slots are never dirtied. Right after a restart, pg_buffercache_mark_dirty_all() has little to do because most of the pool is empty.
Where This Is Useful
- Checkpoint and background writer testing: build a repeatable dirty state, then measure checkpoint duration and write behavior.
- Extension and tooling development: check how monitoring queries, dashboards or custom extensions handle dirty buffers without staging a write workload.
- Regression and TAP tests: a deterministic dirty-buffer setup makes buffer manager tests much simpler.
- Teaching and demos: the full life cycle of a buffer (clean, dirty, flushed, evicted) can be shown live in a handful of queries.
Pitfalls and Troubleshooting
Most surprises with these functions come from the same few causes. This table maps what you see to what is probably happening.
| What you see | Likely cause | What to do |
|---|---|---|
| A permission error when calling a mark-dirty function | The functions are superuser-only | Connect as a superuser on a test instance |
| function pg_buffercache_mark_dirty does not exist | The extension is missing, or an older version is installed | Run CREATE EXTENSION pg_buffercache, or ALTER EXTENSION pg_buffercache UPDATE after upgrading to PostgreSQL 19 |
| buffer_dirtied and buffer_already_dirty are both false | The buffer was invalid or pinned | Pick another buffer, or retry after concurrent activity settles |
| Relation call dirties fewer buffers than the table has pages | Only cached pages count, and some may be pinned | Read the table first, for example with a sequential scan, then retry |
| Dirty count drops before you run CHECKPOINT | The background writer or a checkpoint wrote buffers in between | Run the test on an otherwise idle instance, or lower bgwriter_lru_maxpages on a test system |
| pg_buffercache_mark_dirty_all() runs but the pool looks mostly empty | Most slots are unused right after a restart | Warm the cache first by reading the tables you care about |
Good Habits
- Test instances only. Keep these functions away from production, and never grant superuser access just to run them.
- Start small. Try the single-buffer and relation variants before pg_buffercache_mark_dirty_all().
- Checkpoint first. Begin each test from a clean pool so your numbers mean something.
- Record the environment. Note shared_buffers, storage type and PostgreSQL build when you publish results, because checkpoint timings do not transfer between systems.
- Prefer the cheap views. Use pg_buffercache_summary() and pg_buffercache_usage_counts() in loops and scripts, and reserve the full view for targeted questions.
Conclusion
PostgreSQL 19 rounds out the pg_buffercache testing toolkit. With pg_buffercache_mark_dirty(), pg_buffercache_mark_dirty_relation() and pg_buffercache_mark_dirty_all(), alongside the evict functions and the lightweight pg_buffercache_summary(), you can set up clean, dirty, flushed or evicted states on demand and see how PostgreSQL responds.
The key takeaway: you no longer need a real write workload to study how PostgreSQL handles dirty buffers.