Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

First Contact with SQLite

brandur.org

Post reply on HN
Print view View original

71–80 of 99 posts

Re: First Contact with SQLite

#71

Post by JackMorgan »

willcipriano wrote: post #35
cjs_ac wrote: post #4

> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is. I find these 'different therefore wrong' takes to be immature. Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other…

A lot of people get real obsessive about the "worlds best" thing, take a motorcycle for example, but them themselves lack the technical skill to actually require that level of equipment. Looking for the "best" whatever all the time is a sign you don't know what you are doing.

I've felt this way for a long time, and I enjoy using lower quality things sometimes just to confirm it's worth the investment into something more advanced.

Like violin, I still play my $50 special I bought in 2005, because it sounds fine and I'm terrible so it's Good Enough.

On the other hand, I am building my second computer keyboard that's going to cost me $300+ because, well, I make a living with these things and I use it 60+ hours a week. I have wrist problems, so a better keyboard literally translates into more hours billed.

Re: First Contact with SQLite

#72

Post by rezonant »

stavros wrote: post #30
rezonant wrote: post #28

Earlier quoted context omitted.

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 r…

Your example underscores why it doesn't read that way for me. A Ferrari Enzo and a horse are such different transportation methods that the two barely overlap in use case. A horse needs no gasoline, can work off road, and is much quieter. The Ferrari can travel much faster and farther in a go, doesn't get spooked by loud noises, and doesn't require as much daily maintenance.

I wouldn't bring a Ferrari Enzo to do a horse's job, nor a horse to do a Ferrari Enzo's job.

The same is even more true for Postgres and SQLite. Postgres is a poor fit for an on-device database for a mobile app or embedded device, and SQLite is a poor fit to power a high traffic horizontally scaled web service.

Re: First Contact with SQLite

#73

Post by stavros »

rezonant wrote: post #72
stavros wrote: post #30

Earlier quoted context omitted.

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 r…

Your example underscores why it doesn't read that way for me. A Ferrari Enzo and a horse are such different transportation methods that the two barely overlap in use case. A horse needs no gasoline, can work off road, and is much quieter. The Ferrari can travel much faster and farther in a go, doesn't get spooked by loud noises, and doesn't require as much daily maintenance. I wouldn't bring a Ferrari Enzo to do a ho…

> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is.

I really don't see how you can read this as anything other than "SQLite is worse than Postgres". He basically says "sometimes I doubt if Postgres is the best database, but then I use SQLite, and it really is".

Re: First Contact with SQLite

#75

Post by lenkite »

cjs_ac wrote: post #4

> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is. I find these 'different therefore wrong' takes to be immature. Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other…

I would be happy if SQLite offered a native DATETIME type. That has been in the SQL spec since 1992! and is supported by every RDBMS that exists today in the planet. But nooo - its gets stored as TEXT and causes all kinds of issues for folks who don't know this.

Re: First Contact with SQLite

#76

Post by rezonant »

stavros wrote: post #73
rezonant wrote: post #72

Earlier quoted context omitted.

Your example underscores why it doesn't read that way for me. A Ferrari Enzo and a horse are such different transportation methods that the two barely overlap in use case. A horse needs no gasoline, can work off road, and is much quieter. The Ferrari can travel much faster and farther in a go, doesn't get spooked by loud noises, and doesn't require as much daily maintenance. I wouldn't bring a Ferrari Enzo to do a ho…

> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is. I really don't see how you can read this as anything other than "SQLite is worse than Postgres". He basically says "sometimes I doubt if Postgres is the best database, but then I use SQLite, and it really is".

Take from it what you want, but he did not say that SQLite is worse, that's an inference. He just said Postgres is the best database in the world, and that's quite valid from the perspective of features, correctness, and performance.

SQLite is a tradeoff: It's very small and to make it that small, some traditional database aspects need to be discarded. That small size makes it useful when size is a factor.

Re: First Contact with SQLite

#77

Post by pdimitar »

D13Fd wrote: post #55
bruce511 wrote: post #40

Earlier quoted context omitted.

Not to be contrary but... why? Why make a new vesion that breaks compatibility with the old version? Why make a new version just so it behaves like all the other database engines out there? Isn't having difference the point of having choices?

Agreed. Most of that can be achieved with SQL (how hard is it to add STRICT?) or the code you use to interact with SQLite. I don't see how it's worth sacrificing backwards compatibility to achieve those things, which are mostly non-issues in practice.

I don't see what is so appealing about backwards compatibility. The database is an implementation detail and its specifics should not block anything.

Seeing several comments like yours is puzzling to me because I find myself unable to understand what are the devs preaching for it gaining from SQLite's extremely conservative backwards compatibility policy.

For example, PostgreSQL has `pg_upgrade`. You run that after you upgrade its major version and it's a bulletproof and easy transition 99% of the time.

Re: First Contact with SQLite

#78

Post by silvestrov »

bruce511 wrote: post #40
silvestrov wrote: post #25

I 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 table

Not to be contrary but... why? Why make a new vesion that breaks compatibility with the old version? Why make a new version just so it behaves like all the other database engines out there? Isn't having difference the point of having choices?

As I interpret the record format (https://www.sqlite.org/fileformat.html#record_format) then new native datatypes (datetime, jsonb) cannot be added without breaking the file format.

When file format is no longer backwards compatible, it makes sense to make a new major (semantic) version, so you don't have a situation where version 3.50 can read a file but version 3.43 cannot.

Re: First Contact with SQLite

#79

Post by tazu »

strken wrote: post #58
tazu wrote: post #8

Earlier quoted context omitted.

> 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…

Is that 10ms of network latency? I remember Postgres being pretty low latency if you run it on the same host, although the point about maintenance makes complete sense.

Yea ~10ms is my experience using localhost TCP, using a Unix socket would probably be faster.

Re: First Contact with SQLite

#80

Post by tazu »

mbreese wrote: post #27
tazu wrote: post #8

Earlier quoted context omitted.

> 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 over…

And the value of maintenance-free differs by environment. A lot of my projects are one-offs where I'm not getting paid to maintain a database system, so I don't want to maintain a database system.
Post reply on HN