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)?

Thank you,
Nico

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/