Обсуждение: Observation with Postgres table size
Hello All,
I am having an issue with the size of a Postgres DB table.
The table have 17 columns. Out of them 5 jsonb columns. The table had 470000 rows when I checked from DBBeaver it showed the table size 34GB. After I deleted many rows from the table - it has currently 27038 rows. But the size of the table still shows 34GB.
I was expecting the size of the DB table to be reduced.
I event restarted the PG DB server service.
I am using Postgres v16 .
Can any body please advise, how to reduce the size of the table?
Sabyasachi Mukherjee
> On May 10, 2024, at 7:57 AM, Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com> wrote: > > Can any body please advise, how to reduce the size of the table? VACUUM FULL Read the docs to understand what it does. Deleting a row does not shrink the table. Think about it, what happens when you delete a row that's first in the file on disk? Do you expect the rest of the tableto be rewritten? Do you expect the file to be rewritten every time a row is deleted? On commit of any transaction thatincluded a delete? Postgres will eventually reuse the space left behind by deleted rows (read the docs for VACUUM), so under most use casesthis is not a problem.
On Fri, 2024-05-10 at 13:57 +0000, Sabyasachi Mukherjee wrote: > I am having an issue with the size of a Postgres DB table. > The table have 17 columns. Out of them 5 jsonb columns. The table had 470000 rows when I checked from DBBeaver it showedthe table size 34GB. After I deleted many rows from the table - it has currently 27038 rows. But the size of the tablestill shows 34GB. > I was expecting the size of the DB table to be reduced. > I event restarted the PG DB server service. > I am using Postgres v16 . > Can any body please advise, how to reduce the size of the table? VACUUM (FULL) tablename; Yours, Laurenz Albe
Hi Dear,
Please carefully read the vacuum full documents if you try this this will impacts on current transaction due to exclusive lock if this is production db then you required a download time for that .
Thanks
Sunil
On Fri, 10 May, 2024, 7:27 pm Sabyasachi Mukherjee, <mukherjee.sabyasachi@outlook.com> wrote:
Hello All,I am having an issue with the size of a Postgres DB table.The table have 17 columns. Out of them 5 jsonb columns. The table had 470000 rows when I checked from DBBeaver it showed the table size 34GB. After I deleted many rows from the table - it has currently 27038 rows. But the size of the table still shows 34GB.I was expecting the size of the DB table to be reduced.I event restarted the PG DB server service.I am using Postgres v16 .Can any body please advise, how to reduce the size of the table?Sabyasachi Mukherjee
pg_repack is another solution to rebuild the table (and reclaim space) without the locks of VACUUM FULL, it's quite effective and easy.
On Fri, May 10, 2024 at 7:08 AM Sunil Jadhav <sunilbjpatil@gmail.com> wrote:
Hi Dear,Please carefully read the vacuum full documents if you try this this will impacts on current transaction due to exclusive lock if this is production db then you required a download time for that .ThanksSunilOn Fri, 10 May, 2024, 7:27 pm Sabyasachi Mukherjee, <mukherjee.sabyasachi@outlook.com> wrote:Hello All,I am having an issue with the size of a Postgres DB table.The table have 17 columns. Out of them 5 jsonb columns. The table had 470000 rows when I checked from DBBeaver it showed the table size 34GB. After I deleted many rows from the table - it has currently 27038 rows. But the size of the table still shows 34GB.I was expecting the size of the DB table to be reduced.I event restarted the PG DB server service.I am using Postgres v16 .Can any body please advise, how to reduce the size of the table?Sabyasachi Mukherjee
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com