Whenever a record is deleted, it does not create an extra space in the system. PostgreSQL rather creates what is called a "dead tuple". If there is no more dependency on those tuples by the running transactions, PostgreSQL cleans it up using a process called VACUUM. The space used up by those tuples are sometimes called "Bloat".
However it should be noted that running VACUUM does not actually create any free space in the machine disk, instead it is rather kept by PostgreSQL for future inserts. VACUUM is a non-blocking operation, i.e., it does not create exclusive locks on the tables. Hence, VACUUM process can actually run in parallel to any ongoing transactions to the database. But running VACUUM FULL is a different case and it also locks the tables thereby preventing any further transaction on those tables. More documentation regarding VACUUM can be found here in the PostgreSQL documentation.
PostgreSQL already has settings to configure an autovacuum process. Nowadays, one does not need to think how and when to exceute the PostgreSQL VACUUM, it is done automatically by the database. So let's begin with checking if the autovacuum process if it's on in your case.
postgres=# SELECT name, setting FROM pg_settings WHERE name='autovacuum';
name | setting
------------+---------
autovacuum | on
(1 row)
This tells us that the autovacuum process is already set up. If it's not then one can find the settings in the postgresql.conf file and control when/how the VACUUM daemon runs. Please don't forget to restart the PostgreSQL after any change in the settings in the file.
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 50 # min number of row updates before vacuum
autovacuum_analyze_threshold = 50 # min number of row updates before analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
autovacuum_multixact_freeze_max_age = 400000000
# maximum multixact age
# before forced vacuum
# (change requires restart)
autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
PostgreSQL vacuuming
In order to understand the reason behind the vacuuming process, let's go bit deeper to the PostgreSQL basics. PostgreSQL uses multi-version concurrency control (MVCC) to ensure data consistency and accessibilty in high-concurrency environments. Whenever any transaction begins, it operates in its own snapshot of the database, that means whenever any record is deleted, PostgreSQL instead of actually deleting it, it creates a dead row (called dead tuple). VACUUM process thereby helps in optimising the the resource usage, in a way also helping in the database performance.
VACUUM does the following:
Initiate VACUUM manually for all tables.
postgres=# \c <database_name>;
<database_name>=# VACUUM (VERBOSE, ANALYZE);
Check documentation for more details.
Tracking Dead Tuples
postgres=# \c <database_name>;
<database_name>=# SELECT relname, n_dead_tup FROM pg_stat_user_tables;
relname | n_dead_tup
-----------+------------
table1 | 100
table2 | 280
Space usage by each table
postgres=# \c <database_name>;
<database_name>=# SELECT relname AS "table_name", pg_size_pretty(pg_table_size(pgc.oid)) AS "space_used" FROM pg_class AS pgc LEFT JOIN pg_namespace AS pgns ON (pgns.oid = pgc.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') ORDER BY pg_table_size(pgc.oid) DESC LIMIT 1;
table_name | space_used
-----------------+------------
table1 | 240 MB
(1 row)
Autovacuum executed last time
postgres=# \c <database_name>;
<database_name>=# SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
relname | last_vacuum | last_autovacuum
------------------------+-------------------------------+-------------------------------
table1 | 2019-07-05 11:27:05.612829+00 | 2019-04-27 15:21:59.739417+00
table2 | 2019-07-05 11:27:05.509527+00 | 2019-07-05 08:33:23.747207+00
To check if the autovacuum daemon is running always:
$ps -axww | grep autovacuum
That's it ! Once VACUUM has been executed and then track how many dead tuples are still left, you will find a very significant deacrease in the number of dead tuples in all tables in your database.