17 ms·

> poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation Is this opinion shared by others?

chasil 1y ago
Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. At the same time, strict tables address some of the concern of those coming from conventional databases. Dates and times are a core problem to SQLite not seen elsewhere as far as I know, but this does evade UTC and constantly shifting regional time. My OS gets timezone updates every few months, and avoiding that had foresight. Default conformance with Postel's Law is SQLite's stance, and it does seem to work with the ANSI standard.
- alberth 1y ago
  While nobody expected it … it should not be unexpected. Typically, the Lowest-Common-Denominator wins mass appeal/uasge. By not having safety checks and even typing enforcement, SQLite caters to actually more use cases than less.
- formerly_proven 1y ago
  SQLite probably doesn't do anything with times and dates except punting some functions to the limited libc facilities because including any proper date-time facilities would basically double the footprint of SQLite. Same for encodings and collations.
  - 62 1y ago
    Same for encodings and collations.
- SQLite 1y ago
  > Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. I don't remember ever saying that. Rather, see https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html for detailed explanation of why I think flexible typing ("weak typing" is a purgative and inaccurate label) is a useful and innovative feature, not a limitation or a bug. I am surprised at how successful SQLite has become, but if anything, the flexible typing system is a partial explanation for that success, not a cause of puzzlement.
  - chasil 1y ago
    Did I misinterpret the experts' assertion of imposibility? "I had this crazy idea that I’m going to build a database engine that does not have a server, that talks directly to disk, and ignores the data types, and if you asked any of the experts of the day, they would say, “That’s impossible. That will never work. That’s a stupid idea.” Fortunately, I didn’t know any experts and so I did it anyway, so this sort of thing happens. I think, maybe, just don’t listen to the experts too much and do what makes sense. Solve your problem." https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
    - jmull 1y ago
      > Did I misinterpret the experts' assertion of imposibility? Misstated, I'd say. You said "nobody" but the actual quote is about the assumed conventional wisdom of the time, which is quite different. And while this was probably inadvertent, you phrased it in a way that almost made it sound like that was Dr. Hipp's original opinion, which, of course, is the opposite of true.
- chrisweekly 1y ago
  I often forget or mix up which "Law" refers to which observation, and I'm surely not the only one. So: Postel's Law, also known as the Robustness Principle, is a guideline in software design that states: "be conservative in what you send, be liberal in what you accept."
da_chicken 1y ago
I think it's one of the reasons DuckDB has seen the popularity that it has.
- benjiro 1y ago
  DuckDB is a columnar database, and columnar DBs are way better for analytics, statistics... That is its main reason for its popularity, the ability to run specific workloads that row based databases will struggle/be slower at. Nothing to do with the posters badly formatted complained about Sqlite. By that metric DuckDB has a ton of issues that even out scale Sqlite.
- qaq 1y ago
  thats a strange argument DuckDB is for OLAP and SQLite is for OLTP
  - da_chicken 1y ago
    Yeah, but most applications are small. So, at the scale of most applications you can drop in DuckDB with zero change in actual performance. It still has indexes to support highly selective queries because it needs to have functional primary keys.

chasil 1y ago

Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. At the same time, strict tables address some of the concern of those coming from conventional databases. Dates and times are a core problem to SQLite not seen elsewhere as far as I know, but this does evade UTC and constantly shifting regional time. My OS gets timezone updates every few months, and avoiding that had foresight. Default conformance with Postel's Law is SQLite's stance, and it does seem to work with the ANSI standard.

alberth 1y ago
While nobody expected it … it should not be unexpected. Typically, the Lowest-Common-Denominator wins mass appeal/uasge. By not having safety checks and even typing enforcement, SQLite caters to actually more use cases than less.
formerly_proven 1y ago
SQLite probably doesn't do anything with times and dates except punting some functions to the limited libc facilities because including any proper date-time facilities would basically double the footprint of SQLite. Same for encodings and collations.
- 62 1y ago
  Same for encodings and collations.
SQLite 1y ago
> Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. I don't remember ever saying that. Rather, see https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html for detailed explanation of why I think flexible typing ("weak typing" is a purgative and inaccurate label) is a useful and innovative feature, not a limitation or a bug. I am surprised at how successful SQLite has become, but if anything, the flexible typing system is a partial explanation for that success, not a cause of puzzlement.
- chasil 1y ago
  Did I misinterpret the experts' assertion of imposibility? "I had this crazy idea that I’m going to build a database engine that does not have a server, that talks directly to disk, and ignores the data types, and if you asked any of the experts of the day, they would say, “That’s impossible. That will never work. That’s a stupid idea.” Fortunately, I didn’t know any experts and so I did it anyway, so this sort of thing happens. I think, maybe, just don’t listen to the experts too much and do what makes sense. Solve your problem." https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
  - jmull 1y ago
    > Did I misinterpret the experts' assertion of imposibility? Misstated, I'd say. You said "nobody" but the actual quote is about the assumed conventional wisdom of the time, which is quite different. And while this was probably inadvertent, you phrased it in a way that almost made it sound like that was Dr. Hipp's original opinion, which, of course, is the opposite of true.
chrisweekly 1y ago
I often forget or mix up which "Law" refers to which observation, and I'm surely not the only one. So: Postel's Law, also known as the Robustness Principle, is a guideline in software design that states: "be conservative in what you send, be liberal in what you accept."

da_chicken 1y ago

I think it's one of the reasons DuckDB has seen the popularity that it has.

benjiro 1y ago
DuckDB is a columnar database, and columnar DBs are way better for analytics, statistics... That is its main reason for its popularity, the ability to run specific workloads that row based databases will struggle/be slower at. Nothing to do with the posters badly formatted complained about Sqlite. By that metric DuckDB has a ton of issues that even out scale Sqlite.
qaq 1y ago
thats a strange argument DuckDB is for OLAP and SQLite is for OLTP
- da_chicken 1y ago
  Yeah, but most applications are small. So, at the scale of most applications you can drop in DuckDB with zero change in actual performance. It still has indexes to support highly selective queries because it needs to have functional primary keys.