Re: High Disk write and space taken by PostgreSQL

Hi,

On 16 August 2012 15:40, J Ramesh Kumar <rameshj1977(at)gmail(dot)com> wrote:
> As you said, MySQL with MyISAM is better choice for my app. Because I don't
> need transaction/backup. May be I'll try with InnoDB and find the disk
> write/space difference. Is there any similar methods available in postgresql
> like MyISAM engine ?

You can try unlogged tables:
http://www.postgresql.org/docs/9.1/static/sql-createtable.html

If specified, the table is created as an unlogged table. Data written
to unlogged tables is not written to the write-ahead log (see Chapter
29), which makes them considerably faster than ordinary tables.
However, they are not crash-safe: an unlogged table is automatically
truncated after a crash or unclean shutdown. The contents of an
unlogged table are also not replicated to standby servers. Any indexes
created on an unlogged table are automatically unlogged as well;
however, unlogged GiST indexes are currently not supported and cannot
be created on an unlogged table.

>
>>>> Ahhh but updates are the basically delete / inserts in disguise, so if
>>>> there's enough, then yes, vacuum full would make a difference.
>
> The table which get update has very less data ie, only has 900 rows. Out of
> 10500 tables, only one table is getting update frequently. Is there any way
> to vacuum a specific table instead of whole database ?

You can run "vacuum <table name>" but I doubt if that makes sense to
run it manually when you have 1500 tx / sec. Postgres has HOT updates
which have high change to reuse existing space:

From 8.3 release notes:
Heap-Only Tuples (HOT) accelerate space reuse for most UPDATEs and
DELETEs (Pavan Deolasee, with ideas from many others)
UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs.
Previously only VACUUM could reclaim space taken by dead tuples. With
HOT dead tuple space can be automatically reclaimed at the time of
INSERT or UPDATE if no changes are made to indexed columns. This
allows for more consistent performance. Also, HOT avoids adding
duplicate index entries.

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)