Search code examples
ruby-on-railspostgresqlherokuheroku-postgres

Postgres column too big to clear out

A little less then 2 hours ago I notice a big issue in my code. Because of an accidental infinite for loop I filled a column List.keywords in my database with a gigantic piece of text. Everything blocked.

When I realised the mistake I changed my code to empty that column on each save of the records. Everything kept blocking.

I tried to migrate the database by using rails migration remove_column :lists, :keywords. That migration failed too.

Although that last migration must have done something as now the code is fast again. Except for Lists (where the column keywords gave me the issue). When saving a List, it takes about 5 seconds to do so. Reading it is fast.

If I do heroku pg:psql and check out the lists table, the keywords column is still there, so I'm guessing that's where all the data still is and why the table is so slow.

How would you guys resolve this?

Solution

  • The problem was that calling a List record would try to load this one into memory. The column was so huge that even this would fail in Rails.

    My resolution after talking to a Postgres Engineer:

    I logged into my postgres production console on heroku using heroku pg:psql -a <myapp>. I checked out what the value was for List 2203

    SELECT "keywords"
FROM "lists"
WHERE id = 2203;

    I saw that there was a huge chunk of text in this and was very happy because this was the first time I could actually see the problem. To start I wanted to clear this list

    -- Doing this in a transaction, so if I don't like what happened, I can call ROLLBACK; to start over
BEGIN;
UPDATE "lists"
WHERE id = 2203
SET keywords = '';

SELECT "keywords"
FROM "lists"
WHERE id = 2203;

-- I was happy with the result as it was empty, so I committed the transaction
COMMIT;
-- If you're not happy, do ROLLBACK; to start over

    Alright, this was one List done, time to do the all of them

    -- not setting the WHERE as I want to do this on all of them.
BEGIN;
UPDATE "lists"
SET keywords = '';

-- here you can SELECT to do some checks at some records, see if they are empty.

COMMIT;

    Voila, my Rails app started going fast again and nothing clogged up anymore.

    Hope this can help somebody in the future. Probably me.