On Wed, Jun 12, 2024 at 3:48 AM Chandy G <vgchan...@yahoo.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. >
