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!
mclightning 10 hours ago [-]
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.
solarkraft 10 hours ago [-]
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 :(
It's in wasm so should be possible to code up the next language
Lio 14 hours ago [-]
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).
jwilliams 14 hours ago [-]
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.
trelliumD 16 hours ago [-]
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
Svoka 15 hours ago [-]
I wonder, what amount of PG deployments can be replaced with SQLite. I bet most of them.
faangguyindia 6 hours ago [-]
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.
odie5533 7 hours ago [-]
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.
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
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.
trueno 14 hours ago [-]
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.
nextaccountic 11 hours ago [-]
I am always checking out this project too, looking for a native build. I am quite happy that they added support for extensions though
unnouinceput 6 hours ago [-]
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
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
https://stackoverflow.com/a/49656730
So it’s actually not -lite but -ite. =)
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!
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.
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 :(
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).
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 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.
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
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.
Because a docker container is a magic happening in heaven instead of being just another local file, right? /s