Postgres without SQL: Natural language queries using GPT-3 & Rust Jelte Fennema Senior Software Engineer @ Microsoft Developing Citus, PostgreSQL, & PgBouncer @JelteF | @citusdata | @AzureCosmosDB 2023-04-18

My secret problem I find it very difficult to write working Postgres queries

Solution I asked this question at: https://chat.openai.com/

No content

My not so secret problem • I don’t like doing boring things • Copy pasting schemas and queries around sounds pretty boring

How to avoid boring stuff • Postgres has all this information • Can I tell Postgres talk to ChatGPT directly? • I would need to add some functionality to Postgres

Postgres extension to the rescue • Allows adding new functionality to Postgres such as types or functions • Part 1: A shared library that postgres loads (.so file) • Part 2: Some SQL definitions, e.g. function definitions • So, let’s write an extension!

I like Rust • It feels modern: everything is an expression + pattern matching • Its type system is fancy: Like Haskell but I can read it • Secure: No memory leaks, no double frees, no data races • It’s as fast as C/C++ • It has a package manager Rust logo by Rust Foundation, CC BY 4.0, https://commons.wikimedia.org/w/index.php?curid=40715219

I have been playing with Rust for a long time • I have a popular Rust library: derive_more • It automates writing boring boilerplate • 7 years old

But I never used Rust professionally • So, let’s change that! • How do I combine Postgres and Rust? And maybe even Citus?

Let’s go write a Postgres extension in Rust

Setup is extremely easy • Install pgx, also installs 5 postgres versions: • Create a new extension:

Oh no, we need a name for this extension • pg_gpt is the obvious choice but is already taken  https://github.com/cloudquery/pg_gpt • gptpg: palyndromes are awesome but is hard to pronounce • Final choice: pg_human

Just double checking

Setup continued • Create the new extension

Has built in Hello World

So let’s make some small changes • What? Why does it take 7 seconds to compile this tiny extension?

GNU linker (gold) is really slow

But mold is fast

Okay now can actually start. What do we need? • Getting the schema definition • Sending requests to the OpenAI GPT API • Executing SQL • Returning arbitrary SQL results

Getting the schema definition • Ugh, why doesn’t Postgres have a built-in function for this? • Fine I’ll fetch stuff from the catalog tables myself and concatenate some strings

Sending requests to the GPT API • Use the Rust package manager to get an API client

Sending requests to the GPT API • Give the AI some confidence

Sending requests to the GPT API • Tell it what you have • Tell it what you need • Tell it what you do not want

Sending requests • Rust loves async and threads • Async support in pgx is non-existent • Postgres and pgx don’t like threads

Executing SQL • Postgres has SPI (Server Programming Interface) • pgx has nice Rust bindings for SPI

Returning arbitrary results • Another problem: Postgres requires functions to have known columns • Let’s go fully NoSQL and just return a single json column

Demo time

Where to find this amazing Postgres extension https://github.com/JelteF/pg_human (USE AT YOUR OWN RISK)

Future ideas • Explaining explain plans • Index suggestions • Citus distribution column suggestions

Any questions?

Resources