Why is my database 5 times larger in size than all the individual files combined?

All we need is an easy explanation of the problem, so here it is.

I have 1000 csv files that have a total size of 200 MB.

When I import these files into a new database table, the final size is 1 GB.

Why is this happening, and how do I reduce the size of my database?

I’ve tried using data type constraints to the columns such as using integers instead of varchar where possible, but it doesn’t seem to reduce the size to anywhere near 200 MB.

I’m using Postgres 10.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The size of the database will depend greatly on a number of factors, including:

  • Column data types
  • Column collations
  • Indexes
  • Transaction logs
  • Undo logs
  • Stored procedures
  • Functions
  • and on and on and on

If the goal is to have the smallest possible footprint on the storage device, then all of these things need to be considered. If the goal is to have a decently-indexed, performant database, then sometimes a little extra storage consumption is necessary. Many of the database backups at my employer are about 110GB uncompressed, but consume close to three times this when loaded into the database.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply