PostgreSQL Aggregates with Rust
Writing custom Aggregates for statistical or analytical purposes.
Reaching for something like
AVG(vals) is a common habit when using PostgreSQL. These aggregate functions offer users an easy, efficient way to compute results from a set of inputs.
How do they work? What makes them different than a function? How do we make one? What kinds of other uses exist?
We'll explore creating some basic ones using SQL, then create an extension that defines aggregates in Rust using
pgx 0.3.0's new aggregate support.
Forging SQL from Rust
pgx creates PostgreSQL extension SQL definitions that bind to Rust.
PostgreSQL offers an extension interface, and it's my belief that Rust is a fantastic language to write extensions for it. Eric Ridge thought so too, and started
pgx awhile back. I've been working with him to improve the toolkit, and wanted to share about one of our latest hacks: improving the generation of extension SQL code to interface with Rust.
This post is more on the advanced side, as it assumes knowledge of both Rust and PostgreSQL. We'll approach topics like foreign functions, dynamic linking, procedural macros, and linkers.Continue reading...
Hierarchical Structures in PostgreSQL
Modelling hierarchical/team/categorical/tag data with arbitrary depths.
It's a common pattern: a database developer at a startup is probably on the Product subteam of the Engineering team at their company. In a department store, shoes are a subcategory of clothing, while your favorite thermos is probably in the travel department.
In any Github organization, there are teams within teams within teams. In any large department store there are categories deeply nested. In any recipe book, there are many ways to classify food.
So how can we model them?Continue reading...