Why is the database size larger than sum of table sizes

Question

I wanted to know why database size is a way greater than thesum of table size in a database? How does it change?

Answer 1

why database size is a way greater than the sum of table size in a database?

Databases contain lots of other things besides "just" your data.

Indexes are the largest and most obvious. Every Index contains its key values, possibly some other stuff as well, depending on the Index type, and a pointer to the corresponding Data record.

Every Table, and every Row within every Table, has some overhead associated with it, over and above the actual Data that you or I care about.

Then there's all the stuff that the DBMS creates for itself, like statistics tables, entire schemas for metadata, "code" objects like procedures and functions, and many, many more.

Answer 2

Preliminary analysis - see below for more detail:

To give an illustration to answer your question, I downloaded PostgreSQL source code and compiled and installed it.

The setup after compiling and installing the code is as follows (edited - not all steps are shown here):

    mkdir /usr/local/pgsql/data
    chown postgres /usr/local/pgsql/data
    su - postgres
    /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
    /usr/local/pgsql/bin/createdb test
    /usr/local/pgsql/bin/psql test

Note the mkdir /usr/local/pgsql/data part.

So, after doing the above (a customised version for my own machine), I went into the data subdirectory and did:

du -h

Result:

4.0K    ./pg_tblspc
24M ./base
..
.. complete list snipped for brevity
..
16K ./pg_stat_tmp
4.0K    ./pg_wal/archive_status
17M ./pg_wal
40M .

So, you see that before even creating one single database, there is 40MB of data (small amount of configuration files &c. ignored).

If you search for PostgreSQL system catalogs, you will find the documentation here. At the end of a long list (95) of what are really tables, you will find this passage:

The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL's system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally, one should not change the system catalogs by hand, there are normally SQL commands to do that. (For example, CREATE DATABASE inserts a row into the pg_database catalog — and actually creates the database on disk.) There are some exceptions for particularly esoteric operations, but many of those have been made available as SQL commands over time, and so the need for direct manipulation of the system catalogs is ever decreasing.

So, there are elements that have to be created before one can start adding databases of one's own in order for the system to be able to, for example, reference the new databases and tables.

To take just one - pg_database, I logged in and ran SELECT * FROM pg_database and obtained the following result:

postgres=# SELECT * FROM pg_database;
  oid  |  datname  | datdba | encoding | datcollate |  datctype  | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | d
attablespace |        datacl        
-------+-----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+--
-------------+----------------------
 13674 | postgres  |     10 |        6 | en_IE.utf8 | en_IE.utf8 | f             | t            |           -1 |         13673 |          479 |          1 |  
        1663 | 
     1 | template1 |     10 |        6 | en_IE.utf8 | en_IE.utf8 | t             | t            |           -1 |         13673 |          479 |          1 |  
        1663 | {=c/pol,pol=CTc/pol}
 13673 | template0 |     10 |        6 | en_IE.utf8 | en_IE.utf8 | t             | f            |           -1 |         13673 |          479 |          1 |  
        1663 | {=c/pol,pol=CTc/pol}
(3 rows)

So, you can see that there is already data in these system catalogs before you even create a single database. I suggest that you search for the terms system tables and system catalogs where you should find lots of information. The above result shows PostgreSQL's template databases - also worth understanding.

As a personal, anecdotal factoid, I used to work with Interbase/Firebird and I did something similar with them many moons ago. They have a nice architecture in that each database is a single independent file. I created an empty database and found (to my astonishment at the time - I was wondering the same thing you are) that the new database was a file of (if memory serves) 500KB - it was by asking about this that I learned about system catalogs &c.

EDIT: Detailed analysis.

I performed all of the steps outlined above (initdb, pg_ctl,createdb & psql) and then I created a table and added 2 records, 1 at a time. After each step, I issued the command du -s -B1 in the data directory which gives you the size of the directory (and its subdirectories) to the byte and I followed the changes as these operations were performed.

The results are as follows - notice the changes that take place after each step - except the last one!

After install - nothing done - not even starting server.

du -s -B1

41680896 = 41.68 MB

After starting server

./bin/initdb -D ./data

41693184 - 41.69 MB

Then:

./bin/createdb test

49917952 - 49.91 MB

Followed by:

./bin/psql test

50077696 50.07 MB - no data!

Now, we get down to brass tacks:

CREATE TABLE x (y INT, z TEXT);

50098176 50.1 MB - no data!

Finally, we get to data!

INSERT INTO x VALUES (1, 'abc');

50110464 = 50.11 MB - DATA!!!

Now, I've input an INT (4 bytes) and TEXT (3 bytes - maybe more if it's UTF8 or whatever - let's say 12), so that's 16 bytes.

Why then do I get 12kb more data in the directory?

This is what Laurenz Albe was saying in his answer about the database allocating space in 8kb blocks (this can be varied - and the first record put 12kb in - some additional house-keeping perhaps?).

So, I add another (identical) record:

INSERT INTO x VALUES (1, 'abc');

And now, the result of du -s -B1 is:

50110464 50.11 - we have more data but the result hasn't changed!

Here we see that there is NO difference between the data when I add the same record a second time! The first allocation of a block caused the data directory to increase in size, but the addition of the second record didn't (apparently) do anything because the space for the new record was already allocated!

System tables are being updated all the time with stuff that isn't just your data being inserted - there's a lot of overhead in managing data, ensuring referential integrity &c.

Answer 3

If the "tablesize" you have only counts the data pages in the tables of your database then there will be a huge difference due to the space used by the indexes on each of the tables. If you update the question to include details of how you read and totalled the table sizes we can confirm that. For instance picking a DB at random here it has 1,378 MB allocated, 871 MB of which is used for data pages and 435 MB is used for index pages (this is actually an MS SQL Server database but the concepts being discussed are transferable between many database systems). It is not unusual to see databases where there is more space used by indexes than used by the data itself.

Also note that the figures above leave 72 MB out. This is space that is allocated in the filesystem but is not currently being used by data or index pages. The space is not wasted - it will be used when new space is needed before more is requested from the OS. When data is deleted from a table, something else changes to make the data smaller (string data getting smaller, changes of type perhaps), an index is dropped, or a whole table is dropped, the allocated space is not immediately returned to the filesystem but is instead kept for reuse later as that is generally more efficient. It is only an issue if you have deleted a lot of data and don't expect it to grow back any time soon so want to release the space back to the OS.

If you are calculating the amount of space you expect to see used by totalling the data in each field in each row, then there are a number of other reasons why that figure would be too low. Date is generally held in pages of fixed size so if you have fixed size rows of a length that is not a multiple of the page length then there will be some "wasted" space in each page. For MS SQL page size is always 8KB, this size is typical in Postgres too though IIRC it can vary from 2KB to 32KB.

Also as ordered data grows, if data is not always inserted in-order then page splits will happen. After each split each of the resulting pages will contain about half what was in the original. The newly empty space in each page will usually be filled in later f course, but a very active database will have many "torn" pages at any given time (the figures I quoted from my sample database here are counting whole pages - they don't indicate how much of those pages is actually in use).

There are a number of other overheads too: for instance each page has a header which takes space which therefore can't be used for table or index data. Estimating the space you need for a database containing a given set of data can be a lot less simple than you might naturally first assume!