Res: Res: Archive files growth!!!

---

De: Craig Ringer <craig@postnewspapers.com.au>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: GENERAL <pgsql-general@postgresql.org>
Enviadas: Sábado, 15 de Novembro de 2008 5:53:12
Assunto: Re: Res: [GENERAL] Archive files growth!!!

paulo matadr wrote:
>
> Let me see, for example one insert type (inser into table2 (select *
> from table1), where table have size  26megas,make 226megas for archives
> files.
> (i made a test with parameter wal_bufffer before this is defaul value
> 64k and the same commando make 640megas of archives after modify to
> 1024k a take 226m)
> however continuous confused for me.
> how 26mega of insert generate 226mega of archive.

OK, that's interesting, since those should be full WAL archives, so your
problem isn't to do with unused WAL tails.

When you say that the table is 26 MB, how did you measure that? Is that
the size of a dump of the table using pg_dump ? If so, that is not the
real size of the table as it is actually stored by PostgreSQL.

The best way to find the size of the table is by asking PostgreSQL:

SELECT relname, reltuples, relpages, relpages*8 AS size_kb
FROM pg_class WHERE relname = 'tablename' ORDER BY relpages DESC ;

(I'm assuming that your database uses the default 8kb page size).

This will be quite a bit bigger than the size of the dump.

I am also fairly sure that the WAL contains a record of what is done to
the indexes as well as to the tables. If you have lots of indexes,
especially multi-column indexes, on the table you are interested in then
the WAL files generated by an INSERT will be a lot bigger than the
amount of data inserted.

It might help if you could upload some example data and SQL somewhere.
Right now it is not possible to see what you are doing, so there is a
lot of guesswork involved.

--
Craig Ringer