Postgresql – Disk space efficient foreign key index for insert intensive scientific database

disk-spaceindexpostgresql

I'm working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is starting to cause some problems since the table size is 235 GB with index sizes of 261 GB, and the server only has 800 GB so we would like to free up a bit of space.

Currently there is one foreign key reference (integer data) that is stored as a clustered b-true. This has been good for the summary queries, but likely isn't helping the disk space issues.

Is there a more disk efficient way of storing this foreign key index? Would it make sense to switch over to a hash index instead of the b-tree index?

Best Answer

The referencing columns don't need to be indexed at all. If not, then some operations on the referenced table might be extremely slow (e.g. verifying that a row to be deleted on the referenced table has no referencing rows, or if it does than cascading actions to them) but if you never do those operations it wouldn't matter.

Related Solutions

Mongodb – How do databases store index key values (on-disk) for variable length fields

You can store your index as a list of fixed-size offsets into the block containing your key data. For example:

+--------------+
| 3            | number of entries
+--------------+
| 16           | offset of first key data
+--------------+
| 24           | offset of second key data
+--------------+
| 39           | offset of third key data
+--------------+
| key one |
+----------------+
| key number two |
+-----------------------+
| this is the third key |
+-----------------------+

(well, the key data would be sorted in a real example, but you get the idea).

Note that this does not necessarily reflect how index blocks are actually constructed in any database. This is merely an example of how you might organise a block of index data where the key data is of variable length.

Postgresql – Freeing disk space after dropped database

Try sudo lsof| grep deleted and check if any PostgreSQL process appears. This command looks for files which have been deleted but its file descriptors are still open by any process. Another side effect is that df -h and du -sh / differs. This is because du looks at the file system and sum up the size of all files, and df looks at the physical device.

I have just had an issue with a database which didn't free any space after a DROP table and that was the cause.

The only solution I know is to restart the database. Maybe you can try to send a reload (SIGHUP).

Best Answer

Related Solutions

Mongodb – How do databases store index key values (on-disk) for variable length fields

Postgresql – Freeing disk space after dropped database

Related Question