Learn Postgres at the Playground – Postgres compiled to WASM running in browser(crunchydata.com)
crunchydata.com
Learn Postgres at the Playground – Postgres compiled to WASM running in browser
https://www.crunchydata.com/blog/learn-postgres-at-the-playground
174 comments
Maybe I missed it, but is the licensing info available anywhere? The playground seems to be using v86.js I don't see the required "Redistributions in binary form must reproduce the above copyright notice" -- https://github.com/copy/v86/blob/master/LICENSE
Late to this, hadn't noticed until now. We have the license included and linked from from inside the VM as it starts up.
Your scientists were so preoccupied with whether they could, they didn’t stop to think if they should.
Great job!
Great job!
What were the hardest parts of getting it to compile for WASM?
Very cool, I work on https://sqlpad.io which allows people to practice sql coding interview questions online.
Do you provide 3rd party hosting? Might consider replacing ours to something more flexible like yours in the future.
Do you provide 3rd party hosting? Might consider replacing ours to something more flexible like yours in the future.
Feel free to drop me a note craig.kerstiens at crunchydata and happy to have a conversation to see what options may exist.
Any chance you can give us access to the PSQL sandbox without it being attached to a tutorial. Would both be a great way to learn and also function as a productivity tool if one could fool around with PSQL "scratch pad"
That's a heck of an easter egg. It's like the TS playground feature, letting people explore exactly what you mean with a bug, etc. Kudos.
Broke it trying to load our db schema at https://www.crunchydata.com/developers/playground?sql=https:...
Any ideas?
Any ideas?
Maybe give it another shot or two, it's definitely a slow process to import. We have some improvements coming there in time but it's ideal at moment for smaller schemas and datasets.
You definitely seem to have some issues with memory usage, crashes my tab when trying to load that link on a device with low amount of memory (4GB).
Maybe a per tab memory limit.
Step 11 talks about increasing memory.
https://www.guidingtech.com/fix-google-chrome-out-of-memory-...
https://www.guidingtech.com/fix-google-chrome-out-of-memory-...
This is awesome. Can you talk about how was it implemented? Is it actually a port of the postgres code that's been compiled to WASM?
This is definitely our plan, we've got some follow on posts that go into much more detail on how we built it so stay tuned for those.
Would love to see this! I could see a wasm version of postgres being useful for all sorts of things, especially an easy dev instance of pg runing inside a node app.
This is great, I can't wait for someone to get python in the browser talking to postgres in the browser (and hopefully very soon afterward Django in the browser).
You are going to need K8S in there to manage all the complexity.
And Docker in between to properly isolate processes and dependencies.
I agree. Those damn computers are becoming too fast. It is time to to slow'em down proper so we can pay more for a hardware.
Bring back the turbo button
> Perhaps our favorite easter egg is that you can bring your own SQL into it for example
That is super awesome! I plan to use it to allow people to peruse our table structure easily.
That is super awesome! I plan to use it to allow people to peruse our table structure easily.
This is great. Have you done any benchmarking? I am currently looking to find/create good Wasm benchmarks, and it seems like this might be a good workload.
@Craig, I wonder if you've thought of making this available (would it work??) for test case construction. Today (working in Go) I start a postgres in a docker container for testing database code. Could I instead use a go wasm runtime, and start postgres inside of it? That would potentially free me from the docker dependency in these tests.
The communication in and out is a bit of extra work at the moment. We've got some thoughts on improving this in the future, but it's a bit unclear on timeline and roadmap. We're excited to hear what people want from it and how they use it and that'll inform a lot of how we move forward.
> Perhaps our favorite easter egg is that you can bring your own SQL into it
This is very cool but I wouldn't call it an easter egg, I'd just call it a feature!
This is very cool but I wouldn't call it an easter egg, I'd just call it a feature!
This is a really cool tool. Nice work
Pretty cool project!
This is so awesome for two reasons, Postgress isn't exactly easy to setup, this makes it so much easer for tutorials, anything that makes teaching it quicker is good!
It's also an amazing demonstration of the power of WASM, I am utterly convinced that DBs running in browser via WASM on top of the "coming soon" filesystem/block store api [0] will be the future of all offline first apps. I think it's going to be SQLite that really sines here (it will be a smaller package), but if you can run Postgres in browser, you potentially have a close alignment between your server and browser implementations.
I love the idea of running Django in browser via PyScript with Postgres!
0: https://developer.mozilla.org/en-US/docs/Web/API/FileSystem - This API doesn't grant access to the users file system, but a local virtual one just for the website. It will operate at the block level so can be used to provide efficient persistent storage for WASM DBs.
—
Edit rather than replying individually:
It’s so important when teaching people a new thing, like sql, to hit the ground running. A lot of these people will have no experience with Docker, package managers or compiling from source. It’s so important to make technology accessible to all. This does that! Imagine high school kids having a lesson on SQL and running Postgres’s just by opening a webpage!
We shouldn’t assume that someone learning Postgres, or just SQL via it, is a developer with experience of other areas of development or system administration.
It's also an amazing demonstration of the power of WASM, I am utterly convinced that DBs running in browser via WASM on top of the "coming soon" filesystem/block store api [0] will be the future of all offline first apps. I think it's going to be SQLite that really sines here (it will be a smaller package), but if you can run Postgres in browser, you potentially have a close alignment between your server and browser implementations.
I love the idea of running Django in browser via PyScript with Postgres!
0: https://developer.mozilla.org/en-US/docs/Web/API/FileSystem - This API doesn't grant access to the users file system, but a local virtual one just for the website. It will operate at the block level so can be used to provide efficient persistent storage for WASM DBs.
—
Edit rather than replying individually:
It’s so important when teaching people a new thing, like sql, to hit the ground running. A lot of these people will have no experience with Docker, package managers or compiling from source. It’s so important to make technology accessible to all. This does that! Imagine high school kids having a lesson on SQL and running Postgres’s just by opening a webpage!
We shouldn’t assume that someone learning Postgres, or just SQL via it, is a developer with experience of other areas of development or system administration.
[I retract this comment entirely—see downthread where rewq4321 corrects me.]
> on top of the "coming soon" filesystem/block store api
I’m completely unimpressed by Origin Private File Systems, which I believe is what you’re talking about: it’s just a key-value store, just made to look a little like a file system, but is probably exactly equivalent to IndexedDB in capability and usefulness, quite easily perfectly polyfillable atop it. It is certainly completely unsuitable for building a database on top of as far as ACID transactions or such are concerned—you’ll get appalling write performance because you will have to close the file to commit each write.
I wrote more about this in the thread about Safari having implemented OPFS five months ago: https://news.ycombinator.com/item?id=30394737. (My use of “probably” above is explained in there too.)
> on top of the "coming soon" filesystem/block store api
I’m completely unimpressed by Origin Private File Systems, which I believe is what you’re talking about: it’s just a key-value store, just made to look a little like a file system, but is probably exactly equivalent to IndexedDB in capability and usefulness, quite easily perfectly polyfillable atop it. It is certainly completely unsuitable for building a database on top of as far as ACID transactions or such are concerned—you’ll get appalling write performance because you will have to close the file to commit each write.
I wrote more about this in the thread about Safari having implemented OPFS five months ago: https://news.ycombinator.com/item?id=30394737. (My use of “probably” above is explained in there too.)
Amusingly you where replying to me there, hello again!
I’m sure there are rough edges right now, but I’m complete convinced that even if the api isn’t there yet, this use case will win out and we will see it happen.
From memory the teams working on WASM SQLite are working with the File System API working group to ensure their use case is supported.
I’m sure there are rough edges right now, but I’m complete convinced that even if the api isn’t there yet, this use case will win out and we will see it happen.
From memory the teams working on WASM SQLite are working with the File System API working group to ensure their use case is supported.
[I retract this comment entirely, apart from the pleasantries and the preference of being wrong—see downthread where rewq4321 corrects me.]
Heh, didn’t notice the username match there!
I don’t think “rough edges” is the right characterisation. What OPFS provides is just nothing in the direction required. The kind of file system you need to build a database on is a fundamentally completely different beast, with only unimportant surface-level similarities. It would generally require a complete replacement of the backend, with quite possibly literally no code in common.
I’d like something like this, because it’s certainly genuinely useful for cases like this, but I’d honestly be surprised if it ever happens, because it’s just… not webby. To be useful, it just about requires that the whole thing be backed by an actual file system and exposing that, which is something that has been assiduously avoided so far in the design, probably in significant part because it discloses quite a lot about the host system (fingerprinting; disk performance characteristics, quite possibly even file system identification by various nuances in behaviour; and surprisingly large side-channel attack possibilities, mildly similar to the fuss over high-resolution timers), but also because it tends to be a security hazard, just another moving part where things can go wrong more easily than you imagine.
I strongly suspect it will end up a bit like Web SQL: a nice idea that pretty much everyone agrees is a nice idea, but which is also a non-starter for other reasons.
But I wouldn’t mind being wrong. I do want to be able to deploy a robust, high-performing SQLite in the browser.
Heh, didn’t notice the username match there!
I don’t think “rough edges” is the right characterisation. What OPFS provides is just nothing in the direction required. The kind of file system you need to build a database on is a fundamentally completely different beast, with only unimportant surface-level similarities. It would generally require a complete replacement of the backend, with quite possibly literally no code in common.
I’d like something like this, because it’s certainly genuinely useful for cases like this, but I’d honestly be surprised if it ever happens, because it’s just… not webby. To be useful, it just about requires that the whole thing be backed by an actual file system and exposing that, which is something that has been assiduously avoided so far in the design, probably in significant part because it discloses quite a lot about the host system (fingerprinting; disk performance characteristics, quite possibly even file system identification by various nuances in behaviour; and surprisingly large side-channel attack possibilities, mildly similar to the fuss over high-resolution timers), but also because it tends to be a security hazard, just another moving part where things can go wrong more easily than you imagine.
I strongly suspect it will end up a bit like Web SQL: a nice idea that pretty much everyone agrees is a nice idea, but which is also a non-starter for other reasons.
But I wouldn’t mind being wrong. I do want to be able to deploy a robust, high-performing SQLite in the browser.
Are you sure you're up to date on the latest additions to this API? This wasn't in the MVP:
> The origin private file system provides optional access to a special kind of file that is highly optimized for performance, for example, by offering in-place and exclusive write access to a file's content.
https://web.dev/file-system-access/#accessing-files-optimize...
It was originally going to be a separate high-perf "Storage Foundation" API, but that was merged with the File System Access API.
> The origin private file system provides optional access to a special kind of file that is highly optimized for performance, for example, by offering in-place and exclusive write access to a file's content.
https://web.dev/file-system-access/#accessing-files-optimize...
It was originally going to be a separate high-perf "Storage Foundation" API, but that was merged with the File System Access API.
Huh. Disregard what I wrote entirely, then. Reading through https://github.com/WICG/file-system-access/blob/main/AccessH..., I can see how they’ve bypassed most or all of the problems I saw—I was making unnecessary assumptions.
Thank you for correcting me. I am now enthusiastic about OPFS.
Thank you for correcting me. I am now enthusiastic about OPFS.
This has to be ruled out first:
https://github.com/whatwg/fs/issues/7#issuecomment-116176851...
...but then the OPFS will be a quite decent fit. We (DuckDB-Wasm) are also looking closely at OPFS.
IMHO the requirement here is not even to get to full ACID.
With OPFS, we will get close enough to IndexedDB on steroids and bypassing the js heap limits through out-of-core operators.
After all, we are still running in a browser.
So I see the value of Wasm-based databases to be a front-facing accelerator, not a substitute for robust storage solutions.
...but then the OPFS will be a quite decent fit. We (DuckDB-Wasm) are also looking closely at OPFS.
IMHO the requirement here is not even to get to full ACID.
With OPFS, we will get close enough to IndexedDB on steroids and bypassing the js heap limits through out-of-core operators.
After all, we are still running in a browser.
So I see the value of Wasm-based databases to be a front-facing accelerator, not a substitute for robust storage solutions.
Great to see this kind of pleasant discourse on HN!
Ah yes, can't wait to run my offline-first app on a kubernetes-on-docker-on-linux-on-qemu-on-wasm stack in the browser to have closer alignment between my server and browser implementations.
> Ah yes, can't wait to run my offline-first app on a kubernetes-on-docker-on-linux-on-qemu-on-wasm stack in the browser to have closer alignment between my server and browser implementations.
You can run vscode on docker on kubernetes on the browser with gitpod!
You can run vscode on docker on kubernetes on the browser with gitpod!
Yeah but can I host that Gitpod in Docker running on Kubernetes running on Docker on Linux on Qemu on Wasm as well?
I mean we did already ship the ability to run containers from inside Postgres so why not tackle every possible approach - https://www.crunchydata.com/blog/announcing-postgres-contain...
Speaking of making Postgres easy to setup and use, here are instructions for setting up per-project Postgres ("So I worked out how to set up a Postgres database that’s almost as convenient as SQLite."): https://jamey.thesharps.us/2019/05/29/per-project-postgres/
I think most people who use postgres in their personal projects have a snippet like one that they copy from project to project, I know I do. Curious to see others' scripts!
Mine logs in as `postgres`, creates the database and extensions I normally use, then creates a user and grants the appropriate privileges to that application user for the relevant db/schemas. Incidentally, it's the same script I use for "prod" deployments on my homelab.
Mine logs in as `postgres`, creates the database and extensions I normally use, then creates a user and grants the appropriate privileges to that application user for the relevant db/schemas. Incidentally, it's the same script I use for "prod" deployments on my homelab.
FYI, not all drivers support unix sockets, like the official Java one.
> This is so awesome for two reasons, Postgress isn't exactly easy to setup
for tutorial level usage, "apt-get install postgresql-X" or installing Postgres.app has always worked perfectly. what sort of troubles do you run into?
for tutorial level usage, "apt-get install postgresql-X" or installing Postgres.app has always worked perfectly. what sort of troubles do you run into?
I run into problems with the next steps: now you have to create a database, create a user, grant that user access to the database, setup credentials and start using them.
This isn't impossible - I can do it if I consult my notes - but it has enough steps where something might go wrong that it's a pretty high friction process for newcomers.
This isn't impossible - I can do it if I consult my notes - but it has enough steps where something might go wrong that it's a pretty high friction process for newcomers.
> now you have to create a database, create a user, grant that user access to the database, setup credentials and start using them.
CREATE ROLE dumbo WITH LOGIN PASSWORD 'fuck';
CREATE DATABASE dumbo OWNER dumbo;
It's that easy.
CREATE ROLE dumbo WITH LOGIN PASSWORD 'fuck';
CREATE DATABASE dumbo OWNER dumbo;
It's that easy.
I only have to run these about once every year or two, and have to look them up every time.
Many developers don't run those commands very frequently, so they're easy to forget.
You can do all this pretty easily using `pgAdmin` without having to remember any commands. If you do this infrequently, then great use the GUI. If you do it frequently then learn the commands I guess :P `pgAdmin` can help with that too though as it usually shows you the queries it is running.
> I run into problems with the next steps: now you have to create a database, create a user, grant that user access to the database, setup credentials and start using them.
That's like complaining you need to break eggs when you want to make an omelette.
That's like complaining you need to break eggs when you want to make an omelette.
> That's like complaining you need to break eggs when you want to make an omelette.
I have a friend who we discovered didn't like cooking eggs because picking the shells out of the bowl was tedious. Turns out he was never taught how to crack an egg so he would just throw it in the bowl and have bits of shell everywhere. Point is that breaking eggs may not be so easy for everyone. (Of course, after describing to him how eggs are properly cracked, he was excited to try it out.)
I have a friend who we discovered didn't like cooking eggs because picking the shells out of the bowl was tedious. Turns out he was never taught how to crack an egg so he would just throw it in the bowl and have bits of shell everywhere. Point is that breaking eggs may not be so easy for everyone. (Of course, after describing to him how eggs are properly cracked, he was excited to try it out.)
Being necessary and being exhausting aren't mutually exclusive qualities.
But - why are the steps needed to create a database and setup credentials such a complex, manual process anyway? Why can't all those steps just be automated by a helper script that ships with postgres?
I reach for sqlite whenever I introduce SQL to people because its so much easier to get started with sqlite.
But - why are the steps needed to create a database and setup credentials such a complex, manual process anyway? Why can't all those steps just be automated by a helper script that ships with postgres?
I reach for sqlite whenever I introduce SQL to people because its so much easier to get started with sqlite.
The reason that I reach for MySQL when introducing people to SQL is the formatted output, with nice tables and column names. Is there a simple way to get similar output with SQLite?
Yep! Just set sqlite into table mode:
sqlite> .mode table
sqlite> select * from example;
+-----+----------+
| id | foo |
+-----+----------+
| 123 | afsd |
+-----+----------+
Thank you!
Honestly not my experience, if you have postgresql running locally on your laptop, I find upgrading versions is a pain, even with the automated scripts provided and I often run in socket issues: psql is already connected, not able to connect to this or that socket,...
So yeah, installing is easy and on a clean install it runs fine but it's not unusual to get pretty annoying issues over time in my experience.
So yeah, installing is easy and on a clean install it runs fine but it's not unusual to get pretty annoying issues over time in my experience.
But presumably none of those issues is fixed by using WASM in a browser.
They aren't but they probably won't appear user-side, the companies will most likely have to deal with their servers, fix those issues and the users can enjoy not thinking about that when they're trying PostgreSQL online.
Imagine wanting to learn Postgres but not knowing how to use a package manager, for example
Is this even a possibility?
1000%. Lots of people want to do data analysis, learn SQL, all that kind of stuff, without having to learn a load of other unrelated development crap. Think of all the people out there using Excel who want to take the next step.
After browsing this site for a couple of years, I made an account to reply to your comment and say, "This is exactly right" lol. I'm a teacher who uses Excel for basic stuff (power query, pivot tables), but I'm also using Excel as a "database", which is obviously not a good idea.
So here I am trying to take the next step and learn SQL along with good database design, but learning these things through Postgres is really not appropriate for someone like me. I think I have to swallow my pride and start with Access or something.
So here I am trying to take the next step and learn SQL along with good database design, but learning these things through Postgres is really not appropriate for someone like me. I think I have to swallow my pride and start with Access or something.
You may want to try https://dbeaver.io/ + SQLLight for the start.
You just download binary for your OS, create a db as described here (https://www.sqlite.org/quickstart.html) and then connect to it with dbeaver.
This is more the enough to get familiar with SQL.
You just download binary for your OS, create a db as described here (https://www.sqlite.org/quickstart.html) and then connect to it with dbeaver.
This is more the enough to get familiar with SQL.
No shame in using Access! It's actually part of how I got my start in tech. Started with an Access database, started writing macros for it in VBA, then started writing VB.NET, then convinced a higher up to give me access to a SQL Server database.
I was in an industry with an absurd number of boilerplate forms that needed to be printed and the ability to create Access forms that automated all the manual filling out coworkers were doing felt like magic. Postgres doesn't really have an equivalent of that.
I was in an industry with an absurd number of boilerplate forms that needed to be printed and the ability to create Access forms that automated all the manual filling out coworkers were doing felt like magic. Postgres doesn't really have an equivalent of that.
Yes. See this survey on Twitter from the other day - LOADS of "React" developers (aka front-end developers) are deterred from learning more about backend programming due to friction like this. https://twitter.com/rachelnabors/status/1558888478955421697
I still kind of lost here.
I know this much about frontend development too but I was sure that you still need a package manager to install your npm\yarn and other tools.
PS: Obviously you can live without one. Regardless of being backend\frontend developer.
PPS: and honestly, how can you be scared of postgresql and co. after webpack? If you can actually understand this crap postgres setup should be to easy for you.
I know this much about frontend development too but I was sure that you still need a package manager to install your npm\yarn and other tools.
PS: Obviously you can live without one. Regardless of being backend\frontend developer.
PPS: and honestly, how can you be scared of postgresql and co. after webpack? If you can actually understand this crap postgres setup should be to easy for you.
Yeah I personally find Webpack a whole lot scarier than PostgreSQL / package managers, but that's down to my previous career experience.
Really the key thing here is that learning new things is hard, and anything that can be done to remove potential roadblocks is worthwhile. I've talked to so many people who were put off learning Python because they couldn't get to a working development environment on their own.
Really the key thing here is that learning new things is hard, and anything that can be done to remove potential roadblocks is worthwhile. I've talked to so many people who were put off learning Python because they couldn't get to a working development environment on their own.
> If you can actually understand this crap postgres setup should be to easy for you.
You can figure almost anything out if you spend enough time reading documentation and noodling with it. I'd still rather spend that time solving my actual problem.
You can figure almost anything out if you spend enough time reading documentation and noodling with it. I'd still rather spend that time solving my actual problem.
LOADS of React "developers"
People who come from Windows + MSSQL world might not have touched a package manager before and want to try out Postgres.
$ docker run --name your-db-here -e POSTGRES_PASSWORD=pw -d postgres
Removing docker as a requirement is pretty huge.
Removing docker and also, enabling an entire sandbox environment by clicking a link in a browser.
Do you have a link pointing out the filesystem API actually being developed further? AFAIK that whole API is kind of stable and done but I might be wrong.
The other API worth knowing about is the more direct File System Access API, which is the one that allows direct access now: https://developer.mozilla.org/en-US/docs/Web/API/File_System...
wrt SQLite, icmyi: https://github.com/jlongster/absurd-sql
The other API worth knowing about is the more direct File System Access API, which is the one that allows direct access now: https://developer.mozilla.org/en-US/docs/Web/API/File_System...
wrt SQLite, icmyi: https://github.com/jlongster/absurd-sql
Since I use conda for everything anyway, I just end up doing a `conda install -p ./test postgres` when i want to install a throwaway postgres and then delete the environment later.
Sounds like we are poised to be rewarded with another 10 second boot markup for MS Teams
We don't need to wait for CloudFlare to ship their D2! Just plonk this on Firecracker and you are good to go.
For a database, it is pretty easy. I even compile it from source so that I can put it in my directory structure and have multiple concurrent versions.
This is a 15 minute job with coffee break while it compiles. And I don't even remember the whole thing by heart; I have to consult the docs.
This is a 15 minute job with coffee break while it compiles. And I don't even remember the whole thing by heart; I have to consult the docs.
I think WASM is very interesting and running Postgres in the browser is interesting, but for learning one could readily use something like db-fiddle, which offers Postgres.
[deleted]
Here's my similar project, which uses Python and SQLite compiled to WebAssembly in the browser so that it can run my Datasette server-side application entirely client-side:
https://lite.datasette.io/
More about that project here: https://simonwillison.net/series/datasette-lite/
I just added support for installing additional plugins written in Python this morning: https://simonwillison.net/2022/Aug/17/datasette-lite-plugins...
https://lite.datasette.io/
More about that project here: https://simonwillison.net/series/datasette-lite/
I just added support for installing additional plugins written in Python this morning: https://simonwillison.net/2022/Aug/17/datasette-lite-plugins...
On a different note we built https://academy.bigbinary.com/learn-sql using https://sql.js.org/#/ so that folks could learn SQL in the browser without installing anything.
Full disclosure: I worked on it.
Full disclosure: I worked on it.
That is a fine course.
I've been looking at wasm some. its totally unclear to me how this would support Postgres - which requires a posix filesystem interface, and the ability to fork children that operate on a shared memory segment. Were these facilities added to a wasp runtime, or was there major surgery on the Postgres codebase. I feel like I must be missing a simple answer.
All of Linux is in there, too, I think. Postgres-on-Linux-in-WASM
yeah they download v86.wasm which is prolly https://github.com/copy/v86/
> Were these facilities added to a wasp runtime,
https://github.com/WebAssembly/threads/blob/main/proposals/t...
https://github.com/WebAssembly/threads/blob/main/proposals/t...
I was messing with the tutorials in the playground earlier but I'm getting a 404 now [1].
[1] https://www.crunchydata.com/playground
[1] https://www.crunchydata.com/playground
Edit: Should be all better now.
Well that's embarrassing, looks like one of our underlying APIs hit a rate limit, we're working on a quick fix for it.
Well that's embarrassing, looks like one of our underlying APIs hit a rate limit, we're working on a quick fix for it.
Seems to be working now! Thanks!
Demo is playing here https://www.youtube.com/watch?v=Ryj5c8zLF50
This is a fantastic idea, truly innovating what you can do with Postgres. Kudos!
Postgres, possibly surprising to many, is very "simple": it has essentially no dependencies other than a few OS system calls (open, read, write files) and some optional dependencies (e.g. libssl). Therefore, it is very portable and "easy" to compile on many environments. This includes new environments or ideas like compiling it to WASM.
But you need to come up with the idea. This is a great one and opens the door to other use cases. I hope this serves to push the mindset that Postgres can also be used in lighter-weight environments where SQLite (another fantastic database, don't get me wrong) is often considered as the only viable choice.
edit: typo
Postgres, possibly surprising to many, is very "simple": it has essentially no dependencies other than a few OS system calls (open, read, write files) and some optional dependencies (e.g. libssl). Therefore, it is very portable and "easy" to compile on many environments. This includes new environments or ideas like compiling it to WASM.
But you need to come up with the idea. This is a great one and opens the door to other use cases. I hope this serves to push the mindset that Postgres can also be used in lighter-weight environments where SQLite (another fantastic database, don't get me wrong) is often considered as the only viable choice.
edit: typo
Gary Bernhardt wasn't all that wrong when he mentioned anything that can be written in JavaScript will be written in JavaScript.
This post indeed reminded me of his talk https://www.destroyallsoftware.com/talks/the-birth-and-death...
The only thing he missed is that javascript as a compilation target makes less sense than a byte code format designed for such a thing, which is what WASM is. WASM is more similar to JVM bytecode than it is to Javascript. It seems we have a few implementations of WASM that for historical reasons share a code base with a javascript interpreter. But technically that interpreter is not really needed.
You can actually use WASM to run your own javascript interpreter and people are already doing that to not be dependent on the interpreter that comes with the browser or wasm runtime (outside the browser). If you are going to run node.js in a wasm runtime, that's what you might want to do. Likewise, if you want to offer a browser IDE for a node.js project, you might want to run node.js in a browser and this is probably what you'd be doing rather than passing through the javascript to the browser javascript interpreter, which lacks most of the node.js API. Just easier that way.
Likewise if you want to run some old internet explorer 10 javascript, packaging up on old version of that as wasm might allow you to do that. The hard part of course would be getting your hands on the source code. But MS might help us out here or somebody might implement something compatible. Very much like is being done with flash.
You can actually use WASM to run your own javascript interpreter and people are already doing that to not be dependent on the interpreter that comes with the browser or wasm runtime (outside the browser). If you are going to run node.js in a wasm runtime, that's what you might want to do. Likewise, if you want to offer a browser IDE for a node.js project, you might want to run node.js in a browser and this is probably what you'd be doing rather than passing through the javascript to the browser javascript interpreter, which lacks most of the node.js API. Just easier that way.
Likewise if you want to run some old internet explorer 10 javascript, packaging up on old version of that as wasm might allow you to do that. The hard part of course would be getting your hands on the source code. But MS might help us out here or somebody might implement something compatible. Very much like is being done with flash.
Bypassing the idea that this thing is not run via JavaScript but WASM, he was definitely right :)
Use wasm2js to cross-compile WASM to pure JavaScript: https://www.google.co.nz/search?q=%22wasm2js%22 and there are other ways to not require native WASM!
I think it was by Jeff Atwood and hence is called Atwood's Law.
Aah, yes. My bad.
I was fortunate enough to get to interview Craig Kerstiens about this launch as we talked all about vanilla Postgres. You can see a demo of that here.
https://www.youtube.com/watch?v=Ryj5c8zLF50
https://www.youtube.com/watch?v=Ryj5c8zLF50
There are so many things wrong with running an enterprise class database in a browser that I don't even know where to begin!
Congrats to the team. To me this is one of the great things about the times we are living in - tons of computing horsepower for cheap, open source software, new-ish technology (WASM), and one crazy idea.
Well, at least I know where all my spare time is going to be spent...
BUT IT'S FREAKING AWESOME!
Congrats to the team. To me this is one of the great things about the times we are living in - tons of computing horsepower for cheap, open source software, new-ish technology (WASM), and one crazy idea.
Well, at least I know where all my spare time is going to be spent...
I love that you did such an audacious thing.
FYI, if you're on MacOS, someone has packaged Postgres into a standard "just works" self contained MacOS app. With a GUI & system tray menu to control it. It's so good that I use it instead of a Docker image for PG. All the psql and pg_restore commands are contained in the .app package and can be called from the terminal.
https://postgresapp.com/
FYI, if you're on MacOS, someone has packaged Postgres into a standard "just works" self contained MacOS app. With a GUI & system tray menu to control it. It's so good that I use it instead of a Docker image for PG. All the psql and pg_restore commands are contained in the .app package and can be called from the terminal.
https://postgresapp.com/
> Postgres in a browser?!?!
I cannot wait for the novelty of this to wear off, because the wow factor of "do it, but in a brower" should have a worn off long-ass time ago.
I cannot wait for the novelty of this to wear off, because the wow factor of "do it, but in a brower" should have a worn off long-ass time ago.
In the spirit of hacking, I hope it doesn't. Running A on B when B wasn't intended to run on A definitely piques my interest. I'm continuously impressed by such things, like Doom running on calculators.
I get that, but these are runtime platforms designed specifically to get general-purpose apps running in the browser. That is their sole reason for existence.
Yes, WASM is awesome like that. But we've been messing with stuff like that since enscripten, if not earlier.
Besides, postgres already runs on pretty much everything.
Yes, WASM is awesome like that. But we've been messing with stuff like that since enscripten, if not earlier.
Besides, postgres already runs on pretty much everything.
Could I brew install Postgres to play with some SQL and clean up after myself when I'm done? Yes. I could also just load this, perhaps in multiple tabs, and not think about it.
I'd be impressed if they fit it into a dll with the databases as one file, a la SQLite
Now that would be an achievement!
Now that would be an achievement!
There are still a lot of things that can't be done in browsers but should be doable, so I'm ok with this. Like imagine that instead of having to make a native iPhone app for every little thing, which ironically is probably JS under React Native anyway, Apple supported most of those native features via the browser. Of course Apple has much more to lose than to gain from that. They deliberately don't even support push notifications in PWAs.
Also, Postgres in browser is actually useful.
Also, Postgres in browser is actually useful.
Would be nice to get Alpine or Debian compiled to WASM, that way it would just be another architecture to be run through Docker and we could get a lot of easy-to-run Docker images right in the browser. Is there any project working on this?
That already exists. In fact the Crunchy Data PostgreSQL playground loads a full Linux VM as part of how it works.
Take a look at this project: https://webvm.io/ - explained here: https://leaningtech.com/webvm-server-less-x86-virtual-machin...
Take a look at this project: https://webvm.io/ - explained here: https://leaningtech.com/webvm-server-less-x86-virtual-machin...
But you wouldn't need to emulate x86 itself, the binaries would just compile to WASM like they do for ARM.
I guess for that one would need to implement all Linux syscalls in WASM
I guess for that one would need to implement all Linux syscalls in WASM
The problem is that it would be an actual new architecture (just as much as ARM or MIPS or whatever), which means you wouldn't have compatibility with any existing docker images.
Supabase launch week just got sniped. j/k I have no idea what they have for the rest of the week and someone already referenced Django in the browser.
Hats off to the engineers who pulled this off.
This is not the first time someone has gotten PG running in a browser though. Here's another approach:
https://github.com/oguimbal/pg-mem
This is not the first time someone has gotten PG running in a browser though. Here's another approach:
https://github.com/oguimbal/pg-mem
I don't think thats really Postgres running in the browser, it's more a "compatible" reimplementation of Postgres in TypeScript. Obviously impressive in its own right!
I unironically want to use Postgres WASM and much other software in production.
I want the whole stack to be WASM.
It would provide a lot of interesting advantages for deployment.
Just like the JVM, WASM being relatively limited allows you to assume more about the program you are deploying.
I want the whole stack to be WASM.
It would provide a lot of interesting advantages for deployment.
Just like the JVM, WASM being relatively limited allows you to assume more about the program you are deploying.
Great achievement! Now please PostGIS in the browser would make this incredible. Turfjs is a bit limited for Ionic and electron apps, imagine PostGIS in the browser.
When I see 'in the browser' I read 'in an easy build and ship app'.
When I see 'in the browser' I read 'in an easy build and ship app'.
They are really fast with requests!
https://www.crunchydata.com/developers/playground/basics-of-...
https://www.crunchydata.com/developers/playground/basics-of-...
Oh I have no doubt, but I am thinking of niche use cases in a offline setting.
Can this become an embedded database? I noticed that the load time is quite high. That's prob due to the size of the binary. I think the size can be dramatically reduced if you remove stuff that is not practical in the browser.
The tutorials and interactive learning at SQL Tutorial are pretty good. Not Postgres (well, don't know anything about their implementation).
I've been having my kids go through it. They are learning a lot. I would recommend this site to anyone who needs to start with SQL from the ground-up and get a reasonable understanding of the language with practical hands-on usage.
https://www.sqltutorial.org/
A good first step before jumping into CrunchyData?
I've been having my kids go through it. They are learning a lot. I would recommend this site to anyone who needs to start with SQL from the ground-up and get a reasonable understanding of the language with practical hands-on usage.
https://www.sqltutorial.org/
A good first step before jumping into CrunchyData?
Interesting news, I hope to see the WASM based postgres for dev machine integration tests definitely would make things easier then the current setup through Docker based containers.
Love this! I think it's time for the database to move closer to the browser.
We spend some time getting the node-postgres library working with websockets so we could go browser->websockify->postgres: https://github.com/bitdotioinc/node-postgres - this lets us use a full-featured postgres client (with, eg, cursor support) in the browser.
We spend some time getting the node-postgres library working with websockets so we could go browser->websockify->postgres: https://github.com/bitdotioinc/node-postgres - this lets us use a full-featured postgres client (with, eg, cursor support) in the browser.
This is really cool, one nitpick on the basics tutorial though. It could be confusing for beginners to write `select distinct(event_type)` because it makes it look like `distinct` is a function, and makes it looks like distinct only applies to certain columns. I've seen postgres beginners confused by syntax like this writing queries like `select distinct(event_type), magnitude` and expecting the query to only do a distinct on `event_type`.
Do you have plans to open source any of the work? Would love to use this for a course/training platform.
What we need is Kubernetes running in the browser to orchestrate
web (WASM) applications and infrastructure it takes to render
web 4.0 sites and all the different blockchains that it will require.
Looks very cool! Getting a 404 error at the link though (as of 10:30am PT anyway): https://www.crunchydata.com/developers/tutorials
Should be fixed now! We hit a rate limit a lot sooner than expected haha :)
I seem to remember that Postgres uses SysV shared memory between the master and connection processes - how does this end up working with WebAssembly, is there a build option to use threads for shared memory instead or...?
Answering myself: this is using a x86 emulator apparently, not runnign PG natively in WebAssembly (from https://news.ycombinator.com/item?id=32502781)
See also https://github.com/simonw/datasette-lite: Datasette running in your browser using WebAssembly and Pyodide
A bit OT, but since we’re talking postgresql, can anyone recommend a good course (or book) about postgresql server administration?
Ideally something that goes from installation from the official deb repositories to a knowledgeable (junior?) dba.
Ideally something that goes from installation from the official deb repositories to a knowledgeable (junior?) dba.
The basic https://www.postgresql.org/docs/current/index.html is where I learned everything.
A number of good options. Different folks have their favorites: https://www.postgresql.org/docs/books/
Postgres docs and several recent items here talking about "postgres internals" will probably give you a lot of great starting points.
How does this work if there's no physical storage?
Can WASI use memory as a virtual filesystem?
Can WASI use memory as a virtual filesystem?
You can use anything you can use from Javascript. E.g., https://www.npmjs.com/package/memfs can be used with https://www.npmjs.com/package/@wapython/wasi to provide a ramdisk, and similarly you can make it persist to indexdb or localStorage. This sort of thing is also built into emscripten. You can see this in action at https://jupyter.org/try-jupyter/lab/
It's also possible (on Google Chrome) to use browser API's to directly access your host computer's filesystem (see https://developer.mozilla.org/en-US/docs/Web/API/File_System...).
It's also possible (on Google Chrome) to use browser API's to directly access your host computer's filesystem (see https://developer.mozilla.org/en-US/docs/Web/API/File_System...).
From tfa:
> Due to browser sandboxing there is no way to connect directly to the Postgres instance beyond the embedded psql interface that we establish for you. The current configuration allocates 512MB of memory for your Postgres instance, we may make this more configurable in the future. It’s in the browser, hence if you refresh you’re going to get a fresh instance, we haven’t created any persistence layers (yet).
> Due to browser sandboxing there is no way to connect directly to the Postgres instance beyond the embedded psql interface that we establish for you. The current configuration allocates 512MB of memory for your Postgres instance, we may make this more configurable in the future. It’s in the browser, hence if you refresh you’re going to get a fresh instance, we haven’t created any persistence layers (yet).
There is no WASI in browsers, but yes you can use filesystems in main memory. It's called a 'RAM disk.'
WASI is just a set of standardized interfaces that any WASM host can implement. So while there’s no WASI out of the box on browsers, there’s nothing preventing you as a developer from bringing your own implementation (in which, for example, writes to stdout could sent to console.log, and files could be backed by an in memory VFS).
See this article, where the mention (and link to) polyfill on browsers: https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webas...
See this article, where the mention (and link to) polyfill on browsers: https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webas...
Incredible. Could this be used to host a local instance using webrtc?
Will this work with manifest v3? (Does it need a worker thread like absurd-sql and thus doesn't work with mv3?) If it does work, is there a repo or writeup on how to DIY this?
Pretty awesome.
I'm creating tutorials where I try to let people learn as much as possible without the need to install anything and software in that spirit always makes me happy.
I'm creating tutorials where I try to let people learn as much as possible without the need to install anything and software in that spirit always makes me happy.
[deleted]
That is super cool! Really curious to hear how it's built, how much runs in wasm, how you got (some portions of) postgres to compile to wasm, etc.
That's pretty insane, what does that mean in terms of data storage? Does everyone get a copy of the data locally or is it fetched on the fly?
Both? So for the tutorials they use a SQL initialization file stored in a GIST (our solution, but can be stored at any public storage url) and it loads that data and any other SQL setup commands in that file on startup to the database that exists in your local browser session only.
Waiting for this to be posted in response to noobs asking how to connect to their database from their web page.
If anyone out there wants to work on an open source version of this full-time please reach out to me.
Could this be compiled into a C lib and then we get in-memory postgres for testing?
I'm getting 404 on the Playground link. Do I need to be signed in or something?
Sorry about that, we hit some rate-limits but should be all resolved now.
Also, you can `CREATE EXTENSION postgis` on it, and all that works too!
Doesn't work. Page not found
This is really neat, great work!
really an amazing project. Things like this are the reason I come to HN
maybe we can finally bring back websql
Question. Why are people so positive on Postgre versus MySQL?
I'm partial to MS SQL Server myself.
So many rave about Postgre. Isn't it just another RDBMS?
I'm partial to MS SQL Server myself.
So many rave about Postgre. Isn't it just another RDBMS?
MySQL was the M in the LAMP stack (I’m this old, crap) but it had issues with downright brain dead behaviors and defaults which were only fixed multiple major versions later. In the meantime if you wanted to store what you asked to be stored without silent truncation etc you chose Postgres.
From the user's perspective, it's the little things. The feature set is overall bigger and better than comparable RDBMSs. Not having to deal with Oracle or Microsoft is also nice. But there's not a huge difference in usage vs MySQL.
Those familiar with the internals might say a lot more about how it's faster or something. IDK, wouldn't surprise me if it were.
Those familiar with the internals might say a lot more about how it's faster or something. IDK, wouldn't surprise me if it were.
PostgreSQL's fame is kind of a paradox: it's so good because everyone uses PostgreSQL because it's so good. With the variety and scale of applications PostgreSQL is used for, it has organically evolved to be a swiss army knife of a database.
Basically PostgreSQL is to databases as Linux is to an OS.
Basically PostgreSQL is to databases as Linux is to an OS.
Wasn't always this way...even not that long ago. Back when Heroku was getting serious about building up the Postgres service (I helped some...this is circa 2010) the common question was "that's weird, why not MySQL, isn't that how most Rails applications are run?" RDS only had a MySQL product for a very long time, and that followed from market demand. Tutorials for Rails were generally written with MySQL.
Hot standby (streaming replication) was a key feature to arrive around then. Also, I'd like to think a lot of the work Heroku did to engineer Heroku Postgres, and market it, contributed much better Postgres support in web frameworks and their affiliated ORMs in those critical years from 2010-2014, where encountering headwinds in defects in Postgres support for Rails, Node, etc was common.
When RDS came around with their Postgres offering, I'd say at that point, it could be said that Postgres entered a new stage: it was no longer reflexive for engineers to shrug their shoulders at shaky Postgres support in drivers/ORMs/etc like they did before that.
Hot standby (streaming replication) was a key feature to arrive around then. Also, I'd like to think a lot of the work Heroku did to engineer Heroku Postgres, and market it, contributed much better Postgres support in web frameworks and their affiliated ORMs in those critical years from 2010-2014, where encountering headwinds in defects in Postgres support for Rails, Node, etc was common.
When RDS came around with their Postgres offering, I'd say at that point, it could be said that Postgres entered a new stage: it was no longer reflexive for engineers to shrug their shoulders at shaky Postgres support in drivers/ORMs/etc like they did before that.
We talked about it in this video, about how it's grown the way it has. https://www.youtube.com/watch?v=Ryj5c8zLF50
> it's so good because everyone uses PostgreSQL because it's so good
Is it really a circular relationship if the starting premise is "people use it because it's good"?
Is it really a circular relationship if the starting premise is "people use it because it's good"?
The post explains a lot of the high level, but we're going to being doing some deeper dives as well including the build process, but also some of how the tutorials are powered by an internal notion doc which allows us to easily iterate and collaborate on the tutorials themselves.
Perhaps our favorite easter egg is that you can bring your own SQL into it for example: https://www.crunchydata.com/developers/playground?sql=https:...