PostgreSQL Database blockchain maintenance/optimize

[TechnoL0g]

Why do you need maintenance?
Over time, the bases in the postgres tend to grow, and begin to move more slowly due to problems with indexing. To solve these problems we use:

REINDEX - operation of reindexing, fixing damaged indexes in the database.

VACUUM - garbage collection. VACUUM recovers the space occupied by the "dead" data. When performing normal operations with data, PostgreSQL does not physically remove data from the tables; this happens with the FULL VACUUM operation.

How to use db maintenance in SmartHoldem Node-A?

Log into the server
execute the following commands and wait for the operations to complete, it may take some time

cd node
sh stop.sh

psql sth_smartholdem
REINDEX DATABASE sth_smartholdem;
vacuum full analyze;
\q

sh update.sh

Maintenance operations are recommended once a month.

[TechnoL0g]

or Create Script:

nano reindex.sh

insert

psql "postgresql://$USER:[email protected]/sth_smartholdem" -c 'REINDEX DATABASE sth_smartholdem'
psql "postgresql://$USER:[email protected]/sth_smartholdem" -c 'vacuum full analyze'

run

sh reindex.sh