---
title: Automating Postgres index selection using constraint programming
---

| See what's upcoming for pganalyze Index Advisor based on our PGCon talk, new Enterprise Server release, and more. |
| --- |

| View in browser Hi there, Welcome to the May 2023 issue of the pganalyze newsletter!   Last week we attended our first in-person Postgres conference again after a very long time - PGCon 2023 in Ottawa. It was good be back with the Postgres community, talk about upcoming work in the next Postgres releases, and explore future improvements to extensions such as pg_hint_plan.   In our own talk at PGCon we shared a first look at a new approach to automating Postgres index selection using constraint programming, which you'll soon see in an update to the pganalyze Index Advisor.   Earlier this week we've also published a new pganalyze Enterprise Server release, v2023.06.0. This release contains a number of important bug fixes, and lays the groundwork for the upcoming functionality of autovacuum tuning recommendations.   As always you can also find our most recent episodes of "5mins of Postgres", as well as other product updates in this newsletter. All the best, Lukas PGCon 2023: Automating Postgres Index Selection using Constraint Programming At PGCon 2023 we spoke about our new Constraint Programming Model for Index Selection in Postgres. This model is configured through customizable goals and rules that are passed to a constraint programming model, which then utilizes a solver such as CP-SAT to find the optimal index selection.   Our main goal with this new approach is to provide an iteration on what we've offered as part of pganalyze Index Advisor and the Indexing Engine for over a year, with an emphasis on better developer control, as well as a more sophisticated mechanism for dealing with the trade-off between Index Write Overhead and improved query performance thanks to added indexes.   This new model also represents an important step towards an upcoming feature for index consolidation recommendations within pganalyze, such as when two similar indexes can be simplified into one.   We're actively working on updating the pganalyze Index Advisor with this research, so you can easily run this new constraint programming model against your production query statistics in pganalyze. If you'd like to try this out once its available for testing, let us know by replying to this email.   In the meantime you can take a look at the published open-source model, or try out our experimental lint tool to run it locally during development. We've also published the slides, and anticipate a video recording to be available within the next weeks. pganalyze Enterprise Server v2023.06.0 The new pganalyze Enterprise Server v2023.06.0 release is available now, containing important bug fixes as well as underlying backend functionality for the upcoming autovacuum tuning recommendations.   See the full changelog for details→ 5mins of Postgres E66: Waiting for Postgres 16: Logical decoding on standbys→   We're talking about logical decoding on a standby in Postgres 16, which allows us to keep following the logical replication stream whilst a failover happens. We also touch upon the new pg_log_standby_snapshot function. E67: Forcing Join Order in Postgres Using Optimization Barriers→   We talk about forcing join order in Postgres. We walk through 3 techniques: Adding OFFSET “0” to a sub-SELECT, using common table expressions, and working with the join_collapse_limit. E68: A Postmortem on High Memory Usage with Prepared Statements→   We talk about how using partitioning together with prepared statements can lead to out of memory problems and walk through a real-life example from the Prefect blog. E69: Tuning huge pages in Postgres→   We explain what the Translation Lookaside Buffer is and how, starting with Postgres 15, Postgres can help you calculate how many huge pages to allocate. E70: How bulk loading with COPY can be 300% faster in Postgres 16 Beta 1→   We talk about Postgres 16 Beta 1 being released and how one of its improvements can allow you to bulk load about 300% faster with COPY, thanks to reducing the overhead of relation extension locks. Subscribe on YouTube We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more. Subscribe to pganalyze on YouTube Product updates pganalyze Enterprise Server v2023.06.0 This release contains preparations for upcoming autovacuum tuning recommendations, as well as the constraint programming based Index Advisor - more on this soon! Multiple improvements to wait event monitoring Add additional AWS settings for bundled collector Allow setting SIDEKIQ_CONCURRENCY=0 to turn off workers in combined Enterprise image Object storage: Fix DNS lookup errors from Rust-based snapshot workers And more - see the full changelog pganalyze-collector 0.50.0 Track TOAST table name, reltuples and relpages Reload collector config after successful test run  If you have previously run "--reload --test" you can now simply run "--reload" To restore the old behaviour, pass the "--no-reload" flag together with "--test" RPM packages: Ensure collector gets started after reboot  Collector install script: Add Amazon Linux 2023, refresh other versions Azure Database for PostgreSQL / Azure Cosmos DB for PostgreSQL Add support for Azure Kubernetes Service (AKS) Workload Identity  And additional bug fixes - see the full changelog Review us on G2 Please leave your feedback (good or bad!) in this survey - we appreciate it! Fill out the G2 survey Duboce Labs, Inc., 1501 Mariposa St, Unit 408, San Francisco, CA 94107 Unsubscribe Manage preferences |
| --- |