# pg_statement_cache.rb

Path: | lib/sequel/extensions/pg_statement_cache.rb
Last Update: | Sun May 19 07:59:58 +0000 2013

This extension adds a statement cache to Sequel‘s postgres adapter, with the ability to automatically prepare statements that are executed repeatedly. When combined with the pg_auto_parameterize extension, it can take Sequel code such as:

      DB.extension :pg_auto_parameterize, :pg_statement_cache
      DB[:table].filter(:a=>1)
      DB[:table].filter(:a=>2)
      DB[:table].filter(:a=>3)

And use the same prepared statement to execute the queries.

The backbone of this extension is a modified LRU cache. It considers both the last executed time and the number of executions when determining which queries to keep in the cache. It only cleans the cache when a high water mark has been passed, and removes queries until it reaches the low water mark, in order to avoid thrashing when you are using more than the maximum number of queries. To avoid preparing queries when it isn‘t necessary, it does not prepare them on the server side unless they are being executed more than once. The cache is very tunable, allowing you to set the high and low water marks, the number of executions before preparing the query, and even use a custom callback for determine which queries to keep in the cache.

Note that automatically preparing statements does have some issues. Most notably, if you change the result type that the query returns, PostgreSQL will raise an error. This can happen if you have prepared a statement that selects all columns from a table, and then you add or remove a column from that table. This extension does attempt to check that case and clear the statement caches if you use alter_table from within Sequel, but it cannot fix the case when such a change is made externally.

This extension only works when the pg driver is used as the backend for the postgres adapter.

[Validate]