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 1sZN54-000Yq9-P4 for pgsql-general@arkaria.postgresql.org; Thu, 01 Aug 2024 04:06:22 +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 1sZN43-001k7u-CY for pgsql-general@arkaria.postgresql.org; Thu, 01 Aug 2024 04:05:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sZN42-001k7l-UU for pgsql-general@lists.postgresql.org; Thu, 01 Aug 2024 04:05:19 +0000 Received: from mail-pj1-x1033.google.com ([2607:f8b0:4864:20::1033]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sZN40-002Xoj-8c for pgsql-general@lists.postgresql.org; Thu, 01 Aug 2024 04:05:18 +0000 Received: by mail-pj1-x1033.google.com with SMTP id 98e67ed59e1d1-2cf11b91813so4385475a91.1 for ; Wed, 31 Jul 2024 21:05:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1722485114; x=1723089914; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=4+tdb3Yhs8/77wsi0OAn7UTFy9ZO6+qiSFFqQWRX+RY=; b=H3k8HMF47rKNOZgy8cRdzSV7Nyf61rJlqmZWOI72enBfNDijjfT9NlJG/Lqm5FycdV pL260Id2G39/BPLlmKv4XZwoiEtOTBpDr3p5g8CZm6IHFlBr1sjL2ctL3vgDQz1bW6lz /ztwEra14d9W8/qWmsMpRibUR23Qi0IDXMso0Bn7ZrFLlJ7gyUxVhrlVGgKixg/r5NjO H5o+dhLOMQf9yhs4WO6mOX8FR2gatAH+dmjV7//x4I71PY+RYRbIKOvjVK4UWIVBuKPQ 23CCYRhQvQTFWVJsEROuTRVN+yBjTPdhO8MXRclpaLO83QRCKL2TeHCAr8XFP0msWmCO /omQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1722485114; x=1723089914; h=cc: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=4+tdb3Yhs8/77wsi0OAn7UTFy9ZO6+qiSFFqQWRX+RY=; b=jnMJPh6JcWCUcNCLGgKwuHHKC5BoEBrae0/L2Z5YP8FwSGUbIhh7AsaaIMpLrTww6q 9N9L3vzAQIqhcjABcTElbHNy8eMkmeg3klm/I6vEqB1cC1FukttDtbdUcOO9rVa12MDQ 5Y6WpeNboPHL4i+Yi1dGCfFapCDAuBHFvAsGdcYKxZXoPALcOoGAXQLFDOaR/b4w392m Mkbsjye4YoG37mKAjszR5HBSEKufSOKTJzhlvpCs+/Mn4y4Lgq3ZC3r+wKSF4JarTLlC 0dzzVNV0aP1amqO5bYUFDNvfBxzUA8THCqxaGFW9f/CPDCRE45p9Ps+0jUM8uVRrWgqc NFBA== X-Forwarded-Encrypted: i=1; AJvYcCU15MjzNPmf7BD3e9sPEPxspEV3CwKrHkiJiita5CPXmU45z4Utv+HqubveCn5t6tMIibeGNsksCAxe/kOiQlrdbsn+X1qPiY7gbGIseKYeAqQL X-Gm-Message-State: AOJu0YxI1TkHUuAgAwhW1O79263weI8hSLG4nBmWPzv3qAucIktQt2+e EMHJDwmWdw0KSzxjQFr1z1PGWVCX66Y3W9x81QLV0NTPwdF7aJOF0QCFR1KsDKSNvj8sfkr+SLB 3gUGqvu47QkVM5XgkmFIzJZnBDO8= X-Google-Smtp-Source: AGHT+IHDef2BlqjLmTnNZ7z9lF3eNLGxzGkUZa+p9MfquNlThobn2KGoGa9NMUnEy3emrcYhfnQoEmsE5Ha7NqMGIV8= X-Received: by 2002:a17:90a:909:b0:2cb:f9e:3bfb with SMTP id 98e67ed59e1d1-2cfe7b3caa0mr1530599a91.32.1722485113718; Wed, 31 Jul 2024 21:05:13 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: semab tariq Date: Thu, 1 Aug 2024 09:05:02 +0500 Message-ID: Subject: Re: Monitoring DB size To: "Shenavai, Manuel" Cc: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000558efd061e975103" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000558efd061e975103 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Manuel Sorry for the late reply saw this email just today. Anyways how about using something like? postgres=3D# SELECT postgres-# pg_size_pretty(pg_database_size('postgres')) AS database_size, postgres-# pg_size_pretty(pg_total_relation_size('accounts')) AS table_size, postgres-# pg_size_pretty(tuple_len) AS live_tuple_size, postgres-# pg_size_pretty(dead_tuple_len) AS dead_tuple_size, postgres-# pg_size_pretty(free_space) AS free_space postgres-# FROM postgres-# pgstattuple('accounts'); database_size | table_size | live_tuple_size | dead_tuple_size | free_space ---------------+------------+-----------------+-----------------+----------= -- 8500 kB | 40 kB | 80 bytes | 80 bytes | 7988 byte= s (1 row) Thanks and regards Semab On Tue, Jul 16, 2024 at 4:38=E2=80=AFPM Shenavai, Manuel wrote: > Thanks for the suggestion. I think this will not help us to differentiate > between live tuples, dead tuples and free space. > > > > Best regards, > > Manuel > > > > *From:* Torsten F=C3=B6rtsch > *Sent:* 15 July 2024 18:59 > *To:* Shenavai, Manuel > *Cc:* pgsql-general@lists.postgresql.org > *Subject:* Re: Monitoring DB size > > > > Slightly different approach than you might expect. For larger DBs you'd > likely want to exclude base and instead use pg_database_size() in additio= n. > > > > postgres(2454884) =3D# create temp table xx(dir text, sz bigint); > CREATE TABLE > Time: 2.587 ms > postgres(2454884) =3D# copy xx(sz, dir) from program 'du -s *'; > > COPY 21 > Time: 3.793 ms > postgres(2454884) =3D# select * from xx; > dir | sz > ----------------------+------- > base | 26280 > global | 568 > pg_commit_ts | 12 > pg_dynshmem | 4 > pg_logical | 16 > pg_multixact | 28 > pg_notify | 4 > pg_replslot | 4 > pg_serial | 4 > pg_snapshots | 4 > pg_stat | 4 > pg_stat_tmp | 4 > pg_subtrans | 12 > pg_tblspc | 4 > pg_twophase | 4 > PG_VERSION | 4 > pg_wal | 16392 > pg_xact | 12 > postgresql.auto.conf | 4 > postmaster.opts | 4 > postmaster.pid | 4 > (21 rows) > > Time: 0.282 ms > > > > On Mon, Jul 15, 2024 at 4:42=E2=80=AFPM Shenavai, Manuel > wrote: > > Hi everyone, > > > > we currently capture the db size (pg_database_size) which gives the =E2= =80=9CDisk > space used by the database with the specified name=E2=80=9D. Is it possib= le to > further split this data how much space is occupied by live tuples, dead > tuples and free space? > > > > We would like to have something like: > > DB Size: 200 MB, live tuple 100MB, Dead Tuple: 20 MB, free space 80MB > > > > Is this possible? > > > > Best regards, > > Manuel > > --000000000000558efd061e975103 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Manuel

Sorry for the late reply saw = this email just today. Anyways how about using something like?
postgres=3D# SELECT
postgres-# =C2=A0 =C2=A0 pg_size_pretty= (pg_database_size('postgres')) AS database_size,
postgres-# =C2= =A0 =C2=A0 pg_size_pretty(pg_total_relation_size('accounts')) AS ta= ble_size,
postgres-# =C2=A0 =C2=A0 pg_size_pretty(tuple_len) AS live_tup= le_size,
postgres-# =C2=A0 =C2=A0 pg_size_pretty(dead_tuple_len) AS dead= _tuple_size,
postgres-# =C2=A0 =C2=A0 pg_size_pretty(free_space) AS free= _space
postgres-# FROM
postgres-# =C2=A0 =C2=A0 pgstattuple('acco= unts');
=C2=A0database_size | table_size | live_tuple_size | dead_tu= ple_size | free_space
---------------+------------+-----------------+--= ---------------+------------
=C2=A08500 kB =C2=A0 =C2=A0 =C2=A0 | 40 kB = =C2=A0 =C2=A0 =C2=A0| 80 bytes =C2=A0 =C2=A0 =C2=A0 =C2=A0| 80 bytes =C2=A0= =C2=A0 =C2=A0 =C2=A0| 7988 bytes
(1 row)


Thanks and regards= =C2=A0
Semab


On Tue, Jul 16, 2024 at 4:38=E2=80=AFPM= Shenavai, Manuel <manuel.she= navai@sap.com> wrote:

Thanks for the suggestion. I th= ink this will not help us to differentiate between live tuples, dead tuples= and free space.

=C2=A0

Best regards,

Manuel

=C2=A0

From: Torsten F=C3=B6rtsch <tfoertsch123@gmail.com>
Sent: 15 July 2024 18:59
To: Shenavai, Manuel <manuel.shenavai@sap.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Monitoring DB size

=C2=A0

Slightly different approach than you might expect. F= or larger DBs you'd likely want to exclude base and instead use pg_data= base_size() in addition.

=C2=A0

postgres(2454884) =3D# create temp table xx(dir text, sz bigint= ); =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
CREATE TABLE
Time: 2.587 ms
postgres(2454884) =3D# copy xx(sz, dir) from pr= ogram 'du -s *'; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0
COPY 21
Time: 3.793 ms
postgres(2454884) =3D# select * from xx; =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0dir =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0sz =C2=A0=C2=A0=C2=A0
----------------------+-------
base =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| 26280
global =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0568
pg_commit_ts =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0= =C2=A012
pg_dynshmem =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A04
pg_logical =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| = =C2=A0=C2=A0=C2=A016
pg_multixact =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0= =C2=A028
pg_notify =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A04
pg_replslot =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A04
pg_serial =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A04
pg_snapshots =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0= =C2=A0=C2=A04
pg_stat =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A04
pg_stat_tmp =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A04
pg_subtrans =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A012
pg_tblspc =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A04
pg_twophase =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A04
PG_VERSION =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| = =C2=A0=C2=A0=C2=A0=C2=A04
pg_wal =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| 16392
pg_xact =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| =C2=A0=C2=A0=C2=A012
postgresql.auto.conf | =C2=A0=C2=A0=C2=A0=C2=A04
postmaster.opts =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A04 <= br> postmaster.pid =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2= =A04
(21 rows)

Time: 0.282 ms

=C2=A0

On Mon, Jul 15, 2024 at 4:42=E2=80=AFPM Shenavai, Ma= nuel <manue= l.shenavai@sap.com> wrote:

Hi everyone,

=C2=A0

we currently capture the db siz= e (pg_database_size) which gives the =E2=80=9CDisk space used by the databa= se with the specified name=E2=80=9D. Is it possible to further split this data how much space is occupied by live tuples, dead tuples and free = space?

=C2=A0

We would like to have something= like:

DB Size: 200 MB, live tuple 100= MB, Dead Tuple: 20 MB, free space 80MB

=C2=A0

Is this possible?=

=C2=A0

Best regards,<= /u>

Manuel

--000000000000558efd061e975103--