PGlite – Embeddable Postgres

Posted by dsego 7 days ago

Counter603Comment114OpenOriginal

Comments

Comment by samwillis 7 days ago

Hey everyone, I work on PGlite. Excited to see this on HN again.

If you have any questions I'll be sure to answer them.

We recently crossed a massive usage milestone with over 3M weekly downloads (we're nearly at 4M!) - see https://www.npmjs.com/package/@electric-sql/pglite

While we originally built this for embedding into web apps, we have seen enormous growth in devtools and developer environments - both Google Firebase and Prisma have embedded PGlite into their CLIs to emulate their server products.

Comment by mpweiher 7 days ago

This looks really interesting...but why WASM-only? Naively it seems like WASM-ification would be a 2nd step, after lib-ification.

Obviously missing something...

Comment by OvbiousError 7 days ago

If I understand correctly, what this project does is take the actual postgresql sources, which are written in C, compile them to wasm and provide typescript wrappers. So you need the wasm to be able to use the C code from js/ts.

Comment by mpweiher 7 days ago

Yes. I would like to use the code as a library from something other than js/ts.

Comment by mirrir 7 days ago

You can use it in Rust if you like. I've used pglite through wasmer before. Also [pglite-oxide](https://lib.rs/crates/pglite-oxide) is pretty usable.

Comment by embedding-shape 7 days ago

Sounds you only need to create the APIs for calling into WASM if so, so as long as your language of choice can do that, you're good to go.

Comment by dboreham 7 days ago

That adds extra unnecessary complexity. The code is written in C. There are C compilers for all CPUs. So just call the C code from <other language that's not JS>.

Comment by hombre_fatal 7 days ago

Well, a project has scope.

Looking at the repo, it started as postgres-in-the-browser. An abstract interface with C and wasm as targets is just more scope.

But it looks like the hard part of patching postgres to librar-ify it is already done agnostically in C.

So you just need to ctrl-f for "#if defined(__EMSCRIPTEN__)" to impl those else branches and port the emmake file to make.

Comment by monster_truck 7 days ago

So compile it and use it?

Comment by intrasight 7 days ago

WASM means you only need to develop for one target run time. That's my guess as to why.

Comment by saurik 7 days ago

Yeah... I was super excited by this project when it was first announced--and would even use it from Wasm--but since it ONLY works in Wasm, that seemed way too niche.

Comment by SteveLauC 7 days ago

Hi there, would you like to share the progress of converting PGlite into a native system library? I can see there is a repo for that, but it hasn't been updated for 5 months

Comment by tdrz 6 days ago

We are actively looking into it. But as you can see from the comments here, there are quite a lot of other features that users want and we have limited bandwidth. We will do it!

Comment by DonnyV 7 days ago

I see you guys are working on supporting the postgis extension. This would be HUGE!!! The gis community would be all over this.

If anyone wants to help out who has compiled the postgis extension and is familiar with WASM. You can help out here. https://github.com/electric-sql/pglite/pull/807

Comment by nnnnico 7 days ago

This is awesome, thanks for your work! Could this work with the file system api in the bowser to write to user disk instead of indexeddb? I'm interested in easy ways for syncing fot local-first single user stuff <3 thanks again

Comment by tdrz 6 days ago

That's a very nice idea, we will look into it!

Comment by JackC 7 days ago

Thanks for your work!

Is the project interested in supporting http-vfs readonly usecases? I'm thinking of tools like DuckDB or sql.js-httpvfs that support reading blocks from a remote url via range requests.

Curious because we build stuff like this https://news.ycombinator.com/item?id=45774571 at my lab, and the current ecosystem for http-vfs is very slim — a lot of proofs of concept, not many widely used and optimized libraries.

I have no idea if this makes sense for postgres — are the disk access patterns better or worse for http-vfs in postgres than they are in sqlite?

Comment by phplovesong 7 days ago

This looks REALLY awesome. Could you name a few usecases when i would want to use this. Is the goal to be an sqlite/duckdb alternative?

Comment by sgt 7 days ago

Any chance for a Flutter library?

Comment by oulipo2 7 days ago

I'm interested to use Pglite for local unit-testing, but I'm using timescaledb in prod, do you think you will have this extension pre-built for Pglite?

Comment by tdrz 7 days ago

We have a walk-through on porting extensions to PGlite: https://pglite.dev/extensions/development#building-postgres-...

Comment by samwillis 7 days ago

I'm not aware of anything trying to compile timescale for it. Some extensions are easer than other, if there is limited (or ideally no) network IO and its written in C (Timescale is!) with minimal dependencies then its a little easer to get them working.

Comment by rel 7 days ago

I’ve had incredible success with testcontainers for local unit-testing

Comment by glenjamin 7 days ago

Does pglite in memory outperform “normal” postgres?

If so then supporting the network protocol so it could be run in CI for non-JS languages could be really cool

Comment by jitl 7 days ago

Look into libeatmydata LD_PRELOAD. it disables fsync and other durability syscalls, fabulous for ci. Materialize.com uses it for their ci that’s where i learned about it.

Comment by 7 days ago

Comment by allan_s 7 days ago

for CI you can already use postgresql with "eat-my-data" library ? I don't know if there's more official image , but in my company we're using https://github.com/allan-simon/postgres-eatmydata

Comment by anarazel 7 days ago

You can just set fsync=off if you don't want to flush to disk and are ok with corruption in case of a OS/hw level crash.

Comment by ffsm8 6 days ago

Huh, i always just mounted the data directory as tmpfs/ramdisk. Worked nicely too

Comment by mentalgear 7 days ago

Yupp, this has big potential for local-first !

Comment by dcgudeman 6 days ago

Small world! We spoke about this at the QCon dinner.

Comment by TheDataMaverick 7 days ago

Amazing work! It makes setting up CI so much easier.

Comment by lame_lexem 7 days ago

huh. could you tell how you use it in ci?

Comment by tln 7 days ago

I'm using it for a service that has DB dependencies. Instead of using SQLite in tests and PG in production, or spinning up a Postgres container, you use Postgres via pglite.

In my case, the focus is on DX ie faster tests. I load shared database from `pglite-schema.tgz` (~1040ms) instead of running migrations from a fresh DB and then use transaction rollback isolation (~10ms per test).

This is a lot faster and more convenient than spinning up a container. Test runs are 5x faster.

I'm hoping to get this working on a python service soon as well (with py-pglite).

Comment by TheTaytay 7 days ago

Thank you for the details. This makes a lot of sense!

Comment by reachableceo 7 days ago

Well downloads doesn’t equal usage does it ?

How do you know how many deployments you actually have in the wild?

Comment by pixelatedindex 7 days ago

True downloads don’t equal usage but there’s a correlation. I also doubt deployment equals usage - I can deploy to some env and not make any requests.

Additionally, how you can get data on how many deployments without telemetry? The only telemetry that I’m interested in is for my uses, and don’t really care about sending data on deployment count to a third party. So the download count becomes a “good enough” metric.

Comment by bbkane 7 days ago

I'd love to to use PGLite in a non-JavaScript runtime. For example, embed PGLite into my Go CLI with a WASM runtime and use PGLite as a replacement for SQLite.

https://github.com/electric-sql/pglite/issues/89 makes it sound like there's "third-party" bindings for Rust. Is there any interest in "official" PGLite bindings to other languages?

Comment by papa0101 7 days ago

yep PGLite + Go would be great!

Comment by spicypixel 7 days ago

Yeah would really make testing a tonne easier.

Comment by buremba 7 days ago

PGlite is fantastic. I use it for my in-browser PostgreSQL server for development. It implements the PG protocol on the server; when clients connect, we forward queries to the user's browser, which runs PGlite under the hood.

The result is a PG server that fully lives in your browser: https://dbfor.dev

Comment by nextaccountic 5 days ago

how is this different from pglite itself? pglite also runs in the browser

Comment by buremba 4 days ago

PGlite is embedded so you can't really connect to PGlite using the PG clients. Dbfor.dev runs the PG protocol in the server and use Websockets & PGlite so any PG client can connect to your browser using PG protocol.

The hard work is done by PGlite and we use PGlite, it just enables PGlite to be accessible from everywhere.

Comment by _fzslm 7 days ago

I'm so optimistic about this, especially in the context of local-first web applications. With Postgres on both the client and the server, and something like PowerSync or ElectricSQL to keep the two together, you get a homomorphic database environment between client and the server. That has a lot of architectural benefits I'm actively exploring. The client and the server can share a lot more code, for one.

But I read the following posts, and I have some serious concerns about PGlite's performance:

https://antoine.fi/sqlite-sync-engine-with-reactivity – describes memory leaks, minute-long db startup time, and huge slowdowns with live queries

https://github.com/marcus-pousette/sqlite3-bench - shows performance dropping to multi-second territory for inserts and lookups, compared to sqlite which is significantly faster

It sadly makes me slightly skeptical about adopting what effectively feels like a hack... SQLite has obviously had decades of adoption and I'm not expecting PGlite to match that level of legacy or optimisation - but it's enough to give me pause.

I really, really want to adopt PGlite in a project I'm currently architecting, so would love some insight on this if anybody has any!

Comment by oamaok 7 days ago

At work we started building a new internal service and decided to try this out for the test setup. We built a small wrapper which seamlessly uses PGLite when running tests and actual Postgres instance otherwise. Great success!

The ability to .clone() the database to create "checkpoints" is also great for tests, as we can run all of the migrations and return to that clean state between each test. Running 50 test suites in parallel is also so easy with this setup.

Comment by dvdkon 7 days ago

This is very cool. Having to always set up a server is one major downside of Postgres, with cumbersome updates being the second. This solves the first and has potential to help with the second.

Is there a way to compile this as a native library? I imagine some of the work should be reusable.

Comment by evelant 7 days ago

Yes! I (experimentally) compiled and packaged it for react-native. Postgres on iOS and Android https://github.com/electric-sql/pglite/pull/774

Comment by samwillis 7 days ago

This is such awesome work! We *are* going to get this integrated with the ongoing work for "libpglite".

Comment by patwolf 7 days ago

Glad to see it working in react native. It always surprises me that RN doesn't natively support wasm. I've had to avoid other wasm-based libraries, like loro, for that reason.

Comment by evelant 7 days ago

Yeah, it's unfortunate but it's not really react-native/facebook's fault. Apple doesn't allow any sort of JIT to run on iOS outside of their builtin webkit js engine. That means that AFAIK there's no way to run wasm at reasonable speed on iOS, which means react-native can't really support wasm.

Comment by worthless-trash 7 days ago

Took the words out of my mouth, i can think of many use cases for this.

Imagine being able to go from 'embedded' to 'networked' without having to change any SQL or behavior, so cool.

Comment by tdrz 7 days ago

Native library is on our radar!

Comment by odie5533 7 days ago

For unit testing, I still use TestContainers which spins a full Postgres in Docker. But new alternatives like this make py-pglite (https://github.com/wey-gu/py-pglite) possible which is Python unit testing with PGlite. Even so, for Python unit testing I'm more confident in something like pgserver (https://github.com/orm011/pgserver) which offers the full, real Postgres in a lightweight pip package. Note: my take is specifically for unit testing, not other use cases!

Comment by theptip 7 days ago

What are the trade-offs you’ve seen between the two? Always appreciate this sort of experience report on HN!

Comment by widenrun 7 days ago

Using this for testing... it feels like a sweet spot between in-memory SQLite and spinning up a full Postgres instance. I'd been looking for this for a while and I'm pretty happy with the faster tests. And so far no blockers from its limitations.

Comment by nunobrito 7 days ago

Hello, can you please summarize the advantages of PGLite compared to SQLite?

I've never used Postgres before, my work is mostly on the embedded domain using files and a lot of browser execution on the client side.

With SQLite there is simplicity attached to the databases, with PGLite I see a lot of interesting extensions to try out but what would the big difference when compared to SQLite?

Comment by richbell 7 days ago

The main use case, in my opinion, is for tests/CI. SQLite has traditionally been used to quickly run tests, however, if your actual infra uses PostgreSQL then the value is limited.

Comment by CyberDildonics 7 days ago

You think the main use for sqlite is running tests?

Comment by Fuzzwah 7 days ago

My read is that the person you're responding to thinks that pglite could be a better fit than sqlite for ci/cd, where currently sqlite is used.

Not that testing is the main use of sqlite.

Comment by trillic 7 days ago

I think they meant sqlite is often used in CI/CD testing environments as an alternative to running a client/server database in these environments. For simple crud webapps, or frameworks that are db agnostic it works well.

Comment by lateforwork 7 days ago

There is also Doltgres [1] which is a single-file Postgres. Just like Deno you download and run a single .exe file and voila! you have Postgres!

[1] https://docs.doltgres.com/introduction/installation

Comment by adhamsalama 7 days ago

I tried to use this when I was building a project about peer-to-peer database sharing in the browser using WebAssembly and WebRTC, but I found it a bit heavy so I used SQLite instead.

Here's the project if anyone is interested: https://github.com/adhamsalama/sqlite-wasm-webrtc

Comment by avinassh 7 days ago

previous Show HN post submitted by the author (109 comments) - https://news.ycombinator.com/item?id=41224689

Comment by bhouston 7 days ago

Very neat.

Impressive performance: https://pglite.dev/benchmarks

Even has Drizzle ORM integration: https://orm.drizzle.team/docs/connect-pglite

I will explore this for use in my unit / integration tests. It looks pretty amazing.

I am confused why all my recent compiled tooling (tsgo, biomejs) are shipping native binaries (thus creating multiple binaries, one per supported platform) and not WASM tools that can run cross platform? Is it because of startup times, poor tooling, etc?

Comment by jitl 7 days ago

people want their programs to go as fast as possible, WASM can be better than writing JS but it’s not as fast as actually native code by a wide margin, especially if you want to do io

Comment by TonyAlicea10 7 days ago

One thing this (and any browser-embeddable data store) are fantastic for is vibe coding interactive prototypes for user research.

AI-generated code that doesn’t need to be production ready has been a real boon to usability and design work. Testing with users something that actually saves and displays data, and seeding the app with realistic-looking datasets in both shape and size, reveals usability issues that you just don’t discover in Figma prototypes.

If a product team isn’t performing user research with interactive prototypes as a core part of their dev and design lifecycle, they’re doing themselves a real disservice. It’s so easy now.

Comment by stacktrace 7 days ago

Really cool project! We had a situation a while back where some of our e2e tests needed the DB to be in very specific states. Technically we could have handled it with fixtures/transactions/schema resets, but doing that cleanly across a bunch of tests was pretty painful in our setup at the time

For a few edge-case scenarios we ended up mocking the DB layer (which obviously stops being a true e2e test). Something like PgLite would've been a perfect middle ground - real Postgres, zero container overhead, easy to spin up isolated instances per test, and a clean slate for every run.

Comment by kburman 7 days ago

This looks impressive. Could someone familiar with Postgres internals explain the hidden trade-offs of this approach?

I understand the obvious limitations of it being embedded/single-host, but I'm curious about the engine itself. Does running in this environment compromise standard features like ACID compliance, parallel query execution, or the ecosystem of tools/extensions we usually rely on?

Comment by samwillis 7 days ago

The key limitation (at the moment) is that it only supports a single connection. W're planning to lift that limitation though.

Comment by sigseg1v 7 days ago

This is what I'm most interested in. I have an application which has a smaller trimmed down client version but it shares a lot of code with the larger full version of itself. Part of that code is query logic and it's very dependent on multiple connections and even the simplest transactions on it will deadlock without multiple connections. Right now if one wants to use the Postgres option, it needs Postgres manually installed and connected to it which is a mess. It would be the dream to have a way to easily ship Postgres in a small to medium sized app in a enterprise-Windows-sysadmin-friendly way and be able to use the same Postgres queries.

Comment by korale 6 days ago

Was going to ask exactly about that. Thanks for sharing. Looking forward to it!

Comment by tdrz 7 days ago

You might want to have a look at our extensions catalog page: https://pglite.dev/extensions/

Comment by jherdman 7 days ago

I'm using this with a Bun project for my testing needs. I spin PGLite at the beginning, throw it all away at the end. It's not as nice as transactionally isolated testing (a la Ruby on Rails, or Elixir), but it's a fine replacement until I have time to replicate it.

Comment by replwoacause 7 days ago

Wish there was a way to use this with .NET

Comment by shrubble 7 days ago

No one using Linux uses .NET is probably part of it; it’s not a criticism of the language itself.

Comment by DANmode 7 days ago

> No one using Linux uses .NET

Perhaps once generally true, not as true since .NET Core.

Comment by Robdel12 7 days ago

The last time I ended up trying something like this I was implementing postgres features that the mocks didn’t.

Now, I just tested against a real database in a docker container. I have over 1k tests that run about 1.5 mins. I’m pretty happy with that.

I guess given that, testing isn’t quite the use case for this (for me). Wonder what else this could be used for.

Comment by manzout 7 days ago

Oh, I thought i hallucinated this last night

Comment by huzaifah0x00 7 days ago

This is interesting, I've been looking for something like this that I can use in unit/integration tests. I've used the mongodb memory server for testing but never found something like that for Postgres that didn't require running a full PG server instance...

Definitely going to try this out for tests and see how it goes.

Comment by RichardChu 7 days ago

I was evaluating local storage solutions a while back and I tried setting up PGlite, but unfortunately I couldn't get it to work in Next.js with Turbopack in a web worker.

I've been using SQLite locally instead with wa-sqlite and it's been working great for my use case so far. It's also more lightweight.

Comment by eduction 7 days ago

How are you measuring "lightweight" here, what do you mean by it?

(Not doubting your claim this just seems one of those words that means many different things depending on the context.)

Comment by RichardChu 7 days ago

Primarily bundle size. Pglite is a 3 MB binary, SQLite is <1 MB.

Comment by exceptione 7 days ago

Are people using this in SPA applications with success? I saw on the website that syncing via ElectricSQL is in alpha and that no CRDT is available, afaik. Any other options? Also, I guess pgsql extensions are out of scope?

Nonetheless, this could be interesting for data heavy SPA's.

Comment by samwillis 7 days ago

There are a few people using it in prod for customer facing web apps.

Extensions are also available - we have a list here: https://pglite.dev/extensions/. We would love to extend the availability of more, some are more complex than others though. We are getting close to getting PostGIS to work, there is an open PR that anyone is welcome to pick up and hack on.

Comment by compoundedges 7 days ago

We have been using it for the last 8 months in production at CompoundingEdges. It's been great for allowing users to generate large amounts (100-300k records/day) of their own isolated data and automatically persist it to their local machine. The only hiccup we have had had been throttling data to be saved via the webworker.

We are not syncing with Electric, but I've heard good things about it.

Comment by mythz 7 days ago

It's cool that this is possible, is this just for fun or are there good use-cases for this?

Comment by samwillis 7 days ago

It's now used by a huge number of developers for running local dev environments, and emulating server products (Google firebase and Prisma both embed it in their CLI). Unit testing postgres backed apps is also made significantly easer with it.

Comment by t_mahmood 7 days ago

One use case, when doing unit tests, Docker containers, would make it too expensive with many tests. SQLite's type checking is far less strict than Postgres, which would not catch errors that would occur the real database due to type mismatch.

Having something like this, that I can quickly spawn and know, I am getting exact behavior as prod database would be a lifesaver!

Comment by npodbielski 7 days ago

Hmm single user website run as HTML from some folder? I guess you could embed this from s3 for multiple users but probably this would be like running multiple engines from the same dir.

Comment by ekjhgkejhgk 7 days ago

> Hmm single user website run as HTML from some folder?

Why not just sqlite then?

Comment by somat 7 days ago

Postgres features are much nicer, honestly if you are using any sort of orm none of this matters. by design they isolate you from many of the more interesting features of the database. And in general this is probably a good thing. But if you enjoy hand writing artisanal sql postgres is far more pleasant to use than sqlite, not that sqlite is bad, it is very good, just... thin after using pg.

Comment by vincnetas 7 days ago

More SQL functionality?

Comment by rozenmd 7 days ago

I use it for realistic(ish) testing of my hono API, big fan

Comment by aperture147 7 days ago

Techinically I can build a Postgres DB on Durable Object on Cloudflare right? I'm kinda tired of SQLite migration cascading all of my tables now. Has anyone tried to implement that on DO?

Comment by mrinterweb 7 days ago

When I heard embedded postgres and sync, I immediately thought of pg's logical replication. ElectricSQL looks cool, but any chance of pg's native logical replication working with this?

Comment by tdrz 6 days ago

Yes, we believe PostgreSQL's native logical replication is possible with PGlite. We have some ideas on how to achieve it, but we need more time to try them out.

Comment by throw_m239339 7 days ago

Is there a PGlite but like SQlite (so without a running a server), just with the PG flavor of SQL instead of Sqlite's?

Comment by alexisread 7 days ago

Can this be used as a read-replica to a normal PG instance? I'm thinking synced browser cache here.

Comment by samwillis 7 days ago

You can use http://electric-sql.com to sync into PGlite in the browser from postgres. There are docs here: https://pglite.dev/docs/sync

Comment by guardian5x 7 days ago

What is the advantage of using something like this instead of the IndexedDB Browser Feature

Comment by lgas 7 days ago

You get all the features of postgres.

Comment by STRiDEX 7 days ago

run your backend tests against this in memory and tests can be run in parallel instead of using a single real postgres instance

Comment by Drakim 7 days ago

I was shocked to discover how incredibly poorly IndexedDB works. I always thought it would be fast and snappy if a bit alien. But nope, it's incredibly bad!

Despite being a native feature to the browser it's incredibly slow, and the way it works in terms of fetching records based on non-primary keys forces you to either load your entire dataset into RAM at once or iterate though it record-by-record in a slow callback. Something as trivial as 10k records can bring your webapp to a crawl.

Comment by orthecreedence 7 days ago

I've built some pretty intensive stuff in indexeddb and it was the only thing I've ever done, using native browser features, that I could get to consistently crash the browsers I tested it on (granted, this was many years ago). On top of that, the API is so ugly. I cannot believe indexeddb won over websql (when every browser ever already embeds sqlite). What a shame.

Comment by lionelholt 2 days ago

I wonder if those issues are resolved by using the Dexie.js wrapper, because I've had no problems with that.

Comment by cosmotic 7 days ago

Embeddable (into JS et al)

Comment by samwillis 7 days ago

We have a long on running research project with the intention of carting a "libpglite" with a C FFI and compiled as a dynamic library for native embedding. We're making steady progress towards it.

Comment by urtie 7 days ago

There are projects such as https://github.com/wasmerio/wasmer-java and https://wasmtime.dev/ that extend this embeddability to Java, .net, C, C++, rust, Python, Ruby and Go. Wouldn't want to call those 'JS et al'.

Ofcourse, that ignores the fact that for many of these languages there are existing libraries and drivers to connect to databases that would not work with this embedded one, but still.

Comment by 7 days ago

Comment by ianberdin 7 days ago

An amazing project.

I have built a playground for it today: https://playcode.io/sql-editor

(Full feature set, including extensions, pgdump, database explorer, indexedDB, vscode editor, etc). Free. No ads. No bs.

Comment by iamcreasy 7 days ago

Is there similar attempt for MySQL?

Comment by tmikaeld 7 days ago

Is it just me or is downloading 3MB for the DB runtime plus the database itself, kind of crazy?

At this point, this should be built into the browser which could fetch signed db data and be extremely performant.

Comment by qazswx 7 days ago

ccc

Comment by u834957920 7 days ago

Everyone is trying to copy DuckDB at this point

Comment by SquidJack 7 days ago

Duck db copied from the sqlite

Comment by spcldvlpr 7 days ago

Aaand sqlite uses postgres as reference ”what would psqgl do?” I think it is better hate them all.