Thread

1. TOAST Table / Dead Tuples / Free Pages

   Shenavai, Manuel <manuel.shenavai@sap.com> — 2024-06-13T07:55:02Z

   Hi everyone,
   
   I created a simple scenario to understand the handling of TOASTs<https://www.postgresql.org/docs/current/storage-toast.html>: There is an empty database with a single table and record. The single record gets updated multiple times with 10MB (bytea column). I can see that the table/toasttable size is growing (500MB).
   
   Now I tried to find a way to get the DB size down again (it should be around 10MB instead of 500MB). I don’t want to use VACUUM FULL due to the exclusive lock.
   
   Is there any way to remove the dead tuples and free the pages?
   
   Thanks in advance &
   Best regards,
   Manuel
   
   

2. Re: TOAST Table / Dead Tuples / Free Pages

   Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-06-13T08:08:49Z

   Hi
   
   You can use the CLUSTER command, which will physically reorder the table
   based on index, effectively reducing the size of the table without using
   VACUUM.
   
   CLUSTER your_table USING your_index;
   
   Or you can use the pg_repack extension as well.
   
   pg_repack -d your_database -t your_table
   
   Regards
   Kashif Zeeshan
   
   
   
   On Thu, Jun 13, 2024 at 12:55 PM Shenavai, Manuel <manuel.shenavai@sap.com>
   wrote:
   
   > Hi everyone,
   >
   >
   >
   > I created a simple scenario to understand the handling of TOASTs
   > <https://www.postgresql.org/docs/current/storage-toast.html>: There is an
   > empty database with a single table and record. The single record gets
   > updated multiple times with 10MB (bytea column). I can see that the
   > table/toasttable size is growing (500MB).
   >
   >
   >
   > Now I tried to find a way to get the DB size down again (it should be
   > around 10MB instead of 500MB). I don’t want to use VACUUM FULL due to the
   > exclusive lock.
   >
   >
   >
   > Is there any way to remove the dead tuples and free the pages?
   >
   >
   >
   > Thanks in advance &
   >
   > Best regards,
   >
   > Manuel
   >
   >
   >
   

3. Re: TOAST Table / Dead Tuples / Free Pages

   Muhammad Ikram <mmikram@gmail.com> — 2024-06-13T08:41:52Z

   Hi Shenavai,
   
   Here are some more options..
   
   VACUUM <your tablename>
   
   VACUUM  FULL <your tablename>
   
   You may also reindex to reclaim space
   
   REINDEX TABLE <your tablename>
   
   REINDEX INDEX <indexname>
   
   
   Regards,
   Muhammad Ikram
   Bitnine
   
   On Thu, Jun 13, 2024 at 1:09 PM Kashif Zeeshan <kashi.zeeshan@gmail.com>
   wrote:
   
   > Hi
   >
   > You can use the CLUSTER command, which will physically reorder the table
   > based on index, effectively reducing the size of the table without using
   > VACUUM.
   >
   > CLUSTER your_table USING your_index;
   >
   > Or you can use the pg_repack extension as well.
   >
   > pg_repack -d your_database -t your_table
   >
   > Regards
   > Kashif Zeeshan
   >
   >
   >
   > On Thu, Jun 13, 2024 at 12:55 PM Shenavai, Manuel <manuel.shenavai@sap.com>
   > wrote:
   >
   >> Hi everyone,
   >>
   >>
   >>
   >> I created a simple scenario to understand the handling of TOASTs
   >> <https://www.postgresql.org/docs/current/storage-toast.html>: There is
   >> an empty database with a single table and record. The single record gets
   >> updated multiple times with 10MB (bytea column). I can see that the
   >> table/toasttable size is growing (500MB).
   >>
   >>
   >>
   >> Now I tried to find a way to get the DB size down again (it should be
   >> around 10MB instead of 500MB). I don’t want to use VACUUM FULL due to the
   >> exclusive lock.
   >>
   >>
   >>
   >> Is there any way to remove the dead tuples and free the pages?
   >>
   >>
   >>
   >> Thanks in advance &
   >>
   >> Best regards,
   >>
   >> Manuel
   >>
   >>
   >>
   >
   
   -- 
   Muhammad Ikram
   

4. Re: TOAST Table / Dead Tuples / Free Pages

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-13T14:38:01Z

   On 6/13/24 01:08, Kashif Zeeshan wrote:
   > Hi
   > 
   > You can use the CLUSTER command, which will physically reorder the table 
   > based on index, effectively reducing the size of the table without using 
   > VACUUM.
   
    From OP:
   
   "I don’t want to use VACUUM FULL due to the exclusive lock."
   
    From here
   
   https://www.postgresql.org/docs/current/sql-cluster.html
   
   "When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired 
   on it. This prevents any other database operations (both reads and 
   writes) from operating on the table until the CLUSTER is finished."
   
   
   > 
   > CLUSTER your_table USING your_index;
   > 
   > Or you can use the pg_repack extension as well.
   > 
   > pg_repack -d your_database -t your_table
   > 
   > Regards
   > Kashif Zeeshan
   > 
   > 
   > 
   > On Thu, Jun 13, 2024 at 12:55 PM Shenavai, Manuel 
   > <manuel.shenavai@sap.com <mailto:manuel.shenavai@sap.com>> wrote:
   > 
   >     Hi everyone,____
   > 
   >     __ __
   > 
   >     I created a simple scenario to understand the handling of TOASTs
   >     <https://www.postgresql.org/docs/current/storage-toast.html>: There
   >     is an empty database with a single table and record. The single
   >     record gets updated multiple times with 10MB (bytea column). I can
   >     see that the table/toasttable size is growing (500MB).____
   > 
   >     __ __
   > 
   >     Now I tried to find a way to get the DB size down again (it should
   >     be around 10MB instead of 500MB). I don’t want to use VACUUM FULL
   >     due to the exclusive lock.____
   > 
   >     __ __
   > 
   >     Is there any way to remove the dead tuples and free the pages?____
   > 
   >     __ __
   > 
   >     Thanks in advance &____
   > 
   >     Best regards,____
   > 
   >     Manuel____
   > 
   >     __ __
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com