PGLite Evangelism | Hacker News Reader

PGLite Evangelism

substack.com

78 pointssurprisetalk27 comments

I used PGLite for a project (monkeys.zip) and it was absolutely fantastic! Loved how speedily I could have a "real" postgres instance without spinning up docker. Worked really really well with pnpm workspaces and a monorepo setup (EG pnpm run will run the DB package, as well as frontend and backend).

The only downside (and this applies for SQLite as well) is that it runs too well that you can get some bad habits - or at least follow patterns that don't support horizontal scaling which you would want to do in production. A number of problems across different projects have bit me because I relied too long on SQLite (or PGLite) when moving from local dev to setting up cloud infra. This includes things like connection pooling, read replicas, consistency issues with sharding. Maybe all those people who productionize *Lite have a point!

Unfortunate name furthers the SQLite is "lite" association i.e. underpowered or incapable somehow.

IIRC it is intended to be read as SQL-ite, as having some relationship to SQL.

PG Lite probably makes sense here though, and PGite is not appealing

I've always thought "lite" or light to mean non resource intensive to run, not necessarily less feature-rich.

> I wrote SQLite, and I think it should be pronounced "S-Q-L-ite". Like a mineral. But I'm cool with y'all pronouncing it any way you want. :-)

https://stackoverflow.com/a/49656730

So it’s actually not -lite but -ite. =)

Also checkout https://www.doltgres.com/

This is why I built https://db4.app. This solves the biggest hurdle with using a PGLite instance from your browser, by giving you a universal postgres url. You can connect to it from anywhere with your credentials. Your data and queries are fully encrypted end-to-end. So it is only between your app, and your browser tab. Your data stays with you and db4.app provides the relay basically.

You can also host the Relay app yourself locally, if you'd like.

It comes with support for PG-vector, so you can use it for RAG in LLM Studio. We have a few MCP applications already built in the community apps section.

Happy to answer any questions.

I wonder, what amount of PG deployments can be replaced with SQLite. I bet most of them.

If AWS starts supporting SQLite I'd consider it. I need managed services, PITR backups, read replicas, and failover. My needs are simple like a web app talks to database in us-east-1. It doesn't seem like SQLite is built for that.

I used sqlite when app is writing only a few records per second

If it has to write lots of stuff concurrently I use postgres.

And if I have even higher concurrency needs I write to redis and write an aggregator loop which writes to postgres every few seconds.

Is there a feasible way to run this in a different language? (Go, in my case?)

It's in wasm so should be possible to code up the next language

https://github.com/elliots/go-pglite

I like the idea of PGLite!

Unfortunately, when I tried to use it with https://github.com/wey-gu/py-pglite to speed up tests, I got stuck on it not being able to handle cell contents above a few thousand characters, which was a deal breaker for my project :(

Try pgserver https://github.com/orm011/pgserver It uses real Postgres rather than WASM

Pgserver is not maintained. Had to fork it as pgembed, compile recent versions and bundle BM25 and vector extensions.

Thank you for the fork! Back in November I made a PR to pgserver for postgres 18, but your version with vector extensions is even better!

The biggest selling points for me with SQLite are how well tested it is and how well it scales to large data files.

It’s a really solid piece of work. If the concurrency model fits what you’re doing I wouldn’t hesitate to use it in production.

How good is the testing for PGLite in comparison?

Is it only intended for test/development? (That’s still a useful use case if so, I love Postgres too).

I use pglite for unit/integration tests and it's been fantastic.

Note that it uses "single user mode." This means a single connection at a time, and thus no concurrent transactions. That takes you a little bit closer to SQLite's single-writer.

if you look merely for a replacement of SQLite i can higly recommend Firebird Embedded. it is faster, has better concurrency and has very low memory footprint

Pgembed is pglite for native code.

Which one? https://github.com/Ladybug-Memory/pgembed or https://github.com/faokunega/pg-embed ?

Both will either download or ship the postgres binary with your app, and run it in a separate process. Pglite is different, it's actually a library (they have stripped some parts of the postgres source code) and it could in principle be ported to be used as a native library (meaning, you link to it and run as a single program)

There's even a draft PR to do this, but it became stale

https://github.com/electric-sql/pglite/pull/842

Right now what exists is, grabbing the pglite wasm and running it on native code on wasi

https://github.com/f0rr0/pglite-oxide

frick this gotta be the tenth time ive read about pglite here and i always go look for native library. shame pr became stale, webapps simply arent always my end game. thanks for putting pglite-oxide on my radar tho, this is an interesting almost-there i can tinker with.

I am always checking out this project too, looking for a native build. I am quite happy that they added support for extensions though

The first one. It's forked from pgserver.

Yes, what you get is a multi-server postgres under the covers. But for many users, the convenience of "uv pip install...", auto clean up via context manager is the higher order bit that takes them to SQLite.

Of course the bundled extensions are the key differentiator.

With enough distribution and market opportunities we (yes, there is a for profit company behind it) can invest in making it truly embedded.

Experimenting with the pglite way, this time using vlang here:

https://github.com/adsharma/vpg

  v run cmd/vpg_test.v

> but only in javascript/WASM

Well that's right out then.

Quote: "... you just import ‘pglite’ in code, and you will have a fully-fledged Postgres instance writing to a local file, rather than a separate docker container..."

Because a docker container is a magic happening in heaven instead of being just another local file, right? /s

Reply on news.ycombinator.com