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 1sVrlx-00C3zn-62 for pgsql-general@arkaria.postgresql.org; Mon, 22 Jul 2024 12:04:09 +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 1sVrlv-000bnu-5d for pgsql-general@arkaria.postgresql.org; Mon, 22 Jul 2024 12:04:07 +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 1sVrlu-000bnl-MS for pgsql-general@lists.postgresql.org; Mon, 22 Jul 2024 12:04:07 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sVrls-000qv3-Eb for pgsql-general@postgresql.org; Mon, 22 Jul 2024 12:04:05 +0000 Received: by mail-ed1-x534.google.com with SMTP id 4fb4d7f45d1cf-5a10bb7b237so3504037a12.0 for ; Mon, 22 Jul 2024 05:04:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721649843; x=1722254643; 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=Feg1WUry0+aFyKnmF6XM2KCMdqGqz0va1ekUIc+JmsM=; b=agKIbNsHsTie4yP4xgvIle8OqwJ5IYufZQoB8wexyUDV6Z4py3AOnZR72HDhYi+yTa SC9rbOo3QlnwASivqdFboYeIBbaC1TDaL9Ln8kooS+AXvNz2tFYWrt2GMC/fU5JBmrYG bPF9aMZC98o15MChb7FNVB/41FHfeSmUPDTWExhBaQJ13YvZDhaUdhhyslboO1Zn4xoO mJU2pfix9zDV0oJgtlVJ5XiVz7KrpRhI28eczKvmgGT6+StruDuZ1Lz+4uJP0MW8U4x5 i21LDsohqSeJleauUl+lAUs5f+lK//MsCdSDXtPB5YLpLruh9jBCnSNH0Vw/zhyfniJb r1bw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721649843; x=1722254643; 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=Feg1WUry0+aFyKnmF6XM2KCMdqGqz0va1ekUIc+JmsM=; b=Tn/PF2+n99tHxpLnXFfl4mshvrn+gQ9HcSGORDvQCTGF0b9hMEUp4KE9biltB1i2Md ru9lb5nlfppvFcjr6hHSrEpwhGWWRWzsl615fHohBbmcxLlG2hKRmG1eydKOCBCUKRiH 4SolKoImfqjYfD6ikB/V1lvAOvgzzG3/yuk1xNLZZLTNGLDYRfQegnYtO+cLwEUXtbe6 qBatmL3wJ22sLLbqWPFYcXiYTDzffomw4tYIMfY6wu8l5CS1xDAH2jz02aowJQ8GXtVs Xd54G7OEp3rSNRciJR2YRlceQGHlpBUSkDRbFJYhBMe8ol9KQAVdodpvw9RMJAuR6jAt WpDw== X-Forwarded-Encrypted: i=1; AJvYcCXajAiCz+m7u8PhpKWyUSnw3l08VQbb7XWzkZ7Yh60vKM/Ymf7bKBKLKdTvCc/BSz8PaEyZkIGo+gF5LT+1XOkOt2DaQh2+tx5SSiKf X-Gm-Message-State: AOJu0YxzZoYzJ+ztXBhkvuVTYSH1fUETL3eeQ0oZGS+NE59INYI6QVJa j9eRxP+1lwfTDiYOoWB1Sh56NnxA8VdblEWL+5WdVrt85S4gHsG+Ix1VUOsuoaGCX/pcl9T4vYd E+KfKqU2DJ5BnR9XqhVNpvvNdUrY= X-Google-Smtp-Source: AGHT+IG6dVlpTQcUxAc119A4jsbl+i5Oru4qapu6h2fSiUn8Gy7GH7veGwIBukITbRYSbnXL0G/eDVhEi3vWj+5O8Xg= X-Received: by 2002:a50:8e53:0:b0:5a1:807d:e868 with SMTP id 4fb4d7f45d1cf-5a47afed2f6mr4210707a12.24.1721649842630; Mon, 22 Jul 2024 05:04:02 -0700 (PDT) MIME-Version: 1.0 References: <01010190d2526ef9-5d0a93fe-30ad-4d6b-9bc8-269c188bd42e-000000@us-west-2.amazonses.com> In-Reply-To: From: Priancka Chatz Date: Mon, 22 Jul 2024 14:03:49 +0200 Message-ID: Subject: Re: Bloated pg_catalog.pg_largeobjects To: khan Affan Cc: postgresql@thewickedtribe.net, "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004c365b061dd4d76a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004c365b061dd4d76a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Vacuum Full will not help here if you have large objects present in the pg_largeobjects table but not being referred to by any table. Vacuumlo doesn't require downtime but based on the data it needs to remove can run long and use resources and hence schedule it in off peak hours. You can do a dry run to get an estimate. On Mon, Jul 22, 2024 at 1:21=E2=80=AFPM khan Affan wro= te: > Hi > > I would suggest to backup your DB before doing such a thing. > > Run Vaccum Full, (VACUUM FULL pg_catalog.pg_largeobject) Running this on > the system table might be risky Make sure you backup the database. > > & if you are using PG version above 9.1 use Pg_repack to reclaim the spac= e. > > Note: It can be disruptive, so planning and preparing for potential > downtime is essential. > > Thanks & regards > > > *Muhammad Affan (*=EC=95=84=ED=8C=90*)* > > *PostgreSQL Technical Support Engineer** / Pakistan R&D* > > Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakista= n > > On Sun, Jul 21, 2024 at 3:46=E2=80=AFAM w= rote: > >> Hello All, >> >> I've got a cluster that's having issues with pg_catalog.pg_largeobject >> getting massively bloated. Vacuum is running OK and there's 700GB of fre= e >> space in the table and only 100GB of data, but subsequent inserts seem t= o >> be not using space from the FSM and instead always allocating new pages. >> The table just keeps growing. >> >> Is this a known thing, maybe something special about LOs? >> >> Also, is the only way to recover space here a vacuum full on the table >> since it's a catalog table? >> >> Thanks, >> -- >> Jon Erdman (aka StuckMojo on IRC) >> PostgreSQL Zealot >> > --0000000000004c365b061dd4d76a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Vacuum Full will not help here if you have large objects p= resent in the pg_largeobjects table but not being referred to by any table.= Vacuumlo doesn't require downtime but based on the data it needs to re= move can run long and use resources and hence schedule it in off peak hours= . You can do a dry run to get an estimate.

On Mon, Jul 22, 2024 at 1:21=E2= =80=AFPM khan Affan <bawag773@gmai= l.com> wrote:
Hi=C2=A0

I would suggest to backup your DB bef= ore doing such a thing.

Run Vaccum=C2=A0Full, (VACUUM FULL pg_= catalog.pg_largeobject) Running this on the system table might be ri= sky Make sure you backup the database.

& if you are using PG ver= sion above 9.1 use Pg_repack to reclaim the space.

Note:=C2=A0 It can be disruptive, so planning and preparing for potential downtime is = essential.

Thanks &=C2=A0regards


Muhammad Aff= an (=EC=95=84=ED=8C=90<= font color=3D"#2255ff" face=3D"Arial">)

PostgreSQL Technical Support Engineer= =C2=A0/ Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Ma= rkaz, Islamabad, Pakistan


On Sun, Jul 21, 2024= at 3:46=E2=80=AFAM <postgresql@thewickedtribe.net> wrote:

Hello All,

I've got a cluster that's having = issues with pg_catalog.pg_largeobject getting massively bloated. Vacuum is = running OK and there's 700GB of free space in the table and only 100GB = of data, but subsequent inserts seem to be not using space from the FSM and= instead always allocating new pages. The table just keeps growing.

= Is this a known thing, maybe something special about LOs?

Also, is t= he only way to recover space here a vacuum full on the table since it's= a catalog table?

Thanks,

--=C2=A0
Jon Erdma= n (aka StuckMojo on IRC)
=C2=A0=C2=A0=C2=A0 PostgreSQL Zealot
--0000000000004c365b061dd4d76a--