Re: postgres table statistics

On Wed, Jun 12, 2024 at 3:48 AM Chandy G <vgchandru(at)yahoo(dot)com> wrote:

> Hi,
> We have postgres 13.9 running with tables thats got billions of records
> of varying sizes. Eventhough pg jdbc driver provides a way to set fetch
> size to tune the driver to achieve better throughput, the JVM fails at the
> driver level when records of large size (say 200mb each) flows through.
> this forces to reduce the fetch size (if were to operate at a fixed Xmx
> setting of client jvm).
>
> It get a bit trickier when 100s of such tables exists with varying records
> sizes. trying to see if the fetch size can be set dynamically based on the
> row count and the record size distribution for a table. Unfortunately,
> trying to get this data by a query run against each table (for row size:
> max(length(t::text))) seem to be quite time consuming too.
>

Maybe create your own table with three columns:
table_name (PK; taken from pg_class.relname)
average_rec_size (taken from sum(pg_stat.avg_width))
max_rec_size (calculated yourself)

Periodically refresh it. (How periodic depends on how often the average
and max change substantively.)

Does postgres maintain metadata about tables for the following.
> 1. row count
>

https://www.postgresql.org/docs/13/catalog-pg-class.html

pg_class.reltuples. This is an estimate, so make sure your tables are
regularly analyzed.

> 2. max row size.
>

https://www.postgresql.org/docs/13/view-pg-stats.html

pg_stats.avg_width

> or is there some other pg metadata that can help get this data quicker.
>
> TIA.
>