A trigger to update the row count is needed. The actual row count is important for the application and count(*) by nature is too slow.

The way to implement a proper row counter is to create a trigger on the table which needs to be counted. This trigger will increment or decrement the count on insert or delete.

There are a couple of interesting issues with the row counting implementation. The first is that the counter trigger function can work for both insert and delete and with any table by using trigger information in plpgsql. The second is the issue of initializing the rowcount table.

Suppose you had the table mystuff and set up the table rowcount to hold the row count of many tables.

	CREATE TABLE mystuff (
		name  text NOT NULL,
		description text,
		PRIMARY KEY (name));

	CREATE TABLE rowcount (
		table_name  text NOT NULL,
		total_rows  bigint,
		PRIMARY KEY (table_name));

Most people are familiar with the use of OLD and NEW variables in trigger functions, but there are several other informational variables available.

The count_rows() trigger function uses TG_OP and TG_RELNAME. TG_OP indicates whether it is a DELETE or INSERT and TG_RELNAME is used to store the count in the countrows table by table name. This is the function:

	CREATE OR REPLACE FUNCTION count_rows()
	RETURNS TRIGGER AS
	'
	   BEGIN
	      IF TG_OP = ''INSERT'' THEN
	         UPDATE rowcount
	            SET total_rows = total_rows + 1
	            WHERE table_name = TG_RELNAME;
	      ELSIF TG_OP = ''DELETE'' THEN
	         UPDATE rowcount
	            SET total_rows = total_rows - 1
	            WHERE table_name = TG_RELNAME;
	      END IF;
	      RETURN NULL;
	   END;
	' LANGUAGE plpgsql;

There are many ways the various TG variables can be used to write generic triggers. Different courses of action can be taken based on the exact definition of the trigger. The data in the row can be accessed via NEW and OLD and information about their datatypes is available if decisions or actions need to be done based on the data type.

Now we will look at what is necessary to initialize the trigger. If the trigger function and the tables are all defined in the same transaction, then initialization is not necessary, except for the creation of the row in rowcount for the table in question. However, most of the time if row counting functionality is being added to an existing table, the base row count must be ascertained.

For an existing table, the initialization must be done in a single transaction. The target table is locked to prevent updates during this initialization. Then the trigger is created and the rowcount is inserted with the current row count. Once the initialization transaction is committed, then your counter is operational. Don't forget to test it!

	BEGIN;
	   -- Make sure no rows can be added to mystuff until we have finished
	   LOCK TABLE mystuff IN SHARE ROW EXCLUSIVE MODE;
	
	   create TRIGGER countrows
	      AFTER INSERT OR DELETE on mystuff
	      FOR EACH ROW EXECUTE PROCEDURE count_rows();

	   -- Initialise the row count record
	   DELETE FROM rowcount WHERE table_name = 'mystuff';

	   INSERT INTO rowcount (table_name, total_rows)
	   VALUES  ('mystuff',  (SELECT COUNT(*) FROM mystuff));

	COMMIT;

	--
	-- Testing
	--
	insert into mystuff values ('abacus','mathmatics');
	insert into mystuff values ('bee','insect');
	select * from rowcount;
	insert into mystuff values ('dog','pet');
	insert into mystuff values ('cathedral','building');
	select * from rowcount;
	select * from mystuff;
	delete from mystuff where name='abacus';
	select * from rowcount;
	select * from mystuff;

Contributors: Ling Xiaoyu cdu_lx at yahoo.com Oliver Elphick olly at lfix.co.uk elein at varlena.com

Quick Outline of ErServer Mechanics eRServer 23-Oct-2003

Andrew Sullivan has written up a good overview of the mechanics of the eRServer. This is not a How To guide, but rather a description of what occurrs when the server is set up correctly.

It is very helpful to understanding the mechanics of the replication solution in order to use it to your greatest benefit.

The overview can be accessed through Tidbits or directly HERE

Contributors: andrew at libertyrms.com

7.4 Beta 5 [GENERAL] PostgreSQL v7.4 Beta5 Available for Testing 22-Oct-2003

Beta 5 of PostgreSQL v7.4 has now been released. All testers are encourage to download and use this version. Debian packages are in the experimental part of the Debian archive.

NOTE This version requires an initdb.

The decision to have a Beta 5 was made due to bug fixes which required the initdb. With that type of major change it is not sensible to release without a beta cycle.

We look forward to all of the features and new documentation that will be available in RC1!

Contributors: Marc G. Fournier scrappy at postgresql.org

Fast Port Reports for 7.4 [HACKERS] Call for port reports 25-Oct-2003

Over the last 24 hours or so, about 22 platforms have been certified with PostgreSQL v7.4. The current list is available on Bruce's site.

Very fast definition and correction of problems by Bruce and Peter Eisenstraut and many others helped speed this process. As did all of the people involved in building and running regressions on their platforms.

Knowing what sort of effort this takes in a commercial enterprise I am very impressed with the cooperation, speed and good will invested in this testing.

Contributors: Bruce Momjian pgman at candle.pha.pa.us, Adam Witney awitney at sghms.ac.uk, Peter Eisentraut peter_e at gmx.net, Marko Karppinen marko at karppinen.fi, Larry Rosenman ler at lerctr.org, Kevin Brown kevin at sysexperts.com, Rod Taylor rbt at rbt.ca, Robert Treat xzilla at users.sourceforge.net, Christopher Browne cbbrowne at libertyrms.info, Alvaro Herrera alvherre at dcc.uchile.cl, Kurt Roeckx Q at ping.be, Noèl Köthe noel at debian.org, Hans-Jürgen Schönig hs at cybertec.at, Dave Page dpage at vale-housing.co.uk, Andrew Dunstan andrew at dunslane.net, Johan Henselmans johan at netsense.nl

Linux I/O tuning for VACUUM [GENERAL] VACUUM degrades performance significantly. Database 15-Oct-2003

A discussion of the degradation of query speed during vacuum led to a linux tuning solution. It is common for queries to slow down during vacuum, however, the specific case showed a large slowdown. The initial measurements showed a query running normally in 25.52 msec ran in 2290.22 msec during vacuum.

Initial analysis looked at CPU speed, however, further speculation suggested that the machine was I/O bound. If the system is near I/O saturation, vacuum can push it over the edge.

The use of IDE drives affect this. It seems to be agreed that a SCSI drives would be better, however, many people prefer the less expensive IDE option.

Analysis using vmstat, paying particular attention to the bi/bo columns was suggested to determine if there were actually I/O problems.

The solution pointed to the settings of the linux utility elvtune. This utility tunes the disk I/O elevator in Linux by reordering read/write requests according to disk sectors in order to reduce disk seeks. Different people reported widely differing values from elvtune.

   RH9.0 installation:
   /dev/sda7 elevator ID           5
           read_latency:           64
           write_latency:          8192
           max_bomb_segments:      6

   The default on RH8 is:
   /dev/hda1 elevator ID           0
           read_latency:           2048
           write_latency:          8192
           max_bomb_segments:      0 

   Debian 2.4.23-pre4 box is:
   /dev/hdg elevator ID            3
           read_latency:           128
           write_latency:          512
           max_bomb_segments:      0

The read_latency field changed between 2.4.18-x and 2.4.20-x accounting for the differences between that field in RH8 and RH9.

   The 2.4.18-x elvtune latency values are:
      read_latency:          8192
      write_latency:        16384

   Under 2.4.20-x, it's:
      read_latency:          64
      write_latency:        8192

Experimentation with the original problem query and different read_latency values showed improvement as read_latency was reduced down to 64 but degraded again a bit after that.

   elvtune -r 2048 -w 8192 /dev/hdc (default Redhat 9):
    Total runtime: 2290.22 msec

   elvtune -r 128 -w 8192 /dev/hdc:
    Total runtime: 450.46 msec

   elvtune -r 64 -w 8192 /dev/hdc:
    Total runtime: 206.14 msec

   elvtune -r 32 -w 8192 /dev/hdc:
    Total runtime: 210.75 msec

   elvtune -r 8 -w 8192 /dev/hdc:
    Total runtime: 338.18 msec

   elvtune -r 1 -w 8192 /dev/hdc:
    Total runtime: 390.55 msec

An interesting explanation of the I/O Elevator is found at http://strasbourg.linuxfr.org/jl3/features-2.3-1.html

Contributors: Stephen jleelim at xxxxxx.com, Tom Lane tgl at sss.pgh.pa.us, Vivek Khera khera at kcilink.com, Shridhar Daithankar shridhar_daithankar at persistent.co.in, Greg Stark gsstark at mit.edu, Matthew T. O'Connor matthew at zeut.net, Dann Corbit DCorbit at connx.com, Gaetano Mendola mendola at bigfoot.com, Jeff threshar at torgo.978.org, scott.marlowe scott.marlowe at ihs.com