Hacker News Reader

Comment by mastax | Hacker News Reader

Well I definitely prefer the new strict mode, I'll enable that right away. I've long fantasized about a mode that would make SQLite behave like a normal SQL database, but this is not that. You still can't store a float in the database and get it back out unmodified. SQLite will expand it to a double, and it can't store NaNs. And probably more overhead to be able to support storing strings in my REAL column. Oh well.

I tried out DuckDB which is more my style but the .NET libs are poor and I can't shave that yak right now.

View on HN

  > You still can't store a float in the database and get it back out unmodified. SQLite will expand it to a double,

When would a double not be an acceptable replacement for float? Seriously interested. If the application layer works with floats, then a double-precision float should contain all possible values. I'd really love to hear about the use-case, I'm sure they exist even if I can't think of one.

  > and it can't store NaNs.

Well, neither can other small-time databases such as MySQL. Postgres does, though, which I did not know until googling it now.

Storing 13,000,000 floats to disk per minute on user machines. Doubling storage usage for no reason is not okay, we're already having problems filling up disks with other data.

Storage concern, I see. Yes, that actually was the only thing I could think off. Thank you.

According to the docs, looks like the ANY type in a strict table might do that:

> In a STRICT table, a column of type ANY always preserves the data exactly as it is received. For an ordinary non-strict table, a column of type ANY will attempt to convert strings that look like numbers into a numeric value, and if successful will store the numeric value rather than the original string

There's still much less data types than in most SQL databases though, e.g. float4, boolean, timestamp etc are missing.

SQLite converts NaNs to NULL, and SQLite libraries tend to throw on encountering NaN.

> SQLite will expand it to a double

Is that part a real problem? (The NaN thing certainly is.)

Doubles are a strict superset of floats. You don't risk losing any precision on the float to double conversion, and if you then convert the result back to float, you won't lose any precision either.

It's can be a real problem when you need to store 13,000,000 floats per minute.

Is it hard to work around? No. But every time I use SQLite it requires half a dozen little hacks to work around it's strangeness and I would prefer to not have to do that.

Are you talking about the increased IO and disk usage due to doubles being twice the size? Because the conversion itself is usually done in hardware and is extremely fast.

Anyway, I've never touched a serialization library that doesn't mess with the data types it gets. There is always some conversion needed, and this one is on the easy end of the scale. The fact that composed types (records and arrays) aren't on the list is a much larger troublemaker.

(Regardless, I'm curious about the applications of 13M inserts per minute on a synchronous single-computer database. Pushing a machine to that extreme looks very interesting. Is that application something you can share?)

It's a data acquisition system which records various data collected out in the field by operators on standard laptops. They're frequently operating in places with no cell coverage so we can't "just" stream to the cloud, we store the data locally and then bulk upload later. Also want to be able to visualize the data on the laptops which may be larger than ram.

It's not a particularly demanding use-case, especially with respect to querying. Could just use some serialization format, parquet maybe, but I know SQLite works reliably and has good libs. It's nice to have standard tools available, and I know no matter what changes I'll be able to read these databases in 20 years. Didn't have a lot of time to work on this project so I didn't spend much time evaluating alternatives.

Oh, that's interesting. I didn't think about batch importing local data. Yes, I imagine you want to minimize the time importing data, and so halving the size of your main data would speed things up by a sizeable amount.

Reply on news.ycombinator.com