03 Oct 2022

•

11 minute read

Today we're open sourcingpostgres-wasm with our friends atSnaplet.

postgres-wasm is a PostgreSQL server that runs inside a browser. It provides a full suite of features, including persisting state to browser,restoring from pg_dump, and logical replication from a remote database.

We're not the first to run Postgres in the browser - that title belongs to the team atCrunchy Data who shared theirversion of thison HN a month ago. It's awesome, and we wantedanopen source version, so we teamed up with Snaplet build it.Let's explore how it's built, and some extra features we've added.

Demo#

Before we get into the technical details, try it out yourself (warning: it will download ~30MB):

wasm.supabase.com

To run it locally:

And open a browser atlocalhost:3000.

Features#

Our demo version has a few neat features!

• Postgres 14.5, psql, pg_dump, etc.
• Save & restore state to/from a file.
• Save & restore Postgres state to/from the browser storage (IndexedDB).
• Quick start from a state file or fully reboot the emulator.
• Memory configuration options from 128MB to 1024MB.
• Adjust the font size for the terminal.
• Upload files to the emulator (including database dumps and CSVs).
• Download files from the emulator.
• Outgoing network connectivity from the emulator to the internet.
• Incoming network tunnel to postgres port 5432 inside the emulator.

Why?#

That's a good question.postgres-wasm is currently about 30mb. So at this stage, running Postgres in the browser isn't great for general use-cases.It has a lot of potential though. Some ideas we'll be playing with over the next few months:

• Documentation: for tutorials and demos.
• Offline data: running it in the browser for an offline cache, similar tosql.js orabsurd-sql.
• Offline data analysis: using it in a dashboard for offline data analysis and charts.
• Testing: testing PostgreSQL functions, triggers, data modeling, logical replication, etc.
• Dev environments: use it as a development environment — pull data from production or push new data, functions, triggers, views up to production.
• Snapshots: create a test version of your database with sample data, then take a snapshot to send to other developers.
• Support: send snapshots of your database to support personnel to demonstrate an issue you're having.

Repository Overview#

We've divided in our repository into three folders: a virtual machine, a web application, and a network proxy.

Virtual Machine (VM)#

We create an embeddable Virtual Machine (VM) usingBuildroot.Our VM is a stripped-down Linux build with Postgres installed.

Seesource code.

Web application#

Next, we need to run the VM inside our browser. How?WASM.We usev86 to run our VM inside the browser. Our demo application is very simple - plain HTML and some basic styling.

Seesource code.

Network proxy#

Running Postgres in a browser is great, but connecting to it with PgAdmin is even better. Unfortunately browsers block TCP network access to the VM.To circumvent this, we proxy the traffic through websockets. We run a fork ofWebsockproxywhich allows the emulator to talk to the internet by converting data sent over a websocket port into TCP packets.Our fork of Websockproxy adds the ability to tunnel into the Postgres server.

Seesource code.

Technical deep-dive#

There were a lot of hurdles that we discovered throughout the development of postgres-wasm.

WASM#

The first thing to point out is that our implementation isn'tpure WASM. We attempted to compile Postgres for WASM directly from source,but it was more complicated that we anticipated.

Crunchy's HN post provided some hints about the approach they took, which was to virtualize a machine in the browser. We pursued this strategy too, settling on v86which emulates an x86-compatible CPU and hardware in the browser.

PostgreSQL 14 segfault errors#

We quickly had PostgreSQL 13.3 running using an outdated version of Buildroot. But version PG14+ wouldn't start, giving a segfault during initialization. We tried:

• manually copying build files for PG14 into the older version(s) of Buildroot
• building with (many) newer copies of Buildroot
• adjusting kernel parameters and environment settings such as the amount of memory allocated to the emulator, etc.

Eventually, Fabian, the creator of v86 suggested we turn off JIT compilation for v86 and that solved the issue.He narrowed it down to a bug in v86 and is pushing an update that will fix it.Switching Postgres from posix to sysv memory management also solved the issue for the current release of V86.

Optimizing startup time and image size#

With PG14 running in the emulator, we shifted our focus to performance. The image size for the emulator was too big for a browser-based tool.Even with our best efforts, a compressed snapshot was over 30mb - a fairly large payload to download before you can see any interaction.

We solved this by booting only a minimal Linux image and then dynamically loading the rest of the VM over HTTPS after initialization.

We achieve this by mounting a compressed9P filesystem in the VM.9P provides aPython script which takes a filesystem folder,renames every file an 8-character name and produces afilesystem.json file representing a nested structure with files, original file names, sizes, etc.We thencopy this compressed output to the VM.We modified the kernel command line and v86 boot parameters to boot directly from the 9P filesystem, and even put our kernel file into the p9 filesystem.All non-essential files are loaded asynchronously over HTTPS in the browser as needed.

The initial state was smaller, but still 15-20mb in size. We discussed this with Fabian who pointed us towards thepage_poison=on kernel parameter.This parameter allowed us to clear caches before creating the snapshots, by forcing Linux to write arbitrary bytes on freed memory instead of random bytes,so unused memory is compressed much more efficiently.

The end result of all these changes? The compressed initial state file is about 12mb - including arunning network state and Postgres 14.4 running with psql loaded.

We experimented without Postgres started too - this produced an even smaller initial state but the time to initializePostgres was usually longer than the time to download a few extra megabytes. We opted for a faster startup time.

Additionally, without a running state you'd hit a performance penalty every time you refreshed the page,since Postgres would need to be initialized each time. In our current version, after the initial state isdownloaded and cached by the browser the VM feels almost instant on refresh.

Networking#

Networking was particularly difficult to solve. For security reasons, browsers only support access to a nominal set of ports.In short, browsers speak “80/443” and Postgres speaks “5432” so there was no way for our Postgres to communicate with the outside world.

WASM isn't much help here - a WebAssembly module running in the browser is generally not able to do anything outside itself,except for calling functions exposed from JavaScript.

Browsers provide another connection option for us however: Websockets. The nice thing about websocket connections is that they arepersistent,so all we needed was to figure out how to proxy the virtual machine traffic through a websocket connection.

The VM includes an emulated network card (ne2k-pci) and aproxy_url startup parameter which points to an external serverrunning a websocket proxy (i.e. on the internet). The websocket proxy establishes connections via a websocket port, then accepts raw ethernet packets.It turns those raw packets into TCP/IP packets and routes them between the Internet and the VM.

With our tunnel established, we could now send network traffic to the outside world. For example, try “starting” the network on the VM,"exiting" out of psql (cmd+D), and then runping 1.1.1.1.

Most v86 VM's use an open proxy atwss://relay.widgetry.org/ based onwebsockproxy.This proxy allows the VM to communicate with the outside world. It can handle all the basic use-cases like loadingdata from other PostgreSQL databases usingpg_dump andpsql, or operating as read-only replica of another database.

However, this proxy doesn't allow incoming traffic to be routed into a VM running locally on a laptop.For instance, you can't connect a localPgAdmin4 to the PostgreSQL instance running inside your browser.So weforked theproxy andadded nginx to provide a reverse proxy.

The reverse proxy listens on a range of ports, mapping each port to the VM at a specific private address you want to reach.The simple solution was to take the last 2 segments of the private IP and map it to a port number. So if your private IP is 10.5.6.123,you connect to the proxy on port 6123 and that points you to PostgreSQL (running on port 5432) on private IP 10.5.6.123. Shortened addresses are padded,so 10.5.6.2 maps to port 6002 and 10.5.6.44 to port 6044.

Sending commands to the emulator from the browser UI#

Once the emulator is running, in the words of Fabian, “it runs as a black box process”, and the only way to communicate between theUI and the operating system running in the emulator is throughserial0_send to send keystrokes over the emulated serial port,or through thecreate_file function which uploads a file to the running emulator.

To restart the network after restoring a state file, we need to run a short script to unbind and re-bind the emulated network card(so it gets a new mac address and thus a unique private IP address at the proxy), and then a standard network restart command.We could run this script by sending keystrokes to the emulator, but that's clumsy. What if the user is at apsql prompt? Then we'd send\! script_command.But if they're at an OS prompt, that won't work.

We solved the issue by creating a folder called\inbox and added a listener to the folder. When files arrive there, if the file ends in.sh (a shell script),wechmod +x that file, execute it, then delete it. That allows us to run commands “in the background” without upsetting the UI.

Just for fun#

If you want to do something very cool, you can connect to another user's Postgres instance that they are running in their browser.Here are the steps:

1. Find a friend (sometimes the first step is the hardest)
2. Tell them to go towasm.supabase.com
3. Tell them to “Start Network” and get their Network URL
4. Have them set a password on their databaseALTER ROLE postgres WITH PASSWORD 'my_password';
5. On your own machine, open a terminal and runpsql postgres://postgres:my_password@proxy.wasm.supabase.com:<PORT>
6. And you're in! You're now connecting to a remote Postgres instance, inside a VM, inside a browser, from a terminal on your own computer.

Database Replication

Even more mind-blowing - it's possible to replicate data from an online PostgreSQL database (for example, a Supabase project) to PostgreSQL running inside your browser,and vice-versa using Postgres's logical replication. The steps are no different than any other Postgres database - just use the proxy URL as the target.

What's next?#

For now, this is very experimental - but it has a lot of potential. If you want to get involved, please reach out to us orthe team atSnaplet. The work they're doing over at Snaplet is incredible, and we've had a blast collaborating with them.

• Snaplet repo:https://github.com/snaplet/postgres-wasm
• Supabase fork:https://github.com/supabase-community/postgres-wasm