huge table occupation after updates

[Tom DalPozzo <t.dalpozzo@xxxxxxxxx>]

Hi,

I've a table ('stato') with an indexed bigint ('Id') and 5 bytea fields ('d0','d1',...,'d4').

I populated the table with 10000 rows; each d.. field inizialized with 20 bytes.

Reported table size is 1.5MB. OK.

Now, for 1000 times,  I update  2000 different rows each time, changing d0 filed keeping the same length, and at the end of all,  I issued VACUUM.

Now table size is 29MB. 

Why so big? What is an upper bound to estimate a table occupation on disk?

The same test, redone with dX length=200 bytes instead of 20 reports:

Size before UPDATES = 11MB. OK.

Size after UPDATES = 1.7GB . Why?

Attached a txt file with details of statistical command I issued (max of row size, rows count etc....)

Regards

Pupillo

#############  TABLE stato JUST POPULATED: 

ginopino=# select * from stato where id<10;
 id |                     d0                     |                     d1                     |                     d2                     |                     d3                     |                     d4                     
----+--------------------------------------------+--------------------------------------------+--------------------------------------------+--------------------------------------------+--------------------------------------------
  0 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  1 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  2 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  3 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  4 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  5 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  6 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  7 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  8 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  9 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
(10 rows)

ginopino=# select max(pg_column_size(stato)),count(*) from stato;
 max | count 
-----+-------
 137 | 10000
(1 row)

ginopino=# select pg_table_size('stato'),pg_database_size('ginopino');
 pg_table_size | pg_database_size 
---------------+------------------
       1523712 |          9519276
(1 row)


#############  NOW 2 MILLIONS ROWS UPDATE VIA LIBPQ........................

ginopino=# VACUUM;
VACUUM

ginopino=# select * from stato where id<10;
 id |                     d0                     |                     d1                     |                     d2                     |                     d3                     |                     d4                     
----+--------------------------------------------+--------------------------------------------+--------------------------------------------+--------------------------------------------+--------------------------------------------
  0 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  1 | \x0000000053535353535353535353535353535353 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  2 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  3 | \x0000000053535353535353535353535353535353 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  4 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  5 | \x0000000053535353535353535353535353535353 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  6 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  7 | \x0000000053535353535353535353535353535353 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  8 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
  9 | \x0000000053535353535353535353535353535353 | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d | \x2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
(10 rows)

ginopino=# select max(pg_column_size(stato)),count(*) from stato;
 max | count 
-----+-------
 137 | 10000
(1 row)

ginopino=# select pg_table_size('stato'),pg_database_size('ginopino');
 pg_table_size | pg_database_size 
---------------+------------------
      29179904 |         37331116
(1 row)


#################################################################  NOW TABLE DROPPED, RECREATED AND REPOPULATED WITH 200 bytes FIELDS (select * from stato...COMMANDS EXECUTED LIKE PREVIOUS TEST BUT OMITTED IN THIS REPORT, TOO LONG.....)
#############  TABLE stato JUST POPULATED: 
ginopino=# select max(pg_column_size(stato)),count(*) from stato;
 max  | count 
------+-------
 1052 | 10000
(1 row)

ginopino=# select pg_table_size('stato'),pg_database_size('ginopino');
 pg_table_size | pg_database_size 
---------------+------------------
      11739136 |         19595780
(1 row)

############# 2 MILLIONS ROWS UPDATE........................

ginopino=# VACUUM;
ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table "ginopino.public.stato"
VACUUM
ginopino=# select max(pg_column_size(stato)),count(*) from stato;
 max  | count 
------+-------
 1052 | 10000
(1 row)

ginopino=# select pg_table_size('stato'),pg_database_size('ginopino');
 pg_table_size | pg_database_size 
---------------+------------------
    1711095808 |       1724113068
(1 row)

ginopino=# select * from pg_stat_all_tables where relname='stato';
 relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze |          last_vacuum          |       last_autovacuum        | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------------------+------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
 18611 | public     | stato   |        5 |        40000 |  4020005 |       4020014 |     10000 |   2000000 |         0 |        392157 |       5024 |          0 |                   0 | 2016-12-10 12:48:13.206175+01 | 2016-12-10 12:44:39.64471+01 |              | 2016-12-10 12:49:15.581839+01 |            1 |                1 |             0 |                 3
(1 row)

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general