Re: huge file in pg_xlog and base

[Steve Crawford <scrawford(at)pinpointresearch(dot)com>]

Nicola Benaglia wrote:
> Hi,
> I have 6 little dbs, but I see that my base directory needs 213MB and
> log are 114MB.
> Here my folder structure and size:
> 
> 3571    ./base/1
> 3487    ./base/10792
> 4691    ./base/10793
> 3707    ./base/16384
> 16618   ./base/16386
> 0       ./base/64673/pgsql_tmp
> 143697  ./base/64673
> 0       ./base/86171/pgsql_tmp
> 6133    ./base/86171
> 5790    ./base/89667
> 6401    ./base/106022
> 7926    ./base/106521
> 11597   ./base/120343
> 213615  ./base
> 160     ./pg_subtrans
> 0       ./pg_twophase
> 8       ./pg_multixact/members
> 8       ./pg_multixact/offsets
> 16      ./pg_multixact
> 865     ./global
> 16      ./pg_clog
> 0       ./pg_xlog/archive_status
> 114800  ./pg_xlog
> 0       ./pg_tblspc
> 329484  .
> 
> 
> Could you please suggest me how to reduce that space (if possible)?


Have you been vacuuming? Check your vacuum/autovacuum settings and
procedures. Try "vacuum full" and see if that helps. Reindexing can
reduce the index disk-size as well.

Assuming a typical 8k page size, you can run "select relpages*8 as
kbytes, relname, relkind from pg_class order by 1 desc" to see what
tables are to blame for the large disk-size. Try vacuum full
(relkind='r') or reindex (relkind='i') on the large ones and see what
happens.

Queries that update all rows will cause rapid bloat since every updated
row will be duplicated. One mistake I've seen is apps that have some
sort of flag, perhaps a "processed" flag. Some process looks at the
table then does an "update foo set processed = true" on a table where
only a tiny fraction is not processed. Adding "where not processed" can
reduce the bloat associated with this type of update.

Cheers,
Steve