Re: database is bigger after dump/restore - why? (60 GB to 109 GB)

On Sun, Feb 27, 2011 at 2:52 AM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
Thank you for your kind replies.

> I noticed in your table definition that you seem to store timestamps in text-fields. Restoring those from text-fields shouldn't make any difference, but perhaps your locales are set up differently between the machines and cause some type of conversion to take place?

OK, Alban, I'm game. How would I check how locales are set up?

Adrian, I found pg_indexes_size() is only in 9 (I have 8.4) but I got
the same information from a query based on
http://www.issociate.de/board/post/478501/How_much_space_do_database_objects_take_up_in_data_files.html

I used:

SELECT nspname, relname,
pg_size_pretty(tablesize) AS tablesize, pg_size_pretty(indexsize) AS
indexsize, pg_size_pretty(toastsize) AS toastsize,
pg_size_pretty(toastindexsize) AS toastindexsize
FROM
(SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size(reltoastrelid)
END AS toastsize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
WHERE ct.oid = cl.reltoastrelid))
END AS toastindexsize
FROM pg_class cl, pg_namespace ns
WHERE cl.relnamespace = ns.oid
AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
AND cl.relname IN
(SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE')) ss
ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;

Here is what I see:

nspname | relname | tablesize
| indexsize | toastsize | toastindexsize
------------------------+----------------------------------+------------+------------+------------+----------------
public | big | 744 MB
| 737 MB | 48 GB | 278 MB
public | big | 503 MB
| 387 MB | 99 GB | 278 MB

Check out that toastsize delta. What makes up TOAST? How can I
compare the two TOAST tables in detail?

Tom suggested pgstattuple:

table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
779689984 | 1628348 | 500584290 | 64.2 |
30111 | 8275133 | 1.06 | 243295444 |
31.2 <-- database A (source, 50 GB)
527835136 | 1628348 | 500584290 | 94.84 |
0 | 0 | 0 | 9492072 | 1.8
<-- database B ( target, 100 GB)

I used "dumpe2fs" to check the filesystems - block size is 4096 on both servers.

One filesystem is on a hardware raid device, and one is on a software
raid device.

Thanks,
Aleksey