pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedObservation with Postgres table size
5+ messages / 5 participants
[nested] [flat]
* Observation with Postgres table size
@ 2024-05-10 13:57 Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>
2024-05-10 14:00 ` Re: Observation with Postgres table size Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-10 14:01 ` Re: Observation with Postgres table size Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-10 14:07 ` Re: Observation with Postgres table size Sunil Jadhav <sunilbjpatil@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Sabyasachi Mukherjee @ 2024-05-10 13:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Observation with Postgres table size
2024-05-10 13:57 Observation with Postgres table size Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>
@ 2024-05-10 14:00 ` Scott Ribe <scott_ribe@elevated-dev.com>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Ribe @ 2024-05-10 14:00 UTC (permalink / raw)
To: Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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 table to be rewritten? Do you expect the file to be rewritten every time a row is deleted? On commit of any transaction that included a delete?
Postgres will eventually reuse the space left behind by deleted rows (read the docs for VACUUM), so under most use cases this is not a problem.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Observation with Postgres table size
2024-05-10 13:57 Observation with Postgres table size Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>
@ 2024-05-10 14:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2024-05-10 14:01 UTC (permalink / raw)
To: Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 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?
VACUUM (FULL) tablename;
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Observation with Postgres table size
2024-05-10 13:57 Observation with Postgres table size Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>
@ 2024-05-10 14:07 ` Sunil Jadhav <sunilbjpatil@gmail.com>
2024-05-10 16:20 ` Re: Observation with Postgres table size Wells Oliver <wells.oliver@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Sunil Jadhav @ 2024-05-10 14:07 UTC (permalink / raw)
To: Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Observation with Postgres table size
2024-05-10 13:57 Observation with Postgres table size Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>
2024-05-10 14:07 ` Re: Observation with Postgres table size Sunil Jadhav <sunilbjpatil@gmail.com>
@ 2024-05-10 16:20 ` Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Wells Oliver @ 2024-05-10 16:20 UTC (permalink / raw)
To: Sunil Jadhav <sunilbjpatil@gmail.com>; +Cc: Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>; pgsql-admin@lists.postgresql.org
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 .
>
> 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
>>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-05-10 16:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-10 13:57 Observation with Postgres table size Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>
2024-05-10 14:00 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-10 14:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-10 14:07 ` Sunil Jadhav <sunilbjpatil@gmail.com>
2024-05-10 16:20 ` Wells Oliver <wells.oliver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox