Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHJL2-005t93-QO for pgsql-general@arkaria.postgresql.org; Wed, 12 Jun 2024 08:28:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sHJL0-00Eld8-II for pgsql-general@arkaria.postgresql.org; Wed, 12 Jun 2024 08:28:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHJL0-00Eld0-2n for pgsql-general@lists.postgresql.org; Wed, 12 Jun 2024 08:28:11 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHJKy-000vQP-2T for pgsql-general@postgresql.org; Wed, 12 Jun 2024 08:28:09 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-254764f5472so2513717fac.1 for ; Wed, 12 Jun 2024 01:28:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718180887; x=1718785687; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=NVYK1+3fT0XnQS0kTZFEZeFpURky6Tngt3lLMfw0ImY=; b=dZBZpHMJLugmYXfd3UDu7Gjes/WmN2JN+SmfZVV50J/Lswt6er6BpB9eZU71gNXCFc Z+nIXEWbQ/UffceauMPmqbfrghgKGL/aXo9K8bcUWDpt6z+eOn+LK45V2MaDtU3ivPBz m4pISLEAM7TmBXHPvs7WVXqpNrY18uxGLO2GgBn25emzEduJ4DnbYbG9RMGQJCCGRk80 E/DjQ16+9izDLCkEc+zd2G6iT2ylTdxuD0Drza8d/LFXEMGS6Wmey17lMFbLGHBnnbAg 9HhaCULhLB2ZgBX089VVaPzABCjy7iahiwkzLmiBugo9FGVNBcqealGygpYUcfWs2TaY EFLQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718180887; x=1718785687; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=NVYK1+3fT0XnQS0kTZFEZeFpURky6Tngt3lLMfw0ImY=; b=XuMjLpINXv2dmJvJoiF5mcCI9X9VhhzgkHPcMaS/uixpLJCQX0Yuy+kyUaATBOR91N lWwTRilDuAFZ05i3TjgpAy0+y/SyqIcZiSaI81EoV5rbt1cnKsjc0b3L6wSiMeF1xB6F DttAu7GhrkVW3mJjkEuUFVFPMF4WBP+dU0J0IuvJQgaxChmx+mmsPsjh4vIibXMX8ZsX HhG9W/fkJKOuY48RogQ4BHjlUE82A+XYW/3UdkHRKY8cH2Pm7vSKqNhqmII9u/gUNfdw m+JtMOuvCBv0cNoM5QsLkTzsCKc2Cf3qmRDzaqgxvmTIR3+lLB9wAslMjQyyrTU1wqCD Zc5Q== X-Gm-Message-State: AOJu0YyhpiqBVS4tzr5Qh1OgNbFR2a6es15fXTmJ8zO+T1nOgapJDkB0 LFjHr+/utYr6Dn1uYG2O3Q0cmy8jfPYhnRMM3UanrLar0DmSaHopCi4+nV9klObq0zKbixA93e6 PlWlA0cfVgmFWfrXYh9Qohu5Wb7UEbax3 X-Google-Smtp-Source: AGHT+IHvgPvZRsHT5LilZJWAm2Mb8HWIAZk9QOITYops9OPiUfLduRyTz87JjQQpFwg8qetiPqlxg4uGC8hsKGGoj64= X-Received: by 2002:a05:6870:d1d2:b0:254:cfd4:7758 with SMTP id 586e51a60fabf-25514c93a39mr1158612fac.35.1718180887050; Wed, 12 Jun 2024 01:28:07 -0700 (PDT) MIME-Version: 1.0 References: <1204595253.3110982.1718178463166.ref@mail.yahoo.com> <1204595253.3110982.1718178463166@mail.yahoo.com> In-Reply-To: <1204595253.3110982.1718178463166@mail.yahoo.com> From: Ron Johnson Date: Wed, 12 Jun 2024 04:27:56 -0400 Message-ID: Subject: Re: postgres table statistics To: "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary="0000000000006ec47f061aad295e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006ec47f061aad295e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jun 12, 2024 at 3:48=E2=80=AFAM Chandy G wrot= e: > 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 th= e > 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 record= s > sizes. trying to see if the fetch size can be set dynamically based on th= e > 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. > --0000000000006ec47f061aad295e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jun 12, 2024 at 3:48=E2=80=AFAM C= handy G <vgchandru@yahoo.com&= gt; wrote:
Hi,
=C2=A0 We have postgres 13.9 running with tables thats= got billions of records of varying sizes. Eventhough pg jdbc driver=C2=A0 = provides a way to set fetch size to tune the driver to achieve better throu= ghput, the JVM fails at the driver level when records of large size (say 20= 0mb each) flows through.=C2=A0 this forces to reduce the fetch size (if wer= e to operate at a fixed Xmx setting of client jvm).
<= br>
It get a bit trickier when 100s of such tables ex= ists 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 t= able (for row size: max(length(t::text)= )) seem to be=C2=A0 quite time consuming too.
=C2=A0
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= .=C2=A0 (How periodic depends on how often the average and max change subst= antively.)

Does postgres maintain met= adata about tables for the following.
1. row coun= t


pg_class.reltupl= es.=C2=A0 This is an estimate, so make sure your tables are regularly analy= zed.
=C2=A0
2. max row size.


pg_stats.avg_width
= =C2=A0
or is there some other pg m= etadata that can help get this data quicker.

=
TIA.
=C2=A0
--0000000000006ec47f061aad295e--