Database Technology

Partition pruning typically requires filtering on the partition key, but Haki Benita demonstrates techniques to enable this optimization for non-partition columns. By leveraging specific data patterns and optimizer parameters like constraint_exclusion, queries can eliminate entire partitions even when the predicate targets different attributes. The approach involves managing outliers and potentially using local or global indexes to facilitate the planner's ability to skip irrelevant data segments.

• Partition pruning is not strictly limited to the partition key column.
• Data patterns and outlier handling can enable pruning on other fields.
• The constraint_exclusion parameter influences how the optimizer handles these predicates.
• Local and global indexes may play a role in supporting non-key pruning strategies.

Google has released AlloyDB AI functions in general availability, introducing a proxy model architecture that trains lightweight local models from LLM outputs. This approach allows queries to be processed directly within the database, eliminating the need for external API calls. Internal testing reports a 2,400x throughput improvement, achieving speeds of 100,000 rows per second for the ai.if function.

• Eliminates network latency and API costs by running inference locally inside the database engine.
• Achieves reported 2,400x throughput gains via smart batching and local processing.
• Benchmarks currently apply only to internal tests of the ai.if function.
• New capability requires training a proxy model from existing LLM outputs.

Christophe Pettus details the enable_partitionwise_join GUC, which allows PostgreSQL to break large joins into smaller per-partition operations when both tables share a partition key. This optimization is only active when explicitly enabled and requires strict adherence to partitioning constraints to function correctly. The feature aims to significantly reduce overhead for queries involving large, similarly partitioned datasets.

• Enable partitionwise_join to let the planner decompose joins into per-partition pairs.
• Both tables must be partitioned on the join key for this optimization to apply.
• Strict partitioning constraints are required; misaligned schemas will not trigger the feature.
• Explicitly enabling this GUC is necessary as it is not the default behavior.
• Monitor query plans to verify the planner is using the partition-wise join strategy.

Momentic replaced its PostgreSQL caching layer with ClickHouse to manage over 2 million daily queries across 20 billion entries while keeping latency at 250 ms. The shift to a column-oriented architecture enabled the platform to handle its growing data volume and query load more efficiently. This rearchitecture supports the company's AI-driven software testing platform without compromising response times.

• Columnar databases like ClickHouse excel at high-volume read workloads compared to row-oriented PostgreSQL.
• Architectural shifts can sustain massive scale (20B entries) while maintaining sub-second latency.
• Monitoring query volume and latency trends helps identify when to evaluate alternative storage engines.