How We Built a Cost-Based SQL Optimizer
In this technical retrospective (2018), @andydb shares practical architectural lessons learned from running, scaling, or migrating production software under lean constraints.
Core Principles for Independent Builders:
Defaulting to simpler architectures (such as monolithic frameworks, SQLite, or managed VPS) prevents early engineering bankruptcy.
Evaluating true compute and egress costs reveals that high cloud bills can often be slashed by 80%+ through judicious re-platforming.
Code you write at 2 AM as a solo engineer must be easy to debug six months later without complex microservice dependencies.
Community Debate Themes:
HN engineers debated the tradeoffs between cloud convenience and raw server economics, database indexing strategies, and resisting premature architectural complexity.
CATEGORY Frugal Engineering →
HISTORICAL ERA Cloud & Mobile Era (2014–2019)
COMMUNITY SCORE 198 points
TOTAL DISCUSSIONS 52 comments
Maybe of interest - postgres planner documentation
https://www.postgresql.org/docs/current/planner-optimizer.html ↗
Genetic query optimiser: https://www.postgresql.org/docs/current/geqo.html ↗
postgres has a number of hooks, which can be used to override the default behaviour. In particular, there are a bunch of hooks that can be used to install a custom query planner. https://github.com/AmatanHead/psql-hooks/blob/master/Readme.md#planner-hooks ↗
more generally, ignoring database query optimisation specifically, if you are interested in learning about discrete optimisation techniques, I recommend this course: https://www.coursera.org/learn/discrete-optimization ↗
Any chance you could share more about this? This general area of how to build a production grade SQL optimizer seems to be a thing that's more scattered in tiny pieces across a wide number of papers, or held in peoples' heads, than aggregated in a teaching manner. It seemed that the realistic general advice on how to build a SQL optimizer was to poach someone from the SQL Server team. ;)
I've generally just gone referring back to the unfinished Building Query Compilers[1] when pondering this subject. Not that the hundreds of references don't provide sufficient material to read though as well, but it'd be interesting to hear what a bootcamp like this presented as state of the art.
[1]: http://pi3.informatik.uni-mannheim.de/~moer/querycompiler.pdf ↗
Great article, and it sounds like it took a pretty big leap of faith to go from heuristics to cost-based optimization. If this was addressed in the article I apologize: do you only ever select transformations that yield equivalent results, with immediately lower cost, or do you also explore transformations that incur in immediate cost increase, but then later pay off? i.e. are you doing simple hill climbing, or something more interesting?
I've been looking for books that cover database implementation techniques such as this — the "memo" algorithm was new to me, but I'm also generally interested in query planning and optimization. Anyone got any recommendations? I started a thread: https://news.ycombinator.com/item?id=18410692 ↗.
People generally interested in this topic might also find the CMU Intro to DB Systems youtube of interest.
Note that it's an introduction to building database systems (not just using them).
https://www.youtube.com/playlist?list=PLSE8ODhjZXja3hgmuwhf89qboV1kOxMx7 ↗