| From: | Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Proposal: cross-database unlogged relation size reporting |
| Date: | 2026-11-17 10:06:05 |
| Message-ID: | CAE9k0PmRiTOOi8FmBDVsUjH+TRgtxBqZOv+wU3TVgmsY_saRng@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | shveta malik | 2026-11-17 10:09:32 | Re: Initial table sync copies the whole table when it is no longer published |
| Previous Message | shveta malik | 2026-11-17 10:03:25 | Re: Initial table sync copies the whole table when it is no longer published |