PostgreSQL table expansion terminator

After a PostgreSQL database table is deleted, the disk space is not released. What can I do? What are the main compressed table tools? How to choose?

1, from the space is not released

Recently, a table in the production environment has occupied 2T, and records will be deleted regularly. However, the space has not been released, why?

The reason is VACUUM, and how vacuum stores and cleans data can be checked by referring to the official document. www.postgresql.org/docs/curren…

What can I do if the table keeps expanding? The beauty of the open source community is that there are many tools available to solve this problem, and this problem has two main tools: PG_repack and pgcompacttable

2. Tool comparison

2.1 pg_repack

Pg_repack creates a new table and copies the historical data from the original table to the new table. To prevent the table from being locked during the copy, an additional log table is created to record changes to the original table, and a trigger involving INSERT, UPDATE, and DELETE operations is added to synchronize the changes to the log table. When all data from the original table is imported into the new table, indexes are rebuilt, and log table changes are complete, pg_repack replaces the old table with the new table and drops the old table. This tool process is simple and reliable, requiring only extra disk space to report errors for temporarily created intermediate tables.

2.2 pgcompacttable

Pgcompacttable takes advantage of an interesting feature of PostgreSQL: when performing INSERT and UPDATE operations, all rows of new versions are moved to the first available space of the table. This is key to the pgCompacttable tool, because if all rows are updated backwards from the end, eventually all available space is filled with these rows and all space at the end of the table is freed up to allow periodic VACUUM to perform truncate. In this way, pgCompacttable forces movement through batch updates and vacuum, and eventually the entire table is rearranged to compress. This tool has low disk space requirements and controllable performance impact.

2.3 contrast

In order to facilitate our choice of tools, a simple comparison for reference.

Summary: Since disk space is limited in many scenarios, PGcompacttable is often used. This section describes how to install and use pgcompacttable.

3. Deploy and use instances of pgcompacttable

3.1 add pgstattuple

Pgstattuple must be added to pgCompacttable because pgStattuple is required to be used. Postgresql was installed in source contrib, so compile and install postgresql-contrib.

yum install perl-Time-HiRes perl-DBI perl-DBD-Pg -y
cd contrib/
make
make  install
Copy the code

Several files are generated when the compilation is complete

lib/pgstattuple.so

share/extension/pgstattuple*

Then add pgStattuple to the database you want to use

psql -d testdb

testdb=# create extension if not exists pgstattuple;
CREATE EXTENSION
Copy the code

3.2 deployment pgcompacttable

After downloading the dependency and installation package, you can use it

\# yum install perl-Time-HiRes perl-DBI perl-DBD-Pg -y
# su - postgres
$ git clone https://github.com/dataegret/pgcompacttable.git
Copy the code

**3.3 pgcompacttable uses **

Pgcompacttable compresses database, Schema, and table levels

./pgcompacttable -h localhost -U postgres -d testdb
./pgcompacttable -h localhost -U postgres -d testdb  -n public
./pgcompacttable -h localhost -U postgres -d testdb -n public -t test\_table1
Copy the code

Highlights from the past

MySQL high availability MHA cluster deployment mysql8.0 new users and encryption rule changes those things

Monitoring tools: Prometheus+Grafana monitors MySQL and Redis databases

MySQL sensitive data encryption and decryption

MySQL > restore MySQL

MySQL database backup and restore (2)