Recovering disk spaceMost of the following was learned from this forum post. As tables grow, it can be necessary to compact them to free up disk space. Deleting unnecessary rows won't necessarily free disk space. The space occupied by those rows just becomes available for new rows to consume.

To free up space, you need to truncate (or drop and re-create) the table. If there are constraints referencing some of the rows in the table you want to shrink then you'll have to disable those first.

So, assuming:

you want shrink table FOO

you've deleted the rows you don't need from FOO

table BAR has constraints referencing FOO

-- copy those rows you want to keep into a new tableCREATE TABLE temp_storage_table AS SELECT * FROM foo;-- disable constaints so you can truncateALTER TABLE bar DISABLE CONSTRAINT foo_fk01;-- truncate which will release the disk spaceTRUNCATE TABLE foo;-- copy the rows you want to keep back into the original tableINSERT INTO foo SELECT * FROM temp_storage_table;-- re enable constraintsALTER TABLE bar ENABLE CONSTRAINT foo_fk01;-- remove temp dataDROP TABLE temp_storage_table;

Try this out on a test table first to make sure everything is working as expected.

Depositphotos

Get royalty-free images, photos, vectors, illustrations and videos from the best microstock - Depositphotos.

vsConsole

vsConsole is a tool for developers and testers which lets you easily tail log files on remote servers, monitor the status and version of applications deployed to your environments, and stay informed with broadcast messaging.
Learn more

SSD Cloud Servers‎

Built For Developers. Deploy An SSD Cloud Server In 55 Seconds.
Digital Ocean