Обсуждение: Know bloat percentage after auto vacuum

Поиск
Список
Период
Сортировка

Know bloat percentage after auto vacuum

От
satish v
Дата:
Hi all.

We have the table with 2 months data (approx 450GB). We deleted 17 days of data and auto vacuum performed on the table.new insertions are utilizing  that space,we want to perform the vacuum full and reclaim the space at os level but don't know how much free (unused) space available.how can we get the details.

Thanks in advance.

Regards,
Satish.

Re: Know bloat percentage after auto vacuum

От
Laurenz Albe
Дата:
On Sat, 2021-10-30 at 21:33 +0530, satish v wrote:
> We have the table with 2 months data (approx 450GB). We deleted 17 days of data and auto vacuum
> performed on the table.new insertions are utilizing  that space,we want to perform the vacuum full
> and reclaim the space at os level but don't know how much free (unused) space available.how can we get the details.

CREATE EXTENSION pgstattuple;

SELECT tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('table_name');

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: Know bloat percentage after auto vacuum

От
Keith
Дата:


On Mon, Nov 1, 2021 at 12:35 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Sat, 2021-10-30 at 21:33 +0530, satish v wrote:
> We have the table with 2 months data (approx 450GB). We deleted 17 days of data and auto vacuum
> performed on the table.new insertions are utilizing  that space,we want to perform the vacuum full
> and reclaim the space at os level but don't know how much free (unused) space available.how can we get the details.

CREATE EXTENSION pgstattuple;

SELECT tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('table_name');

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


Note that pgstattuple('tablename') will only show the bloat on the table itself and not the indexes, nor any possibly associated TOAST tables.

Script I've been working on tries to help make pgstattuple a little more useful by scanning all relevant pieces that it can for a given table.


Keith