public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: postgres table statistics
Date: Wed, 12 Jun 2024 04:27:56 -0400
Message-ID: <CANzqJaAcCDZX3L1O9Dj3ETZ4JZRyX6S1JJ_EMp8NaDgCovkbbw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Wed, Jun 12, 2024 at 3:48 AM Chandy G <[email protected]> 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.
>

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: postgres table statistics
  In-Reply-To: <CANzqJaAcCDZX3L1O9Dj3ETZ4JZRyX6S1JJ_EMp8NaDgCovkbbw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox