Archive for September 2020

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:

#[pg_extern]
fn pgx_s3sign_pre_get(
  server: String,
  input_bucket: String,
  input_identity: String,
  input_secret: String,
  input_file: String,
  duration: i32,
) -> String {
  let bucket = bucket_create(server, input_bucket, input_identity, input_secret);
  let url = bucket
      .presign_get(input_file, duration.try_into().unwrap())
      .unwrap();
  Url

 

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

Magnus Brun Falch     September 28, 2020     extensions zombodb postgresql postgres rust

What’s your view?

Over 25 years ago I got into an argument with my boss. He was frustrated with all the tasks that needed to be completed and feeling overwhelmed. I said, “Hey, why not go to a park and take in the scenery? You could work there too.” Those were the days before the Internet was required to complete your day to day. He was incredulous, “I don’t pay 1200.00 a month for an office to go work at a park.” It is funny how a single conversation can stick with you throughout life. Now that person is in the Cannabis industry and takes a completely different view on life.

 

Today I write this newsletter from Henry’s Lake, Idaho just about 30 minutes from Yellowstone National Park and the newsletter image is a photo I took this morning as I was about to make coffee. During these trying times with the pandemic and economic uncertainty it is vital that all of our community take a moment to reflect on what their view is. How is it that you are living your life? Are you putting people first? Are you helping each other as you move through the day? Are you in need of help? These are all questions we should ask ourselves and others every day.

 

With People, Postgres, Data we make an earnest effort to put people first, professionally and personally. This is why when we have physical events, we have tracks that are not traditional PostgreSQL faire including our Career Fair and Professional Development and Leadership tracks.It is also why we try to keep our digital events free. The better people we are, the better professionals we can be and the better the professional we are, the more we are able to help people. It is also why we started a new community chat server with Discord. Though the server is there to help you with Postgres, it also exists to embrace community with channels such as #watercooler, #food, #games and #professional-advice. It is a holistic approach that allows people to be people, not bytes.

 

RSVP for upcoming free digital Events

  • September 29, 10AM PST: Introduction to PostgreSQL ColumnStore Indexes

  • September 30, 10AM PT: Live Demo: Creating A Single Point Of Access To Multiple Postgres Servers Using Starburst Presto

  • October 6, 9am PT: Data processing more than billion rows per second

  • October 7, 10am PT: Database Isolation Levels, Data Issues and Global Transaction Consistency

  • October 14, 10AM PT: Live Demo: Unlock Data In Postgres Servers To Query It With Other Data Sources LIke Hive, Kafka, Other DBMSs, And More.

  • October 20, 10AM PT: PGX: Build Postgres Extensions with Rust

  • October 21, 10am PT: Using PostgreSQL, PostGIS, and pgRouting for street sweeping

  • October 27, 10AM PT: Logical Replication lessons learned for the Data Warehouse

  • October 28, 10AM PT: How to build local communities: a meetup perspective

  • November 10, 10AM PT: CYPEX: Revolutionizing PostgreSQL Application Development

  • November 12th, 10am PT: Blockchain as a Database



Joshua D. Drake     September 22, 2020

Latest Posts

  • By Magnus Brun Falch CoFounder of Bamboo Solutions ASFrom zero to PostgreSQL extension in three hours. I recently had a personal side-project which included setting up a working REST backend for a...
  • What’s your view? Over 25 years ago I got into an argument with my boss. He was frustrated with all the tasks that needed to be completed and feeling overwhelmed. I said, “Hey, why not go to a p...