PostgreSQL database compression.

PostgreSQL database compression.

When using PostgreSQL in O&K Print Watch and configuring to save the contents of printed documents, the size of the database on disk can be very large. Use a size limit to store printed documents in O&K Print Watch.

One of the features of PostgreSQL is the accumulation of “dead” records that occur as a result of data updates and deletions. To optimize database performance and free up the occupied space, you should periodically use the VACUUM command.

Why is the database size not decreasing when records are deleted?

When records are deleted in PostgreSQL, they are not physically removed from disk immediately. Instead, they are marked as “dead” and become unavailable for future queries. Physically deleting dead records and freeing up the space they occupy is done only with the VACUUM FULL command.

It is important to realize that standard VACUUM frees space within a table for reuse, but this space is not returned to the operating system. Only VACUUM FULL compresses tables and reduces their physical size on disk, returning unused space.

What does VACUUM do?

The VACUUM FULL command performs a database cleanup, removing dead records and freeing up space that can then be used for new operations. Its execution returns the unused space to the operating system.

This process requires exclusive table locking, which makes it unavailable to other operations for the duration of its execution. It is recommended to run the PRNWATCH database compression process during off-hours when no users are printing.

Example of using VACUUM FULL in pgAdmin:

  1. Open pgAdmin and connect to the PRNWATCH database.

  2. Right-click on the table and select “Maintenance...”.

  3. In the window that opens, select the “VACUUM” option.

  4. Set the FULL and ANALYZE switches.

  5. pgAdmin will execute the operation and report on its completion.

An example of running VACUUM FULL using psql:

  1. On the command line, set the PGPASSWORD environment variable and assign the current postgres user password to it: 
    set PGPASSWORD = password
  2. Start compression: 
    psql -U postgres -d PRNWATCH -c "VACUUM FULL VERBOSE;"
  3. Start reindexing: 
    psql -U postgres -d PRNWATCH -c "REINDEX DATABASE PRNWATCH;"

IMPORTANT: When running VACUUM FULL, PostgreSQL temporarily creates a full copy of the table, which requires free space on the database disk.

An example of reducing the size of a database using Backup and Restore:

If the database has accumulated a lot of unused space, but the VACUUM FULL operation is unavailable due to lack of disk space or downtime, you can use the Backup and Restore strategy to reduce the size of the database on disk.

Steps to perform Backup and Restore using pgAdmin:

  1. Open pgAdmin and connect to the database server.

  2. Right-click on the PRNWATCH database and select “Backup...”.

  3. In the window that opens, specify the name of the backup file and select the “Custom” format.

  4. On the Data Options tab, use the Blobs switch to include the contents of printed documents in the backup.

  5. Click “OK” to create a backup.

  6. Delete the existing database:
    • Right-click on the database and select “Delete ”.
    • Confirm the deletion.

  7. Create a new database:
    • Right-click on the server and select “Create -> Database...”.
    • Enter the name of the new PRNWATCH database and click “Save”.

  8. Restore the data from the backup:
    • Right-click on the new database and select “Restore...”.
    • In the window that opens, select the backup file and click “Restore”.

  9. After restoring the PRNWATCH database, you must restart the O&K Print Watch Service.
Using PostgreSQL as a database server in O&K Print Watch.