0

I have a table with a TEXT field not null that contains logs.

After updating all records older than X to an empty string ``, when I compare select pg_size_pretty(pg_relation_size('my_table')); before and after the update, the table size actually increased a bit.

PS: the table have 880k rows and 511k rows were updated to "". Also, this field isn't part of index.

Improve this question
9
  • Why not set to NULL instead, or even try deleting them? You'll have to change your schema definition. Commented Jan 7, 2019 at 19:56
  • 3
    Each update inserts a new row and makes the old one invisible. Read also this answer about unused disk space. Commented Jan 7, 2019 at 20:03
  • I can't delete the rows as it has more information that still relevant. I was just trying to reduce table size without changing table structure. I know changing to NULL should be better or maybe even creating a separate table only to hold this column and making a FK(then I could delete these instances). But shouldn't updating to an empty string reduce size as well? Commented Jan 7, 2019 at 20:04
  • Try vacuuming the table and see if it reduces in size. Commented Jan 7, 2019 at 20:14
  • @tadman NULL means "unknown", which doesn't seem to be a good fit here. Commented Jan 8, 2019 at 7:23
Related questions
2
PostgreSQL database size increasing
22
Database table size did not decrease proportionately
0
PostgreSQL Size doubled
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.