1

I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the same no matter what I do.

I'm obtaining the size by issuing \dt+ in psql.

I've tried VACUUM FULL and dp_dumpall followed by deleting the database and restoring it.

The size of the table did not change.

I added a second TEXT column, watched the size increase by a few hundred MB, deleted the column, and I cannot get the size to go down again.

How can I get the size of the table to go down after deleting these columns?

  • 2
    VACUUM FULL rewrites the table. If there's less data in the table then it would've shrunk. Did you actually DROP the column? Are you sure you vacuumed the right column? – Craig Ringer Dec 13 '15 at 9:47
  • Please show the actual table definition and some sample rows. And always your version of Postgres. There might be padding effects to swallow up to 7 additional bytes. Related: dba.stackexchange.com/a/23933/3684 and dba.stackexchange.com/a/37032/3684 – Erwin Brandstetter Dec 13 '15 at 13:10
1

TEXT type columns arent stored with the table data. PostgreSQL treats them like a blob and use a toast table to store the actual data. Maybe thats the reason that you are not seeing the table size shrink.

  • 1
    When using \dt+, the size reported will include size of the TOAST table. – jjanes Dec 15 '15 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.