Running PostgreSQL Inside Your Browser
Written by Nikos Vaggalis   
Thursday, 08 September 2022

Yes it is possible thanks to, what else, WebAssembly.  And with it, the emergence of the Postgres playground, brought to us by Crunchy Data, with live-guided SQL exercises for any level.

One of PostgreSQL's engineers had a crazy idea which he has now managed to realize.The idea came from the fact that it was possible to run SQLite inside your browser, so the reasoning was could the same happen for Postgres too?

But compiling an embedded and small in size and scale
DBMS to Webassembly (as sql.js does for instance) is one thing; compiling a full scale client-server one is another. Proof of that is, as the folks at Cruncy Data found out that the web browser simply did not offer the networking features that Postgres needed which was deemed a pretty big obstacle.Or was it ?

But there was a workaround. By building a v86 virtual machine based on an Alpine Linux image and installing Postgres on it,
they could run it inside the browser because v86 emulates an x86-compatible CPU and hardware by translating machine code to WebAssembly at runtime. So Joey Mezzacappa went WebAssembly indirectly documenting the how-to in his blog post.

However there's two limitations with that approach; there's no internet access from inside the VM and there's no data persistence, so changes are lost when leaving or refreshing the page.

That latter disadvantage is one they actually took advantage of,  building on it to create ephemeral playgrounds that let you
practice your SQL inside your browser and then just let go when finished. As such the official Crunchy Data Postgres Playground took off and let's you run SQL inside your ephemeral WASM container containing guided exercises for any level :

The playgrond offers: 

  • psql basics
    Start here with a loaded a sample database
  • Partitioning
    Learn how to create partitions with native Postgres and pg_partman with an IoT sample dataset
  • High level performance analysis
    A quick introduction to some of the most important Postgres performance metrics, including cache hit ratio, index hit, and bloat
  • Joins in Postgres
    Learn about inner and outer joins in this tutorial
  • Basics of PostGIS
    Test some sample spatial queries and functions with PostGIS
  • Indexing (B-Tree Indexes)
    Learn how to create a b-tree index in Postgres.
  • CTEs and Window Functions
    Learn how to query data with US Birth data 

So what's the big deal? I could very well spin a Postgres instance on SQL/DB Fiddle SQL playgrounds and run my queries there, no problem. How does my local Postgres copy offer more?

For one, it is fast as it runs locally without the need to set up a VM lab. There are no restrictions which would be needed for an  internet-based playground; you have control of a full instance and the full spectrum of SQL and functionality. And of course privacy since you can use your own data at your own machine.

Finally there's an extra bonus.With the news of Heroku dropping its free plans, which include the Postgres instances, having an option to run such services locally is going to be a great advantage.I don't know whether it's related to crypto mining, but other SaaS providers like CI webapp.io, GitLab, TravisCI, and Shippable are all limiting or shutting down their free tiers due to cryptocurrency mining attacks.So again, having a service running locally could prove a great alternative.

It's still early days for Postgres and WASM so there's some polishing to do, but the people at Crunchy Data are working on it.

 

More Information

Crazy Idea to Postgres in the Browser

Postgres Playground

Related Articles

The PostgresSQL Transition Guide Helps You Make The Switch

pg_ivm - Materialised Views On Steroids

 Heroku Announces End Of Free Service

In Praise Of SQLite

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Linux And Android Waste Coding Effort
14/09/2022

For many years it has been standard practice to test that you get the memory you ask for, but it has all be a huge waste of time. Operating systems get in on the act before you have a chance to do any [ ... ]



NetBeans 15 Improves Language Server Protocol Support
12/09/2022

Apache NetBeans 15 has been released with improvements including better support for Language Server Protocol (LSP), along with updates to Maven integration and better YAML editing.


More News

pythondata

 



 

Comments




or email your comment to: comments@i-programmer.info

Last Updated ( Thursday, 08 September 2022 )