current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

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?

share|improve this question
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
up vote 1 down vote

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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