CODEWITHSUNDEEP
postgresql
yan479
yan479

Reputation: 3

fast growing table in postgresql

We run postgresql 9.5.2 in an RDS instance. One thing we noticed was that a certain table sometimes grow very rapidly in size.

The table in question has only 33k rows and ~600 columns. All columns are numeric (decimal(25, 6)). After vacuum full, the "total_bytes" as reported in the following query

select c.relname, pg_total_relation_size(c.oid) AS total_bytes
from pg_class c;

is about 150MB. However, we observed this grew to 71GB at one point. In a recent episode, total_bytes grew by 10GB in a 30 minute period.

During the episode mentioned above, we had a batch update query that runs ~4 times per minute that updates every record in the table. However, during other times table size remained constant despite similar update activities.

I understand this is probably caused by "dead records" being left over from the updates. Indeed when this table grow too big simply running vacuum full will shrink it to its normal size (150M). My questions are

but couldn't -- table size remained the same before and after the query.

Upvotes: 0

Views: 2409

Answers (1)

Schwern
Schwern

Reputation: 164829

The problem is having 600 columns in a single table, which is never a good idea. This is going to cause a lot of problems, table size is just one of them.

From the PostgreSQL docs...

The actual storage requirement [for numeric values] is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

So decimal(25, 6) is something like 8 + (31 / 4 * 2) or about 24 bytes per column. At 600 columns per row that's about 14,400 bytes per row or 14k per row. At 33,000 rows that's about 450 megs.

If you're updating every row 4 times per minute, that's going to leave about 1.8 gigs per minute of dead rows.

  1. You should fix your schema design.
  2. You shouldn't need to touch every row of a table 4 times a minute.

You should ask a question about redesigning that table and process.

Upvotes: 2

Related Questions