It is often the case that you want to know how many rows are in a table. If you find yourself in this situation, you should also ask how accurate that count should be. If you are doing accounting, you want it to be exactly accurate. If you are decorating a web page with counts, perhaps it would be OK to be off by a few.

COUNT(*)

	webstat=# select count(*) from rawlog;
  	count
	---------
 	2058704
	(1 row)

	Time: 7202.873 ms

Reltuples

This number is stored in pg_class.reltuples. To see all of the row count estimates for all the tables in the public schema of your database use the following:

	SELECT relname, reltuples 
	FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) 
	WHERE relkind = 'r' AND n.nspname = 'public';

	SELECT reltuples 
	FROM pg_class r 
	WHERE relkind = 'r' AND relname = 'mytable';

Simple Count Triggers

Create a table called, for instance, row_counts. row_counts would consist of the table name (relname) and the row count for that table. First you would create table, then the triggers and then initialize the row_counts table.

	CREATE TABLE row_counts (
   relname  text PRIMARY KEY,
   reltuples   numeric);

Being properly lazy, I've written one trigger function which handles both the insert and delete cases on the table. This is easy to do using the TG_OP constant which says what the operation is and the TG_RELNAME constant which contains the table name. These are trigger specific contants. And thank goodness, once more, for dollar quoting.

	CREATE OR REPLACE FUNCTION count_trig()
	RETURNS TRIGGER AS
	$$
	   DECLARE
	   BEGIN
	   IF TG_OP = 'INSERT' THEN
	      EXECUTE 'UPDATE row_counts set reltuples=reltuples +1 where relname = ''' || TG_RELNAME || '''';
	      RETURN NEW;
	   ELSIF TG_OP = 'DELETE' THEN
	      EXECUTE 'UPDATE row_counts set reltuples=reltuples -1 where relname = ''' || TG_RELNAME || '''';
	      RETURN OLD;
	   END IF;
	   END;
	$$
	LANGUAGE 'plpgsql';

Still being lazy I wrote a function to add the triggers to all of my tables in the public schema. You may or may not want to do this. If you do, I suggest writing a corresponding drop trigger function (which is left as an exercise for the reader :).

	CREATE OR REPLACE FUNCTION add_count_trigs()
	RETURNS void AS
	$$
	   DECLARE
	      rec   RECORD;
	      q     text;
	   BEGIN
	      FOR rec IN SELECT relname
	               FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid)
	               WHERE relkind = 'r' AND n.nspname = 'public' LOOP
	         q := 'CREATE TRIGGER ' || rec.relname || '_count BEFORE INSERT OR DELETE ON ' ;
	         q := q || rec.relname || ' FOR EACH ROW EXECUTE PROCEDURE count_trig()';
	         EXECUTE q;
	      END LOOP;
	   RETURN;
	   END;
	$$
	LANGUAGE 'plpgsql';

	insert into row_counts select relname, reltuples from pg_class;

Vacuum the tables explicitly if you can. I found a case where the count was off by one on a quiescent database after a normal vacuum. It corrected itself after an explicit "vacuum mytable".

Even though you should run vacuum for this case explicitly on each table, if for any reason you are not sure of when your vacuum has run, you can write a function to do the count(*) initialization instead of using vacuum's counts to start off with. This function is slower and can still be off by some if your database is active during this time.

	CREATE OR REPLACE FUNCTION init_row_counts()
	RETURNS void AS
	$$
	   DECLARE
	      rec   RECORD;
	      crec  RECORD;
	   BEGIN
	      FOR rec IN SELECT relname
	               FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid)
	               WHERE relkind = 'r' AND n.nspname = 'public' LOOP
	         FOR crec IN EXECUTE 'SELECT count(*) as rows from '|| rec.relname LOOP
	            -- nothing here, move along
	         END LOOP;
	         INSERT INTO row_counts values (rec.relname, crec.rows) ;
	      END LOOP;
	
	   RETURN;
	   END;
	$$
	LANGUAGE 'plpgsql';

Putting it all together, this is the order of events:

• Create row counts table
• Create trigger function
• Stop server activity if possible
• Vacuum tables -- cannot be done in a transaction
• In a transaction
  • Add triggers
  • Initialize row counts

Contributors: elein at varlena.com