In PostgreSQL, the old version rows or dead tuples or obsolete records that are generated due to delete and update will not release the space unless the table is vacuumed. The vacuum helps to release the space occupied by obsolete records but the released space will be under table or segment. These blocks will be used for subsequent inserts and updates into the table. The table will be vacuumed whenever it reaches the threshold. The following parameters will help to calculate when a table should be vacuumed.

autovacuum_vacuum_scale_factor=0.2(whenever 20 % of records modified) 

autovacuum_vacuum_threshold=50(No. of records)

Suppose if there is a table with 1000 rows, it will get vacuumed if there are deletes (or updates) 1000 * 0.2 + 50 = 250 rows i.e 20% + 50.

The above parameters can be set at the cluster level as well as table level.

Oracle reuses the space in blocks generated during deletes (No bloat for updates like PostgreSQL). Oracle reuses space in the blocks that are listed in freelist of the segment. PCTUSED helps to calculate when a block should be listed in freelist. Suppose if the value of the PCTUSED is 40, and the space occupied by rows in a block is less than 40% then that block will be listed in freelist. These blocks will be used for subsequent inserts and updates into the table. PCTUSED is usually set at tablespace level however this can be set at table level as well.

Again, going back to PostgreSQL, once the table is vacuumed, the space occupied by obsolete records can be reused but space is not released to disk. Similarly, how Oracle will not release the blocks and maintains the blocks under segment freelist below the high-water mark. 

Vacuum full is something similar to “Move” (Not Shrink) in Oracle which will release space to the disk. Like “Move” operation, the “vacuum full” will lock the table and it requires additional free space of the size the table to complete the operation and helps to reduce the size of the table. “Shrink” in Oracle has more advantage like no need of additional space and lock time on a table is also very less compared to “Move”

Created “sample” table with 1999 tuples or rows both in Oracle and PostgreSQL to show the example of reuse space(vacuum), bloat and vacuum full(defragmentation)

select count(1) from sample;

 count 

——-

   1999

PostgreSQL : Table Size and Space occupied with rows(estimated size)

DEVL=# select table_len as table_size,tuple_len as estimated_size from pgstattuple(‘sample’);

 table_size | estimated_size 

————+—————-

     286720 |         267452

Oracle: Table Size and Space occupied with rows (estimated size)

SQL> select blocks*8*1024 as table_size,num_rows*avg_row_len as estimated_size from user_tables where table_name = ‘SAMPLE’;

TABLE_SIZE ESTIMATED_SIZE

———-              ————–

    262144   217891

Note: There is a slight difference in table size vs estimated size(total bytes occupied by data) because of overhead metadata.

Bloat Example

PostgreSQL: Update

Bloat in Postgresql: updating all the 1999 records, due to MVCC architecture, table size will increase even during update, vacuum helps to reuse the space below the high water mark. 

DEVL=# UPDATE sample SET entity_ind = ‘T’ WHERE entity_ind = ‘I’;

UPDATE 1999

DEVL=# select table_len as table_size,tuple_len as estimated_size from pgstattuple(‘sample’);

 table_size | estimated_size 

————+—————-

     565248 |         267452

DEVL=# UPDATE sample SET entity_ind = ‘I’ WHERE entity_ind = ‘T’;

UPDATE 1999

DEVL=# select table_len as table_size,tuple_len as estimated_size from pgstattuple(‘sample’);

 table_size | estimated_size 

————+—————-

     843776 |         267452

DEVL=# vacuum sample;

VACUUM

DEVL=# analyze sample;

ANALYZE

Started reusing the space because the table is vacuumed

DEVL=# UPDATE sample SET entity_ind = ‘E’ WHERE entity_ind = ‘I’;

UPDATE 1999

DEVL=# select table_len as table_size,tuple_len as estimated_size from pgstattuple(‘sample’);

 table_size | estimated_size 

————+—————-

     843776 |         267452

(1 row)

Oracle: Update

There is no bloat, Oracle uses undo segments for old version of records

SQL> UPDATE sample SET entity_ind = ‘T’ WHERE entity_ind = ‘I’;

1999 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(”,’SAMPLE’);

PL/SQL procedure successfully completed.

SQL> select blocks*8*1024 as table_size,num_rows*avg_row_len as estimated_size from user_tables where table_name = ‘SAMPLE’;

TABLE_SIZE ESTIMATED_SIZE

     ———-             ————–

    262144   217891

PostgreSQL: DELETE— fragmentation

DEVL=# delete from sample where de_user=’NA’;

DELETE 1752

DEVL=# select table_len as table_size,tuple_len as estimated_size from pgstattuple(‘sample’);

 table_size | estimated_size 

————+—————-

     843776 |          34896

(1 row)

DEVL=# vacuum full sample;  —Helps to release waste space back from segment or table and also creates new table file

VACUUM

DEVL=# analyze sample;

ANALYZE

DEVL=# select table_len as table_size,tuple_len as estimated_size from pgstattuple(‘sample’);

 table_size | estimated_size 

————+—————-

      40960 |          34896

Oracle -DELETE — Creates fragmentation

SQL> delete from sample where de_user=’NA’;

1752 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(”,’SAMPLE’);

PL/SQL procedure successfully completed.

SQL> select blocks*8*1024 as table_size,num_rows*avg_row_len as estimated_size from user_tables where table_name = ‘SAMPLE’;

TABLE_SIZE ESTIMATED_SIZE

———- ————–

    262144     28899

SQL> alter table sample move; —- Helps reclaiming space in a table. It recovers space above and below the high-water mark

Table altered.

SQL> exec dbms_stats.gather_table_stats(”,’SAMPLE’);

PL/SQL procedure successfully completed.

SQL> select blocks*8*1024 as table_size,num_rows*avg_row_len as estimated_size from user_tables where table_name = ‘SAMPLE’;

TABLE_SIZE ESTIMATED_SIZE

———-                 ————–

     40960   28899

Note: Didn’t consider overhead space used by a block or page both in PostgreSQL and Oracle.