2

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?

Erwin Brandstetter
  • 146,376
  • 19
  • 347
  • 493
Jon S.
  • 21
  • 1
  • 3
  • 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 09: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: http://dba.stackexchange.com/a/23933/3684 and http://dba.stackexchange.com/a/37032/3684 – Erwin Brandstetter Dec 13 '15 at 13:10

1 Answers1

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.

Pivobispo
  • 111
  • 5