PostgreSQL-- several small tip for performance optimization

1. Reclaim disk space

In PostgreSQL, rows deleted or updated using delete and update statements are not actually deleted, but are simply set to be deleted or expired on the physical address of the old version of the row. Therefore, when the data in the data table changes very frequently, the space occupied by the table will become very large after a period of time, but the amount of data may not change much. To solve this problem, you need to perform regular VACUUM operations on data tables where the data changes frequently. Now the new version of PostgreSQL automatically executes VACUUM.

Use the VACUUM and VACUUM FULL commands to reclaim disk space

Postgres=# vacuum arr_test

Postgres=# vacuum full arr_test

Create test data: postgres=# create table arr (id serial, value int, age int) # create test table postgres=# insert into arr (value, age) select generate_series (1, 1000000) as value, (random () * (10 ^ 2)):: integer; # insert 100W test data postgres=# select pg_relation_size ('arr') # View table size pg_relation_size-44285952 (1 row) postgres=# delete from arr where id=300000 and id