PostgreSQL 9.3中pg_stat_database查询缓慢，pg_database_size拖慢性能求优化

Got it, let's break down the root causes first, then jump into actionable fixes that fit your environment.

Root Causes of Slow pg_database_size() Calls #

• PostgreSQL 9.3's inherent limitations: This version is pretty old (released in 2013, long out of official support). Back then, pg_database_size() worked by scanning every single object in the database—tables, indexes, TOAST tables, and more—and summing their sizes from scratch every time you called it. No caching, no shortcuts. For large databases, this translates to massive disk I/O and CPU overhead.
• Storage & filesystem bottlenecks: If you're using a mechanical HDD instead of an SSD, traversing all the individual files that make up PostgreSQL objects will be way slower (HDDs are terrible at random I/O). Even with SSDs, if the filesystem cache doesn't hold your database's metadata, PostgreSQL has to pull that data from disk every time, adding unnecessary latency.
• Concurrency conflicts: If your database is under heavy write load when you run this query, PostgreSQL might have to wait for locks on file metadata or deal with inconsistent state while calculating sizes, which further slows things down.

Optimization Strategies #

Let's go through the most effective fixes, ordered by long-term impact:

1. Upgrade PostgreSQL (Most Impactful Long-Term Fix) #

PostgreSQL 9.3 is ancient—later versions (starting with 9.6, and even better with 12+) introduced major optimizations to how database sizes are calculated. Newer versions maintain cached statistics for object sizes, so pg_database_size() doesn't have to scan everything every time. Yes, upgrading takes planning (always backup first, test in staging), but it's the only way to eliminate the core limitation here.

2. Cache the Results Instead of Calling It Every Time #

Since you're running this query periodically, you don't need real-time size data. Create a cache table to store the results, and update it less frequently (like every 5-10 minutes) instead of calculating sizes on every run.

Here's a quick implementation:

-- Create a cache table to store database sizes
CREATE TABLE IF NOT EXISTS db_size_cache (
    db_name name PRIMARY KEY,
    size_bytes bigint NOT NULL,
    last_updated timestamptz DEFAULT CURRENT_TIMESTAMP
);

-- Function to refresh the cache
CREATE OR REPLACE FUNCTION refresh_db_size_cache() RETURNS void AS $$
BEGIN
    -- Upsert: update existing entries, insert new ones
    INSERT INTO db_size_cache (db_name, size_bytes)
    SELECT datname, pg_database_size(datname)
    FROM pg_database
    ON CONFLICT (db_name) DO UPDATE
    SET size_bytes = EXCLUDED.size_bytes, last_updated = CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

Then set up a cron job on your CentOS server to run this function on a schedule:

# Example: Refresh cache every 10 minutes (run as postgres user)
*/10 * * * * psql -U postgres -d your_monitoring_db -c "SELECT refresh_db_size_cache();"

Now your stats query can pull data directly from db_size_cache instead of calling pg_database_size() every time—this will cut your query time drastically.

3. Use Approximate Sizes from pg_stat_database #

If you can tolerate a small amount of inaccuracy (the size is updated periodically by PostgreSQL's autovacuum), use the size column in pg_stat_database. This value is maintained by PostgreSQL's background statistics collector, so accessing it is almost instant.

Example query:

SELECT datname, pg_size_pretty(size) AS db_size
FROM pg_stat_database;

Note: In PostgreSQL 9.3, this size field is an approximation, but it's usually close enough for most monitoring purposes.

4. Optimize Storage & Filesystem #

• Switch to SSDs: If you're on HDDs, upgrading to SSDs will drastically reduce the time it takes to scan database object metadata.
• Tune filesystem caching: Adjust CentOS's kernel parameters to give more memory to filesystem caching (e.g., increase vm.dirty_ratio and vm.dirty_background_ratio if you have enough RAM). This keeps more database metadata in memory, reducing disk I/O for size calculations.

5. Schedule Queries During Low Load #

If you can't avoid calling pg_database_size() entirely, run your stats query during off-peak hours (like midnight) when the database isn't busy. This minimizes the impact of the slow call on your application.

内容的提问来源于stack exchange，提问作者Ivan Voras