$ show postgrespro/aqo
This repository implements the Adaptive Query Optimization (AQO) extension for PostgreSQL, a machine learning-based system that improves query planning by learning from historical query execution statistics. The extension integrates with PostgreSQL's planner and executor via hook mechanisms to override cardinality estimation with predictions from per-query-class k-nearest neighbors regression models. It features a multi-layer architecture: preprocessing (query classification and mode handling), cardinality estimation (ML-based prediction), postprocessing (statistics collection and model updates), and shared memory persistence for query statistics and ML models. Key operational modes include disabled, learn, intelligent, controlled, and forced, controlled via GUCs. The system automatically tunes learning/usage flags based on prediction stability and includes background workers for maintenance. The extension requires patching PostgreSQL source and rebuilding, with tight coupling to internal planner structures (e.g., RelOptInfo, Plan nodes) and executor functions. Deployment involves setting shared_preload_libraries, configuring GUCs like AQO.mode, and managing memory limits for query classes and feature subspaces. It provides SQL interfaces for managing query classes (enable/disable, status checks) and EXPLAIN integration for runtime visibility. The test suite includes pgbench integration, SQL scripts for feature validation, and Perl scripts for pg_stat_statements compatibility. While the core AQO logic is highly specialized to PostgreSQL, two components demonstrate extractable excellence: the hash function for query normalization (ignoring constants/whitespace) and the standalone k-NN regression implementation with linear smoothing. These show non-obvious skill in handling SQL variability and efficient online learning. However, the deep integration with PostgreSQL internals, version-specific patching requirements, and complex state management introduce maintenance challenges and technical debt risks, particularly during PostgreSQL version upgrades.
The LLM identified 2 files worth highlighting for reuse:
Implements a robust hash function that normalizes SQL queries by stripping constants, whitespace, and location-insensitive elements to create structure-based identifiers. This enables grouping of semantically similar queries for collective learning. The module is self-contained with no internal dependencies beyond standard headers, demonstrating skill in designing a collision-resistant hash for SQL query classification that could be reused in any SQL analysis tool.
Provides a standalone implementation of optimized k-nearest neighbors regression with linear smoothing and a fixed-size buffer (max 30 neighbors) for efficient prediction and incremental learning. The module includes functions for model training (learning from new samples) and prediction (estimating target values) with minimal dependencies. Its design showcases non-obvious optimizations for embedded systems, such as avoiding dynamic memory allocation and using incremental updates, making it suitable for reuse in other real-time learning scenarios outside PostgreSQL.