Applied Database Systems - Tutorial 8

In response to the disk space problems we encountered recently, there is a little exercise for you to help free up some unneeded disk space.

 Exercise

As some of you have noticed, we ran into disk space problems over the weekend due to the size of the individual databases the database server currently has to manage. You can use the following command to retrieve the current size of your own database (replace 'sXXXXXX' with your user name):

select pg_size_pretty(pg_database_size('sXXXXXX'));

For the project, the size of your database should not exceed the 750 MB mark.

You will not need the tables entry, comment, dbxref, go_term, feature, and omim for the third assignment. Instead, you are expected to work solely with the data in the database that you poplulated from the given data in the second assignment. In order to release to storage space required by the data that was loaded in the first assignment please drop the respective tables before you start working on the last assignment:

DROP TABLE entry CASCADE;
DROP TABLE comment CASCADE;
DROP TABLE dbxref CASCADE;
DROP TABLE go_term CASCADE;
DROP TABLE feature CASCADE;
DROP TABLE omim CASCADE;

Note: You can always recreate the tables from the data on the Web in case of an emergency.

Note: If you have defined any views on the above tables, these views will be deleted as well.

Furthermore, you can drop table ukweather after you completed the previous two assignments. This should free up another +100 MB of disk space.

After dropping the above mentioned tables you should use VACUUM to reclaim storage occupied by deleted tuples:

VACUUM [ table ]

In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables. With no parameter, VACUUM processes every table in the current database. With a parameter, VACUUM processes only that table.

After VACUUM check the size of your database again. It should not exceed 500 MB before you start with the last assignment. If your database is larger than 500 MB you might want to delete additional temporary tables that you created during the previous assignment.

End of Tutorial 8. Please send any corrections or suggestions to Heiko Müller.