How to Handle Large Database Files in PostgreSQL 12 and Prevent Disk Crashes

Discover solutions to manage large database files in PostgreSQL 12, protect your disk space, and optimize performance without data loss.
---
This video is based on the question https://stackoverflow.com/q/65215166/ asked by the user 'Marcin Zdunek' ( https://stackoverflow.com/u/9832911/ ) and on the answer https://stackoverflow.com/a/65216052/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PostgreSQL 12 - large cache files in "base" directory

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Managing Large Database Files in PostgreSQL 12: A Comprehensive Guide

PostgreSQL is a powerful relational database management system that offers various features, but it can sometimes behave unexpectedly, especially when it comes to disk usage. If you find yourself facing issues with large files in your database's base directory, especially after noticing that your 8MB database has ballooned to occupy significant disk space, you're not alone. In this guide, we’ll explore why these files exist, how to manage them, and ensure your database runs smoothly without crashing your disk.

Understanding the Problem

What You Observed

Users often confuse large files in PostgreSQL with cache files. This confusion can lead to mishaps, like attempting to delete these files manually, which may corrupt your database. In your case, you specified that the folder /var/lib/postgresql/12/main/base/16384 contains multiple 1GB files, threatening your disk space capacity and leading to crashes.

Why it Happens

The files located in the base directory are not temporary cache files. They represent the actual tables and indexes in your database. When these files grow excessively large, it can be attributed to various factors, including bloated tables or indexes.

Steps to Identify and Manage Large Files

Step 1: Identify the Database

To address the issue, the first action should be identifying the database associated with the file size you encountered. You can do this by running the following SQL command:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Investigate the Specific File

Once you have identified the database, you can pinpoint the offending file, "16417" in this case, by executing this command:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Identify Bloat

After identifying the database and the specific file, check if the size of that object is disproportionately large. This could denote a possible bloating issue. Tables and indexes can bloat over time as data is inserted, updated, or deleted.

Solutions to Manage Database Size

VACUUM Command

In PostgreSQL, a bloated table can be addressed using the VACUUM command. Be cautious: using VACUUM (FULL) rewrites the table and makes it inaccessible for the duration of the operation. Here is how you can execute it:

[[See Video to Reveal this Text or Code Snippet]]

This process helps to reclaim space from dead tuples, making the database more efficient and reducing file size.

Regular Maintenance Practices

Monitor Database Size: Regularly monitor the size of your tables and indexes to catch any potential bloat early.

Schedule VACUUM Operations: Set up periodic VACUUM operations to keep your database optimized.

Configure PostgreSQL Settings: Adjust your configuration settings to help manage file sizes better and prevent excessive growth:

Consider enhancing the temp_file_limit.

Set appropriate shared_buffers values.

Adjust max_wal_size and min_wal_size settings based on your usage pattern.

Avoid Manual Deletion of Files

Be sure not to manually delete files directly from the PostgreSQL data directory. Doing so can lead to data corruption and potentially a loss of your database integrity.

Conclusion

It's crucial to recognize the nature of large files in PostgreSQL and apply the appropriate management strategies rather than attempting fixes that may lead to further issues. By following the steps outlined in this guide, you can maintain a more efficient and responsive PostgreSQL database, keeping your disk usage under control and ensuring data integrity. Remember, regular monitoring and maintenance are key to PostgreSQL success.

If you have any other questions or concerns, feel free to reach out or leave a comment belo