The database has become somewhat heavy

[Changaco]

The size of our DB backups is currently slightly above 150MB. It's not gigantic, but it could be smaller.

Below is some information on the size of each table:

SELECT table_name
     , pg_size_pretty(pg_table_size(table_name::regclass)) AS table_size, s.n_live_tup, s.n_dead_tup
     , round(pg_table_size(table_name::regclass)::numeric / (s.n_live_tup + s.n_dead_tup)) as average_bytes_per_row
  FROM information_schema.tables t
  JOIN pg_stat_user_tables s ON s.relname = t.table_name AND s.schemaname = t.table_schema
 WHERE t.table_schema = 'public'
   AND (s.n_live_tup > 0 OR s.n_dead_tup > 0)
 ORDER BY pg_table_size(table_name::regclass) DESC;

       table_name        | table_size | n_live_tup | n_dead_tup | average_bytes_per_row 
-------------------------+------------+------------+------------+-----------------------
 elsewhere               | 53 MB      |      27338 |          0 |                  2032
 repositories            | 43 MB      |      45515 |          0 |                   999
 transfers               | 27 MB      |     197458 |          0 |                   144
 participants            | 13 MB      |      33882 |          0 |                   404
 notifications           | 12 MB      |      27069 |          0 |                   462
 events                  | 6712 kB    |      54731 |          0 |                   126
 statements              | 5512 kB    |       5313 |          0 |                  1062
 tips                    | 5144 kB    |      31589 |          0 |                   167
 cash_bundles            | 4760 kB    |      40249 |          0 |                   121
 exchanges               | 1896 kB    |       8337 |          0 |                   233
 user_secrets            | 1568 kB    |      12640 |          0 |                   127
 emails                  | 1240 kB    |      12642 |          0 |                   100
 exchange_events         | 1096 kB    |      10924 |          0 |                   103
 exchange_routes         | 888 kB     |       6017 |          0 |                   151
 takes                   | 696 kB     |       3114 |          0 |                   229
 wallets                 | 424 kB     |       3760 |          0 |                   115
 mangopay_users          | 336 kB     |       5694 |          0 |                    60
 payday_transfers        | 304 kB     |       2055 |          0 |                   151
 payin_transfers         | 232 kB     |        912 |          0 |                   260
 balances_at             | 208 kB     |       2206 |          0 |                    97
 payins                  | 208 kB     |        927 |          0 |                   230
 rate_limiting           | 200 kB     |        565 |          0 |                   362
 subscriptions           | 160 kB     |       1055 |          0 |                   155
 email_blacklist         | 160 kB     |        328 |          0 |                   500
 community_memberships   | 152 kB     |       1765 |          0 |                    88
 payin_events            | 136 kB     |       2044 |          0 |                    68
 payment_accounts        | 120 kB     |        650 |          0 |                   189
 oauth_apps              | 104 kB     |        299 |          0 |                   356
 paydays                 | 104 kB     |        142 |          0 |                   750
 communities             | 80 kB      |        419 |          0 |                   196
 payin_transfer_events   | 64 kB      |        467 |          0 |                   140
 currency_exchange_rates | 56 kB      |         64 |          0 |                   896
 redirections            | 56 kB      |        123 |          0 |                   466
 db_meta                 | 48 kB      |          1 |          0 |                 49152
 app_conf                | 48 kB      |         73 |          0 |                   673
 invoices                | 48 kB      |          9 |          0 |                  5461
 disputes                | 48 kB      |         18 |          0 |                  2731
 invoice_events          | 48 kB      |         23 |          0 |                  2137
 debts                   | 48 kB      |         58 |          0 |                   847
(39 rows)

(All the n_dead_tup values are zero because I ran a VACUUM ANALYZE.)

Relevant issues: #1061 and #135.

[Changaco]

1300 was successful in removing a significant amount of junk data, but for some reason the elsewhere table is now 10MB bigger than before according to PostgreSQL, even after another VACUUM ANALYZE. :confused: