'NOTIFY' slows down postgres?

Started by Cheirie Shumabout 24 years ago2 messagesgeneral
Jump to latest
#1Cheirie Shum
cshum@cats.ucsc.edu

Hi,
I currently have a tcl program that: (script 1)
-communicates with the postgres database
-'listens' to every update
( a notify was previously executed on postgres' side)
-then sends the new updated information to a client.

I have another tcl script that: (script2)
-communicates with the postgres database
-runs 400 updates on one table
( I change a value and then change it back )
-prints out the elapsed time.

So anytime script2 updates something, script1 knows about it.

a.) When I run script2 without script1, without a listen or a notify,
script2 finishes running in 3 seconds. (ie: script1 has no clue as to
what is going on... and postgres doesn't let anyone know what was updated)

b.) Adding a 'notify script1' to the above configuration, script2
finishes running in 7 seconds. (!!)

c.) Adding script1 ( ie: script1 is now running, listens for updates
and postgres notifies script1 about every update but does nothing with
its new knowledge) script2 finishes in 16 seconds.

Note: Everytime i run script2, the results of parts c increase
steadily by about 1 second. However, the results of parts a and b have
been consistent.

What is it about the notifies and listens that slow my script down? What
can i do to make these notifies go faster?

Any help would be appreciated. Thanks in advance,
cheirie
cshum@cats.ucsc.edu

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Cheirie Shum (#1)
Re: 'NOTIFY' slows down postgres?

Cheirie Shum <cshum@cats.ucsc.edu> writes:

What is it about the notifies and listens that slow my script down?

Has the pg_listener table been vacuumed lately?

regards, tom lane