如何移除PostgreSQL内存中的缓存表？除pg_prewarm外无需重启数据库的替代方法

Great question—since you already know pg_prewarm for loading data into cache, let’s break down practical ways to remove tables from PostgreSQL’s memory cache (both shared_buffers and OS-level cache) without rebooting the instance.

1. Use pg_prewarm in UNBUFFER Mode (Official, Targeted) #

You might not have realized it, but pg_prewarm has an unbuffer mode specifically designed to evict a table from PostgreSQL’s shared_buffers. This is the cleanest, most targeted method for individual tables:

-- Evict a single table (include schema if needed)
SELECT pg_prewarm('your_schema.your_table', mode => 'unbuffer');

-- Evict all tables in a specific schema
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT schemaname || '.' || tablename AS relname FROM pg_tables WHERE schemaname = 'your_schema'
    LOOP
        EXECUTE 'SELECT pg_prewarm(''' || rec.relname || ''', mode => ''unbuffer'')';
    END LOOP;
END $$;

• Permissions: Requires EXECUTE on pg_prewarm (usually granted to superusers or via explicit role grants).
• Pros: Targeted, no system-wide impact, built-in PostgreSQL tool.

2. Drop Specific Buffers with pg_buffercache (Granular Control) #

For fine-grained control over which blocks get evicted, use the pg_buffercache extension to identify cached blocks for your table, then drop them one by one. This works well if you only want to clear part of a table’s cache:

First, enable the extension (superuser required):

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

Then, evict all cached blocks for your table:

SELECT pg_drop_buffer(relfilenode, blocknum)
FROM pg_buffercache
WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE relname = 'your_table' AND schemaname = 'your_schema');

• Notes: This only affects shared_buffers (not OS-level cache) and can be slow for large tables since it processes each block individually.

3. Temporarily Adjust shared_buffers (Brute-Force, System-Wide) #

If you need to clear most of shared_buffers (not just a single table), you can temporarily shrink the shared_buffers setting, reload the config, then restore it. This forces PostgreSQL to release excess memory, flushing cached data:

1. Check your current shared_buffers value:

   SHOW shared_buffers;
   

2. Edit your postgresql.conf file to set shared_buffers to a smaller value (e.g., 64MB if it was originally 4GB).
3. Reload the configuration without restarting:

   SELECT pg_reload_conf();
   

4. Restore the original shared_buffers value and reload again.

• Warning: This will flush most of shared_buffers, which can temporarily hurt query performance as PostgreSQL re-caches frequently used data. Only use this during low-traffic periods.

4. Clear OS-Level File Cache (System-Wide, Linux Only) #

PostgreSQL also relies on the operating system’s page cache for data not stored in shared_buffers. To clear this (along with all other OS cache), run these commands as root:

sync; echo 3 > /proc/sys/vm/drop_caches

• Caveats: This clears all OS cache, not just PostgreSQL’s. Avoid this in production unless you’re sure it won’t impact other applications running on the server.

Key Considerations #

• Always test these methods in a staging environment first to avoid unexpected performance hits.
• Superuser privileges are required for most of these operations.
• Targeted methods (like pg_prewarm unbuffer) are always preferred over system-wide ones for production systems.

内容的提问来源于stack exchange，提问作者Anil kumar