Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcgQe-0004gn-VU for pgsql-general@arkaria.postgresql.org; Thu, 16 Mar 2023 05:45:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pcgQd-0004rj-0m for pgsql-general@arkaria.postgresql.org; Thu, 16 Mar 2023 05:45:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcgQc-0004rZ-JU for pgsql-general@lists.postgresql.org; Thu, 16 Mar 2023 05:45:30 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pcgQY-0003RH-LU for pgsql-general@lists.postgresql.org; Thu, 16 Mar 2023 05:45:30 +0000 Received: by mail-ed1-x52b.google.com with SMTP id w9so3187518edc.3 for ; Wed, 15 Mar 2023 22:45:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; t=1678945525; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=K+ra8KqnufqO08sF/AME2iPuaqR0VqlqkTQJZT3bxu8=; b=tMmP0+jO5i6FOJvf0D1lft+JZO8kwk1Wf+I4ZwS8Ht7WOXA8JEl9XrrHlO9VypgSAF /Ct9QhpEe6ORL9+8CujcfkSwxdrzeUY/8sER4Q1oTCsckndpooAxqyu+XQSwDuwv8OOv 5ku7yeILVkhsJRqY42WaLMuM2m6L9GSFO0Psifwpeg1MLkLHok5nN3D/xx1X0hAfWERQ REgS5BGuqltVmpZWpN2MlakVjSJZg6bmLJh8+3hYdcHmk6FDz1S8Ug0w0y5LnOjNZyV6 5FUIYZvsmFnfqZyMZXvri/O9U4yL+xAni87xNalNy2PoiwMkqHyb1XSixq8ofp+9OM3A H4Iw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678945525; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=K+ra8KqnufqO08sF/AME2iPuaqR0VqlqkTQJZT3bxu8=; b=Yzqe9EirrlEYBfkXAIip6EZWxndc6iKoQO4+z6l5rY7Ccdgfjp6eS5WrsSsxFwXAgk XEfFKaj7IkIbOG+BSIDcKGB+kOFIVIu/7g1W6e/YV/gmUvljeZiPylsRyp1x1WbOVJ/t bgSKhOIDi7sqxW6dHCPmnW3zwGqzpazUf8Z1dmVAQuFo3W4UxKzcKxCqsRktDELf2lTV Fs0J7DHH2gLGZ7k8ZzSNEUGm0IZZnutmlW+Ob6LUgsZqXaOzomXWGROZKH7EclHFbuUR dIsBZNxXgqo5AK1JpvjFyRDHXOGi/vBwZ0sidlhJT6jXez5hzFOWiIWYdsAjVX2yF8zN 7iaw== X-Gm-Message-State: AO0yUKVPKcxmEYN+1pXjEBfSVFWVVDswlqvuAfljrq+pBapd6aw+jjtB x0OXAZGGSO3EIJRxZfHGoj6AiA== X-Google-Smtp-Source: AK7set94M4bzKjcLRwJSP3IPOvFQVukHuusq46ubps0YEWPo0NV0dzxyGRNG+vsLwLJHOUkJTd9Adw== X-Received: by 2002:a05:6402:13:b0:4fa:eccd:9849 with SMTP id d19-20020a056402001300b004faeccd9849mr4798517edu.9.1678945525160; Wed, 15 Mar 2023 22:45:25 -0700 (PDT) Received: from localhost.localdomain (p50992f7c.dip0.t-ipconnect.de. [80.153.47.124]) by smtp.gmail.com with ESMTPSA id z22-20020a170906271600b0091f58083a15sm3379307ejc.175.2023.03.15.22.45.24 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 15 Mar 2023 22:45:24 -0700 (PDT) Message-ID: Subject: Re: "No Free extents", table using all allocated space but no rows! From: Laurenz Albe To: "Dolan, Sean" , "pgsql-general@lists.postgresql.org" Date: Thu, 16 Mar 2023 06:45:24 +0100 In-Reply-To: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 2023-03-16 at 01:58 +0000, Dolan, Sean wrote: > Environment: PostGres 13 on RedHat 7.9.=C2=A0=C2=A0=20 > =C2=A0 > I am using logical replication (publisher/subscriber) between two databas= es and there > are times where one of our schemas gets to 100% of allocated space (No Fr= ee Extents). > I went into the schema and did a \dt+ to see the amount of size being use= d and I > could see one of the tables somehow shows 16GB, essentially the amount of= allocated size. > Wanting to see what is in that table, I did a simple select * from the ta= ble and it > returns no rows.=C2=A0 Doing a count(*) also returns 0 rows. > =C2=A0 > How can the table be using all that space but there is nothing =E2=80=9Ci= n=E2=80=9D the table? > I don=E2=80=99t care about the data (as I am testing) so I can drop and r= ecreate that one table. > \dt+ would then show 0 bytes.=C2=A0=C2=A0=C2=A0 Later, I will then create= a subscription and then > I will get a No Free Extents error again and again the table has filled u= p. > =C2=A0 > What can I look for? I don't think that there is an error message "no free extents". It can easily happen that a table is large, but SELECT count(*) returns 0. That would mean that either the table is empty and VACUUM truncation didn't= work, or that the table contains tuples that are not visible to your user, either because VACUUM didn't process the table yet, or because your snapshot is to= o old to see the data, or because the transaction that created the rows is st= ill open. If you don't care about the data, your easiest option is to TRUNCATE the ta= ble. If TRUNCATE is blocked, kill all transactions that block it. Yours, Laurenz Albe