4 ms·
Have any of the problems that led people to use Postgres instead of SQLite actually been solved? Are we doomed to repeat the same mistakes? Also, any plans to
by endisneigh 5y ago
Have any of the problems that led people to use Postgres instead of SQLite actually been solved? Are we doomed to repeat the same mistakes?
Also, any plans to support PATCH x-update-range so SQLite can be used entirely in the browser via SQLite.js?
Can someone enlighten me with the types of use cases this would be better for vs say Postgres?
- jve 5y agoWhat problems? Both are for different use cases albeit overlapping.
- endisneigh 5y agoConcurrent writes, for one.
- nindalf 5y agoWhich problems were you thinking of? Cloudflare and fly.io both promise hassle free read replicas and backup. They will both offer only a single node capable of writes, because that’s how SQLite rolls. This is a pretty good fit for a read heavy load that requires SQL and very low latency.
- endisneigh 5y agoI guess I’m not understanding what the benefit is vs hosted Postgres. Also low latency and setup can be equally trivial - see supabase for example.
- simonw 5y agoBiggest benefit over hosted PostgreSQL is that you get SELECT queries that are measured in microseconds, because SQLite avoids needing network overhead per query. https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
- endisneigh 5y agoWouldn’t D1 introduce network overhead?
- simonw 5y agoYes for writes, but it shouldn't for reads: it looks like it works by replicating the full database down to each edge location where the code is running.
- jve 5y agoHope this can give you some concrete answers: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- hn_ei_ser_23 5y agoThe important drawback is async replication and therefore the lack of full consistency. On the other hand, this is the big advantage of hosted Postgres and the like. Those offerings are great for use-cases that don't need that kind of consistency, which are many.
- hn_ei_ser_23 5y agoNo and no. I think this is great for Edge computing, where there is currently no solution. So, it's better than nothing. It all depends on the use-case, of course. A traditional hosted Postgres or MySQL database or cluster is certainly the go-to solution for all who need advanced features or full consistency, which only synchronous replication could provide.
- ignoramous 5y agoIt isn't as much as folks who need Postgres features are moving to SQLite just because it is cool, but it is folks who don't want those Postgres features moving to SQLite, because the latter has just enough features they only ever really need.
- endisneigh 5y agoSQLite made sense as an embedded database on day a desktop or phone because there’s only a single person generally writing to it. The perfect use case. I don’t understand how it will be usable at all in a website with multiple users. Is the idea to make your site to every user gets their own database? How do you stop SQL injection? Once you solve all of these problems aren’t you better off just using Postgres?
- adamckay 5y ago> I don’t understand how it will be usable at all in a website with multiple users With WAL mode enabled the database is locked during writes only, and concurrent writes are queued but you can still perform reads concurrently. If you keep your write transactions small and consider that a lot of apps aren't writing a lot, it can give perfectly good performance for a lot of usecases. > Is the idea to make your site to every user gets their own database? You can do... I know of B2B apps that give each billable customer their own database. > How do you stop SQL injection? In the exact same way you do in all other flavours of SQL - with parameterized queries. > Once you solve all of these problems aren’t you better off just using Postgres? Not necessarily. Postgres gives you a different set of problems and limitations to consider and work around.
- endisneigh 5y agoMost of the problems with Postgres are a result of it not being embedded. If you’re using SQLite in an non embedded fashion I don’t see how you don’t inherit the same problems.