Hi All, PostgreSQL provides pg_database_size() to measure a database’s physical size across tablespaces without connecting to that database. However, it does not expose how much of that storage belongs to unlogged relations.
This breakdown is useful for storage monitoring and understanding the proportion of database storage that is not crash-durable. Within the connected database, this information can be obtained by aggregating pg_total_relation_size() over unlogged relations in pg_class. Collecting it across an instance, however, requires connecting to each database. On instances hosting thousands of databases, this adds connection management and catalog-query overhead. I would like to discuss adding a function, provisionally: - pg_database_unlogged_size(oid) - pg_database_unlogged_size(name) Proposed semantics: Return the physical size in bytes of unlogged relations belonging to the specified database, across all tablespaces, including: - Main, initialization, free-space-map, and visibility-map forks were present. - All file segments. - Unlogged indexes, TOAST relations, and sequences. Temporary relations and unrelated database metadata files would be excluded. The result would be a component of and not additional to pg_database_size(). Possible implementation: Traverse the database’s directories across tablespaces, identify unlogged relfilenumbers through initialization forks, and sum the associated relation files. PostgreSQL already uses initialization forks to identify unlogged relations during recovery. Points requiring discussion: - Accounting guarantees during concurrent creation, deletion, and persistence changes. - Directory-scanning cost and memory requirements. - Whether a separate function or a combined total/unlogged size interface would be preferable. The goal is to avoid per-database connections, not to claim constant-time or transactionally consistent accounting. Benchmarks would compare this approach against per-database catalog aggregation and existing database-size collection. Please let me know if this little new addition is appropriate for core PostgreSQL? If yes, I can prepare a patch and share it here. -- With Regards, Ashutosh Sharma.
