Just wanted to thank you @raba1der and @rockstream for this!
My database had gotten to be 40GB… so when I tried running the previously suggested DELETE FROM statistics_bandwidth WHERE account_id IS NULL, it was still going after 4 hours. With yours, I was able to get everything resolved within an hour.
For any Windows users using this method, it looks like some of the characters in the commands under ‘Recreate the indexes’ were breaking when I pasted them into CMD.
I rewrote them below (and wrapped everything in script tags):
-- Create a new table without the unwanted rows (Took about ~20 minutes for a 40GB DB).
CREATE TABLE statistics_bandwidth_new AS SELECT * FROM statistics_bandwidth WHERE account_id IS NOT NULL;
-- Drop the old bloated table (Took about ~40 minutes for a 40GB DB).
DROP TABLE statistics_bandwidth;
-- Rename the new compacted table.
ALTER TABLE statistics_bandwidth_new RENAME TO statistics_bandwidth;
-- Recreate the indexes (Thanks to @rockstream).
CREATE INDEX index_statistics_bandwidth_on_at ON statistics_bandwidth ('at');
CREATE INDEX 'index_statistics_bandwidth_on_account_id_and_timespan_and_at' ON 'statistics_bandwidth' ('account_id', 'timespan', 'at');
-- Vacuum the db to reclaim space. (This step is where you'll actually notice the DB size reduction.)
vacuum;
Again, appreciate the work you put in on this!
