8 ms·

Show HN: PGlite – in-browser WASM Postgres with pgvector and live sync

509 points by samwillis 2y ago

Hey, Sam and the team from ElectricSQL here. PGlite is a WASM Postgres build packaged into a TypeScript/JavaScript client library, that enables you to run Postgres in the browser, Node.js and Bun, with no need to install any other dependencies. It's 3mb Gzipped, now has support for many Postgres extensions, including pgvector, and it has a reactive "live query" API. It's also fast, with CRUD style queries executing in under 0.3 ms, and larger, multi-row select queries occurring within a fraction of a single frame. PGlite started as an experimental project we shared on X, and the response to it was incredible, encouraging us to see how far we could take it. Since then we have been working to get it to a point where people can use it to build real things. We are incredibly excited as today, with the release of v0.2, the Supabase team has released the amazing http://postgres.new http://postgres.new site built on top of it. Working with them to deliver both PGlite and postgres.new has been a joy. - https://pglite.dev https://pglite.dev - PGlite website - https://github.com/electric-sql/pglite https://github.com/electric-sql/pglite - GitHub repo - https://pglite.dev/docs https://pglite.dev/docs - Docs on how to use PGlite - https://pglite.dev/extensions https://pglite.dev/extensions - Extensions catalog - https://pglite.dev/benchmarks https://pglite.dev/benchmarks - Early micro-benchmarks - https://pglite.dev/repl https://pglite.dev/repl - An online REPL so that you can try it in the browser We would love you to try it out, and we will be around to answer any questions.

blueprint 2y ago
Anyone know how this would play with at-rest encryption extensions?
- samwillis 2y ago
  There are two options here: - Build a Postgres at-rest encryption extensions as WASM, that should "just work" as long as you can build the extension. - Create an encrypted VFS for PGlite. This is doable now, but we haven't yet documents the VFS API as we want to make it a little cleaner. But yes, it's 100% possible to build an at-rest encryption scheme for PGlite.
sgbeal 2y ago
To keep the next person from having to look (or ask without looking): it supports browser-side persistence via IndexedDB and OPFS: <https://pglite.dev/docs/filesystems https://pglite.dev/docs/filesystems>
- samwillis 2y ago
  Yours and Roys work on WASM SQLite VFSs has directly influenced my work on this. Thank you!
  - sgbeal 2y ago
    > Yours and Roys work on ... (blush) But to be 100% clear: the AHP-based VFS in the SQLite project is a direct port of Roy's, so all of the credit for that one truly goes to him!
creativedg 2y ago
Huge fan of PGlite. It's the perfect solution to have Postgres without the need of Docker. With just `npm install`, you can have a Postgres instance on your computer. So, it's extremely easy to onboard a new developer in your team. And, the good news, PGlite works perfectly with Next.js. I'm using PGlite in local and development environment with Next.js Boilerplate: https://github.com/ixartz/Next-js-Boilerplate https://github.com/ixartz/Next-js-Boilerplate With only one command `npm install`, you can have a full-stack application, which also includes the database (a working Postgres). And, no need to have/install external tools.
- tracker1 2y ago
  Definitely interesting, though I often just use docker compose for dev dependencies, this is definitely a cool alternative.
  - creativedg 2y ago
    Yes, it's very cool alternative and it removes the need for docker compose for dev environment. On top of it, PGlite also perfect for CI and testing.
    - ggregoire 2y ago
      Wouldn't be easier to have a shared dev database? So that each developer doesn't have to apply the migrations and import the dumps in their local db, and figure out why the local db for dev A is different than the local db for dev B.
      - andruby 2y ago
        That sounds cumbersome when devs are working in their respective branches and changing the schema (adding migrations)
      - motolov 2y ago
        IME, creating scaffolding to allow for a known, reproducible and clean state (of both DDL and DML) are wild systematic (eg across the team and the estate) productivity and stability boosts. Not having to be "connected to the shared dev DB frees engineers of latency (every ms counts) and also of mutulple pollutants to that database (eg Sally was writing protractor/cypresss tests and now the foo table has 3m rows, george was doing data exploration and manually entered records to the DB that now causes a runtime exception fot the team, etc) If a shared dev DB is really what everyone wants, then at least having the scaffolding mentioned above to fix the DB state when pollution happens (it will) will help heal foot shots. In industrialized practice, what you are mentioning (a shared dev/env) is really the "QA"/pre-prod environment. Ideologically and personally (putting on down vote helmet) if you can't run the whole stack locally you're in for a world of trouble down the road.. local first, test extensively there, then promote changes.
saurik 2y ago
Does there happen to be a native analog to this? So like, if I eventually want some kind of native app, I wouldn't have to throw away the architecture entirely and start over? I only see mention of the WASM version of this in the website/docs.
- samwillis 2y ago
  It's planned. We want to extract the core changes to Postgres that we had to make and create a "libpglite" C lib that can be linked to from any language/platform. Support important for React Native, as it doesn't have WASM support.
  - nikki93 2y ago
    This would be nice also to have an API that's usable from C/C++ code running in Wasm. I see libraries often do this where they expose a C/C++ library like Postgres to Wasm and the main / documented API is JS and you have to dig a bit to find the C/C++ API if it's possible to access it that way.
simonw 2y ago
I'd seen this running in a browser before (the ~3MB download is really impressive for that), but I hadn't clocked that it runs server-side with Node.js and Bun as well: https://pglite.dev/docs/ https://pglite.dev/docs/ Since that's still not spinning up an actual network server, that feels like it's an alternative to SQLite - you can spin up a full in-process PostgreSQL implementation, that persists to disk, as part of an existing Node.js/Bun application. That's really interesting! I'd love to use this from Python, via something like https://github.com/wasmerio/wasmer-python https://github.com/wasmerio/wasmer-python or https://github.com/bytecodealliance/wasmtime-py https://github.com/bytecodealliance/wasmtime-py - has anyone run PGlite via one of those wrappers yet?
- samwillis 2y ago
  Getting PGlite working with other languages is very high on my list. We are working on two approaches: - a WASI build with a lower level api that users can wrap with a higher level api. - a "libpglite" that can be linked to by any native language. The WASI build is likely a WASM build of this. Most languages already have a Postgres wire protocol implementation and so wrapping a low level API that reads/emits this is relatively easy - it's what the JS side of PGlite does.
  - sitkack 2y ago
    I'd love to see this for SQLite and DuckDB at the same time. I could see them using a common wasi filesystem shim so they can transparently do range requests over http blobs. Do you have a branch for this work?
    - samwillis 2y ago
      No branch yet, we're still in the early research and experimental stage.
  - rubenfiszel 2y ago
    If you can make it work with Rust, and compatible with wasm targets as well, it opens a huge field of possibilities. Windmill.dev is a workflow engine based fully on postgresql. This would be the missing piece to offer a local development workflow that doesn't require spinning a full pg.
batmansmk 2y ago
Pretty awesome. Would love to use it in CI and locally for our PG product. We use Prisma, so I guess we have to wait for the connector that looks like pg to plug it in.
- dboreham 2y ago
  Curious, what's the benefit vs running real PG in a container for this specific use case?
- creativedg 2y ago
  I confirm it works perfectly in CI and locally. I'm using it in CI and locally. Not sure about Prisma but it works with Drizzle ORM.
- samwillis 2y ago
  pg-gateway from Supabase does exactly this, you can start a PGlite and then connect to it from any standard PG client: https://github.com/supabase-community/pg-gateway https://github.com/supabase-community/pg-gateway
metadat 2y ago
How did they manage to fit Postgres in 3MB?
- AlexErrant 2y ago
  Answered here https://news.ycombinator.com/item?id=41224295 https://news.ycombinator.com/item?id=41224295 > You might remember an earlier WASM build[1] that was around ~30MB. The Electric team [2] have gone one step further and created a complete build of Postgres that’s under 3MB. > Their implementation is technically interesting. Postgres is normally multi-process - each client connection is handed to a child process by the postmaster process. In WASM there’s limited/no support for process forking and threads. Fortunately, Postgres has a relatively unknown built-in “single user mode” [3] primarily designed for bootstrapping a new database and disaster recovery. Single-user mode only supports a minimal cancel REPL, so PGlite adds wire-protocol support which enables parametrised queries etc.
drzaiusx11 2y ago
Anyone know if something similar is available for MySQL/Maria? This is very cool, especially given the tiny size of it
namanyayg 2y ago
How does the performance compare to actual postgres?
- samwillis 2y ago
  WASM is inherently slower than native, but it does very well! We have some micro-benchmarks here that have some baseline native numbers to compare to: https://pglite.dev/benchmarks https://pglite.dev/benchmarks Much of the complexity with WASM in the browser actually comes down to the performance of the underlying VFS for storage. When running in-memory or using the OPFS VFSs both PGlite and the WASM SQLite builds are very performant, absolutely capable of handing the embedded use case.
  - jfeiwell 2y ago
    Any benchmarks against redis?
    - samwillis 2y ago
      Redis is a very different database, I'm not sure you could do a comparison that was meaningful in any way.
bezbac 2y ago
I recently experimented with using pglite for API integration tests in one of my side projects. It worked pretty well and has much better DX than using something like testcontainers[0] to spin up postgres running in docker. [0]: https://testcontainers.com https://testcontainers.com
- riverdroid 2y ago
  I would also love to use it this way in Go. Currently we dump SQLite compatible schema to use for SQLite powered integration tests even though we use postgres when live. It allows us to have zero dependency sub-second integration tests using a real database. Being able to use postgres would be even better, allowing us to test functionality requiring PG specific functionality like full text search.
  - koeng 2y ago
    I do exactly the same thing. I’m even running SQLite in wasm so I don’t have any C dependencies. Switching out for Postgres would be awesome
  - FlyingSnake 2y ago
    I'm bothering the PGLite team a lot to help us enable this :-) We have different options like embedded-postgres or integreSQL, but none match the simplicity of PGLite. I hope this wish comes true soon. https://github.com/fergusstrange/embedded-postgres/tree/master https://github.com/fergusstrange/embedded-postgres/tree/mast... https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql
    - koeng 2y ago
      What is the problem with https://github.com/fergusstrange/embedded-postgres/tree/master?tab=readme-ov-file https://github.com/fergusstrange/embedded-postgres/tree/mast... ?
- samwillis 2y ago
  I see unit and integration testing as a massive opportunity for PGlite. I know of projects (such as Drizzle ORM) that are already making good use of it. The team at Supabase have built pg-gateway (https://github.com/supabase-community/pg-gateway https://github.com/supabase-community/pg-gateway) that lets you connect to PGlite from any Postgres client. That's absolutely a way you could use it for CI. One thing I would love to explore (or maybe commenting on it here will inspire someone else ;-) ) is a copy on write page level VFS for PGLite. I could imagine starting and loading a PGlite with a dataset and then instantly forking a database for each test. Not complexities with devcontainers or using Postgres template databases. Sub ms forks of a database for each test.
makkesk8 2y ago
Awesome work! A killer feature of sqlite that I would love to see in pglite would be javascript window functions.
- isoprophlex 2y ago
  Huh, can you do that? I can't google anything just now, got a link to some docs about js window functions in sqlite?! Sounds very powerful
  - AlexErrant 2y ago
    Maybe they're talking about https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html where you can define a window fn in the (perhaps JS) app via `sqlite3_create_window_function()`.
    - samwillis 2y ago
      This is high on my list, I have a few ideas how to do it, one being a "PL/JS" extension that calls out to the host JS environment to run JS functions.
      - isoprophlex 2y ago
        I think PG is relatively ideal for that. In a classical data warehousing/ETL context, I've called python directly from inside PG, which has its quirks but is pretty doable, all in all... https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html
      - AlexErrant 2y ago
        FYI Turso does it with WASM https://turso.tech/blog/webassembly-functions-for-your-sqlite-compatible-database-7e1ad95a2aa7 https://turso.tech/blog/webassembly-functions-for-your-sqlit...
teaearlgraycold 2y ago
Heads up, the supabase link on the front page 404s
- samwillis 2y ago
  Thanks, fixed!
AlexErrant 2y ago
Bug report? On https://pglite.dev/repl/ https://pglite.dev/repl/ running `SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;` works, which is cool. However, running `SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');` fails with `y is not a function`. Then trying to run the first query yields `null`, which is weird. I can open an issue if you want :)
- kylemathews 2y ago
  Posting the issue to GitHub would be great!
  - AlexErrant 2y ago
    ack
bearjaws 2y ago
PGlite / ElectricSQL is definitely something I want to use in a future job. The ability to replicate and subscribe to the changes, all within the browser seems incredibly powerful. Having worked on medical software that runs a 2,000,000+ patient workload that perform refills for chemotherapy/HIV/immuno drugs, where it was common for people to trample each others work as they ran insurance, called patients etc... We had to roll our own locking system that relied on interval functions (yay IE7) and websockets... This meant when you called the patient, someone running financial assistance would be unable to work on the same profile. I can envision other uses for FTS or even vector search locally, since they are insanely expensive at scale.
- TeeWEE 2y ago
  That means getting those 2 million patients on every device... Really bad choice for medical software.
  - thruflo 2y ago
    Electric does partial replication. It’s specifically a system for syncing a different subset of data onto each local device.
  - bearjaws 2y ago
    Yeah not sure how you read it that way... Theres obviously segmentation as to what employees can see what patients, baked into the schema level.
isoprophlex 2y ago
That's great! Corpo beancounters want us to move off docker on EC2 onto bare metal, making both dev and deploy experience terrible. This will help ease pain!
- samstave 2y ago
  Weird question: Since you mention 'bare-metal' and this is "enables you to run Postgres in the browser" UH, could you do: bare_metal --> HeadlessGPU-Chrome --> PGLite with whatever orchesrtration and you have a an automatable 'browser' that can scrape/store/logic/log/be-config'd from/lookup directly to your browserDB and maybe run a fastAPI ontop of such that you have a full stack, baremetal, headless, browserDB/fastAPI/endpoint processing doohicky? Or does this sound dumb? Because this is exactly what I actually need and am attempting to create - but its beyond my capabilities in any functional/elegant implementation... https://www.browserless.io/blog/browserless-gpu-instances https://www.browserless.io/blog/browserless-gpu-instances https://github.com/dhamaniasad/HeadlessBrowsers https://github.com/dhamaniasad/HeadlessBrowsers
  - samwillis 2y ago
    PGlite also runs in Node and Bun, so you can easily run it on backend servers. (couldn't fit that in the HN headline)
    - samstave 2y ago
      Sorry if this is in the RTFM; So, you can basically use this to deploy a portable fully enclosed little webapp. Basically any little web-app that has a bunch of backend DB: A sales USB drive that loads a page that then has the pglite of all your product stuff on there. and you can just provide custom buttons to slurp data either way. Utils to yank a [data] from a postgress server, onto your little sneaker-DB-USB -- then carry it over and to a DB function... A little util USB that you already have it setup such that you can curl a bunch of data directly into tables on your little headless-browser-pglite minion? Full Kisok DB USB (SSD) upgrades. Basically - running a pico headless postgress DB with a webUI? --- aside: samwillis March 2008 samstave March 2009 :-) --- WOW: https://i.imgur.com/6YLwEuj.png https://i.imgur.com/6YLwEuj.png -- Yeah this is pretty dopes.
srameshc 2y ago
If I understand correctly, this is like SQLite, but Postgres. I love SQLite, but sometimes I need a little more. So, no more saving Date as text and we have arrays, jsonb etc and all the good stuff from Postgres. Am I right ?
- samwillis 2y ago
  Exactly, all your favourite PG types, plus any that come with extensions such as vectors with pgvector. We are working on PostGIS to, which will bring the geo types to PGlite.
- aitchnyu 2y ago
  IIRC Sqlite can exist as flat file and can be backed up. Will this work the same? And will it allow multiple writers?
  - srameshc 2y ago
    Every use case and expectations are different IMO. And yes if it's a file system , you can always grab a way to keep a snapshot. Too early for this project to deliver everything at one go.
cpursley 2y ago
Woah, this is neat - insane that it can handle extensions. Just added a new section to my "Postgres Is Enough" gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#in-browser--wasm https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
FlyingSnake 2y ago
PGLite is a wonderful achievement and I've a fan of it since it's early days. If they get it to a stage where other languages apart from JS can use it, it could be revolutionary. The possibilities are limitless.
revskill 2y ago
The migration docs to integrate with Vite/webpack is still lacking though.
loevborg 2y ago
PGLite is amazing for integration testing of queries against a real in-memory db. Is it possible to use PGLite for that purpose in non-JS environments, like e.g. golang?
- samwillis 2y ago
  You can use pg-gateway from supabase (https://github.com/supabase-community/pg-gateway https://github.com/supabase-community/pg-gateway) to start a PGlite and connect form any standard PG client. Support for other languages is high on my list, the plan is to build a "libpglite" as a native C lib and a WASI build that can run in any WASM runtime.
  - loevborg 2y ago
    Thanks! From my perspective, being able to pull pglite into the same golang process is preferable - faster, less IPC complexity, no risk of leaving lingering state. Making postgres an in-process dependency is much of the appeal of pglite for me.
Kinrany 2y ago
Can it run in Node while still persisting data to disk?
- samwillis 2y ago
  Yes, we can persistent to the normal filesystem with Node and Bun, and to IndexedDB and OPFS in the browser: https://pglite.dev/docs/filesystems https://pglite.dev/docs/filesystems
NetOpWibby 2y ago
This is exciting. My database of choice is EdgeDB, which uses Postgres under the hood. It's not so far-fetched to imagine EdgeDB in the browser now!
Retr0id 2y ago
Normally I'd say the main difference between postgres and SQLite is that the latter is in-process. Now that they can both be in-process, is there a more detailed comparison of the two? When might I prefer one over the other?
- deleted 2y ago
  [deleted]
- nbbaier 2y ago
  I'd also love to know this!
- AlexErrant 2y ago
  Well, Sqlite is battle-tested to hell and back... and with PGlite I managed to get a `memory access out of bounds` in less than 3min of playing around. So perhaps maybe not prod-ready yet, even if it's bigger brother is one of the most solid DBs. (To be fair I've also seen & experienced issues with SQLite - no software is bug-free.)
  - grsmvg 2y ago
    What I always wonder is which people use SQLite to run tests for a Postgres application. Isn’t the difference in dialect pretty much always an issue unless you do only the most basic type of queries? pglite fills a hole where imho currently only dockerized Postgres sits
    - Kinrany 2y ago
      You can write all queries twice and run the same test suite on both, then use sqlite in other tests. Of course at that point you can just use a struct that stores all the same data.
non- 2y ago
Would really like to see a benchmarking analysis vs SQLite. How do they compare on memory usage and overhead? I already default to SQLite for new side projects with the idea that "eventually I'll migrate to Postgres if this project gets legs", so switching to PGlite feels like a no-brainer as long as it's not going to weigh my apps down or force me to upgrade out of whatever entry-level server solution I'm using is for a given project. EDIT: Found the benchmarks here https://pglite.dev/benchmarks https://pglite.dev/benchmarks
- samwillis 2y ago
  Ah, you beat me to it, yes those benchmarks give you a good overview of baseline performance. But it'a a little nuanced as the underlying VFS performance is very significant. Both PGlite and SQLite in the browser is incredible. The latter is a little lighter weight and more mature, but PGlite brings with it all the type support and extensions that you love with Postgres. It comes down to what works best for your project.
throwaway290232 2y ago
[dead]
satvikpendem 2y ago
Congrats on the Show HN. I follow the ElectricSQL Discord server and I was distinctly interested in this but for other languages than TypeScript, so it's nice to see that making it language independent is high on your list. I also saw that ElectricSQL is being rewritten due to architectural changes, does that impact PGlite at all or are they separate projects now? What is the relationship between PGlite and ElectricSQL as well, just curious? Also, fun etymological thing, SQLite is actually SQL-ite, as in, urbanite, not SQ-Lite, but due to rebracketing [0] and libfixing [1], now people seem to use the -lite suffix rather than the -ite one, presumably because lite actually implies something whereas ite would not, as much. It's like how helicopter is actually helic and opter, a helical wing that spins, but now people think of it as heli and copter, calling other things related to it like helipad, or quadcopter, as Wikipedia states. [0] https://en.wikipedia.org/wiki/Rebracketing https://en.wikipedia.org/wiki/Rebracketing [1] https://en.wikipedia.org/wiki/Libfix https://en.wikipedia.org/wiki/Libfix
- samwillis 2y ago
  Hey, yes, PGlite and Electric sync are separate projects, but we are very much building PGlite as a sync target for Electric. The changes we are making with Electric is towards a more loosely coupled stack, rather than the tightly integrated stack we had before. PGlite is one possible client store for Electric sync, and fulfils the use case where you want to have a full SQL database on the client along with (potentially) the same schema on client and server. The name is (obviously) a nod to SQLite, which goes without saying is an incredible database. We went with the small "l" in the name to add a slight differentiator and a nod towards "light", and we are a light weight Postgres.
  - zooq_ai 2y ago
    what kind of data load are we talking here? I know it gets stored locally. So, is it limited by my local disk size. How will it perform if I have 1TB of data?
Cu3PO42 2y ago
When I saw the headline, I immediately thought "I bet this would go really well with ElectricSQL", so it's great to see this coming from you! The immediate DX is incredible and I'm itching to use PGLite (and ElectricSQL) in a production project, I expect it would remove quite a few pain points I'm currently experiencing. (Also, I just like working with CRDTs.) I don't have any constructive criticism to offer right this moment, but I just wanted to congratulate you on the launch and an incredible looking product!
ggregoire 2y ago
I was gonna ask what do people use to sync their local pg with their remote pg, but turns out, that's exactly what the authors are building. Cool stuff, and gratz on pglite!
herpdyderp 2y ago
What's the equivalent Postgres version? Meaning, if I wanted to make sure my app that is currently using a Postgres Docker image is compatible with PGlite before switching, which version of Postgres should my Docker image be on? I see "PostgreSQL 15devel" in a screenshot so I'm assuming that means v15 but explicit documentation on current and future Postgres version usage would be very nice. For example, how will I know when (if) you update to v16?
- samwillis 2y ago
  Ah, screen shot is out of date. We are on v16.3.
  - herpdyderp 2y ago
    Thanks for the quick response! I'd love to see a list of PGlite version vs Postgres version somewhere. Regardless though, congrats on launch! For small projects where I won't care much which Postgres version I'm on, I'll totally give this a shot.
ZitchDog 2y ago
> it has a reactive "live query" API Very cool! Most of the examples for reactive queries are very basic (only single tables). Do live queries support joins / aggregations?
- samwillis 2y ago
  Yes, they should all work. We save the query as a view and then introspect it to see what tables it depends on. It then uses pg_notify to watch for changes. The plan is to integrate pg_ivm (https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm) to make it even more efficient.
  - cheema33 2y ago
    The existing list of extensions is already quite impressive. But addition of pg_ivm would really take it to the next level.
  - ZitchDog 2y ago
    Cool!! Is this available outside of pglite or does it require it for some reason? I could see this being very widely useful.
  - netghost 2y ago
    Oh! That's really clever. The live queries make this a very compelling tool for some form of local first development.
sam_perez 2y ago
Oh cool, how would you compare this to DuckDB?
cheema33 2y ago
The filesystems page at https://pglite.dev/docs/filesystems https://pglite.dev/docs/filesystems says the following: > "We would recommend using the IndexedDB VFS in the browser at the current time as the OPFS VFS is not supported by Safari." Is it possible to configure PGLite to use IndexedDB on Safari and OPFS on Chrome & Firefox?
- netghost 2y ago
  Looks like you configure it with the connection string, so assuming you can just detect whether OPFS is available or swap based on the browser you should be fine.
sabr 2y ago
This is amazing! Will there be something like Litestream/rqlite for PGlite?
ChrisArchitect 2y ago
Related: Subabase announcement post Postgres.new: In-browser Postgres with an AI interface https://news.ycombinator.com/item?id=41224286 https://news.ycombinator.com/item?id=41224286
vivzkestrel 2y ago
How does it compare with https://postgres.new/ https://postgres.new/
- uzyn 2y ago
  It runs on PGlite. https://supabase.com/blog/postgres-new#how-it-works https://supabase.com/blog/postgres-new#how-it-works
wejick 2y ago
Curious to know How "vanilla" the pglite compared to the vanilla postgres? I saw many people are using this for automated testing and dev environment.
AIDataWhiz 2y ago
This is exactly the tool I needed and I will try it out in my new project. Thanks for your contribution

509 points by samwillis 2y ago

blueprint 2y ago

Anyone know how this would play with at-rest encryption extensions?

samwillis 2y ago
There are two options here: - Build a Postgres at-rest encryption extensions as WASM, that should "just work" as long as you can build the extension. - Create an encrypted VFS for PGlite. This is doable now, but we haven't yet documents the VFS API as we want to make it a little cleaner. But yes, it's 100% possible to build an at-rest encryption scheme for PGlite.

sgbeal 2y ago

To keep the next person from having to look (or ask without looking): it supports browser-side persistence via IndexedDB and OPFS: <https://pglite.dev/docs/filesystems https://pglite.dev/docs/filesystems>

samwillis 2y ago
Yours and Roys work on WASM SQLite VFSs has directly influenced my work on this. Thank you!
- sgbeal 2y ago
  > Yours and Roys work on ... (blush) But to be 100% clear: the AHP-based VFS in the SQLite project is a direct port of Roy's, so all of the credit for that one truly goes to him!

creativedg 2y ago

Huge fan of PGlite. It's the perfect solution to have Postgres without the need of Docker. With just `npm install`, you can have a Postgres instance on your computer. So, it's extremely easy to onboard a new developer in your team. And, the good news, PGlite works perfectly with Next.js. I'm using PGlite in local and development environment with Next.js Boilerplate: https://github.com/ixartz/Next-js-Boilerplate https://github.com/ixartz/Next-js-Boilerplate With only one command `npm install`, you can have a full-stack application, which also includes the database (a working Postgres). And, no need to have/install external tools.

tracker1 2y ago
Definitely interesting, though I often just use docker compose for dev dependencies, this is definitely a cool alternative.
- creativedg 2y ago
  Yes, it's very cool alternative and it removes the need for docker compose for dev environment. On top of it, PGlite also perfect for CI and testing.
  - ggregoire 2y ago
    Wouldn't be easier to have a shared dev database? So that each developer doesn't have to apply the migrations and import the dumps in their local db, and figure out why the local db for dev A is different than the local db for dev B.
    - andruby 2y ago
      That sounds cumbersome when devs are working in their respective branches and changing the schema (adding migrations)
    - motolov 2y ago
      IME, creating scaffolding to allow for a known, reproducible and clean state (of both DDL and DML) are wild systematic (eg across the team and the estate) productivity and stability boosts. Not having to be "connected to the shared dev DB frees engineers of latency (every ms counts) and also of mutulple pollutants to that database (eg Sally was writing protractor/cypresss tests and now the foo table has 3m rows, george was doing data exploration and manually entered records to the DB that now causes a runtime exception fot the team, etc) If a shared dev DB is really what everyone wants, then at least having the scaffolding mentioned above to fix the DB state when pollution happens (it will) will help heal foot shots. In industrialized practice, what you are mentioning (a shared dev/env) is really the "QA"/pre-prod environment. Ideologically and personally (putting on down vote helmet) if you can't run the whole stack locally you're in for a world of trouble down the road.. local first, test extensively there, then promote changes.

saurik 2y ago

Does there happen to be a native analog to this? So like, if I eventually want some kind of native app, I wouldn't have to throw away the architecture entirely and start over? I only see mention of the WASM version of this in the website/docs.

samwillis 2y ago
It's planned. We want to extract the core changes to Postgres that we had to make and create a "libpglite" C lib that can be linked to from any language/platform. Support important for React Native, as it doesn't have WASM support.
- nikki93 2y ago
  This would be nice also to have an API that's usable from C/C++ code running in Wasm. I see libraries often do this where they expose a C/C++ library like Postgres to Wasm and the main / documented API is JS and you have to dig a bit to find the C/C++ API if it's possible to access it that way.

simonw 2y ago

I'd seen this running in a browser before (the ~3MB download is really impressive for that), but I hadn't clocked that it runs server-side with Node.js and Bun as well: https://pglite.dev/docs/ https://pglite.dev/docs/ Since that's still not spinning up an actual network server, that feels like it's an alternative to SQLite - you can spin up a full in-process PostgreSQL implementation, that persists to disk, as part of an existing Node.js/Bun application. That's really interesting! I'd love to use this from Python, via something like https://github.com/wasmerio/wasmer-python https://github.com/wasmerio/wasmer-python or https://github.com/bytecodealliance/wasmtime-py https://github.com/bytecodealliance/wasmtime-py - has anyone run PGlite via one of those wrappers yet?

samwillis 2y ago
Getting PGlite working with other languages is very high on my list. We are working on two approaches: - a WASI build with a lower level api that users can wrap with a higher level api. - a "libpglite" that can be linked to by any native language. The WASI build is likely a WASM build of this. Most languages already have a Postgres wire protocol implementation and so wrapping a low level API that reads/emits this is relatively easy - it's what the JS side of PGlite does.
- sitkack 2y ago
  I'd love to see this for SQLite and DuckDB at the same time. I could see them using a common wasi filesystem shim so they can transparently do range requests over http blobs. Do you have a branch for this work?
  - samwillis 2y ago
    No branch yet, we're still in the early research and experimental stage.
- rubenfiszel 2y ago
  If you can make it work with Rust, and compatible with wasm targets as well, it opens a huge field of possibilities. Windmill.dev is a workflow engine based fully on postgresql. This would be the missing piece to offer a local development workflow that doesn't require spinning a full pg.

batmansmk 2y ago

Pretty awesome. Would love to use it in CI and locally for our PG product. We use Prisma, so I guess we have to wait for the connector that looks like pg to plug it in.

dboreham 2y ago
Curious, what's the benefit vs running real PG in a container for this specific use case?
creativedg 2y ago
I confirm it works perfectly in CI and locally. I'm using it in CI and locally. Not sure about Prisma but it works with Drizzle ORM.
samwillis 2y ago
pg-gateway from Supabase does exactly this, you can start a PGlite and then connect to it from any standard PG client: https://github.com/supabase-community/pg-gateway https://github.com/supabase-community/pg-gateway

metadat 2y ago

How did they manage to fit Postgres in 3MB?

AlexErrant 2y ago
Answered here https://news.ycombinator.com/item?id=41224295 https://news.ycombinator.com/item?id=41224295 > You might remember an earlier WASM build[1] that was around ~30MB. The Electric team [2] have gone one step further and created a complete build of Postgres that’s under 3MB. > Their implementation is technically interesting. Postgres is normally multi-process - each client connection is handed to a child process by the postmaster process. In WASM there’s limited/no support for process forking and threads. Fortunately, Postgres has a relatively unknown built-in “single user mode” [3] primarily designed for bootstrapping a new database and disaster recovery. Single-user mode only supports a minimal cancel REPL, so PGlite adds wire-protocol support which enables parametrised queries etc.

drzaiusx11 2y ago

Anyone know if something similar is available for MySQL/Maria? This is very cool, especially given the tiny size of it

namanyayg 2y ago

How does the performance compare to actual postgres?

samwillis 2y ago
WASM is inherently slower than native, but it does very well! We have some micro-benchmarks here that have some baseline native numbers to compare to: https://pglite.dev/benchmarks https://pglite.dev/benchmarks Much of the complexity with WASM in the browser actually comes down to the performance of the underlying VFS for storage. When running in-memory or using the OPFS VFSs both PGlite and the WASM SQLite builds are very performant, absolutely capable of handing the embedded use case.
- jfeiwell 2y ago
  Any benchmarks against redis?
  - samwillis 2y ago
    Redis is a very different database, I'm not sure you could do a comparison that was meaningful in any way.

bezbac 2y ago

I recently experimented with using pglite for API integration tests in one of my side projects. It worked pretty well and has much better DX than using something like testcontainers[0] to spin up postgres running in docker. [0]: https://testcontainers.com https://testcontainers.com

riverdroid 2y ago
I would also love to use it this way in Go. Currently we dump SQLite compatible schema to use for SQLite powered integration tests even though we use postgres when live. It allows us to have zero dependency sub-second integration tests using a real database. Being able to use postgres would be even better, allowing us to test functionality requiring PG specific functionality like full text search.
- koeng 2y ago
  I do exactly the same thing. I’m even running SQLite in wasm so I don’t have any C dependencies. Switching out for Postgres would be awesome
- FlyingSnake 2y ago
  I'm bothering the PGLite team a lot to help us enable this :-) We have different options like embedded-postgres or integreSQL, but none match the simplicity of PGLite. I hope this wish comes true soon. https://github.com/fergusstrange/embedded-postgres/tree/master https://github.com/fergusstrange/embedded-postgres/tree/mast... https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql
  - koeng 2y ago
    What is the problem with https://github.com/fergusstrange/embedded-postgres/tree/master?tab=readme-ov-file https://github.com/fergusstrange/embedded-postgres/tree/mast... ?
samwillis 2y ago
I see unit and integration testing as a massive opportunity for PGlite. I know of projects (such as Drizzle ORM) that are already making good use of it. The team at Supabase have built pg-gateway (https://github.com/supabase-community/pg-gateway https://github.com/supabase-community/pg-gateway) that lets you connect to PGlite from any Postgres client. That's absolutely a way you could use it for CI. One thing I would love to explore (or maybe commenting on it here will inspire someone else ;-) ) is a copy on write page level VFS for PGLite. I could imagine starting and loading a PGlite with a dataset and then instantly forking a database for each test. Not complexities with devcontainers or using Postgres template databases. Sub ms forks of a database for each test.

makkesk8 2y ago

Awesome work! A killer feature of sqlite that I would love to see in pglite would be javascript window functions.

isoprophlex 2y ago
Huh, can you do that? I can't google anything just now, got a link to some docs about js window functions in sqlite?! Sounds very powerful
- AlexErrant 2y ago
  Maybe they're talking about https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html where you can define a window fn in the (perhaps JS) app via `sqlite3_create_window_function()`.
  - samwillis 2y ago
    This is high on my list, I have a few ideas how to do it, one being a "PL/JS" extension that calls out to the host JS environment to run JS functions.
    - isoprophlex 2y ago
      I think PG is relatively ideal for that. In a classical data warehousing/ETL context, I've called python directly from inside PG, which has its quirks but is pretty doable, all in all... https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html
    - AlexErrant 2y ago
      FYI Turso does it with WASM https://turso.tech/blog/webassembly-functions-for-your-sqlite-compatible-database-7e1ad95a2aa7 https://turso.tech/blog/webassembly-functions-for-your-sqlit...

teaearlgraycold 2y ago

Heads up, the supabase link on the front page 404s

samwillis 2y ago
Thanks, fixed!

AlexErrant 2y ago

Bug report? On https://pglite.dev/repl/ https://pglite.dev/repl/ running `SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;` works, which is cool. However, running `SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');` fails with `y is not a function`. Then trying to run the first query yields `null`, which is weird. I can open an issue if you want :)

kylemathews 2y ago
Posting the issue to GitHub would be great!
- AlexErrant 2y ago
  ack

bearjaws 2y ago

PGlite / ElectricSQL is definitely something I want to use in a future job. The ability to replicate and subscribe to the changes, all within the browser seems incredibly powerful. Having worked on medical software that runs a 2,000,000+ patient workload that perform refills for chemotherapy/HIV/immuno drugs, where it was common for people to trample each others work as they ran insurance, called patients etc... We had to roll our own locking system that relied on interval functions (yay IE7) and websockets... This meant when you called the patient, someone running financial assistance would be unable to work on the same profile. I can envision other uses for FTS or even vector search locally, since they are insanely expensive at scale.

TeeWEE 2y ago
That means getting those 2 million patients on every device... Really bad choice for medical software.
- thruflo 2y ago
  Electric does partial replication. It’s specifically a system for syncing a different subset of data onto each local device.
- bearjaws 2y ago
  Yeah not sure how you read it that way... Theres obviously segmentation as to what employees can see what patients, baked into the schema level.

isoprophlex 2y ago

That's great! Corpo beancounters want us to move off docker on EC2 onto bare metal, making both dev and deploy experience terrible. This will help ease pain!

samstave 2y ago
Weird question: Since you mention 'bare-metal' and this is "enables you to run Postgres in the browser" UH, could you do: bare_metal --> HeadlessGPU-Chrome --> PGLite with whatever orchesrtration and you have a an automatable 'browser' that can scrape/store/logic/log/be-config'd from/lookup directly to your browserDB and maybe run a fastAPI ontop of such that you have a full stack, baremetal, headless, browserDB/fastAPI/endpoint processing doohicky? Or does this sound dumb? Because this is exactly what I actually need and am attempting to create - but its beyond my capabilities in any functional/elegant implementation... https://www.browserless.io/blog/browserless-gpu-instances https://www.browserless.io/blog/browserless-gpu-instances https://github.com/dhamaniasad/HeadlessBrowsers https://github.com/dhamaniasad/HeadlessBrowsers
- samwillis 2y ago
  PGlite also runs in Node and Bun, so you can easily run it on backend servers. (couldn't fit that in the HN headline)
  - samstave 2y ago
    Sorry if this is in the RTFM; So, you can basically use this to deploy a portable fully enclosed little webapp. Basically any little web-app that has a bunch of backend DB: A sales USB drive that loads a page that then has the pglite of all your product stuff on there. and you can just provide custom buttons to slurp data either way. Utils to yank a [data] from a postgress server, onto your little sneaker-DB-USB -- then carry it over and to a DB function... A little util USB that you already have it setup such that you can curl a bunch of data directly into tables on your little headless-browser-pglite minion? Full Kisok DB USB (SSD) upgrades. Basically - running a pico headless postgress DB with a webUI? --- aside: samwillis March 2008 samstave March 2009 :-) --- WOW: https://i.imgur.com/6YLwEuj.png https://i.imgur.com/6YLwEuj.png -- Yeah this is pretty dopes.

srameshc 2y ago

If I understand correctly, this is like SQLite, but Postgres. I love SQLite, but sometimes I need a little more. So, no more saving Date as text and we have arrays, jsonb etc and all the good stuff from Postgres. Am I right ?

samwillis 2y ago
Exactly, all your favourite PG types, plus any that come with extensions such as vectors with pgvector. We are working on PostGIS to, which will bring the geo types to PGlite.
aitchnyu 2y ago
IIRC Sqlite can exist as flat file and can be backed up. Will this work the same? And will it allow multiple writers?
- srameshc 2y ago
  Every use case and expectations are different IMO. And yes if it's a file system , you can always grab a way to keep a snapshot. Too early for this project to deliver everything at one go.

cpursley 2y ago

Woah, this is neat - insane that it can handle extensions. Just added a new section to my "Postgres Is Enough" gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#in-browser--wasm https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

FlyingSnake 2y ago

PGLite is a wonderful achievement and I've a fan of it since it's early days. If they get it to a stage where other languages apart from JS can use it, it could be revolutionary. The possibilities are limitless.

revskill 2y ago

The migration docs to integrate with Vite/webpack is still lacking though.

loevborg 2y ago

PGLite is amazing for integration testing of queries against a real in-memory db. Is it possible to use PGLite for that purpose in non-JS environments, like e.g. golang?

samwillis 2y ago
You can use pg-gateway from supabase (https://github.com/supabase-community/pg-gateway https://github.com/supabase-community/pg-gateway) to start a PGlite and connect form any standard PG client. Support for other languages is high on my list, the plan is to build a "libpglite" as a native C lib and a WASI build that can run in any WASM runtime.
- loevborg 2y ago
  Thanks! From my perspective, being able to pull pglite into the same golang process is preferable - faster, less IPC complexity, no risk of leaving lingering state. Making postgres an in-process dependency is much of the appeal of pglite for me.

Kinrany 2y ago

Can it run in Node while still persisting data to disk?

samwillis 2y ago
Yes, we can persistent to the normal filesystem with Node and Bun, and to IndexedDB and OPFS in the browser: https://pglite.dev/docs/filesystems https://pglite.dev/docs/filesystems

NetOpWibby 2y ago

This is exciting. My database of choice is EdgeDB, which uses Postgres under the hood. It's not so far-fetched to imagine EdgeDB in the browser now!

Retr0id 2y ago

Normally I'd say the main difference between postgres and SQLite is that the latter is in-process. Now that they can both be in-process, is there a more detailed comparison of the two? When might I prefer one over the other?

deleted 2y ago
[deleted]
nbbaier 2y ago
I'd also love to know this!
AlexErrant 2y ago
Well, Sqlite is battle-tested to hell and back... and with PGlite I managed to get a `memory access out of bounds` in less than 3min of playing around. So perhaps maybe not prod-ready yet, even if it's bigger brother is one of the most solid DBs. (To be fair I've also seen & experienced issues with SQLite - no software is bug-free.)
- grsmvg 2y ago
  What I always wonder is which people use SQLite to run tests for a Postgres application. Isn’t the difference in dialect pretty much always an issue unless you do only the most basic type of queries? pglite fills a hole where imho currently only dockerized Postgres sits
  - Kinrany 2y ago
    You can write all queries twice and run the same test suite on both, then use sqlite in other tests. Of course at that point you can just use a struct that stores all the same data.

non- 2y ago

Would really like to see a benchmarking analysis vs SQLite. How do they compare on memory usage and overhead? I already default to SQLite for new side projects with the idea that "eventually I'll migrate to Postgres if this project gets legs", so switching to PGlite feels like a no-brainer as long as it's not going to weigh my apps down or force me to upgrade out of whatever entry-level server solution I'm using is for a given project. EDIT: Found the benchmarks here https://pglite.dev/benchmarks https://pglite.dev/benchmarks

samwillis 2y ago
Ah, you beat me to it, yes those benchmarks give you a good overview of baseline performance. But it'a a little nuanced as the underlying VFS performance is very significant. Both PGlite and SQLite in the browser is incredible. The latter is a little lighter weight and more mature, but PGlite brings with it all the type support and extensions that you love with Postgres. It comes down to what works best for your project.

throwaway290232 2y ago

[dead]

satvikpendem 2y ago

Congrats on the Show HN. I follow the ElectricSQL Discord server and I was distinctly interested in this but for other languages than TypeScript, so it's nice to see that making it language independent is high on your list. I also saw that ElectricSQL is being rewritten due to architectural changes, does that impact PGlite at all or are they separate projects now? What is the relationship between PGlite and ElectricSQL as well, just curious? Also, fun etymological thing, SQLite is actually SQL-ite, as in, urbanite, not SQ-Lite, but due to rebracketing [0] and libfixing [1], now people seem to use the -lite suffix rather than the -ite one, presumably because lite actually implies something whereas ite would not, as much. It's like how helicopter is actually helic and opter, a helical wing that spins, but now people think of it as heli and copter, calling other things related to it like helipad, or quadcopter, as Wikipedia states. [0] https://en.wikipedia.org/wiki/Rebracketing https://en.wikipedia.org/wiki/Rebracketing [1] https://en.wikipedia.org/wiki/Libfix https://en.wikipedia.org/wiki/Libfix

samwillis 2y ago
Hey, yes, PGlite and Electric sync are separate projects, but we are very much building PGlite as a sync target for Electric. The changes we are making with Electric is towards a more loosely coupled stack, rather than the tightly integrated stack we had before. PGlite is one possible client store for Electric sync, and fulfils the use case where you want to have a full SQL database on the client along with (potentially) the same schema on client and server. The name is (obviously) a nod to SQLite, which goes without saying is an incredible database. We went with the small "l" in the name to add a slight differentiator and a nod towards "light", and we are a light weight Postgres.
- zooq_ai 2y ago
  what kind of data load are we talking here? I know it gets stored locally. So, is it limited by my local disk size. How will it perform if I have 1TB of data?

Cu3PO42 2y ago

When I saw the headline, I immediately thought "I bet this would go really well with ElectricSQL", so it's great to see this coming from you! The immediate DX is incredible and I'm itching to use PGLite (and ElectricSQL) in a production project, I expect it would remove quite a few pain points I'm currently experiencing. (Also, I just like working with CRDTs.) I don't have any constructive criticism to offer right this moment, but I just wanted to congratulate you on the launch and an incredible looking product!

ggregoire 2y ago

I was gonna ask what do people use to sync their local pg with their remote pg, but turns out, that's exactly what the authors are building. Cool stuff, and gratz on pglite!

herpdyderp 2y ago

What's the equivalent Postgres version? Meaning, if I wanted to make sure my app that is currently using a Postgres Docker image is compatible with PGlite before switching, which version of Postgres should my Docker image be on? I see "PostgreSQL 15devel" in a screenshot so I'm assuming that means v15 but explicit documentation on current and future Postgres version usage would be very nice. For example, how will I know when (if) you update to v16?

samwillis 2y ago
Ah, screen shot is out of date. We are on v16.3.
- herpdyderp 2y ago
  Thanks for the quick response! I'd love to see a list of PGlite version vs Postgres version somewhere. Regardless though, congrats on launch! For small projects where I won't care much which Postgres version I'm on, I'll totally give this a shot.

ZitchDog 2y ago

> it has a reactive "live query" API Very cool! Most of the examples for reactive queries are very basic (only single tables). Do live queries support joins / aggregations?

samwillis 2y ago
Yes, they should all work. We save the query as a view and then introspect it to see what tables it depends on. It then uses pg_notify to watch for changes. The plan is to integrate pg_ivm (https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm) to make it even more efficient.
- cheema33 2y ago
  The existing list of extensions is already quite impressive. But addition of pg_ivm would really take it to the next level.
- ZitchDog 2y ago
  Cool!! Is this available outside of pglite or does it require it for some reason? I could see this being very widely useful.
- netghost 2y ago
  Oh! That's really clever. The live queries make this a very compelling tool for some form of local first development.

sam_perez 2y ago

Oh cool, how would you compare this to DuckDB?

cheema33 2y ago

The filesystems page at https://pglite.dev/docs/filesystems https://pglite.dev/docs/filesystems says the following: > "We would recommend using the IndexedDB VFS in the browser at the current time as the OPFS VFS is not supported by Safari." Is it possible to configure PGLite to use IndexedDB on Safari and OPFS on Chrome & Firefox?

netghost 2y ago
Looks like you configure it with the connection string, so assuming you can just detect whether OPFS is available or swap based on the browser you should be fine.

sabr 2y ago

This is amazing! Will there be something like Litestream/rqlite for PGlite?

ChrisArchitect 2y ago

Related: Subabase announcement post Postgres.new: In-browser Postgres with an AI interface https://news.ycombinator.com/item?id=41224286 https://news.ycombinator.com/item?id=41224286

vivzkestrel 2y ago

How does it compare with https://postgres.new/ https://postgres.new/

uzyn 2y ago
It runs on PGlite. https://supabase.com/blog/postgres-new#how-it-works https://supabase.com/blog/postgres-new#how-it-works

wejick 2y ago

Curious to know How "vanilla" the pglite compared to the vanilla postgres? I saw many people are using this for automated testing and dev environment.

AIDataWhiz 2y ago

This is exactly the tool I needed and I will try it out in my new project. Thanks for your contribution