Re: weird table sizes

On Saturday, July 23, 2011 12:18:25 pm MirrorX wrote:
> thx for the reply :)
>
> the table are identical, and i mean that they have the same columns, the
> same constraints, the same indexes etc
>
> 1) the small table(65gb) is on version 8.4.7 and the big one(430gb) on
> 8.4.4 2) the small in on Red Hat 4.1.2-50 and the big on Red Hat 4.1.2-46
> 3) the 2nd was restored from a dump with data in it (my bad for the msg
> before where i said that it was just the schema). now it is running and
> has more and more data but the total rowcount is the one i gave, around
> 250million rows for that table

So where was the data dumped from? Did it come from the original table?

> 4) the data in these tables are 2 columns with dates, 10 integers and some
> varchar columns. these columns are varchar(128) but i checked and they data
> in there are far less and almost the same on the 2 tables. so if the
> varchar is working "properly" and gets only the actual size of the string
> inside, then the difference in the sizes of the 2 tables is not coming
> from these columns.
> 5) i dont know why the sequence jumped. is there anything i can run to find
> that? i just saw that it started from 9 billions (the min(id) for that
> table is 9billions)

One place to look is the dump file you seeded the second database with. Look for
the sequence name. Usually there is a CREATE SEQUENCE followed by a SELECT
setval(sequence_name) that actually sets the sequence value. See if it started
out big or not. Also if comes from the data in table1, does the max(id) for
table1 correspond to min(id) for table2?

Another cause for the jump could be an enormous amount of churn in the second
table i.e. a lot of deletes followed by inserts or failed inserts. These leads
to the next question, has the table been VACUUMed lately?

>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p462657
> 7.html Sent from the PostgreSQL - general mailing list archive at
> Nabble.com.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com