Postgres WASM
supabase.com
supabase.com
> If anyone out there wants to work on an open source version of this full-time please reach out to me. [0]
Paul reached out and we started working on it almost immediately. Check out the repo here: https://github.com/snaplet/postgres-wasm
We have a blog post about some of the interesting technical challenges that we faced whilst building this: https://www.snaplet.dev/post/postgresql-in-the-browser
Like most things, this is built on-top of the amazing open-source projects that made this possible, but special mention goes to v86.js and buildroot. We just glued it together.
My hope is that we as a community can own this project and make PostgresQL, and the software that runs on it, accessible to a larger audience.
---
[0] Request for collaboration: https://news.ycombinator.com/item?id=32500526
When I was actively doing stuff with wasm (~2019), Brotli was the best compression approach. eg 16MB uncompressed -> 2.4MB compressed
https://github.com/golang/go/wiki/WebAssembly#reducing-the-s...
On my 50mbit/s (Germany :/) connection it's ~2 seconds
a2b67f25.bin : 8.26% (16777216 => 1386514 bytes, a2b67f25.bin.zst)(zstd at --ultra -22 level: 1149751 bytes).
postgres-wasm is an embeddable Linux VM with Postgres installed, which runs inside a browser. It provides some neat features: persisting state to browser, restoring from pg_dump, logical replication from a remote database, etc.
The idea was inspired by CrunchyData’s HN post about a month ago [1]. We love the possibilities of Postgres+WASM, and so Supabase & Snaplet teamed up to create an open source version. The linked blog post explains the technical difficulties we encountered, and the architecture decisions we made.
We’re still working hard on this, but it’s at a good “MVP” stage where you can run it yourself. Snaplet are working on a feature where you can drag-and-drop a snapshot into your browser to restore the state from any backup. Supabase are exploring ways we can run the entire Supabase stack inside the browser. You can find the Snaplet repo here [2], and the Supabase fork here [3]. There’s very little difference between these two, we just have a different browser UI.
Both Supabase team and the Snaplet team will be in here commenting if you want to know anything else about the technical details.
[0] Snaplet: https://www.snaplet.dev/
[1] Crunchy post: https://news.ycombinator.com/item?id=32498435
[2] Snaplet repo: https://github.com/snaplet/postgres-wasm
[3] Supabase fork: https://github.com/supabase-community/postgres-wasm
Wow! I feel like this is the lede. How much work was done supporting the VM and OS privatives (eg networking) vs PG specific work? I feel like a minimal Linux in the browser opens up a LOT more opportunities than just a database.
When figma got bought out, a lot of articles were written about “where’s the wasm applications”, and I feel like throwing Linux into a browser really shows potential. One commenter already wondered if it could be used to compile microcontrollers (so creative, i now want that too), I wonder if it can be used similar to Repl.it, with packaging test environments.
To be very, very clear, I would LOVE a write up about just the linux portion of this interesting project.
All of the heavy lifting here is done by v86: https://github.com/copy/v86
v86 can be used for a number of things besides Postgres - things like Repls or other entire applications are definitely achievable.
Networking between Postgres and the internet was a lot of work, and Mark came up with a neat solution detailed in the blog post. This solution can be used for any other application. If you're looking to run a native application in the browser using v86, the repo & blog post is a good launching pad.
The dream is a real wasm native postgres; in fact to get all of postgresql's cool shared mem proccess stuff and make it something like shared array buffer! The dream is also that WASI interface and new-school OS interfaces like memfd_create are increasingly aligned.
Instead of rationalization our interfaces, however, it's just emulation layer on top of emulation layer, tech debt all the way down.
----
I am not blaming you all in the slightest, to be clear. Obviously one needs to start somewhere. Just sighing at the state of things.
Do ask if you want to know more, especially if you are interested in throwing some $$ that way! :)
Making Postgres Wasm helped:
- v86[0] to find a new bug
- Providing a great deep-dive article that will trigger new ideas in the future
- Showcase the possibilities of Wasm and how you can overcome the current challenges
I really appreciate these projects are OSS :)
Congratulations for the project!
sadly though it's chrome only, not a standard of any kind (no other browsers support it that I know of). ESPHome can use it to program microcontrollers (along with the serial port support).
Classic Google...
1. Training websites
2. Interview challenges involving SQL
3. Client side tooling that loads data into your local machine and displays into a SaaS web app without the SaaS app ever having your data
Appreciate the hard work from Supabase and Snaplet on this!
I've used this to move data from a live Supabase database down to the browser for testing and playing around with things in a "sandbox" environment. Then I save snapshots along the way in case I mess things up.
To move a table over from my Supabase-hosted postgres instance to the browser, I just exit out of psql and run something like this:
pg_dump --clean --if-exists --quote-all-identifiers -t my_table -h db.xxxxx.supabase.co -U postgres | psql -U postgres
Keep in mind if you try something like this, our proxy is rate limited for now to prevent abuse, so it might not be super fast. It's easy to remove rate limiting at the proxy, though.
That only works if you live a blissful "all my hosted pg instances use the exact same version" world, which I've never seen be the case for even moderately sized projects. You're going to need multiple Postgres installs if you're going to need pg_dump/pg_restore, which you probably are.
(How you solve that problem, of course, is not a one-size-fits-all, and Docker may be the answer... or it may not)
There is already an official image available so you don’t need to write dockerfile yourself. Having an instance up and running is literally just a docker run command away.
Member mini mongo?
Supabase will be Meteor in no time.
As in they will crash and burn?
There's only one way to architect a PaaS. Next.js and Vercel's offerings are, essentially, also the same.
The real risk is having one person build it all. They have a team but not really. Personally I believe that's a good risk to take.
But I think it will take a powerful psychological toll to operate this way, having to pretend to have a team (because investors like teams and not solo founders), having to pretend this isn't Meteor (because investors don't like being reminded of "losers"), etc. etc.
Like downvote random Internet comments all you want, but actually I think it's a great idea to have one person do "Better Meteor," it's not my fault investors don't.
> having to pretend to have a team
In case you're talking specifically about supabase here, we're a full team: https://supabase.com/humans.txt
Whatever Postgres in WASM ends up being used for there's no way it repeats all those circumstances - at minimum Postgres is just a more appropriate tool then MongoDB circa 2015.
1. training website: you can use a hosted PG, or use a sqlite wasm
2. same as above
3. if the use case is being offline, then the web browser isn't very relevant. If the use case is to avoid a load on the server, the sqlite in wasm will be just fine.
It's only if you go into triggers and such that it might start being relevant, but then I'd start seriously questioning what on earth are you trying to do :D
All of that to say: well done to the team that has done it, really fun and interesting work, I just can't see the use from where I stand.
from a supabase POV (which is in the business of hosting Postgres databases), we will definitely be using this for training/tutorials. We have several thousand visitors to our docs every day, and hosting a database for every one of them is expensive.
We can now provide a fresh database for every user, and they can "save/restore" it with the click of a button is huge.
> use case is being offline
The offline use-case is definitely far-fetched in the current iteration. but that's the beauty of technology - something that seems impossible today can be mainstream in a decade.
And there are plenty of reasons why you may want to use PG over sqlite. Especially if you are trying to mimic a production environment which is PG. Personally I only ever use PG, and never have a reason to use sqlite.
What I suspect may happen is, the rise of web browsers of a 3rd kind where these are not really for browsing the web but running code written for native domains. So instead of browsing web of linked text, we can have a web of algorithms to process data and requests.
I shudder what performance a full-fledged application would demand. I know some people will embed this on an Electron app, for double the fun.
You know that it will end up being used for regular consumer apps. And once everyone is doing it, regular web pages being over 30MB and including an enterprise-grade SQL server engine will simply be accepted as normal, and everyone not doing it is a luddite.
Are there future plans at creating a native WASM version of Postgres? Making it run many times faster would certainly open up a lot more use cases.
(Disclaimer: I work on DuckDB, but have not worked on the WASM version myself)
> what hurdles you encountered in making a native WASM
I'm sure Mark & Peter can jump in with specifics but mostly it was due to complexity - there it probably can be done it's just that we took the path of least resistance.
> Are there future plans at creating a native WASM version of Postgres
We'd like that. If anyone would like to collaborate with Supabase + Snaplet to create a more "native WASM" version then please reach out
At the moment the CPU and memory snapshot of the VM (with Postgres) is 12 MB, and subsequent reloads are cached. So yeah, not the worst, but not great.
An optimization is that we're using 9P filesystem. So accessing anything on disk is lazily loaded over the network.
> Are there future plans at creating a native WASM version of Postgres?
Yup! I think that should be the goal, and we (Supabase & Snaplet) would be very happy to work with anyone that wants to build towards that.
This would be amazing! I can imagine a situation where external tables are managed by some MPP, and a WASM compute engine (Postgres, DuckDB, etc) would be able to at least read subsets/partitions of the full external table.
I wonder if the work required to make a native WASM Postgres would have to be split up into efforts for row-based vs column-based. Selfishly, I would love to have access to a column-based version first.
And here we see some ideas forming around "pluggable storage for PostgresQL": https://wiki.postgresql.org/wiki/Future_of_storage#Pluggable...
Seriously! If any of this sounds interesting to build, reach out, and we'll make it happen!
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 v86 which emulates an x86-compatible CPU and hardware in the browser.
I’m out-of-domain but very curious about this part - it seems like a pretty extreme solution with a lot of possible downsides. Does this mean the “just compile native code to WASM” goal is still far off?
For example, for just one of many hairy problems, consider that Postgres uses global variables in each backend for backend-local state (global state as such is in shared memory). How does this look in assembly, accounting for both the kernel and userspace components? This is the problem.
A general way to convey this is: the more system calls a piece of software uses, the more difficult a WASM target without architecture emulation becomes. And Postgres doesn't even obligate that many obscure ones.
In my experience in a large+mature enough codebase (particularly one that is already multi-platform, like Postgres appears to be) many of those requirements are wrapped in an abstraction layer to allow targeting new platforms, but some requirements (like memory mapping) could definitely be dealbreakers if the target platform doesn't naturally support them.
This solution still seems awfully complex (and probably not very efficient) but I certainly see why it's probably the "easiest" option.
Long story short, I think the need to bypass MMU hardware emulation would prove among the most difficult problems. It will probably require assistance from the compiler, I don't know enough about WASM to guess how mature such relocations would be.
The engineering and support teams at Greenplum, a fork of Postgres, have a tool (minirepro[0]) which, given a sql query, can grab a minimal set of DDLs and the associated statistics for the tables involved in the query that can then be loaded into a "local" GPDB instance. Having the DDL and the statistics meant the team was able to debug issues in the optimizer (example [1]), without having access to a full set of data. This approach, if my understanding is correct, could be enabled in the browser with this Postgres WASM capability.
[0] https://github.com/greenplum-db/gpdb/blob/6X_STABLE/gpMgmt/b...
[1] https://github.com/greenplum-db/gpdb/issues/5740#issuecommen... (has an example output)
https://github.com/wasmerio/wasmer-postgres
It only works for PG10, but I can't imagine it will take much effort to bring it up to the latest version
Overall this seems an inspiring thing. Thanks!
Do you mean, connect from 1 browser tab to another?
At this point, the proxy is necessary because all the major browsers block direct TCP/IP traffic. They allow websocket connections so that's how we're getting around it.
There have been proposals to open up TCP/IP traffic but they've all been shot down so for the security implications.
Here is my use case: We use firestore + PG. PG has a copy of all firestore data. And PG is used for search, aggregation, etc. (Everything firestore can't do). Sadly, the developer flow breaks during local development. Because each developer would need to have a local PG server running. My dream is to simply add PG via NPM and use the wasm version during local development. That way, everything simply works via NPM/node
I think an in-memory node only version of PG might even be simpler to achieve than the already developed approach. As it doesn't need the websocket workaround.
Can you substantiate this?
> It'd be naive to say that those in the groups are working only and only for the common good and don't pursue their employer's interests
This may be partly true without backing up what you were saying. You were saying something much stronger, which is that Google is deliberately holding back the internet. Now you're saying, "Well they aren't only working for the common good" which is just insinuation. Making the internet work better helps Google, which is why they do all sorts of things, from making Web browsers, to open sourcing codecs, to laying undersea cables. That doesn't imply anything negative.
Not to say there isn't anything negative. Just that your points don't seem to back that at all.
Since the topic is WASM here; here is the webassembly working group: https://www.w3.org/groups/wg/wasm/participants
15 Google, 7 Microsoft and rest are 1-2 seats max.
WebRTC https://www.w3.org/groups/wg/webrtc/participants
22 Google, 12 Microsoft, 7 Mozilla, rest are 1-2 seats max
Web Performance https://www.w3.org/groups/wg/webperf/participants
32 Google, 10 Meta
And the situation is similar in most groups: https://www.w3.org/groups/wg/
I agree that making the web work better is good for Google. But making the web good and advertiser friendly even at the cost of users and their privacy is better for them.
[EDIT]: I'm zero for three thus far. I give up.
The other one is pressing esc in some web ui if I've been vimming too recently and having it nuke whatever I've typed :-( (it was almost certainly in an Atlassian product, cause they're awesome like that)
The key combo still appears to be intercepted, as the menu flashes but it doesn't close the tab. So I doubt you can use it in the terminal, but at least you won't lose your work.
If you pin the tab but not in its own window, ctrl-W doesn't close the tab but it does switch to another tab.
Source for the first point: https://www.reddit.com/r/firefox/comments/rs2bhn/comment/hqn... The rest is from me trying things out just now on MacOS, where really I used cmd-W, because ctrl-W doesn't do anything on MacOS. I'm assuming the corresponding behaviours will apply to ctrl-W on Windows and Linux, but you should test before relying on this.
[1] https://github.com/johnhenry/actually-serverless [2] https://github.com/cloudflare/workerd
In my case, I use postgres along with postGIS for some of my services. Could this allow me to have some parity where the client can have a 1:1 table but populated and kept up-to-date with their own data to cut down on making network requests?
- Documentation: for tutorials and demos.
- Offline data: running it in the browser for an offline cache, similar to sql.js or absurd-sql.
- Offline data analysis: using it in a dashboard for offline data analysis and charts.
- Testing: testing PostgresSQL 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.
edit: formatting
I feel like Postgres would make this even more powerful.
edit: as always, I should read the whole article first. The idea of using it as a dev environment is very cool.
Not any more - iirc it was depreciated.
In the case of IndexedDB, I haven't looked into it, but Mozilla has the following to say about it:
>Note: IndexedDB API is powerful, but may seem too complicated for simple cases. If you'd prefer a simple API, try libraries in See also section that make IndexedDB more programmer-friendly.[0]
I suppose this project could make development easier by allowing developers to share server-side code? And it has the benefit of already having a large userbase.
[0]https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_A...
OSM + PostGIS in the browser has the potential to do for Maps, what Figma's WASM approach did for design.
Can you elaborate on this analogy?
---
I translate the JSON-based query interface into the corresponding SQL statements, leveraging the excellent JSON support that PostgreSQL offers.
One thing we are working on is putting postgres on an alternative filesystem using 9p. There's some really cool work by humphd that creates a filesystem inside IndexedDB[0]. We'd also like to maybe use the browser filesystem component to let you store the database on the host device in a path of your choosing. Not sure if these are possible yet, though.
Where's all those kernel hackers? Your help, we need. :)
The real problem is how you deal with the average user (who doesn't really backup properly) losing or crashing their device and thus their encryption key/data. You quickly end up with serverside storage and an email-based password reset again...
I can appreciate the technical effort made here. And I think opensourcing something closesourced is always a good thing to do. Documenting also contribute of the global knowledge for different but similar project. I liked the 'page_poisioning' part for size optimization on the blogpost, nice trick.
I have to admit I don't see a lot of "real case application" where this might came really handy, but, who knows, sometimes I'm surprised how OSS make the most of anything seemingly not interesting at first.
If you're building a product on Postgres and you want expose an evaluation version, or teach someone a Postgres functionality then build on top of this.
In a lot of ways, repl.it is doing this.
[0] pg-sql.com/
[0]https://medium.com/wasmer/announcing-the-first-postgres-exte...
Don't see why thats a hindrance... Presumably you'd be storing GBs of data with this client-side. Whats 30mb in that context?
Amazing job... very excited to see this develop along with more wasm.
I've lost count of the number of projects I saw get burned by this common mistake.
https://developer.mozilla.org/en-US/docs/WebAssembly/Caching...
[1] https://direnv.net/ [2] https://asdf-vm.com/guide/introduction.html#direnv
It seems that WASM could be an alternative container solution.
This approach is like an intermediate step before recompiling it to wasm.
I would be interesting to see a comparison between a full recompiled version running on top of a wasm runtime vs some container solution but seems they found a lot of problems recompiling it.
1. It's a slightly difficult task, and in doing this we hope to spur others to think about using WASM to run things they didn't think were possible before. Before Crunchy did this, nobody really knew this was possible. This project is a framework for you to port something new and exciting to run under WASM in the browser. What's that going to be?
2. We love Postgres. It's our favorite database and this tool gives us a quick and simple sandbox to try out new things that might mess up our production (or even dev) database. Got a crazy idea that might not work? Try it in the browser and if it doesn't work, refresh the page and start over.
3. My goal is to eventually have an entire version of Supabase running in the browser as a basic dev / experiment tool. This would make a great quick and easy way to try out Supabase, or even to do full scale development, after which you can migrate your data up to your staging or production databases.
While I've since switched to the native https://postgres.app/, it will be nice to be able to spin up a fresh postgres test db in the browser in the future.