• PostgreSQL 17 is nearly out with the first release candidate published on September 5th and the final release expected on September 26th.
• At Lifen, they have several Rails applications using PostgreSQL. They monitor endpoints with timeouts and found issues on the database side.
• A problematic query with a large table and multiple conditions was slow. The EXPLAIN plan showed not all indexes were used.
• It was mentioned that PostgreSQL is not optimized for queries with multiple IN or ANY query parameters.
• Peter Geoghegan worked on a patch that significantly reduced query time by 90% (from 110ms to 10ms) and fully utilized indexes.
• The patch is explained in a blog post on PgAnalyze and was included in PostgreSQL 17 beta with additional fixes.
• With a basic Rails application, the response time improved from 8.45 seconds with PostgreSQL 16 to 0.11 seconds with PostgreSQL 17.
• It is recommended to upgrade to PostgreSQL 17 and dig deeper into the database side of applications for faster response times. Developers are welcome to share feedback on the PostgreSQL hackers' mailing list.
• There is a gist to follow benchmarks and the Rails app used to test response times is on Github. Written by Benoit Tigeot.