All of the issues are well documented. It would make much sense to make ones first contact with the documentation to decide if given piece of software is suitable for ones needs. SQLite is like a bicycle, postgresql is like a freight train. Each has it's use.
First Contact with SQLite
21–30 of 99 posts
Re: First Contact with SQLite
#22[deleted]
Re: First Contact with SQLite
#23There are things that different databases are best for. This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week): * Using postgreSQL to store data in an iPhone app * Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and e…
The author is both ignorant of SQLite history, and philosophy as also ignorant of the real use-cases SQLite solves for embedding a lightweight SQL-ish database into applications. Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor…
I didn't take this post to be overly negative, just a reflection of their experience in working with the tech.
For anyone coming from a heavyweight SQL background, it is indeed pretty easy to be caught off guard with SQLite. That doesn't reduce its value of course.
Re: First Contact with SQLite
#24Fact of the matter is that SQLite shouldn’t be used in a non embedded context. There’s a reason people use Postgres. There are some neat things you can do with it like using HTTP range queries to query it directly from object store or Litestream, but my point stands.
It's just your opinion, not a fact.
Re: First Contact with SQLite
#25I think it is time for a real version/generation 4 of SQLite which drops some backwards compatibility (e.g. file format) and has
1) strict always enforced.
2) full datatypes (ints, floats, datetime, jsonb)
3) all "ALTER TABLE" functionality, even if it has to rewrite the tableRe: First Contact with SQLite
#26Advanced alter table operations do involve creating a new table, but the pattern for doing that is actually pretty robust: you start a new transaction, create the new table, copy the data across and then atomically swap the table names before you commit: https://www.sqlite.org/lang_altertable.html#otheralter My favourite feature of my sqlite-utils CLI tool is the "transform" command which implements this pattern for…
This does not work (easily) when other tables have "foreign key" constraints to the table. You need to manually recreate them.
Re: First Contact with SQLite
#27I feel like the recent hype around SQLite made people use it for a lot of stuff that is not really suitable for SQLite. It has too many caveats especially around data types. I'm not saying it does not deserve the attention, it is a fantastic piece of software but if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it. It shines when you don't need or want something mor…
> if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it I had the option for a recent project. It's a niche forum-like application with around 2,000 users. Went with a monolithic design and vertical scaling (if needed), so SQLite was perfect. Every dynamic HTML page renders in under 1ms. Litestream for live-replication to a couple S3 buckets. Running PostgreSQL for so…
> There would genuinely be more maintenance required for the PostgreSQL server/daemon than the application itself. It just makes no sense.
I think this is the key point the original post was missing. When you’re working on a project with a DB, you should factor in the cost/overhead of the DB as well. Postgres is a wonderful DB. But it is also big and requires work to keep it running. For many projects, the extra overhead is well worth it.
(And if you’re using a “cloud” DB, the overhead is still there, but you’re explicitly paying for the privilege of making it someone else’s problem. )
But for smaller projects, or ones with less DB requirements, something small like SQLite is much more appropriate. And it nearly removes the DB overhead from the maintenance equation.
Re: First Contact with SQLite
#28I find comparing SQLite with Postgres moot to begin with. I use SQLite when I don't want to run a database server out-of-band, or when I want to need to copy a single file to copy an entire database. For that, it is unparallelled, easily the best in the world, by far. I don't understand the comparison here at all.
You can reflect on your early experience with SQLite while simultaneously reflecting on why Postgres is a great database. If it wasn't that way, we'd all be running SQLite for our web services, but we're not.
All technologies, and especially databases, are best applied with thoughtful consideration of the context. I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres. Just the same as a use case best served by Postgres is not suitable for SQLite.
Re: First Contact with SQLite
#29There are things that different databases are best for. This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week): * Using postgreSQL to store data in an iPhone app * Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and e…
The author is both ignorant of SQLite history, and philosophy as also ignorant of the real use-cases SQLite solves for embedding a lightweight SQL-ish database into applications. Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor…
None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here.
If want to have more of an apples-to-apples comparison you could swap out Postgres for DuckDB (which aims to follow Postgres in SQL dialect), and all of the mentioned points should still hold.
Re: First Contact with SQLite
#30I find comparing SQLite with Postgres moot to begin with. I use SQLite when I don't want to run a database server out-of-band, or when I want to need to copy a single file to copy an entire database. For that, it is unparallelled, easily the best in the world, by far. I don't understand the comparison here at all.
You can reflect on your early experience with SQLite while simultaneously reflecting on why Postgres is a great database. If it wasn't that way, we'd all be running SQLite for our web services, but we're not. All technologies, and especially databases, are best applied with thoughtful consideration of the context. I didn't get the sense the author was suggesting their SQLite use case would actually be better served b…
Sure. I can also reflect on my early experience with a Ferrari Enzo while I simultaneously reflect on how much I love my horse, but it should be clear that that's just irrelevant musing, rather than a direct comparison.
> I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres.
I got exactly that sense, from "I tried SQLite, it lacked these things. Postgres really is the best database".