Magnus Brun Falch Blog Posts

By Magnus Brun Falch CoFounder of Bamboo Solutions AS

From zero to PostgreSQL extension in three hours.

I recently had a personal side-project which included setting up a working REST backend for a mobile and web application. There were numerous challenges to overcome but my available hours to work on them were limited. I had a few evenings to spare on this project and didn’t feel like writing a lot of boilerplate in C# to achieve it.

A trusted friend had praised PostgREST many times. My testing showed it outclassing ASP.NET Core for all my test cases by an order of magnitude, half a year earlier.
I finally had a chance to use it as the base for my API layer in one of my projects.

One thing that this project called for which would not normally be easy to achieve using PostgreSQL alone, is to pre sign requests for an S3 compatible storage platform. I had 6 hours left in the budget for this feature, after all the firm requirements were estimated.

I didn’t want to write a full implementation of the relevant parts of the S3 spec from scratch or using pgcrypto. My first instinct was to have a look at the different procedural languages available in PostgreSQL, some of which are Tcl, Java, JS, Python and Julia. None of them were too tempting at the moment.

Luckily I was an early member of the new People, Postgres, Data: Discord server and there are some very interesting and clever people there, Eric Ridge, the main author of ZomboDB (ZDB) among them.

I’ve kept an eye on ZDB for years after doing some research into ElasticSearch. My project included text search so I relished the opportunity to chat with him. Among our many topics of conversation were the upcoming release of a new Rust based version of ZDB and its underlying framework: PGX. I asked him about whether he thought PGX would be a good fit for my remaining challenge. He said “Absolutely”. I spent an hour setting up an Ubuntu VM with all the needed tooling & dependencies including building and testing the “hello world” example. A thorough reading of the man page would likely have saved me half an hour.

The next 30 minutes were spent identifying the most promising Crate to use. Within three hours of writing the first line of code I had a working plugin. About 80% of the time spent was lost to unfamiliarity with the language and writing it using nano over ssh instead of setting up a proper IDE like Jetbrains CLion. I also struggled a bit with finding the correct way of extracting the value from a result of an async function in a foreign language.

In total I spent 4 hours and 25 minutes from the time I started the fresh Ubuntu 20.04 server I was going to use as a Dev environment, to having a working build of an extension. It can be called inline in PostgreSQL to sign download requests in less than 1ms on my Intel I5 2500 testbed.

 I’ve had zero experience with Rust and have never read any teaching materials or looked at any tutorials for it before that evening. What I’d heard about Rust previously was that it’s really fast, and that the compiler would yell at you until your code is of acceptable quality.

Example of the PGX simplicity:

The extension was merely for internal use in a specific project and has some rough edges. It is published on GitHub and needs some more polish before I would consider it a production ready extension. Pull requests are welcome if you have suggestions before I get the time to work on it some more.

Notes:

• PGX is a framework for writing PostgreSQL extensions using Rust.
• ZomboDB integrates Elasticsearch and PostgreSQL.
• PostgREST consumes a PostgreSQL schema and generates API endpoints.

This work is unrelated to my job at Bamboo Solutions AS

We all know that we should produce good documentation, but we’ve all skipped out on it every now and then. It’s often a lot of  mundane effort when you could be doing new exciting things.

Then days, weeks, months or even later the questions start coming in from your team-mates or new hires: why is this column like this, what does this column really describe etc. You no longer remember and you have to spend time to understand the database you’ve made.

One of the great things about PostgreSQL is that documenting your database is a piece of cake and that it saves you more time than you spend on adding comments. It’s without exaggeration less time consuming per table than answering a single question like the ones above.
Thankfully the syntax isn’t horrible and adding comments won’t be a slow affair if you have a lot of existing data unlike some other DB engines I’ve worked with.

PostgreSQL has support for storing comments directly in your schema:
You can add a comment to a table, a schema, an operator and so on.

Adding them is simple:

Some tools have better support for showing them than others and I hope to see that being improved in the future, for instance: Datagrip, my editor of choice will not show comments in the database explorer unless you hover over the object it does include it in the in-editor information pop-up when hovering over a column, function or table.

My personal favorite for generating visually pleasing documentation with little effort is SchemaSpy it requires Java and the JDBC driver to run and it generates pretty documentation in about a minute for a complex schema:

The output is in the form of a relatively pretty website like this : Sample

I would say that this is quite a few levels of quality above what most companies have for their internal databases and with a lot less effort. As you can see from the sample it even supports markdown.

If you have suggestions for even better tools for the job, please join the People, Postgres, Data: Discord server

This work is unrelated to my job at Bamboo Solutions AS