Thread: Know bloat percentage after auto vacuum
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.
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
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