14 ms·

Postgres WASM

887 points by kiwicopple 4y ago

kiwicopple 4y ago
Hey HN, we’re excited about this launch. This was a collaborative effort with the team at Snaplet [0]. 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/ https://www.snaplet.dev/ [1] Crunchy post: https://news.ycombinator.com/item?id=32498435 https://news.ycombinator.com/item?id=32498435 [2] Snaplet repo: https://github.com/snaplet/postgres-wasm https://github.com/snaplet/postgres-wasm [3] Supabase fork: https://github.com/supabase-community/postgres-wasm https://github.com/supabase-community/postgres-wasm
- boltzmann-brain 4y ago
  Do you have a benchmark of load times after the includes are cached? Depending on whether it's < 100 ms or > 1s it will make the applications for this very different.
  - kiwicopple 4y ago
    We don't have any benchmarks yet, but it's definitely something we should add/automate now that we're past the "POC" phase
- gurjeet 4y ago
  The links at the bottom are broken; presumably the post was written in Markdown, HN doesn’t understand that.
  - kiwicopple 4y ago
    Thanks Gurjeet - fixed
- vineyardmike 4y ago
  > an embeddable Linux VM with Postgres installed, which runs inside a browser. 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.
  - kiwicopple 4y ago
    > How much work was done supporting the VM All of the heavy lifting here is done by v86: https://github.com/copy/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.
    - Ericson2314 4y ago
      This is cool, but is also somewhat saddening too. 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.
      - pistoriusp 4y ago
        I think we'll see this - and it would be amazing to get someone to work towards this - once there is an amazingly compelling use case that resonates with the market, then I think there's room for optimization!
CSDude 4y ago
This is really cool. It'd also make a great teaching environment.
pistoriusp 4y ago
Peter from Snaplet here. A month ago I saw the CrunchyData post and wanted to play around with the code that made it happen, it wasn't OSS so I asked for help: > 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 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 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 https://news.ycombinator.com/item?id=32500526
- justinclift 4y ago
  Have you looked at compressing that ~16MB wasm file with something like Brotli? 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-size-of-wasm-files https://github.com/golang/go/wiki/WebAssembly#reducing-the-s...
  - pistoriusp 4y ago
    Our demo is on Netlify, so I'm assuming that they've got gzip or something along those lines enabled. On my 50mbit/s (Germany :/) connection it's ~2 seconds
  - ankraft 4y ago
    Zstandard is able to compress this file to 1.4MB. a2b67f25.bin : 8.26% (16777216 => 1386514 bytes, a2b67f25.bin.zst)
    - dchest 4y ago
nitsky 4y ago
This is a really exciting milestone for WASM! Has anyone tried compiling postgres for wasm32-wasi and implementing a wasi-compatible interface for the file system and other OS bits postgres needs? I think that would be a big improvement.
- burggraf 4y ago
  Supabase developer here. I worked on this project with the guys over at Snaplet. 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. [0] https://humphd.github.io/browser-shell/ https://humphd.github.io/browser-shell/
  - nitsky 4y ago
    Using IndexedDB, the forthcoming SQLite support in Chrome, or the browser filesystem components all might be good paths. Either way, what do you think about using WASI instead of Linux? I think it would reduce the overhead significantly.
    - burggraf 4y ago
      I'm not familiar with WASI but I'll take a look. We're using Buildroot now and it's nice and small, but we could make it smaller by optimizing the kernel. Now, if I only know how to optimize the kernel without breaking things :) Where's all those kernel hackers? Your help, we need. :)
danielvaughn 4y ago
Very impressive technically, but what are the practical use cases for this? We have IndexedDB and WebSQL, so apart from the other comments mentioning using it in tutorials, I'm not sure what the value add is. edit: as always, I should read the whole article first. The idea of using it as a dev environment is very cool.
- jdmdmdmdmd 4y ago
  >We have [...] WebSQL 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_API https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_A...
  - danielvaughn 4y ago
    Oh wow, I never knew WebSQL was deprecated. And yeah IndexedDB is pretty complicated, I've tried working with the API a few times but always abandoned it for simpler solutions.
- kiwicopple 4y ago
  There are some things that are uniquely beneficial for Postgres beyond the data storage - the main one I'm excited about is using PostGIS inside the browswer. OSM + PostGIS in the browser has the potential to do for Maps, what Figma's WASM approach did for design.
  - bobmaxup 4y ago
    > 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?
  - 4y ago
bearjaws 4y ago
The use cases here are going to be really wide spread in my opinion, just a few ideas off the cuff. Obviously the 30mb size means it won't really be for regular consumer apps, but for enterprise or specific tasks it can make a lot sense. 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!
- burggraf 4y ago
  Supabase developer here. 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.
  - alex_duf 4y ago
    Correct me if I'm wrong, but given your profile (I assume someone in the tech world), nothing stopping you from doing all of the above with a local pg. If installing is annoying you could run it in docker.
    - burggraf 4y ago
      You're absolutely right, you can use a local pg. This just makes it easier for me, as it's sort of a "sandbox" environment and I can easily take snapshots to do A/B testing or roll things back. I can also send a snapshot to a coworker so they can get my entire environment with all my data in a few seconds.
      - aaronax 4y ago
pashkinelfe 4y ago
For me it's interesting can this work without the need of external proxy server which seems to be needed only to overcome browser connections limitations and nothing else. May there be some more "internal" way to implement/allow these connections? Overall this seems an inspiring thing. Thanks!
- pistoriusp 4y ago
  Snaplet person here: Totally. It cannot, and it very likely will never be able to do that. Raw sockets were added, and subsequently removed, from Chromium. Do you mean, connect from 1 browser tab to another?
  - burggraf 4y ago
    Interestingly we can do this, sorta, but in Chrome only. Using arp you can open a network between multiple tabs of the same Chrome instance, putting each browser vm on a different private ip. I only see this useful, though, for testing clustering systems. There won't be any outside internet connectivity in a setup like this though.
- burggraf 4y ago
  Supabase developer here. 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.
vivekin 4y ago
This is very cool. This will open up a lot of different use cases.Congratulations!
yangosoft 4y ago
Great! Really impressive. It seems that WASM could be an alternative container solution. This approach is like an intermediate step before recompiling it to wasm.
- encryptluks2 4y ago
  One that performs way worse then existing container solutions but that will of course be used to distribute binary blobs.
  - yangosoft 4y ago
    Agree, it is much slower. 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.
    - burggraf 4y ago
      I love how you guys are already pushing us to think about performance issues on this project. Hacker news really pushes technology to the next level!
      - encryptluks2 4y ago
        A good comparison might be something like Flatpak or gVisor.
angelmm 4y ago
One of the things I love from this post is the collaboration on this kind of OSS projects. 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! [0] https://github.com/copy/v86 https://github.com/copy/v86
- MuffinFlavored 4y ago
  Could I hypothetically pass USB through to v86? Like libusb on the host into v86 on the guest? Or do USB over IP or something?
  - simcop2387 4y ago
    Chrome supports a USB api that could potentially work for that, https://developer.chrome.com/docs/extensions/reference/usb/ https://developer.chrome.com/docs/extensions/reference/usb/ 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).
    - rjh29 4y ago
      > This API is part of the deprecated Chrome Apps platform. Learn more about migrating your app. Classic Google...
      - lights0123 4y ago
        That's the old one. https://web.dev/usb/ https://web.dev/usb/ is the supported one.
    - MuffinFlavored 4y ago
      What about this: https://wicg.github.io/webusb/ https://wicg.github.io/webusb/
    - 4y ago
jd3 4y ago
This is amazing! I used https://rextester.com/l/postgresql_online_compiler https://rextester.com/l/postgresql_online_compiler to rapidly iterate/prototype some complex jsonb queries for a project at work ~3 years ago, but it has since been locked behind a patreon paywall. While I've since switched to the native https://postgres.app/ https://postgres.app/, it will be nice to be able to spin up a fresh postgres test db in the browser in the future.
frankfrank13 4y ago
One interesting use case that wasn't mentioned is edge computing. If you buy into Chrome-as-a-container that can run on an "edge" more easily than a data center, this is a step in that direction
- pistoriusp 4y ago
  And those are very likely to have tcp sockets at some point in the future (or at least that is the hope!)
  - tonetheman 4y ago
    Funny thing is that Chrome used to have sockets. As an extension but they solidly worked. I wrote a socks proxy that ran in the browser. It worked really well.
    - pistoriusp 4y ago
      I was reading about it, but I assumed it was just in Chromium? Did it actually make it into Chrome as well!
mockery 4y ago
The first thing to point out is that our implementation isn't pure 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 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?
- burggraf 4y ago
  Supabase developer here. I've tried compiling directly to WASM, but it did not go well. As I recall, there were features used by PostgreSQL that WASM didn't support yet. This is definitely something we'll revisit though, especially as WASM matures!
- fdr 4y ago
  The problem is you need operating system features to run Postgres as-is, e.g. mapping memory, forking processes, manipulating files. What is missing is a WASM kernel that skips the x86 emulation but implements enough of the other stuff. 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.
  - mockery 4y ago
    Thanks, those are specific requirements I could definitely see WASM struggling to meet. 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.
mytherin 4y ago
Looks really nice, but I'm really curious about what hurdles you encountered in making a native WASM version of Postgres. It seems that both the performance and binary size suffer immensely from the VM running underneath. For example, if I compare to DuckDB-WASM [1] there is an immense difference in load time and query execution speed. 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) [1] https://shell.duckdb.org https://shell.duckdb.org
- kiwicopple 4y ago
  congrats to the DuckDB team for their WASM version. I've been following it from afar and it's very impressive. > 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
  - burggraf 4y ago
    Supabase developer here. Yes we'd love to collaborate on a native WASM version. I tried this first before starting on this project and I didn't get very far. I'll have to go back to my tests on that, but it seemed like WASM wasn't yet capable of compiling Postgres due do some libraries not being supported. Hopefully we can find workarounds or push WASM into a new era of compatibility :)
- pistoriusp 4y ago
  Snaplet person here: Having a native WASM version would be a huge win for everyone, especially is storage is decoupled from compute! 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.
netcraft 4y ago
I don't mean to take anything away from this post - its quite amazing and I can't wait to play with it more - but wanted to mention that there are sites out there that I believe solve the training portion maybe a little bit better, at least if all you want is to train on SQL not DBA type actions. My favorite is https://dbfiddle.uk/ https://dbfiddle.uk/ - the ability to link and fork a set of statements is extremely handy.
- pistoriusp 4y ago
  Totally, and what we're really trying to achieve here is to make this available to everyone, so that we can improve it as a collective. 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.
deleted 4y ago
[deleted]
r3tr0 4y ago
Another implementation: https://xn--yv9h.fm/emu/?m=jbdf7ZqvfgR4dCizypXxMP https://xn--yv9h.fm/emu/?m=jbdf7ZqvfgR4dCizypXxMP
- burggraf 4y ago
  This is cool! It's running debian and posgres 11. It's quite a bit larger (that's why we went with Buildroot, and frankly, we could do a lot better at trimming things down even more). But it's definitely a great implementation!
  - r3tr0 4y ago
    yes. this is just a test dummy for something bigger I have. we should chat. Feel free to DM me.
    - r3tr0 4y ago
      I originally went with buildroot too, but then I realized some things...
    - burggraf 4y ago
      Would love to chat! Love to get your ideas on this.
      - r3tr0 4y ago
        what is the best way to contact you.
        burggraf 4y ago
        You can email me at mark @ supabase dot io. (Yeah, I know there are probably robots that can parse that as valid email address, but I'm old-school :))
        r3tr0 4y ago
jcuenod 4y ago
Hint: Don't use ctrl-w in their terminal. Turns out that has another meaning in Firefox... [EDIT]: I'm zero for three thus far. I give up.
- mdaniel 4y ago
  That drives me starkraving mad in AWS CloudShell, because muscle memory happens faster than thinking. I thought there was a web/dom method designed for gaming like "capture all input events" but maybe it has to be in full-screen to work or something 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)
- jlokier 4y ago
  If you open the tab in its own window (i.e. no other tabs) and also pin the tab, turns out ctrl-W doesn't close the tab in Firefox. 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/hqnylbj/?utm_source=reddit&utm_medium=web2x&context=3 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.
xani_ 4y ago
Huh, thought for a second that was a WASM support for stored procedures or something and was excited for a second
- supamichelp 4y ago
  Well it's not what you were expecting, but your idea sounds interesting too! Maybe you could muster the same open source forces to make it a reality?
  - kiwicopple 4y ago
    I believe the team at Wasmer have worked on something like this: https://github.com/wasmerio/wasmer-postgres 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
- michaelmior 4y ago
  This was also what I was expecting. Another reply to your comment mentioned Wasmer, which I found after a quick Google search. Unfortunately it looks like the last commit was almost 2 years ago and there's an issue where people are inquiring about the status of the project that hasn't seen a reply from the maintainers in about the same amount of time.
steinlagems 4y ago
Scott from JavaScript Jam here. We are so excited that we are going to have Peter with us on Twitter Spaces this Wednesday to chat about Postgres WASM. Join us Live here: https://twitter.com/i/spaces/1yoKMZbgngkGQ https://twitter.com/i/spaces/1yoKMZbgngkGQ See you Wednesday!
101008 4y ago
Now I only need Django WASM and I can distribute standalone applications :')
- pistoriusp 4y ago
  Interesting, you're the second person to ask about this! Is there something in Django world that makes standalone application appealing?
  - 101008 4y ago
    I think it is just personal: I feel very proficient with Django and as developer I love it. It probably would be possible to do the same with any other framework such as Flask or Laravel (in PHP)
mradek 4y ago
I haven't messed around with wasm but I like postgres a lot. Just curious what are some use cases for this? Also not a supabase user even tho it looks cool. 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?
- spiderice 4y ago
  There is a "why" section in the blog post that gives a number of examples. Namely: - 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
tmpz22 4y ago
I expect to see WASM tooling expose that Google's stewardship of Chromium has left many, many, features to be desired. You can blame it on the bureaucracy and legacy cruft of other companies like Microsoft and Apple, but at the end of the day there's just so much stuff you can't do with web apps (and oh wow just happens to align with Google's Ad and Cloud businesses).
- robertlagrant 4y ago
  Can you list the features you mean that Firefox and Safari have that the Chromium project has left out because of Google's business interests? Be interesting to see.
  - tmpz22 4y ago
    My comment doesn't assert Firefox or Safari are ahead on features. My comment is about Google/Chrome still being culpable as a market leader despite Apple (Safari) and Microsoft (IE legacy) likely slowing down browser development in general.
    - robertlagrant 4y ago
      I don't understand what you're comparing Chromium to if you're saying it doesn't have features for business reasons, when the other browsers also don't have those features.
      - neoberg 4y ago
        In almost every standards working group Google holds the most seats. 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.
        robertlagrant 4y ago
        > In almost every standards working group Google holds the most seats 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.
_joel 4y ago
Completely naive question here, but could you cluster the WASM instances (with mods, I'd assume, more is it possible via the WASM abstractions?). Not sure why but seems like a interesting thing :)
- burggraf 4y ago
  Yes, you probably could do that. It's something I've considered. Early on before I got the networking working through the proxy I found a way to use arp to connect multiple browser tab versions of this in a pseudo-private-network configuration. It only works locally and only works inside chrome, and only a single instance of chrome at that. But it was cool idea and the only use case I could think of was setting up a local cluster of Postgres instances within the browser. And my kids think playing video games is fun! Meh.
fcoury 4y ago
Interesting. I have written a translation layer for the MongoDB wire protocol [0] that persists to PostgreSQL. I am wondering if I could make this layer also compile to wasm and run in the browser. --- [0] http://oxidedb.com http://oxidedb.com
- burggraf 4y ago
  Wow, this is awesome! So as I understand it, this uses Postgres and JSON fields to emulate the features of MongoDB, sort of as an abstraction?
  - fcoury 4y ago
    Thank you! It's exactly that: basically you don't need to keep an additional MongoDB instance if you already use and maintain a Postgres server somewhere. I translate the JSON-based query interface into the corresponding SQL statements, leveraging the excellent JSON support that PostgreSQL offers.
agustinbena 4y ago
I can not still agree to the usefulness to run this in a WebBrowser at 30MB. But for running in the backend over a wasm runtime, seems that the use case that make sense for the moment.
kaboomshebang 4y ago
The more I read about WASM the more magical it becomes...
jhd3 4y ago
I was wondering if anyone had thought about using this to experiment with the planner. 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/bin/minirepro https://github.com/greenplum-db/gpdb/blob/6X_STABLE/gpMgmt/b... [1] https://github.com/greenplum-db/gpdb/issues/5740#issuecomment-420542147 https://github.com/greenplum-db/gpdb/issues/5740#issuecommen... (has an example output)
NoGravitas 4y ago
- pistoriusp 4y ago
  Oh man, sorry.
rkagerer 4y ago
Aside from folks who already have Postgres infrastructure they want to replicate, what sort of applications would find this a better fit than using SQLite on the client side?
javajosh 4y ago
This is very cool. An important area of ignorance (for me) is how well browsers do with local data. A nightmare scenario is that a user happily uses your app, stores data locally, and then Chrome decides they don't need localstorage anymore. What's the status on local data reliability in browsers?
- Joel_Mckay 4y ago
  Wise-men know better than to integrate google stuff into mission critical systems. Their behavior is purely governed by the information consolidation facet of any product or service they offer. Thus, given they already sample user traffic on 80% of the web, the company will continue to cull unpopular projects at astonishingly high rates. I've lost count of the number of projects I saw get burned by this common mistake.
aitchnyu 4y ago
I wish we could replace Docker for dev environments with asdf which examines your project and makes available language tooling for Ruby, Node etc and other tools with pinned versions. With this we could cd to our project and have a Postgres server ready for this project.
- iib 4y ago
  I think `direnv` [1] may be closer to `asdf` than this. They also mention it, and the integration, `asdf-direnv` on their website [2]. [1] https://direnv.net/ https://direnv.net/ [2] https://asdf-vm.com/guide/introduction.html#direnv https://asdf-vm.com/guide/introduction.html#direnv
termcaps 4y ago
Well done. 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.
Kye 4y ago
There have been a lot of new WASM things popping up on HN. Was there some major new change the enabled all this?
- lioeters 4y ago
  I've also noticed a number of fun WASM projects this year. I think the underlying WASM features that enable all this have been around for a while, so the breakthroughs are probably coming from the community and ecosystem growing incrementally and synergistically, building on each other's work and collaborating.
  - encryptluks2 4y ago
    I believe they are coming from people wanting to push people to get confortable with running binary blobs in their browsers so they can suck up all that juicy personal information leaked by browsers.
    - termcaps 4y ago
      Sadly, I'm not sure you really need binary blobs to "suck up all juicy personal informations" in the first place.
    - lioeters 4y ago
      WASM is unnecessary and unsuitable for such a purpose, since it would need to go through JavaScript to get that information anyway. It's far more sandboxed than JS, and cannot get any more personal data than JS can already.
  - Kye 4y ago
    It does have a similar feel to how things went when DHTML was becoming The Thing. People would share increasingly elaborate demos that built on older stuff until there were whole web applications. Then AJAX happened and it was all over. It seems like WASM is finally fulfilling all those early fantasies of the browser as the operating system.
andersonmvd 4y ago
I was trying to find a way to make apps "data-leak resistant" and one step into this direction was to let the user store the data by bringing his/her own database. I even made a poc https://github.com/andersonDadario/byoda https://github.com/andersonDadario/byoda (explanation in the blog post link found on the readme) - but no user would manage his own database. Having a database in the browser opens more possibilities though. I will give it some thought. Looking forward to what else will be built on WASM.
- unilynx 4y ago
  For that to work you won't even need a database in the user's browser - you could just store an encryption key as a cookie today which you use to encrypt all your serverside data when the user logs in 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...
  - andersonmvd 4y ago
    It's complicated indeed. A simple "clear browsing data" would make him lose the data forever hehe. And if you keep the key in the server, it would be "hackable", which is what I was trying to avoid. I haven't looked at it but https://solid.mit.edu/ https://solid.mit.edu/ looked promising.
yarg 4y ago
30mb wouldn't be that bad if the browser was caching WASM dependencies. https://developer.mozilla.org/en-US/docs/WebAssembly/Caching_modules https://developer.mozilla.org/en-US/docs/WebAssembly/Caching...
raitucarp 4y ago
Wow, the dream of decentralized web comes true, soon. With encryption and wasm, we are gonna conquer the goals toward true decentralized web.
burggraf 4y ago
So I've been using this for a couple weeks now on my mobile phone. There's really no reason to do this, other than I'm sitting in a car waiting for my wife to come out of the grocery store and I just feel like writing some some SQL code -- because I can. If you noticed, there's a little keyboard icon at the top of the demo that toggles on a mini keyboard with ^c, ^d and cursor keys. That's because the iPhone doesn't have those keys, and I'd start pinging a server and realize I can't break out of it and now I need to reset the whole emulator. Anyhow, that's why that little toggle is in there. :)
OOPMan 4y ago
This seems a step closer to the embeddable postgres I've wanted for ages.
- swyx 4y ago
  why most it be postgres and not sqlite? just curious on what you were looking for out of it
  - burggraf 4y ago
    Why Postgres? (There's already a WASM version of SQLite [0], btw, and it's very cool.) We did this with Postgres for a number of reasons: 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. [0] https://sql.js.org/ https://sql.js.org/
    - swyx 4y ago
      oh i mean i can see why you as a supabase employee would want it, just wanted to get more opinions from third parties :)
Woberto 4y ago
Stephen Grider made a cool website[0] that allows people to make and use databases using PostgreSQL statements for his udemy course[1] about SQL. It's great for learning and testing and has been around a while so I feel like he should get some attention/credit too [0] pg-sql.com/ [1] https://www.udemy.com/course/sql-and-postgresql/ https://www.udemy.com/course/sql-and-postgresql/
- znpy 4y ago
  I’d love to see udemy courses on postgresql administration beyond the basics… I’d love something to get you to, say, a junior-level dba. Focused on postgresql of course.
bluepnume 4y ago
Designed for browsers but... presumably can be run in Node? I'm thinking this would be perfect for tests, to avoid spinning up a standalone postgres server every time.
tracker1 4y ago
Got excited thinking this was the wasmer runtime for postgres that I'd looked at and hasn't seemed to make any progress for a couple years not.
_alex_ 4y ago
Please don’t put Postgres into webpages
anonu 4y ago
> postgres-wasm is currently about 30mb. So at this stage, running Postgres in the browser isn't great for general use-cases. 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.
djmashko2 4y ago
would this make it possible to install Postgres from NPM for local development? could make it much easier to build/run an app vs. installing Postgres on the machine directly, or running in a Docker container.
Existenceblinks 4y ago
The most important piece of jigsaw left is writable local file access api. Yet, no energy put to do it! Security shouldn't be a stopper but a thing to be solved, probably only allow installed pwa app write with little cute settings widget like on mobile to see which apps have accessed to local machine's storage, showing path, usage size, etc. It's like conspiracy to leave Chrome do it alone. Strange!
- mathiasrw 4y ago
  WASI will blow your mind
  - Existenceblinks 4y ago
    Progress is quite slow. The "Component Model" proposal is very early days. There's .wit and .world going on that will blow your mind!
deleted 4y ago
[deleted]
johnhenry 4y ago
I wrote an in-browser serverless platfrom [1] a few months ago with which this would pair well; but seeing the techniques described here, it might make sense to compile workerd [2] to wasm instead. [1] https://github.com/johnhenry/actually-serverless https://github.com/johnhenry/actually-serverless [2] https://github.com/cloudflare/workerd https://github.com/cloudflare/workerd
BrandiATMuhkuh 4y ago
This is a really exciting development. It's super awesome to see again what the supabase team does. Thanks for your great work. 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.
cranium 4y ago
I love watching the WASM space evolve and get use cases like that. I feel it could become an easier Docker for people wanting to try programming with little fuss.
- isthisreality 4y ago
  > I feel it could become an easier Docker for people wanting to try programming with little fuss. In a lot of ways, repl.it is doing this. https://blog.replit.com/powered-by-nix https://blog.replit.com/powered-by-nix
mchaynes 4y ago
would it be possible to install an extension like postgis into this? if so, you might have saved me countless hours attempting to port gdal to wasm…
- burggraf 4y ago
  Yes, we can include postgis. I'm currently investigating how to include other PostgreSQL extensions as well. A fair number of standard extensions are already included, but there are more we need for a full Supabase implementation.
javcasas 4y ago
Can I have the opposite? I want to be able to run webassembly-based extensions in postgresql. I don't like writing C, and most postgresql languages offer too much (no sandboxing, need slow interpreters and shit). But a WASM based sandbox for creating custom functions for postgres in your favourite WASM-outputting language sounds very interesting
- burggraf 4y ago
  This exists, but with limitations: [0][1] [0]https://medium.com/wasmer/announcing-the-first-postgres-extension-to-run-webassembly-561af2cfcb1 https://medium.com/wasmer/announcing-the-first-postgres-exte... [1]https://github.com/wasmerio/wasmer-postgres https://github.com/wasmerio/wasmer-postgres
statico 4y ago
Somewhat related, I've been working on a tool to help do ad hoc work on CSV files. My current solution is embedding SQLite in the browser: https://csvhacker.statico.io/ https://csvhacker.statico.io/ I feel like Postgres would make this even more powerful.
alexpotato 4y ago
I was most impressed that you can exit out of psql and you have a Linux VM running in your browser that has some basic commands e.g. ping
- burggraf 4y ago
  It’s a busybox shell that does quite a bit. Plus you have pg_dump which you can use to shuttle data from an external database to the local database or vice-versa.

887 points by kiwicopple 4y ago

Hey HN, we’re excited about this launch. This was a collaborative effort with the team at Snaplet [0]. 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/ https://www.snaplet.dev/ [1] Crunchy post: https://news.ycombinator.com/item?id=32498435 https://news.ycombinator.com/item?id=32498435 [2] Snaplet repo: https://github.com/snaplet/postgres-wasm https://github.com/snaplet/postgres-wasm [3] Supabase fork: https://github.com/supabase-community/postgres-wasm https://github.com/supabase-community/postgres-wasm

boltzmann-brain 4y ago
Do you have a benchmark of load times after the includes are cached? Depending on whether it's < 100 ms or > 1s it will make the applications for this very different.
- kiwicopple 4y ago
  We don't have any benchmarks yet, but it's definitely something we should add/automate now that we're past the "POC" phase
gurjeet 4y ago
The links at the bottom are broken; presumably the post was written in Markdown, HN doesn’t understand that.
- kiwicopple 4y ago
  Thanks Gurjeet - fixed
vineyardmike 4y ago
> an embeddable Linux VM with Postgres installed, which runs inside a browser. 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.
- kiwicopple 4y ago
  > How much work was done supporting the VM All of the heavy lifting here is done by v86: https://github.com/copy/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.
  - Ericson2314 4y ago
    This is cool, but is also somewhat saddening too. 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.
    - pistoriusp 4y ago
      I think we'll see this - and it would be amazing to get someone to work towards this - once there is an amazingly compelling use case that resonates with the market, then I think there's room for optimization!

CSDude 4y ago

This is really cool. It'd also make a great teaching environment.

pistoriusp 4y ago

Peter from Snaplet here. A month ago I saw the CrunchyData post and wanted to play around with the code that made it happen, it wasn't OSS so I asked for help: > 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 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 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 https://news.ycombinator.com/item?id=32500526

justinclift 4y ago
Have you looked at compressing that ~16MB wasm file with something like Brotli? 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-size-of-wasm-files https://github.com/golang/go/wiki/WebAssembly#reducing-the-s...
- pistoriusp 4y ago
  Our demo is on Netlify, so I'm assuming that they've got gzip or something along those lines enabled. On my 50mbit/s (Germany :/) connection it's ~2 seconds
- ankraft 4y ago
  Zstandard is able to compress this file to 1.4MB. a2b67f25.bin : 8.26% (16777216 => 1386514 bytes, a2b67f25.bin.zst)
  - dchest 4y ago

nitsky 4y ago

This is a really exciting milestone for WASM! Has anyone tried compiling postgres for wasm32-wasi and implementing a wasi-compatible interface for the file system and other OS bits postgres needs? I think that would be a big improvement.

burggraf 4y ago
Supabase developer here. I worked on this project with the guys over at Snaplet. 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. [0] https://humphd.github.io/browser-shell/ https://humphd.github.io/browser-shell/
- nitsky 4y ago
  Using IndexedDB, the forthcoming SQLite support in Chrome, or the browser filesystem components all might be good paths. Either way, what do you think about using WASI instead of Linux? I think it would reduce the overhead significantly.
  - burggraf 4y ago
    I'm not familiar with WASI but I'll take a look. We're using Buildroot now and it's nice and small, but we could make it smaller by optimizing the kernel. Now, if I only know how to optimize the kernel without breaking things :) Where's all those kernel hackers? Your help, we need. :)

danielvaughn 4y ago

Very impressive technically, but what are the practical use cases for this? We have IndexedDB and WebSQL, so apart from the other comments mentioning using it in tutorials, I'm not sure what the value add is. edit: as always, I should read the whole article first. The idea of using it as a dev environment is very cool.

jdmdmdmdmd 4y ago
>We have [...] WebSQL 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_API https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_A...
- danielvaughn 4y ago
  Oh wow, I never knew WebSQL was deprecated. And yeah IndexedDB is pretty complicated, I've tried working with the API a few times but always abandoned it for simpler solutions.
kiwicopple 4y ago
There are some things that are uniquely beneficial for Postgres beyond the data storage - the main one I'm excited about is using PostGIS inside the browswer. OSM + PostGIS in the browser has the potential to do for Maps, what Figma's WASM approach did for design.
- bobmaxup 4y ago
  > 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?
- 4y ago

bearjaws 4y ago

The use cases here are going to be really wide spread in my opinion, just a few ideas off the cuff. Obviously the 30mb size means it won't really be for regular consumer apps, but for enterprise or specific tasks it can make a lot sense. 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!

burggraf 4y ago
Supabase developer here. 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.
- alex_duf 4y ago
  Correct me if I'm wrong, but given your profile (I assume someone in the tech world), nothing stopping you from doing all of the above with a local pg. If installing is annoying you could run it in docker.
  - burggraf 4y ago
    You're absolutely right, you can use a local pg. This just makes it easier for me, as it's sort of a "sandbox" environment and I can easily take snapshots to do A/B testing or roll things back. I can also send a snapshot to a coworker so they can get my entire environment with all my data in a few seconds.
    - aaronax 4y ago

pashkinelfe 4y ago

For me it's interesting can this work without the need of external proxy server which seems to be needed only to overcome browser connections limitations and nothing else. May there be some more "internal" way to implement/allow these connections? Overall this seems an inspiring thing. Thanks!

pistoriusp 4y ago
Snaplet person here: Totally. It cannot, and it very likely will never be able to do that. Raw sockets were added, and subsequently removed, from Chromium. Do you mean, connect from 1 browser tab to another?
- burggraf 4y ago
  Interestingly we can do this, sorta, but in Chrome only. Using arp you can open a network between multiple tabs of the same Chrome instance, putting each browser vm on a different private ip. I only see this useful, though, for testing clustering systems. There won't be any outside internet connectivity in a setup like this though.
burggraf 4y ago
Supabase developer here. 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.

vivekin 4y ago

This is very cool. This will open up a lot of different use cases.Congratulations!

yangosoft 4y ago

Great! Really impressive. It seems that WASM could be an alternative container solution. This approach is like an intermediate step before recompiling it to wasm.

encryptluks2 4y ago
One that performs way worse then existing container solutions but that will of course be used to distribute binary blobs.
- yangosoft 4y ago
  Agree, it is much slower. 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.
  - burggraf 4y ago
    I love how you guys are already pushing us to think about performance issues on this project. Hacker news really pushes technology to the next level!
    - encryptluks2 4y ago
      A good comparison might be something like Flatpak or gVisor.

angelmm 4y ago

One of the things I love from this post is the collaboration on this kind of OSS projects. 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! [0] https://github.com/copy/v86 https://github.com/copy/v86

MuffinFlavored 4y ago
Could I hypothetically pass USB through to v86? Like libusb on the host into v86 on the guest? Or do USB over IP or something?
- simcop2387 4y ago
  Chrome supports a USB api that could potentially work for that, https://developer.chrome.com/docs/extensions/reference/usb/ https://developer.chrome.com/docs/extensions/reference/usb/ 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).
  - rjh29 4y ago
    > This API is part of the deprecated Chrome Apps platform. Learn more about migrating your app. Classic Google...
    - lights0123 4y ago
      That's the old one. https://web.dev/usb/ https://web.dev/usb/ is the supported one.
  - MuffinFlavored 4y ago
    What about this: https://wicg.github.io/webusb/ https://wicg.github.io/webusb/
  - 4y ago

jd3 4y ago

This is amazing! I used https://rextester.com/l/postgresql_online_compiler https://rextester.com/l/postgresql_online_compiler to rapidly iterate/prototype some complex jsonb queries for a project at work ~3 years ago, but it has since been locked behind a patreon paywall. While I've since switched to the native https://postgres.app/ https://postgres.app/, it will be nice to be able to spin up a fresh postgres test db in the browser in the future.

frankfrank13 4y ago

One interesting use case that wasn't mentioned is edge computing. If you buy into Chrome-as-a-container that can run on an "edge" more easily than a data center, this is a step in that direction

pistoriusp 4y ago
And those are very likely to have tcp sockets at some point in the future (or at least that is the hope!)
- tonetheman 4y ago
  Funny thing is that Chrome used to have sockets. As an extension but they solidly worked. I wrote a socks proxy that ran in the browser. It worked really well.
  - pistoriusp 4y ago
    I was reading about it, but I assumed it was just in Chromium? Did it actually make it into Chrome as well!

mockery 4y ago

The first thing to point out is that our implementation isn't pure 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 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?

burggraf 4y ago
Supabase developer here. I've tried compiling directly to WASM, but it did not go well. As I recall, there were features used by PostgreSQL that WASM didn't support yet. This is definitely something we'll revisit though, especially as WASM matures!
fdr 4y ago
The problem is you need operating system features to run Postgres as-is, e.g. mapping memory, forking processes, manipulating files. What is missing is a WASM kernel that skips the x86 emulation but implements enough of the other stuff. 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.
- mockery 4y ago
  Thanks, those are specific requirements I could definitely see WASM struggling to meet. 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.

mytherin 4y ago

Looks really nice, but I'm really curious about what hurdles you encountered in making a native WASM version of Postgres. It seems that both the performance and binary size suffer immensely from the VM running underneath. For example, if I compare to DuckDB-WASM [1] there is an immense difference in load time and query execution speed. 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) [1] https://shell.duckdb.org https://shell.duckdb.org

kiwicopple 4y ago
congrats to the DuckDB team for their WASM version. I've been following it from afar and it's very impressive. > 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
- burggraf 4y ago
  Supabase developer here. Yes we'd love to collaborate on a native WASM version. I tried this first before starting on this project and I didn't get very far. I'll have to go back to my tests on that, but it seemed like WASM wasn't yet capable of compiling Postgres due do some libraries not being supported. Hopefully we can find workarounds or push WASM into a new era of compatibility :)
pistoriusp 4y ago
Snaplet person here: Having a native WASM version would be a huge win for everyone, especially is storage is decoupled from compute! 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.

netcraft 4y ago

I don't mean to take anything away from this post - its quite amazing and I can't wait to play with it more - but wanted to mention that there are sites out there that I believe solve the training portion maybe a little bit better, at least if all you want is to train on SQL not DBA type actions. My favorite is https://dbfiddle.uk/ https://dbfiddle.uk/ - the ability to link and fork a set of statements is extremely handy.

pistoriusp 4y ago
Totally, and what we're really trying to achieve here is to make this available to everyone, so that we can improve it as a collective. 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.

deleted 4y ago

[deleted]

r3tr0 4y ago

Another implementation: https://xn--yv9h.fm/emu/?m=jbdf7ZqvfgR4dCizypXxMP https://xn--yv9h.fm/emu/?m=jbdf7ZqvfgR4dCizypXxMP

burggraf 4y ago
This is cool! It's running debian and posgres 11. It's quite a bit larger (that's why we went with Buildroot, and frankly, we could do a lot better at trimming things down even more). But it's definitely a great implementation!
- r3tr0 4y ago
  yes. this is just a test dummy for something bigger I have. we should chat. Feel free to DM me.
  - r3tr0 4y ago
    I originally went with buildroot too, but then I realized some things...
  - burggraf 4y ago
    Would love to chat! Love to get your ideas on this.
    - r3tr0 4y ago
      what is the best way to contact you.
      - burggraf 4y ago
        You can email me at mark @ supabase dot io. (Yeah, I know there are probably robots that can parse that as valid email address, but I'm old-school :))
        r3tr0 4y ago

jcuenod 4y ago

Hint: Don't use ctrl-w in their terminal. Turns out that has another meaning in Firefox... [EDIT]: I'm zero for three thus far. I give up.

mdaniel 4y ago
That drives me starkraving mad in AWS CloudShell, because muscle memory happens faster than thinking. I thought there was a web/dom method designed for gaming like "capture all input events" but maybe it has to be in full-screen to work or something 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)
jlokier 4y ago
If you open the tab in its own window (i.e. no other tabs) and also pin the tab, turns out ctrl-W doesn't close the tab in Firefox. 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/hqnylbj/?utm_source=reddit&utm_medium=web2x&context=3 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.

xani_ 4y ago

Huh, thought for a second that was a WASM support for stored procedures or something and was excited for a second

supamichelp 4y ago
Well it's not what you were expecting, but your idea sounds interesting too! Maybe you could muster the same open source forces to make it a reality?
- kiwicopple 4y ago
  I believe the team at Wasmer have worked on something like this: https://github.com/wasmerio/wasmer-postgres 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
michaelmior 4y ago
This was also what I was expecting. Another reply to your comment mentioned Wasmer, which I found after a quick Google search. Unfortunately it looks like the last commit was almost 2 years ago and there's an issue where people are inquiring about the status of the project that hasn't seen a reply from the maintainers in about the same amount of time.

steinlagems 4y ago

Scott from JavaScript Jam here. We are so excited that we are going to have Peter with us on Twitter Spaces this Wednesday to chat about Postgres WASM. Join us Live here: https://twitter.com/i/spaces/1yoKMZbgngkGQ https://twitter.com/i/spaces/1yoKMZbgngkGQ See you Wednesday!

101008 4y ago

Now I only need Django WASM and I can distribute standalone applications :')

pistoriusp 4y ago
Interesting, you're the second person to ask about this! Is there something in Django world that makes standalone application appealing?
- 101008 4y ago
  I think it is just personal: I feel very proficient with Django and as developer I love it. It probably would be possible to do the same with any other framework such as Flask or Laravel (in PHP)

mradek 4y ago

I haven't messed around with wasm but I like postgres a lot. Just curious what are some use cases for this? Also not a supabase user even tho it looks cool. 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?

spiderice 4y ago
There is a "why" section in the blog post that gives a number of examples. Namely: - 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

tmpz22 4y ago

I expect to see WASM tooling expose that Google's stewardship of Chromium has left many, many, features to be desired. You can blame it on the bureaucracy and legacy cruft of other companies like Microsoft and Apple, but at the end of the day there's just so much stuff you can't do with web apps (and oh wow just happens to align with Google's Ad and Cloud businesses).

robertlagrant 4y ago
Can you list the features you mean that Firefox and Safari have that the Chromium project has left out because of Google's business interests? Be interesting to see.
- tmpz22 4y ago
  My comment doesn't assert Firefox or Safari are ahead on features. My comment is about Google/Chrome still being culpable as a market leader despite Apple (Safari) and Microsoft (IE legacy) likely slowing down browser development in general.
  - robertlagrant 4y ago
    I don't understand what you're comparing Chromium to if you're saying it doesn't have features for business reasons, when the other browsers also don't have those features.
    - neoberg 4y ago
      In almost every standards working group Google holds the most seats. 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.
      - robertlagrant 4y ago
        > In almost every standards working group Google holds the most seats 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.

_joel 4y ago

Completely naive question here, but could you cluster the WASM instances (with mods, I'd assume, more is it possible via the WASM abstractions?). Not sure why but seems like a interesting thing :)

burggraf 4y ago
Yes, you probably could do that. It's something I've considered. Early on before I got the networking working through the proxy I found a way to use arp to connect multiple browser tab versions of this in a pseudo-private-network configuration. It only works locally and only works inside chrome, and only a single instance of chrome at that. But it was cool idea and the only use case I could think of was setting up a local cluster of Postgres instances within the browser. And my kids think playing video games is fun! Meh.

fcoury 4y ago

Interesting. I have written a translation layer for the MongoDB wire protocol [0] that persists to PostgreSQL. I am wondering if I could make this layer also compile to wasm and run in the browser. --- [0] http://oxidedb.com http://oxidedb.com

burggraf 4y ago
Wow, this is awesome! So as I understand it, this uses Postgres and JSON fields to emulate the features of MongoDB, sort of as an abstraction?
- fcoury 4y ago
  Thank you! It's exactly that: basically you don't need to keep an additional MongoDB instance if you already use and maintain a Postgres server somewhere. I translate the JSON-based query interface into the corresponding SQL statements, leveraging the excellent JSON support that PostgreSQL offers.

agustinbena 4y ago

I can not still agree to the usefulness to run this in a WebBrowser at 30MB. But for running in the backend over a wasm runtime, seems that the use case that make sense for the moment.

kaboomshebang 4y ago

The more I read about WASM the more magical it becomes...

jhd3 4y ago

I was wondering if anyone had thought about using this to experiment with the planner. 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/bin/minirepro https://github.com/greenplum-db/gpdb/blob/6X_STABLE/gpMgmt/b... [1] https://github.com/greenplum-db/gpdb/issues/5740#issuecomment-420542147 https://github.com/greenplum-db/gpdb/issues/5740#issuecommen... (has an example output)

NoGravitas 4y ago

pistoriusp 4y ago
Oh man, sorry.

rkagerer 4y ago

Aside from folks who already have Postgres infrastructure they want to replicate, what sort of applications would find this a better fit than using SQLite on the client side?

javajosh 4y ago

This is very cool. An important area of ignorance (for me) is how well browsers do with local data. A nightmare scenario is that a user happily uses your app, stores data locally, and then Chrome decides they don't need localstorage anymore. What's the status on local data reliability in browsers?

Joel_Mckay 4y ago
Wise-men know better than to integrate google stuff into mission critical systems. Their behavior is purely governed by the information consolidation facet of any product or service they offer. Thus, given they already sample user traffic on 80% of the web, the company will continue to cull unpopular projects at astonishingly high rates. I've lost count of the number of projects I saw get burned by this common mistake.

aitchnyu 4y ago

I wish we could replace Docker for dev environments with asdf which examines your project and makes available language tooling for Ruby, Node etc and other tools with pinned versions. With this we could cd to our project and have a Postgres server ready for this project.

iib 4y ago
I think `direnv` [1] may be closer to `asdf` than this. They also mention it, and the integration, `asdf-direnv` on their website [2]. [1] https://direnv.net/ https://direnv.net/ [2] https://asdf-vm.com/guide/introduction.html#direnv https://asdf-vm.com/guide/introduction.html#direnv

termcaps 4y ago

Well done. 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.

Kye 4y ago

There have been a lot of new WASM things popping up on HN. Was there some major new change the enabled all this?

lioeters 4y ago
I've also noticed a number of fun WASM projects this year. I think the underlying WASM features that enable all this have been around for a while, so the breakthroughs are probably coming from the community and ecosystem growing incrementally and synergistically, building on each other's work and collaborating.
- encryptluks2 4y ago
  I believe they are coming from people wanting to push people to get confortable with running binary blobs in their browsers so they can suck up all that juicy personal information leaked by browsers.
  - termcaps 4y ago
    Sadly, I'm not sure you really need binary blobs to "suck up all juicy personal informations" in the first place.
  - lioeters 4y ago
    WASM is unnecessary and unsuitable for such a purpose, since it would need to go through JavaScript to get that information anyway. It's far more sandboxed than JS, and cannot get any more personal data than JS can already.
- Kye 4y ago
  It does have a similar feel to how things went when DHTML was becoming The Thing. People would share increasingly elaborate demos that built on older stuff until there were whole web applications. Then AJAX happened and it was all over. It seems like WASM is finally fulfilling all those early fantasies of the browser as the operating system.

andersonmvd 4y ago

I was trying to find a way to make apps "data-leak resistant" and one step into this direction was to let the user store the data by bringing his/her own database. I even made a poc https://github.com/andersonDadario/byoda https://github.com/andersonDadario/byoda (explanation in the blog post link found on the readme) - but no user would manage his own database. Having a database in the browser opens more possibilities though. I will give it some thought. Looking forward to what else will be built on WASM.

unilynx 4y ago
For that to work you won't even need a database in the user's browser - you could just store an encryption key as a cookie today which you use to encrypt all your serverside data when the user logs in 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...
- andersonmvd 4y ago
  It's complicated indeed. A simple "clear browsing data" would make him lose the data forever hehe. And if you keep the key in the server, it would be "hackable", which is what I was trying to avoid. I haven't looked at it but https://solid.mit.edu/ https://solid.mit.edu/ looked promising.

yarg 4y ago

30mb wouldn't be that bad if the browser was caching WASM dependencies. https://developer.mozilla.org/en-US/docs/WebAssembly/Caching_modules https://developer.mozilla.org/en-US/docs/WebAssembly/Caching...

raitucarp 4y ago

Wow, the dream of decentralized web comes true, soon. With encryption and wasm, we are gonna conquer the goals toward true decentralized web.

burggraf 4y ago

So I've been using this for a couple weeks now on my mobile phone. There's really no reason to do this, other than I'm sitting in a car waiting for my wife to come out of the grocery store and I just feel like writing some some SQL code -- because I can. If you noticed, there's a little keyboard icon at the top of the demo that toggles on a mini keyboard with ^c, ^d and cursor keys. That's because the iPhone doesn't have those keys, and I'd start pinging a server and realize I can't break out of it and now I need to reset the whole emulator. Anyhow, that's why that little toggle is in there. :)

OOPMan 4y ago

This seems a step closer to the embeddable postgres I've wanted for ages.

swyx 4y ago
why most it be postgres and not sqlite? just curious on what you were looking for out of it
- burggraf 4y ago
  Why Postgres? (There's already a WASM version of SQLite [0], btw, and it's very cool.) We did this with Postgres for a number of reasons: 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. [0] https://sql.js.org/ https://sql.js.org/
  - swyx 4y ago
    oh i mean i can see why you as a supabase employee would want it, just wanted to get more opinions from third parties :)

Woberto 4y ago

Stephen Grider made a cool website[0] that allows people to make and use databases using PostgreSQL statements for his udemy course[1] about SQL. It's great for learning and testing and has been around a while so I feel like he should get some attention/credit too [0] pg-sql.com/ [1] https://www.udemy.com/course/sql-and-postgresql/ https://www.udemy.com/course/sql-and-postgresql/

znpy 4y ago
I’d love to see udemy courses on postgresql administration beyond the basics… I’d love something to get you to, say, a junior-level dba. Focused on postgresql of course.

bluepnume 4y ago

Designed for browsers but... presumably can be run in Node? I'm thinking this would be perfect for tests, to avoid spinning up a standalone postgres server every time.

tracker1 4y ago

Got excited thinking this was the wasmer runtime for postgres that I'd looked at and hasn't seemed to make any progress for a couple years not.

_alex_ 4y ago

Please don’t put Postgres into webpages

anonu 4y ago

> postgres-wasm is currently about 30mb. So at this stage, running Postgres in the browser isn't great for general use-cases. 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.

djmashko2 4y ago

would this make it possible to install Postgres from NPM for local development? could make it much easier to build/run an app vs. installing Postgres on the machine directly, or running in a Docker container.

Existenceblinks 4y ago

The most important piece of jigsaw left is writable local file access api. Yet, no energy put to do it! Security shouldn't be a stopper but a thing to be solved, probably only allow installed pwa app write with little cute settings widget like on mobile to see which apps have accessed to local machine's storage, showing path, usage size, etc. It's like conspiracy to leave Chrome do it alone. Strange!

mathiasrw 4y ago
WASI will blow your mind
- Existenceblinks 4y ago
  Progress is quite slow. The "Component Model" proposal is very early days. There's .wit and .world going on that will blow your mind!

deleted 4y ago

[deleted]

johnhenry 4y ago

I wrote an in-browser serverless platfrom [1] a few months ago with which this would pair well; but seeing the techniques described here, it might make sense to compile workerd [2] to wasm instead. [1] https://github.com/johnhenry/actually-serverless https://github.com/johnhenry/actually-serverless [2] https://github.com/cloudflare/workerd https://github.com/cloudflare/workerd

BrandiATMuhkuh 4y ago

This is a really exciting development. It's super awesome to see again what the supabase team does. Thanks for your great work. 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.

cranium 4y ago

I love watching the WASM space evolve and get use cases like that. I feel it could become an easier Docker for people wanting to try programming with little fuss.

isthisreality 4y ago
> I feel it could become an easier Docker for people wanting to try programming with little fuss. In a lot of ways, repl.it is doing this. https://blog.replit.com/powered-by-nix https://blog.replit.com/powered-by-nix

mchaynes 4y ago

would it be possible to install an extension like postgis into this? if so, you might have saved me countless hours attempting to port gdal to wasm…

burggraf 4y ago
Yes, we can include postgis. I'm currently investigating how to include other PostgreSQL extensions as well. A fair number of standard extensions are already included, but there are more we need for a full Supabase implementation.

javcasas 4y ago

Can I have the opposite? I want to be able to run webassembly-based extensions in postgresql. I don't like writing C, and most postgresql languages offer too much (no sandboxing, need slow interpreters and shit). But a WASM based sandbox for creating custom functions for postgres in your favourite WASM-outputting language sounds very interesting

burggraf 4y ago
This exists, but with limitations: [0][1] [0]https://medium.com/wasmer/announcing-the-first-postgres-extension-to-run-webassembly-561af2cfcb1 https://medium.com/wasmer/announcing-the-first-postgres-exte... [1]https://github.com/wasmerio/wasmer-postgres https://github.com/wasmerio/wasmer-postgres

statico 4y ago

Somewhat related, I've been working on a tool to help do ad hoc work on CSV files. My current solution is embedding SQLite in the browser: https://csvhacker.statico.io/ https://csvhacker.statico.io/ I feel like Postgres would make this even more powerful.

alexpotato 4y ago

I was most impressed that you can exit out of psql and you have a Linux VM running in your browser that has some basic commands e.g. ping

burggraf 4y ago
It’s a busybox shell that does quite a bit. Plus you have pg_dump which you can use to shuttle data from an external database to the local database or vice-versa.