Hacker News Reader

Comment by nilved | Hacker News Reader

This used to be how I felt, since the performance criticism of SQLite is vastly overblown. However, the safety criticism of a dynamically typed database is vastly underblown.

Since it completes with fopen(), you get about as much structure and validity.

View on HN

I'm currently developping a text format called WSL[1]. By nature text files don't have indices, but it is strongly typed, supports standard relational integrity constraints, and indices can be automatically created when reading the file.

There is a currently only a simplistic python library which reads databases at about 1MB/s. On the plus side it's dead simple to use, only a single library call to parse a file as schema, tables, and indices.

There is also a C library in development which lexes at about 300-600 MB/s in a single thread (depending on how many columns are actually needed and thus have to be written to per-column lexem buffers) and which I hope will have a release next month.

[1] http://jstimpfle.de/projects/wsl/main.html

What's the safety issue? Will your data corrupt, or are your types just not 100% guaranteed?

Because I can live with the ladder: weak types suck in programming languages, but are okay in DBs, and the types get verified multiple times on their way in and out of the DB in most systems.

Besides, it won't mangle your data. Unlike some DBs that I could name...

I don't think it would mangle your data directly, but it could lead to incorrect results since there is a degree of mystery from query to query. You should definitely base a conclusion on their words and not mine.

https://www.sqlite.org/datatype3.html

Thanks for the link. Yeah, it's as I remember: type affinities will convert to their type if possible, and if not... well, you get out what you put in.

The reason most people don't complain about this is that it's a far from common issue to totally miswrite your SQL statements so badly that you wind up mixing up columns. And when you do, it's usually detected pretty fast.

Since 3.3 you can use check constraints to enforce things, but I don't know what kind of performance impact that has.

You also need to enable them each time you connect.

Really? dang that's terrible. I thought they'd stay that way as part of the table schema. Glad I normally use an ORM that'll take care of the type checking ahead of time.

Apparently, they actually do stay around.

No you don't. CHECK constraints always work.

Maybe you are thinking about foreign key constraints, which for backwards compatibility are off by default, unless you use a compile-time option to make then on by default.

> the performance criticism of SQLite is vastly overblown

That kind of depends on how you use it. Obviously this depends on the row size, constraints, etc., but if you want to write more that a few thousand rows per second for longer periods, the performance limitations of SQLite will become very obvious very quickly.

When your little side business needs to more than a few thousand rows per second for longer periods, you're either having enough customers to justify migration, or have the most inefficient database scheme of the decade.

Not every piece of software that needs to store data is a CRUD application and "little side businesses" are not the only use cases for an embedded database. For example, an embedded application logging readings from a number of sensors could easily reach thousands of writes per second. Another example would be a desktop (or mobile) email client such as Apple Mail using SQLite to store data, it could easily reach thousands of writes per second when e.g. downloading the entire mailbox from an IMAP server.

Certainly true. I'm not saying that SQLite is always the best choice. But people like to say "SQLite is not performant" when they really mean "SQLite is not performant for concurrent-write-heavy applications" which is a small minority. In most but not all cases the performance of SQLite is perfectly adequate.

> validity

Are you thinking of corrupted files? Sqlite disk operations are atomic.

Reply on news.ycombinator.com