Asynchronous PostgreSQL


Signals in View

Line 19 installs a watcher for the database socket, which ends the main event loop in line 29 once the result of the SQL operation exists. The two signal watchers created in line 25 are more interesting. The $cancel function, which they call, cancels the database operation and ends the event loop. Again, to avoid a race condition, the initialized signals must not occur between installing the handler and the end of the execute operation, that is, between lines 25 and 26. The two sigprocmask calls in lines 24 and 27 ensure this.

What happens in detail when Apache sends the SIGTERM signal? If it occurs before line 24, the program just stops, but because no SQL query is running, this is not critical. Between lines 24 and 27, delivery is delayed at the kernel level. If the signal arrives in this period, the process does not see it until after line 27. Here, the signal handlers are already installed, and the SQL operation is initiated. The signal handler installed by AnyEvent at the C level writes to eventfd or the self-pipe and returns. Now, at least, one file descriptor, eventfd, is ready to be read. That is, the epoll system call used by the main event loop reports this. The signal watcher is called.


The pitfalls you can encounter with a simple CGI program are amazing. Fortunately, the query function from final version can be fairly easily shifted out into a module and reused. Attentive programmers should know what to look for and what the potential pitfalls are. Unfortunately, all of the work so far only solves half the problem. If the timeout does not occur on the web server, but instead the problem is sitting in front of the screen reloading the page again and again, you will very quickly have a bottleneck in the database (see "What About Performance?"). With mod_perl instead of mod_cgi you can quite easily identify the disappearance of the browser. The timeout on the user side could thus be detected quickly and the current SQL operation aborted, which is the next solution I'll tackle.

What About Performance?

Will using asynchronous requests cause a bottleneck? Will it slow down normal operation? To answer these questions, I wrote a small benchmark program that compares the query in Listing 6 with the following function:

sub query_sync {
  my $sql=pop;
  state $dbh||=DBI->connect('dbi:Pg:dbname=r2', 'ipp', undef, {RaiseError=>1});
  my $stmt=$dbh->prepare_cached($sql);
  return $stmt->execute(@_), $stmt;

If the SQL statement select 1 is used for comparison, the synchronous version is in fact much faster. On my test machine, it achieves about 2,500 operations per second, whereas the asynchronous variant only just reaches 1,000.

However, if you consider real queries that can take a while, the whole thing shifts into perspective. If select pg_sleep (0.05) is used (i.e., an SQL query that requires 50ms on the server), the synchronous function achieves 18.6 operations per second, and asynchronous 17.9 operations per second. The difference is small, but still noticeable.

If the operation on the server takes 200ms, the synchronous version achieves 4.83 operations per second, and the asynchronous 4.90 operations per second. The difference is negligible.

Real-life reliable requests, as commonly occurs in web applications, are usually somewhere between these two values.


  1. DBD::Pg:
  2. PostgreSQL functions for system administration:
  3. Boost application performance using asynchronous I/O:
  4. pselect(2):
  5. Self-pipe trick:
  6. Linux::FD:
  7. AnyEvent:
  8. Libev documentation:
  9. Scherbaum, Andreas, PostgreSQL . Open Source Press, 2009

The Author

Torsten Foertsch programs and manages web servers for customers in Germany and abroad. He has worked with Perl since 1998. In his spare time he works on mod_perl and fixes bugs.

Buy ADMIN Magazine

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus
Subscribe to our ADMIN Newsletters
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs

Support Our Work

ADMIN content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.

Learn More”>


		<div class=