postgresql

PostgreSQL’s query optimizer decision-making process is opaque, hindering both pedagogical understanding and production-level query tuning. To address this, we present the first interactive, web-based visualization tool—built with D3.js and React—that supports end-to-end, log-driven analysis of both the dynamic programming (DP) and genetic algorithm (GEQO) optimizers. By parsing native PostgreSQL optimizer logs, our tool visualizes critical decisions—including access path selection and join order enumeration—as topology graphs, decision trees, and animated execution paths. It is the first to enable *comparable*, *interpretable*, and *debuggable* inspection of both optimization strategies. Empirical evaluation demonstrates significant improvements in database teaching effectiveness and practitioner query tuning proficiency. The open-source implementation has been officially adopted by the PostgreSQL community, establishing a new paradigm for optimizer strategy analysis, validation, and refinement.

SQL/PGQ standards enable hybrid relational and graph query processing, yet experiments reveal substantial performance disparities—even for semantically equivalent SQL and PGQ queries—due to syntax-driven, model-isolated optimization in existing systems, lacking cross-model synergy. This paper proposes the first holistic unified optimization framework that transcends syntactic boundaries between SQL and PGQ, instead leveraging query semantics and data characteristics to automatically select optimal execution strategies. Key technical innovations include: (i) cross-model query normalization via equivalence-aware analysis; (ii) cost-aware operator fusion; and (iii) adaptive execution plan generation. Experimental evaluation demonstrates that the framework significantly narrows performance gaps among functionally identical queries, achieving an average 3.2× speedup. It thus provides critical enabling technology for efficient, standardized SQL/PGQ deployment.

Traditional database query optimization relies solely on explicit schema constraints (e.g., primary and foreign keys), overlooking abundant implicit data dependencies—such as ordering or functional dependencies—that remain undetected and unexploited. Method: This paper introduces a workload-driven, lightweight dependency discovery technique that enables millisecond-scale automatic identification of such dependencies. It tightly integrates dependency-aware optimization into both the query optimizer and execution engine, supporting SQL rewriting, dependency propagation, and subquery optimization. Contribution/Results: The approach transcends conventional schema-only optimization by establishing an end-to-end dependency-aware framework. Evaluated across five mainstream DBMSs using standard benchmarks (e.g., TPC-C), it achieves up to 10% higher throughput versus primary/foreign-key–only optimization, 22% over no-dependency optimization, and overall improvements of 5%–33%. Discovery overhead is fully amortized after a single workload execution.

Contemporary relational database management systems (RDBMSs) suffer from insufficient logical data independence, reducing them to passive storage layers incapable of supporting modern architectural innovation. This paper argues that the Entity-Relationship (ER) model must serve as the native abstraction layer of RDBMSs to overcome this limitation, and it provides the first systematic theoretical justification and empirical validation of the ER model’s necessity and feasibility for ensuring logical independence. Based on this insight, we design and implement ErbiumDB—a prototype system integrating metadata-driven schema management, declarative relational semantic modeling, and runtime relationship evolution. Experimental evaluation demonstrates that ER-based abstraction significantly enhances decoupling between application and storage layers, enabling flexible, semantics-aware data management. ErbiumDB establishes a novel paradigm for intelligent database architectures and delivers a rigorously validated, extensible prototype foundation for future research and development.

This paper addresses the lack of a rigorous theoretical foundation for graph query languages (GQL and SQL/PGQ). To this end, it introduces the first lightweight graph pattern calculus (GPC) tailored to property graphs. GPC systematically abstracts the common semantic core of pattern matching across GQL and SQL/PGQ, achieving expressive power while enabling formal verification. The authors develop a sound and complete type system alongside both operational and denotational semantics, rigorously defining syntax, typing rules, and semantics. They establish key algebraic properties—including pattern equivalence and composability—thereby ensuring mathematical robustness. GPC provides a verifiable theoretical basis for correctness proofs, query optimization, and standardized language extensions, effectively bridging a critical gap in the formal modeling of industrial-grade graph query languages.

This work addresses the limitations of traditional database parameter tuning, which relies on static documentation and struggles to adapt to version evolution, heterogeneous workloads, and complex inter-parameter dependencies. The authors propose a novel large language model–based approach that transforms expert knowledge into executable tuning skills, enabling version-consistent validation, workload-aware analysis, and joint optimization of multiple parameters. By dynamically operationalizing the tuning process, this method overcomes the constraints of static guidelines and supports adaptive configuration tailored to real-world execution environments. Experimental evaluations on PostgreSQL using TPC-C and TPC-H benchmarks demonstrate performance improvements of up to 35.2% compared to existing documentation-driven tuning strategies.

This work addresses the challenge of identifying subtle performance bugs in database management systems, which are often difficult to detect and lack systematic validation methods for optimization logic. The authors propose Branch Flipping Analysis (BFA), a novel white-box, fine-grained detection paradigm that combines static analysis with dynamic execution to precisely control and compare performance differences between code paths with optimizations enabled versus disabled. By directly manipulating conditional branches related to query optimizations, BFA overcomes the limitations of traditional black-box testing. The approach has been successfully applied to four major database systems—including PostgreSQL and MySQL—uncovering 21 previously unknown performance defects, thereby demonstrating its effectiveness and broad applicability.

Existing relational databases lack the persistent isolation and concurrent branching capabilities required by agent workloads, hindering support for speculative modifications and nonlinear state exploration. This work presents the first systematic definition and quantitative evaluation of database branching for intelligent agents, introducing BranchBench—a benchmark combining parameterized macrobenchmarks (modeling branch-mutate-evaluate cycles) and microbenchmarks (measuring branch lifecycle overhead). We evaluate systems including Neon, DoltgreSQL, TigerBeetle, Xata, and PostgreSQL, revealing that fast-branching systems suffer 5–4000× read performance degradation as branch depth increases, while fast data operating systems exhibit 25–1500× higher latency in branch creation and switching. Our experiments demonstrate that none of the current systems can effectively scale to support representative agent workloads such as software engineering tasks, fault reproduction, or Monte Carlo Tree Search (MCTS).

Existing reinforcement learning (RL)-based query optimizers suffer from unstable performance, severe degradation, and slow convergence in single-query settings, hindering their practical deployment. This work proposes RELOAD, the first RL-based query optimizer that simultaneously achieves high robustness and high training efficiency. By incorporating stability constraints and an efficient policy learning mechanism, RELOAD effectively suppresses performance fluctuations and accelerates convergence to expert-level plan quality. Experimental results on the JOB, TPC-DS, and SSB benchmarks demonstrate that RELOAD improves robustness by up to 2.4× and training efficiency by up to 3.1× compared to the current state-of-the-art RL-based optimizers.

This study addresses the problem of validating graph database instances against property graph schemas (PG-Schemas) that lack integrity constraints. Employing computational complexity theory and formal verification techniques, the authors establish that this validation problem is NP-complete under combined complexity, yet falls within PTIME under data complexity. The primary contribution lies in precisely delineating the complexity boundary of PG-Schema validation and demonstrating that imposing suitable syntactic restrictions on alternations between composition and union operations in type constructors can reduce the combined complexity to PTIME. This insight opens a feasible pathway toward efficient schema validation for property graph databases.