Vivek Khera writes:
how many actual pages of data (and index) do you have? you want to
see how many relpages you're relations are using in the pg_class
view.
In 1 database:
select relname,relpages,reltuples from pg_class order by reltuples desc
limit 7;
relname | relpages | reltuples
----------------------------------+----------+-------------
pg_toast_988195_index | 148159 | 3.87392e+07
pg_toast_988195 | 9551848 | 3.87391e+07
pg_toast_988154_index | 123991 | 3.24807e+07
pg_toast_988154 | 8113192 | 3.24807e+07
msgrcpnts_mssgid_recpttp_recptid | 11192 | 2.52415e+06
messagerecipients | 16078 | 2.52415e+06
messagerecipients_pkey | 6375 | 2.52415e+06
In another:
select relname,relpages,reltuples from pg_class order by reltuples desc
limit 7;
relname | relpages | reltuples
---------------------------+----------+------------
pg_toast_2352480 | 14902626 | 3.0651e+07
pg_toast_2352480_index | 222896 | 3.0651e+07
archiveque_host | 11848 | 831494
archiveque_messageid | 13223 | 831494
archiveque_pkey | 4732 | 831494
archiveque | 161097 | 831494
archiveque_archive_que_id | 5119 | 831494
i would venture to say if you have deleted rows in every
single page, you'd want at least one entry per page of your table(s).
So about 15 million according to the above?
once you're done with your migration, you should consider running a
cluster on the big tables
Plan to.
Data will divide nicely by dates. All searches will
have date parameters.
After the migration is done and it is stable I should bring the value down?
