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 1s1TIL-00CXUX-Rm for pgsql-general@arkaria.postgresql.org; Mon, 29 Apr 2024 15:51:57 +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 1s1TII-000K15-V9 for pgsql-general@arkaria.postgresql.org; Mon, 29 Apr 2024 15:51:55 +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 1s1TII-000K0u-Ba for pgsql-general@lists.postgresql.org; Mon, 29 Apr 2024 15:51:55 +0000 Received: from mail-wr1-x435.google.com ([2a00:1450:4864:20::435]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s1TIF-000cuC-Rv for pgsql-general@postgresql.org; Mon, 29 Apr 2024 15:51:53 +0000 Received: by mail-wr1-x435.google.com with SMTP id ffacd0b85a97d-34d2178fc7fso675101f8f.0 for ; Mon, 29 Apr 2024 08:51:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714405910; x=1715010710; darn=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=ltHMkefebmgb+Z9uPYYH+NENvvjWlgwB5/mckwX88gY=; b=ACLvZduGdOSqQ8iXodq9/grRF/18NW9CvmaCzAc90s9zjNvcuXSzsGrRoUPdChR3PG IYsSJ6v1mdeiSonv0qKtWfNvweqQj7zfPB1uCFwxV73z4wT8LZwZcRwCFnOtge/zfeCc ksYnNsI4XTyH4r7QW72uLpOtR2N89QGaY/KOy2Rs+9m30IhCVTa5CHbkYHP8P1PbFFYQ a/m0ZbcJpIZ+Hm5gtuMMidW0ibI/CY1vdoN1w4I6Q9O6Ry0d7k/sArZRdnu/UtHN+cmj OS5H3FFRe5TvYe9kIfM0A6tmhl9tvOiE78sopVcZtHIrE2iNMdoV9V6GnbZipZbS9KT8 lNLA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714405910; x=1715010710; 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=ltHMkefebmgb+Z9uPYYH+NENvvjWlgwB5/mckwX88gY=; b=KxzUqO3j0K3MWNvgI3qQ77qbs9XBBdk3JiBldOiW3N6DQRMTaL6g8vu6I7IlBXN6u2 QljefHpNIJLI7tWMIRAp8KSLszM+1hYs6D4VaHNjerj0dNdexot6TSI+5+jwb4N2FTd5 7nG6WscLXGGRr2Rx0DdB4Ykv3i/rJ4eneQ9eYGfHUrAn4IPUnmw6q7f6DBbAPB7ItG2x TqswAmq116nE4sJkA0v7R+5s+PKqLv2PQr1wV/RirhL+xz1ijHzy0V0mxUSjFTY7i/wj sUFHRW/K20XvGxtUnKDLT6wsCkPbROvdb16CRZyy257ozlteO9S8Xo58k0z99j53B6yQ dCrw== X-Gm-Message-State: AOJu0YwCODRgzhfcaQUt6HMTgYypv+gaIjWd91+8TCGf0hG+425E31Ru c0XkAUEry4A4PQzl4sg7J5ZUoJkb03GShES7ZquoLsP99bEwI23Y804WUnkOjV62BFwgRTBxRhd rIDKiRCzGOtLUIFsdrfCvy19MnaOv6LuueLI= X-Google-Smtp-Source: AGHT+IEWyouQzAq6k06YN/ls2CyOCxwnrh/opC/YxyHp5fSB8L6Yo1Z34ffG4JEUcv00ED1EYqgk45+07tku4o4fosM= X-Received: by 2002:a5d:4210:0:b0:34a:9afe:762 with SMTP id n16-20020a5d4210000000b0034a9afe0762mr8213318wrq.43.1714405910289; Mon, 29 Apr 2024 08:51:50 -0700 (PDT) MIME-Version: 1.0 References: <5874a355-51fb-4fa3-acf4-df81a6d5b5f0@aklaver.com> <2fab499b-2f96-4b34-a4ea-4817c2414b4f@aklaver.com> <8f942f5d-9c35-427d-9c1f-18aa405a7b8d@aklaver.com> In-Reply-To: <8f942f5d-9c35-427d-9c1f-18aa405a7b8d@aklaver.com> From: "Cocam' server" Date: Mon, 29 Apr 2024 17:51:36 +0200 Message-ID: Subject: Re: Need help to make space on my database To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000048a4cf06173e3b47" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000048a4cf06173e3b47 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > Did the above work for each table? Yes, except for the biggest table > Have you done something like?: > > select relname, n_dead_tup from pg_stat_all_tables where relname =3D > ''; I hadn't thought of that, but it seems that some tables have dead tuples Le lun. 29 avr. 2024 =C3=A0 17:34, Adrian Klaver a =C3=A9crit : > On 4/29/24 08:04, Cocam' server wrote: > > When replying use Reply All to include the mailing list > Ccing list > > > > How much current free space do you have available on the disk? > > as we speak, I only have 6 GB available on the machine running the serv= er > > > > > Did you VACUUM FULL a table at a time or all of them at once? > > I tried to make a VACUUM FULL. I also tried on the biggest tables (200 > > Mb and +) but not on all of them > > Did the above work for each table? > > Have you done something like?: > > select relname, n_dead_tup from pg_stat_all_tables where relname =3D > ''; > > to see if there any dead tuples to clean out. > > Or if you use the contrib extension pgstattuple: > > https://www.postgresql.org/docs/current/pgstattuple.html > > then: > > SELECT * FROM pgstattuple(''); > > This returns something like: > > -[ RECORD 1 ]------+-------- > table_len | 3940352 > tuple_count | 4310 > tuple_len | 3755414 > tuple_percent | 95.31 > dead_tuple_count | 0 > dead_tuple_len | 0 > dead_tuple_percent | 0 > free_space | 124060 > free_percent | 3.15 > > > > > > The two biggest are these: > > state_groups_state | 5475 MB > > event_json | 2328 MB > > > > (I'd particularly like to make room on these two tables, which take up > > the most space) > > > > By the way, excuse me if I make a few mistakes (especially when > > replying), this is the first time I've used Postgres community support > > directly > > > > > > Le lun. 29 avr. 2024 =C3=A0 16:37, Adrian Klaver > > a =C3=A9crit : > > > > On 4/29/24 07:33, Cocam' server wrote: > > > > Please reply to list also > > Ccing list > > > > > No, the aim is also to reallocate free space to the system for > > the other > > > tasks it performs.(That's why I said I'd like it returned to the > OS) > > > > You led with: > > > > "I need help to make space on my database". > > > > How much current free space do you have available on the disk? > > > > Did you VACUUM FULL a table at a time or all of them at once? > > > > What are the individual tables sizes? > > > > > > > > Le lun. 29 avr. 2024 =C3=A0 16:19, Adrian Klaver > > > > > > >> a =C3=A9crit : > > > > > > On 4/29/24 06:45, Cocam' server wrote: > > > > Hello. > > > > > > > > I need help to make space on my database. I have tables > > that are > > > several > > > > GB in size. I used to use the VACUUM FULL VERBOSE command= ; > but > > > now, this > > > > command is too greedy in free space to be used and I'm > looking > > > for a way > > > > to make free space (given back to the OS) > > > > > > > > Thanks in advance to everyone who responds > > > > > > Per > > > > > > https://www.postgresql.org/docs/current/sql-vacuum.html > > > > > > > > > > > > > "VACUUM reclaims storage occupied by dead tuples. In normal > > PostgreSQL > > > operation, tuples that are deleted or obsoleted by an update > > are not > > > physically removed from their table; they remain present > > until a VACUUM > > > is done. Therefore it's necessary to do VACUUM periodically, > > especially > > > on frequently-updated tables. > > > > > > <...> > > > > > > Plain VACUUM (without FULL) simply reclaims space and makes = it > > > available > > > for re-use. This form of the command can operate in parallel > with > > > normal > > > reading and writing of the table, as an exclusive lock is no= t > > obtained. > > > However, extra space is not returned to the operating system > > (in most > > > cases); it's just kept available for re-use within the same > > table. > > > " > > > > > > So a regular VACUUM should work if all you want to do is giv= e > the > > > database the ability to recycle the vacuumed tuple space. > > > > > > -- > > > Adrian Klaver > > > adrian.klaver@aklaver.com > > >> > > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --00000000000048a4cf06173e3b47 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>=C2=A0 Did the above work for each table?
Yes, except for the biggest table
=
>=C2=A0=C2=A0 Have you done something like?:
>
> select relname, n_dead_tup f= rom pg_stat_all_tables where relname =3D
> '<table_name>= 9;;

I hadn't thought of that, but it seems that some tables have= dead tuples

Le=C2=A0lun. 29 avr. 2024 =C3=A0=C2=A017:34, Adrian Klaver = <adrian.klaver@aklaver.com<= /a>> a =C3=A9crit=C2=A0:
On 4/29/24 08:04, Cocam' server wrote:

When replying use Reply All to include the mailing list
Ccing list

>=C2=A0 > How much current free space do you have available on the di= sk?
> as we speak, I only have 6 GB available on the machine running the ser= ver
>
>=C2=A0 > Did you VACUUM FULL a table at a time or all of them at onc= e?
> I tried to make a VACUUM FULL. I also tried on the biggest tables (200=
> Mb and +) but not on all of them

Did the above work for each table?

Have you done something like?:

select relname, n_dead_tup from pg_stat_all_tables where relname =3D
'<table_name>';

to see if there any dead tuples to clean out.

Or if you use the contrib extension pgstattuple:

https://www.postgresql.org/docs/current/pgst= attuple.html

then:

SELECT * FROM pgstattuple('<table_name>');

This returns something like:

-[ RECORD 1 ]------+--------
table_len=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 3940352
tuple_count=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 4310
tuple_len=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 3755414
tuple_percent=C2=A0 =C2=A0 =C2=A0 | 95.31
dead_tuple_count=C2=A0 =C2=A0| 0
dead_tuple_len=C2=A0 =C2=A0 =C2=A0| 0
dead_tuple_percent | 0
free_space=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 124060
free_percent=C2=A0 =C2=A0 =C2=A0 =C2=A0| 3.15


>
> The two biggest are these:
>=C2=A0 =C2=A0state_groups_state | 5475 MB
>=C2=A0 =C2=A0event_json | 2328 MB
>
> (I'd particularly like to make room on these two tables, which tak= e up
> the most space)
>
> By the way, excuse me if I make a few mistakes (especially when
> replying), this is the first time I've used Postgres community sup= port
> directly
>
>
> Le=C2=A0lun. 29 avr. 2024 =C3=A0=C2=A016:37, Adrian Klaver <adrian.klaver@aklav= er.com
> <mailto:adrian.klaver@aklaver.com>> a =C3=A9crit=C2=A0:
>
>=C2=A0 =C2=A0 =C2=A0On 4/29/24 07:33, Cocam' server wrote:
>
>=C2=A0 =C2=A0 =C2=A0Please reply to list also
>=C2=A0 =C2=A0 =C2=A0Ccing list
>
>=C2=A0 =C2=A0 =C2=A0 > No, the aim is also to reallocate free space = to the system for
>=C2=A0 =C2=A0 =C2=A0the other
>=C2=A0 =C2=A0 =C2=A0 > tasks it performs.(That's why I said I= 9;d like it returned to the OS)
>
>=C2=A0 =C2=A0 =C2=A0You led with:
>
>=C2=A0 =C2=A0 =C2=A0"I need help to make space on my database"= ;.
>
>=C2=A0 =C2=A0 =C2=A0How much current free space do you have available o= n the disk?
>
>=C2=A0 =C2=A0 =C2=A0Did you VACUUM FULL a table at a time or all of the= m at once?
>
>=C2=A0 =C2=A0 =C2=A0What are the individual tables sizes?
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Le=C2=A0lun. 29 avr. 2024 =C3=A0=C2=A016:19, = Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com&= gt;
>=C2=A0 =C2=A0 =C2=A0 > <mailto:adrian.klaver@aklaver.com
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com>>> a =C3=A9cr= it=C2=A0:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0On 4/29/24 06:45, Cocam= 9; server wrote:
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > Hello.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > I need help to make = space on my database. I have tables
>=C2=A0 =C2=A0 =C2=A0that are
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0several
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > GB in size. I used t= o use the VACUUM FULL VERBOSE command; but
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0now, this
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > command is too greed= y in free space to be used and I'm looking
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0for a way
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > to make free space (= given back to the OS)
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > Thanks in advance to= everyone who responds
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Per
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > https://www.pos= tgresql.org/docs/current/sql-vacuum.html
>=C2=A0 =C2=A0 =C2=A0<https://www.postg= resql.org/docs/current/sql-vacuum.html>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0<https://www.postgresql.org/docs/current/sql-vacuum.html
>=C2=A0 =C2=A0 =C2=A0<https://www.postg= resql.org/docs/current/sql-vacuum.html>>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0"VACUUM reclaims stor= age occupied by dead tuples. In normal
>=C2=A0 =C2=A0 =C2=A0PostgreSQL
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0operation, tuples that are= deleted or obsoleted by an update
>=C2=A0 =C2=A0 =C2=A0are not
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0physically removed from th= eir table; they remain present
>=C2=A0 =C2=A0 =C2=A0until a VACUUM
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0is done. Therefore it'= s necessary to do VACUUM periodically,
>=C2=A0 =C2=A0 =C2=A0especially
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0on frequently-updated tabl= es.
>=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=A0Plain VACUUM (without FULL= ) simply reclaims space and makes it
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0available
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0for re-use. This form of t= he command can operate in parallel with
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0normal
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0reading and writing of the= table, as an exclusive lock is not
>=C2=A0 =C2=A0 =C2=A0obtained.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0However, extra space is no= t returned to the operating system
>=C2=A0 =C2=A0 =C2=A0(in most
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0cases); it's just kept= available for re-use within the same
>=C2=A0 =C2=A0 =C2=A0table.
>=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=A0So a regular VACUUM should= work if all you want to do is give the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0database the ability to re= cycle the vacuumed tuple space.
>=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=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0 > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.c= om>>
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com

--00000000000048a4cf06173e3b47--