Re: Async IO HTTP server frontend for PostgreSQL

Dear Dmitriy,

To add on to David's suggestions, Data caching is a difficult task to undertake. Consider an example where your data may not all fit into memory, when you cache these data outside PostgreSQL you would need to look into memory management as well as issues around concurrent population of this cache as well as means to keep the data in the cache fresh in tune with any changes to the data. These are no trivial tasks and the database community has spent years constructing and improving algorithms to do this on behalf of the front end database application developer. Also each time a TCP connection is created, additional compute resources are consumed by the OS as well as the database management server software.

A simpler way would be to use connection pooling where a thread of your running application "borrows" a connection from a pool of open connections, executes the SQL command then returns the connection immediately on completion of the SQL command. This will require few concurrent connections (depending of configuration) and let the database do the caching of the data for you. For effective database data caching may need to make adjustments of the PostgreSQL configuration file (postgresql.conf file) as well as the operating system resources configuration. This way the response time of your client application will degrade gracefully with the increase of concurrent client requests.

For small number of concurrent connections, the speed advantage direct “streaming” solution may have over the traditional connection pooling solution may hardly be noticeable to end user. The easier way to increase response time is to look into PostgreSQL performance tuning as well as investing in faster hardware (mainly the the disk subsystem and more RAM).

Regards,

Allan.