# RFC: Comprehensive Benchmarking + Performance Optimization Initiative

[muk2]

Summary

This issue proposes a full-scale benchmarking and performance optimization initiative to make pgrsql:

• Extremely fast
• Predictably performant
• Competitive with modern embedded SQL engines
• Scalable for analytical workloads
• Efficient for OLTP-style queries
• Comprehensive across advanced SQL features

The goal is to design a performance-first architecture backed by measurable benchmarks and continuous regression testing.

---

Motivation

As pgrsql expands to support:

• CTEs (WITH, WITH RECURSIVE)
• LATERAL joins
• Window functions
• GROUPING SETS (ROLLUP, CUBE)
• JSON/JSONB operations
• Range types
• Advanced operators
• Complex joins

Performance must scale with feature depth.

Without structured benchmarking:

• Performance regressions go unnoticed
• Query planner weaknesses remain hidden
• Join inefficiencies compound
• Memory usage grows unpredictably
• Complex queries degrade non-linearly

We need measurable, reproducible, automated performance validation.

---

High-Level Goals

1. Establish repeatable benchmark suite
2. Identify bottlenecks in:
   • Parsing
   • Planning
   • Optimization
   • Execution
3. Optimize join strategies
4. Improve memory locality
5. Reduce allocations
6. Improve aggregation performance
7. Improve window function execution
8. Add performance regression testing in CI

---

Phase 1: Benchmarking Infrastructure

1. Add Benchmark Harness

Use:

• Criterion (Rust)
• Custom query microbenchmarks
• End-to-end query benchmarks

Bench categories:

• Parser benchmarks
• AST transformation benchmarks
• Logical planner benchmarks
• Physical execution benchmarks

---

2. Benchmark Query Categories

Basic Select

SELECT * FROM users;

Filtered Select
SELECT * FROM users WHERE age > 30;

Aggregation
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Join
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Multi-Join
SELECT *
FROM a
JOIN b ON a.id = b.a_id
JOIN c ON b.id = c.b_id;

Window Functions
SELECT
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;

Recursive CTE
WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM t WHERE n < 1000
)
SELECT * FROM t;

SELECT data->>'name'
FROM events
WHERE data @> '{"active": true}';

JSON Query
SELECT data->>'name'
FROM events
WHERE data @> '{"active": true}';

Phase 2: Join Optimization

Joins are typically the largest bottleneck.

Evaluate and Implement
	•	Nested Loop Join
	•	Hash Join
	•	Sort-Merge Join

Add cost-based selection between:
	•	Hash Join for large datasets
	•	Nested loop for small datasets
	•	Merge join when sorted inputs available

Optimizations
	•	Early filter pushdown
	•	Join reordering
	•	Projection pruning before join
	•	Memory-efficient hash table design
	•	SIMD-aware comparison (if applicable)

⸻

Phase 3: Query Planner Improvements

Add Cost Model

Estimate:
	•	Row counts
	•	Cardinality
	•	Selectivity
	•	Join cost
	•	Aggregation cost

Introduce simple statistics:
	•	Table row counts
	•	Distinct value counts
	•	Basic histograms (future)

⸻

Phase 4: Execution Engine Optimizations

1. Reduce Allocations
	•	Reuse buffers
	•	Arena allocation for query lifetime
	•	Avoid unnecessary Vec cloning

2. Improve Memory Layout
	•	Columnar execution mode (future goal)
	•	Cache-friendly row layout
	•	Compact data structures

3. Iterator Fusion

Avoid:
	•	Multiple passes over data
	•	Intermediate materialization when unnecessary

⸻

Phase 5: Aggregation Optimization

Optimize:
	•	Hash aggregation
	•	Group-by memory layout
	•	Distinct counting
	•	Partial aggregation

Add benchmarks for:
SELECT COUNT(*)
FROM large_table;

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Phase 6: Window Function Optimization

Window functions are expensive.

Optimize:
	•	Partition grouping strategy
	•	Pre-sorted input detection
	•	Avoid repeated sorting
	•	Efficient ranking calculation

Benchmark:
SELECT
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at)
FROM products;

Phase 7: CTE Optimization

Improve:
	•	CTE inlining
	•	Avoid unnecessary materialization
	•	Detect reusable CTE results
	•	Recursive CTE memory efficiency

Benchmark:
	•	Large recursive traversal
	•	Multi-CTE dependency chains

⸻

Phase 8: Indexing Strategy (If Applicable)

If indexing exists or is planned:
	•	B-Tree performance benchmarks
	•	Index-only scans
	•	Filter pushdown effectiveness
	•	Index selectivity improvements

⸻

Phase 9: Parallel Execution (Future)

Explore:
	•	Parallel joins
	•	Parallel aggregation
	•	Parallel scanning

Requires:
	•	Thread pool architecture
	•	Work partitioning
	•	Safe memory sharing

⸻

Performance Regression Testing

Add CI benchmarks:
	•	Detect >5% regression
	•	Track performance trends over time
	•	Maintain historical benchmark data

⸻

Metrics to Track
	•	Query execution time
	•	Planning time
	•	Memory usage
	•	Allocation count
	•	CPU usage
	•	Throughput under load

⸻

Competitive Benchmarking

Compare against:
	•	SQLite (where applicable)
	•	DuckDB (analytical queries)
	•	PostgreSQL (selected workloads)

Focus on:
	•	Embedded performance
	•	Analytical workloads
	•	Join-heavy queries
	•	Window-heavy queries

⸻

Deliverables
	•	Benchmark harness added
	•	Core query benchmarks implemented
	•	Join algorithm evaluation
	•	Basic cost model implemented
	•	Memory profiling added
	•	Regression detection in CI
	•	Performance documentation page

⸻

Acceptance Criteria
	•	Query benchmarks reproducible
	•	Join-heavy queries optimized
	•	No major memory leaks
	•	Performance regressions detected automatically
	•	Execution time competitive with modern embedded engines (where applicable)

⸻

Long-Term Vision

Make pgrsql:
	•	Blazing fast for analytics
	•	Efficient for transactional queries
	•	Capable of handling complex SQL constructs
	•	Performance-transparent and measurable
	•	Designed for optimization experimentation

This initiative ensures pgrsql is not just feature-rich — but also extremely fast and architecturally strong.

⸻

Conclusion

Features attract users.

Performance retains them.

This initiative ensures pgrsql becomes:
	•	A serious systems project
	•	A high-performance SQL engine
	•	A research-friendly experimentation platform
	•	A competitive embedded database engine

Let’s build something fast, measurable, and uncompromising.