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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
