git.8kb.co.uk Git - postgresql/pg_update_chunks/blob - full_table_vs_chunks.txt

   1 Before:
   2
   3 SEE=# \dt+ emailad
   4                        List of relations
   5   Schema   |  Name   | Type  |   Owner   |  Size  | Description
   6 -----------+---------+-------+-----------+--------+-------------
   7  customers | emailad | table | pgcontrol | 436 MB |
   8 (1 row)
   9
  10 SEE=# \di+ emailad*
  11                                         List of relations
  12   Schema   |              Name              | Type  |   Owner   |  Table  |  Size  | Description
  13 -----------+--------------------------------+-------+-----------+---------+--------+-------------
  14  customers | emailad_index00                | index | pgcontrol | emailad | 72 MB  |
  15  customers | emailad_index01                | index | pgcontrol | emailad | 144 MB |
  16  customers | emailad_index02                | index | pgcontrol | emailad | 129 MB |
  17  customers | emailad_index03                | index | pgcontrol | emailad | 101 MB |
  18  customers | emailad_index04                | index | pgcontrol | emailad | 101 MB |
  19  customers | emailad_index05                | index | pgcontrol | emailad | 101 MB |
  20  customers | emailad_index06                | index | pgcontrol | emailad | 129 MB |
  21  customers | emailad_index_auto_past_booker | index | pgcontrol | emailad | 144 MB |
  22  customers | emailad_lower_address          | index | pgcontrol | emailad | 144 MB |
  23 (9 rows)
  24
  25 Full update:
  26
  27 SEE=# \dt+ emailad;
  28                        List of relations
  29   Schema   |  Name   | Type  |   Owner   |  Size  | Description
  30 -----------+---------+-------+-----------+--------+-------------
  31  customers | emailad | table | pgcontrol | 871 MB |
  32 (1 row)
  33
  34 SEE=# \di+ emailad*
  35                                         List of relations
  36   Schema   |              Name              | Type  |   Owner   |  Table  |  Size  | Description
  37 -----------+--------------------------------+-------+-----------+---------+--------+-------------
  38  customers | emailad_index00                | index | pgcontrol | emailad | 167 MB |
  39  customers | emailad_index01                | index | pgcontrol | emailad | 289 MB |
  40  customers | emailad_index02                | index | pgcontrol | emailad | 328 MB |
  41  customers | emailad_index03                | index | pgcontrol | emailad | 249 MB |
  42  customers | emailad_index04                | index | pgcontrol | emailad | 235 MB |
  43  customers | emailad_index05                | index | pgcontrol | emailad | 233 MB |
  44  customers | emailad_index06                | index | pgcontrol | emailad | 308 MB |
  45  customers | emailad_index_auto_past_booker | index | pgcontrol | emailad | 289 MB |
  46  customers | emailad_lower_address          | index | pgcontrol | emailad | 290 MB |
  47 (9 rows)
  48
  49 1000 updates with vacuums (will have to be reindexed but otherwise much better):
  50
  51 SEE=# \dt+ emailad*
  52                        List of relations
  53   Schema   |  Name   | Type  |   Owner   |  Size  | Description
  54 -----------+---------+-------+-----------+--------+-------------
  55  customers | emailad | table | pgcontrol | 441 MB |
  56 (1 row)
  57
  58 SEE=# \di+ emailad*
  59                                         List of relations
  60   Schema   |              Name              | Type  |   Owner   |  Table  |  Size  | Description
  61 -----------+--------------------------------+-------+-----------+---------+--------+-------------
  62  customers | emailad_index00                | index | pgcontrol | emailad | 162 MB |
  63  customers | emailad_index01                | index | pgcontrol | emailad | 144 MB |
  64  customers | emailad_index02                | index | pgcontrol | emailad | 267 MB |
  65  customers | emailad_index03                | index | pgcontrol | emailad | 225 MB |
  66  customers | emailad_index04                | index | pgcontrol | emailad | 227 MB |
  67  customers | emailad_index05                | index | pgcontrol | emailad | 226 MB |
  68  customers | emailad_index06                | index | pgcontrol | emailad | 250 MB |
  69  customers | emailad_index_auto_past_booker | index | pgcontrol | emailad | 144 MB |
  70  customers | emailad_lower_address          | index | pgcontrol | emailad | 144 MB |
  71 (9 rows)