Neil,
> I am using a simple PostgreSQL 7.3 database in a soft-realtime
> application.
Then you're not going to like the answer I have for you, see below.
> I have a problem where an update on a record within a (fully indexed)
> table containing less than ten records needs to occur as fast as
> possible.
Have you considered dropping the indexes? On such a small table, they won't
be used, and they are detracting significantly from your update speed.
> Immediately after performing a vaccum, updates take upto 50 milliseconds
> to occur, however the update performance degrades over time, such that
> after a few hours of continuous updates, each update takes about half a
> second. Regular vacuuming improves the performance temporarily, but
> during the vacuum operation (which takes upto 2 minutes), performance of
> concurrent updates falls below an acceptable level (sometimes > 2
> seconds per update).
This is "normal" depending on your platform and concurrent activity. More
frequent vacuums would take less time each. What is your max_fsm_pages set
to? Increasing this may decrease the necessity of vacuums as well as
speeding them up. Also, are you vacuuming the whole DB or just that table?
2 mintues seems like a long time; I can vacuum a 100GB database in less than
4.
> Is there a way to disable this behaviour such that an update operation
> would overwrite the current record and does not generate an outdated
> tuple each time? (My application does not need transactional support).
No. Our ACID Transaction compliance depends on "that behaviour" (MVCC). We
don't offer PostgreSQL in a "non-ACID mode". If your application truly does
not need transactional support, you may want to consider an embedded database
instead, such as BerkeleyDB or SQLite. PostgreSQL has a *lot* of "baggage"
associated with having 99.99% incorruptable transactions.
Alternately, you may also want to take a look at TelegraphCG, a derivative of
PostgreSQL designed to handle "streaming data". They may have already
conquered some of your difficulties for you.
http://telegraph.cs.berkeley.edu/
Were I you, I would start with tuning the database first through
PostgreSQL.conf and a careful look at my hardware usage and DB maintenance.
Then I would consider testing 8.0, which has some specific improvements
designed to address some of the problems you are having. Particularly,
Jan's Background Writer and Lazy Vacuum.
--
Josh Berkus
Aglio Database Solutions
San Francisco
