[GENERAL] huge table occupation after updates

Sat, 10 Dec 2016 04:16:12 -0800 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to