Re:Appropriate Uses for SQLite
by allyjweir on Monday February 12, 2018 @06:58AM
(#16358099)
This is one of my favourite technical sites. It is very clear and to the point about its good use cases while being clear where an alternative would be a better choice.
In a similar vein, VueJS has a comprehensive exploration/comparison of itself compared to other frameworks[0].
I really appreciate open source maintainers that have the humility to compare their work fairly with others within the domain while still taking pride in their own acheivements.
Re:Appropriate Uses for SQLite
by majewsky on Monday February 12, 2018 @08:04AM
(#16358314)
I'm trying to make a habit of adding a "Do NOT use if..." section near the top of my projects' READMEs whenever there are simple exclusion criteria.
For example https://github.com/sapcc/swift-http-import#do-not-use-if -- This particular instance was born out of watching users bending over backwards to use the software even though standard software does the job just fine or better in their concrete case.
Re:Appropriate Uses for SQLite
by abrookewood on Tuesday February 13, 2018 @01:00AM
(#16365058)
Agreed. One of my pet peeves are projects that gleefully point out all of the issues that other projects face ('Not ACID compliant! Bound by CAP theorem! Difficult replication!') then present themselves as magical solutions free of all trade-offs.
Re:Appropriate Uses for SQLite
by JepZ on Monday February 12, 2018 @08:08AM
(#16358326)
Yeah, sadly those comparisons are mostly feature driven. Coming from riot.js I have to say that while I was very happy with the features riot.js offered, I am kinda unhappy how the development is done lately (discussing the same issues over and over again, but nothing changes).
So while I have no idea about the status of vue.js regarding that topic, I belief that even if they are just in a normal state it is a huge positive aspect in comparison to riot.js, but the whole issue might be a little subjective too.
Re:Appropriate Uses for SQLite
by JepZ on Monday February 12, 2018 @08:00AM
(#16358303)
Well, actually it is very simple when you understand the why SQLite performs differently than other database systems. The major difference is that 'normal' database systems have a process which receives and processes SQL queries and has the exclusive access to the database file.
SQlite on the other hand doesn't have such a process so, every client has to open the database file itself, process the SQL and close/write the file again.
So the biggest difference is, that if you want to use SQLite, you will have to limit your database operation to one or fewer ;-) processes to not get into a situation where one process has to wait until the other finished writing the database file to the filesystem just to read it again in the next moment. Otherwise your filesystem and the continuing reading and writing of the database file will make the whole thing very slow.
But as long as you have only one process using the SQLite file and keep it open for a while you will see a comparable performance to other database systems. Actually, SQLite can be very fast if you do use it in a sane way.
Re:Appropriate Uses for SQLite
by krylon on Monday February 12, 2018 @12:00PM
(#16359954)
As far as I understand, with WAL, SQLite supports multiple readers plus one writer on a database. And multiple concurrent readers were always supported.
So the limiting factor is not the degree of concurrency, but the amount of write-activity. Workloads that mostly read and only occasionally write to the database work well with SQLite.
Re:Appropriate Uses for SQLite
by beagle3 on Monday February 12, 2018 @08:55AM
(#16358522)
SQLite is fine with multiple processes as long as they are read mostly. And if you switch journal to WAL mode, it is as good any multiprocess DB without MVCC (which, depending on access patterns might be as good as it gets - although mist patterns do benefit immensely from MVCC)
Re:Appropriate Uses for SQLite
by dhd415 on Monday February 12, 2018 @09:18AM
(#16358668)
I'm not sure what you mean by "multi-process DB", but there are plenty of workloads in which SQLite under-performs non-MVCC databases. It is simply not designed for highly concurrent access patterns. In a previous job, I had to replace the persistence layer for a medium-load (100-500 events/sec) event tracking system from SQLite to SQL Server which is, by default, non-MVCC. The latter was considerably more complicated and considerably faster and more scalable than the former.
I say this not because SQLite is not good. It's _great_ for what it intends to do which is, as the linked page says, offer an excellent alternative to fopen(). It doesn't really offer an alternative to a full DBMS, though. There may be some gray area between a "heavy fopen()" workload and a "light DBMS" workload where there might be a legitimate case to be made for SQLite over a DBMS, but standard OLTP or OLAP workloads with any degree of concurrency are almost uncertainly outside of SQLite's intended use case.
Re:Appropriate Uses for SQLite
by kuschku on Monday February 12, 2018 @12:11PM
(#16360046)
Not really.
In a usual quassel (irc bouncer that can, optionally, store logs in SQLite) setup, you’ll have between 1 and 10 writers, constantly writing hundreds of IRC messages a second to SQLite, while you’ll also have readers that need to respond with low latency for queries loading hundredthousands of messages, including those that were just written.
The result is that SQLite gets so slow that the writers queue up so much work that the IRC connection times out.
SQLite is absolutely useless for multiple writers.
Re:Appropriate Uses for SQLite
by rpcope1 on Monday February 12, 2018 @02:28PM
(#16361265)
> SQLite is absolutely useless for multiple writers.
I don't really quite agree with this, but would say that with SQLite it's more important be be aware of how concurrency is being used with regards to the database, in comparison to something like MySQL or PostgreSQL. As far as IRC logs, I think the important thing (that would almost certainly hamstring any SQL database that's actually being ACID) is to not use autocommit on inserts, and to not use a transaction for every single insert (which is possibly what's happening). I wrote a plugin for ZNC to do exactly what you're doing above, and by batching my writes (either waiting 15 seconds or until n (where n is maybe 1000 or more) messages are queued), I've gotten superb performance out of SQLite3 running with a WAL journal, with the trade off being a little bit of data might be lost if your bouncer happens to fall down in an unhandled way. I was also able to get really good read performance without hurting raw write performance by leveraging partial indices, which something like MySQL doesn't give you.
Re:Appropriate Uses for SQLite
by tracker1 on Tuesday February 13, 2018 @04:08AM
(#16365742)
It really depends... I've worked on systems that PostgreSQL (or any SQL rdbms) wouldn't be able to keep up with. I've seen many others that would do fine with SQLite. It depends.
In the end, it's a matter of testing. Also, with faster drive options (Optane and whatever is next), it could very well be a decent option. It all depends.
Frankly, for logging, I'd lean towards elasticsearch (ELK) these days, which is about the most write heavy situation you're likely to see. Depending on the scaling Mongo, RethinkDB, ElasticSearch and Cassandra can all be better solutions than RDBMS. I'd still reach for SQLite or PostgreSQL first depending on the situation though.
Re:Appropriate Uses for SQLite
by kuschku on Tuesday February 13, 2018 @07:12AM
(#16366510)
Sure, with optane it may work — but the usual usecase for SQLite is on the slowest SD cards or spinning HDDs you can imagine, where the user didn't want to spend time configuring postgres
Re:Appropriate Uses for SQLite
by anewhnaccount2 on Monday February 12, 2018 @09:01AM
(#16358565)
The key thing you omitted is you can have multiple readers at once. As long as you don't have really slow reads or writes and don't have a tonne of writes trying to queue up, nobody should get starved with database level locking.
Re:Appropriate Uses for SQLite
by Cyberdog on Monday February 12, 2018 @03:09PM
(#16361590)
One of my favorite points about SQLite which hasn't been addressed yet in the comment thread is that its databases are just single flat files. Need to copy data from a server to a local dev machine? No need to log into the server, make a dump, copy the dump file to the local machine, and import the dump. Just copy the database file as you would any other file. That's it.
Making a backup before trying something risky (and then restoring the backup when something screws up) is similarly trivial.
Maybe to some of you it's not worth that much, but it's a legitimate timesaver in my opinion.
Re:Appropriate Uses for SQLite
by PricelessValue on Monday February 12, 2018 @04:01PM
(#16362090)
That's why it is sql "lite". It's great for certain things because it's generally lightweight and manageable.
But you wouldn't want a single file format on enterprise level systems where enterprise level performance and stability is required. At the very least, you'd want your data and transaction log files to be different files on different disks. Especially when IO throughput is important.
Also, since DBs tend to have important data on them, being able to copy a file (db) and move it anywhere you want isn't much of a selling point on serious systems.
Having said that, sqlite is great for application/client side storage.
Re:Appropriate Uses for SQLite
by jjrh on Monday February 12, 2018 @03:31PM
(#16361789)
This is the main reason I choose SQLite for simple stuff and experiments. Just being a file also makes it really easy to come back to a half finished project or pick up from another machine with a simple rsync.
Re:Appropriate Uses for SQLite
by bullen on Monday February 12, 2018 @04:43PM
(#16362553)
Try https://github.com/tinspin/rupy/wiki/Storage it stores each value (not column/row/table or database) as a single JSON file! Make sure you "mkfs.ext4 -T small"! ;) Upside is tar+gzip compresses the db 100x!
Re:Appropriate Uses for SQLite
by virtualwhys on Monday February 12, 2018 @08:53AM
(#16358506)
Sadly, in the browser SQLite has been kicked to the curb, replaced by IndexedDB.
It's deeply disappointing to not be able to use SQL on both client and server. Yes, there's Lovefield, which employs its own DSL that has nothing to do with SQL, so you can scrap everything on the server and redo it on the client.
Or just give up and go with NoSQL on client and server, that seems to be what the browser vendors are implicitly pushing developers toward.
Re:Appropriate Uses for SQLite
by dragonwriter on Monday February 12, 2018 @11:45AM
(#16359827)
> Sadly, in the browser SQLite has been kicked to the curb, replaced by IndexedDB.
SQLite hadn't been kicked to the curb, the WebSQL API whose “standard” was essentially “act like version x of SQLite” was kicked to the curb.
The concept of WebSQL was good, but the standard itself wasn't great, and the absence of any intent to implement from other vendors or intent to improve the standard from those who had embraced it made it a dead-end standard.
Re:Appropriate Uses for SQLite
by rakoo on Monday February 12, 2018 @07:04PM
(#16363586)
A standard needs to have multiple open source implementations to be accepted. What you're asking is basically to reimplement sqlite, feature for feature, bug for bug, and keep up with it as soon as it changes. No wonder no one wanted to try that.
Re:Appropriate Uses for SQLite
by hungerstrike on Monday February 12, 2018 @07:16PM
(#16363654)
What about JSON? That became a standard without multiple implementations or even any discussion about what people would’ve wanted, like comments.
I just don’t think there’s any rule that says that we need multiple implementations of thing to make it a standard. Also, we already have a standard for SQL and they could’ve just used the parts of SQLite that were compliant with that.
> Also, we already have a standard for SQL and they could’ve just used the parts of SQLite that were compliant with that.
That would probably have been the best solution, I agree. And then people would have complained about bloat again, that browsers are now required to expose a SQL engine, that they are too big, etc...
Re:Appropriate Uses for SQLite
by hungerstrike on Tuesday February 13, 2018 @01:32PM
(#16369497)
I know that you need multiple implementation of a proposed standard to make it a standard. What I was trying to (and failed to) convey is that you don’t need multiple implementations of a thing, as in SQLite itself, to let that become the standard because it’s mostly already compliant with SQL, the standard.
Anyway, there are multiple implementations of SQLite itself. It’s been implemented in JavaScript and .NET and Java as well I believe.
Re:Appropriate Uses for SQLite
by virtualwhys on Monday February 12, 2018 @10:52PM
(#16364604)
> Also, we already have a standard for SQL
I know right, there are in fact several (which may have been part of the problem). Most SQL database vendors tend to stray slightly (or not so slightly) from the SQL standard that they target; SQLite is no exception.
Just unfortunate that the browser vendors couldn't come together and build a client-side SQL based API rather than punting and implementing a very basic key/value store on top of which someone will eventually reimplement SQLite. It's like 5 or 10 years from now we'll have what we had 10+ years ago, such progress!
Re:Appropriate Uses for SQLite
by coleifer on Monday February 12, 2018 @10:48AM
(#16359369)
Yeah, Mozilla killed it out of concerns that there was only one implementation (which admittedly has some quirks)...so the question was, do we standardize SQLite and all its quirks? That didn't seem like a good option to them. Plus this is the heyday of nosql fanboi-ism, so you have all the sheep at mozilla clamoring for NoSQL in the browser. "Devs don't want SQL" Mozilla said.
It's hard to say which reason was more compelling for Mozilla, but my gut feeling is that they were betting hard on NoSQL and came up with convenient technical justifications afterwards.
Re:Appropriate Uses for SQLite
by dahart on Monday February 12, 2018 @09:48AM
(#16358866)
> Sadly, in the browser SQLite has been kicked to the curb, replaced by IndexedDB
Are you thinking of WebSQL? I don't remember a SQLite interface client-side in any browser. I might be wrong, but I thought IndexedDB was originally implemented on top of SQLite in both Chrome and Firefox. I think Chrome moved to a different backing store, but that has no bearing on the API.
Re:Appropriate Uses for SQLite
by tejasmanohar on Monday February 12, 2018 @10:00AM
(#16358965)
SQLite has been ported to the browser via Emscripten. I’ve seen it used in a couple niche projects (generally those that revolve around the SQL language), but I don’t remember it being a standard or even a primary use of SQLite.
Re:Appropriate Uses for SQLite
by kbenson on Monday February 12, 2018 @12:35PM
(#16360246)
At that point, why wouldn't you just look for some SQL to LocalStorage API/shim, since it's bound to be faster than (and likely more stable with regard to page resets than) putting a full SQL engine in place that just serializes to a LocalStorage object anyway?
Re:Appropriate Uses for SQLite
by gcp on Monday February 12, 2018 @10:44AM
(#16359330)
Are you thinking of WebSQL? I don't remember a SQLite interface client-side in any browser.
Well, yes and no. The whole problem with WebSQL was that it effectively meant using SQLite and never anything else that wasn't completely bug compatible.
So the whole problem with WebSQL was that it was, de facto, a web standard built around giving an SQLite interface client-side.
Re:Appropriate Uses for SQLite
by adrianmonk on Monday February 12, 2018 @03:45PM
(#16361942)
> so you can scrap everything on the server and redo it on the client.
Were you writing the same queries on the client and the server? Generally client and server are different moving parts of the same machine, so they don't really share a lot of code anyway. (Perhaps in some cases computations could optionally run on client or server depending on the circumstances, but security concerns limit the opportunities to do that.)
Yes, it's a pain to have to know two different technologies, but it doesn't seem like this often leads to literally needing to scrap code and rewrite it.
Re:Appropriate Uses for SQLite
by virtualwhys on Monday February 12, 2018 @06:53PM
(#16363519)
> Were you writing the same queries on the client and the server?
Well, yeah, that's the idea, isn't it? These days every language under the sun compiles to javascript, why wouldn't you want shared model, validation, queries, etc.?
> Yes, it's a pain to have to know two different technologies, but it doesn't seem like this often leads to literally needing to scrap code and rewrite it.
Currently working in an Angular 5 + TypeScript frontend with Scala on the backend, a shared nothing architecture, really blows compared to a single typed shared everything stack.
All the stats based queries run on the server? Throw 'em out and manually recalculate on the client, probably literally 100X the amount of code; less efficient and error prone to boot. Same for form validation, model layer (hey, why does this new property exist client-side and not on the server?), etc. -- it's a house of cards.
Re:Appropriate Uses for SQLite
by mwexler on Monday February 12, 2018 @08:40AM
(#16358461)
I find this page to be somewhat vague. For example, any data that revolves around dates or time may require some addition hoops to jump through since, as https://www.sqlite.org/datatype3.html points out, "SQLite does not have a storage class set aside for storing dates and/or times."
As much as I love SQLite, I find this little issue always adding an extra bit of work to deal with. Not a blocker, but useful to know. I've found that in some (rare) cases, H2 became a better solution (horrendous start time, slow data load, but ability to use real date and time types reduced errors and improved analyses).
Finally, knowing that SQLite is not right for a situation is great, but it wouldn't hurt for them to mention a potential alternative, if the devs or community know of one.
Re:Appropriate Uses for SQLite
by coleifer on Monday February 12, 2018 @10:52AM
(#16359402)
The lack of a datetime data-type hasn't been a problem in my experience. I have, at different times, stored the timestamp as an integer representing number of microseconds since the epoch, or stored in YYYY-mm-dd HH:MM:SS (which sorts lexicographically so comparisons come out right). SQLite has some built-in facilities for working with datetimes as well, along the lines of strftime, etc.
H2, being java, is in a whole different universe from SQLite...not a valid comparison in my view.
Re:Appropriate Uses for SQLite
by hinkley on Monday February 12, 2018 @11:49AM
(#16359847)
It grew up arm in arm with Hibernate, one of the better ORM wrappers. Fast enough for local use that you could do functional tests against your domain objects without using mocks, or crying.
Re:Appropriate Uses for SQLite
by module0000 on Monday February 12, 2018 @01:15PM
(#16360574)
This may not be helpful to you, but I've always used the epoch time stored in sqlite. It's blazing fast, indexes well(integers !), and is 100% convertible to any format you might need to represent.
Re:Appropriate Uses for SQLite
by duskwuff on Monday February 12, 2018 @02:46PM
(#16361418)
SQLite's INTEGER type is 64 bits, and its REAL type is double-precision (64-bit) floating point. Either type is quite sufficient for representing a time. :)
Re:Appropriate Uses for SQLite
by oneweekwonder on Monday February 12, 2018 @11:39AM
(#16359772)
Not as lightweight as sqllite. But I always wonder why firebird embedded-able server[0] does not get more love? The 32-bit so library just under 7MB and then you have a full rdbms available to your application.
Re:Appropriate Uses for SQLite
by jug on Monday February 12, 2018 @03:47PM
(#16361962)
Poorly advertised? With a lack of marketing/funding I can understand it though. But as for me, I had heard of Firebird but not the embeddable, and of course of SQLite everywhere. Maybe it's also in part because the OSS space has limited room for this niche and SQLite kind of happened to grab the largest share.
Re:Appropriate Uses for SQLite
by le-mark on Monday February 12, 2018 @08:51AM
(#16358493)
That's a valid observation, but the status of sqlite as a couple of ansi c file you can compile with or link to any damn thing is really what we're talking about here. And H2, awesome as it is, is no comparison.
Re:Appropriate Uses for SQLite
by mikeash on Monday February 12, 2018 @09:08AM
(#16358610)
I don’t know if they want to be in the business of recommending alternatives. Doing so has the unfortunate tendency of making people think it’s your problem if you use them and they don’t work out.
Re:Appropriate Uses for SQLite
by pmontra on Monday February 12, 2018 @01:36PM
(#16360772)
That bite me more than once with Sqlite. I inherited small webapp with extremely messed up datetimes because Sqlite doesn't defend its data in any way. Same column, three different formats. Ouch. It's also a problem with the original developer but the database should not tolerate such mistakes.
Re:Appropriate Uses for SQLite
by HumanDrivenDev on Monday February 12, 2018 @03:47PM
(#16361954)
One of my (many) tasks at my current position is to maintain a SQL Server database where a lot of the date times are stored as raw strings in various different formats.
If you're determined enough you can turn any datastore into a mess, though I suppose SQLite gives you less tools.
Re:Appropriate Uses for SQLite
by Cyberdog on Monday February 12, 2018 @02:50PM
(#16361446)
Though I would agree that the option to have stricter type checking in SQLite would be a good thing, I don't think your criticism is fair in this case, since it sounds like a case of poor discipline on behalf of the coder(s).
Re:Appropriate Uses for SQLite
by petepete on Monday February 12, 2018 @07:37AM
(#16358207)
> Dynamic content uses about 200 SQL statements per webpage.
Genuinely curious, what kind of page would require 200 queries? I've written plenty of moderately-complex applications and don't think I've come close to 10% of that on a single (non-crazy dashboard) page.
Re:Appropriate Uses for SQLite
by zimpenfish on Monday February 12, 2018 @07:56AM
(#16358282)
A Wordpress page with 12 posts will do 60 queries - shamusyoung.com/twentysidedtale/?p=41607
I should imagine something running on a generic ORM (ie without the optimised queries) would use considerably more (Rails, IIRC, will often do two queries to load a single object.)
Re:Appropriate Uses for SQLite
by TimWolla on Monday February 12, 2018 @07:42AM
(#16358224)
It doesn't say "require". Possibly they are selecting the same information over and over again, instead of passing it along the different functions, because it is fast enough.
Re:Appropriate Uses for SQLite
by krylon on Monday February 12, 2018 @07:52AM
(#16358266)
Even so, querying data once and reusing it is usually the more maintainable option and probably means less code, too. It is not only about performance.
(And just to be clear, for my use cases, I have been very happy with SQLite's performance.)
Re:Appropriate Uses for SQLite
by hinkley on Monday February 12, 2018 @12:02PM
(#16359968)
This requires an information architecture and these days those seem to be harder to come by. I think we may be favoring velocity over Stop and Think time a bit too much.
(If you’re thinking, “no dummy, just use caching” then I regret to inform you that cache invalidation and eviction without an info architecture are as hard if not harder. And caching bugs make you look like a clown in front of your bosses. Because the whack-a-mole process never looks professional)
Re:Appropriate Uses for SQLite
by hinkley on Monday February 12, 2018 @12:13PM
(#16360061)
When the app is being dumb, people put caching in to hide the data flow problems. When the data flow problems are hidden, they multiply, and then turning it off again is highly improbable.
Caching has sucked all the fun out of too many projects I’ve been associated with.
Do cache headers and cache busting first. You can always put a transparent cache in front of your REST endpoints in prod. After debugging the browser caching. Don’t write your own caching implementation. Please.
Re:Appropriate Uses for SQLite
by Cyberdog on Monday February 12, 2018 @02:59PM
(#16361505)
Unoptimized Drupal sites of moderate complexity with caches disabled (a use case only intended for development workflows, but it's of course quite simple to run a live site under such situations) can easily invoke 200-400 queries per page load.
Re:Appropriate Uses for SQLite
by gaius on Monday February 12, 2018 @08:55AM
(#16358525)
Bulk loading a CSV or similar into SQLite for analysis with SQL statements is an incredibly productive technique. With a few lines of code you can load any log file, load and merge multiple logs by timestamp, whatever you like
Re:Appropriate Uses for SQLite
by joshvm on Monday February 12, 2018 @08:58AM
(#16358545)
I use SQLite for logging data from a bunch of distributed sensors. The server (RPi) sends a measurement request over MQTT, the sensors reply and the server logs each response grouped by the request timestamp. Seems to work very well for 25 sensors on the same WLAN and for continuous monitoring (about 100 measurements per minute).
This can then be displayed to the user via a web server which has read-only access to the measurement db.
Re:Appropriate Uses for SQLite
by michaelmcmillan on Monday February 12, 2018 @11:00AM
(#16359465)
I run a complex CRM that handles 1.2 million USD in transactions every year. It is running sqlite with a simple in-memory cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is simple and fast enough.
Re:Appropriate Uses for SQLite
by pvg on Monday February 12, 2018 @01:27PM
(#16360679)
If you assume each of these transactions is a dollar, you get a bit under 0.04 transactions/sec on average. If peak load is 25x average, that would come out to 1/sec.
Re:Appropriate Uses for SQLite
by drej on Monday February 12, 2018 @03:14PM
(#16361635)
I may have gone a bit beyond what SQLite is useful for, but I just wanted to try this. I built an experimental application-level file system (so not FUSE-like, you can't mount this, it's just within an application).
Re:Appropriate Uses for SQLite
by pedrocr on Monday February 12, 2018 @11:34AM
(#16359736)
I've recently used SQLite to store some metadata for a FUSE filesystem that pretends all your data is available but that just keeps a local cache and fetches it from the server on demand[1]. Initially performance was atrocious because INSERT is really slow if you don't batch several into transactions. I've worked around it with some application caching but there does seem to be a large gap between what the postgres engine can do and what sqlite can do. I definitely don't want to depend on a postgres setup for this application but it would be nice to have an SQL database for app use that had a bit more write performance. It seems that when you want that you end up going to LevelDB or LMDB and losing the niceness of an SQL interface.
Re:Appropriate Uses for SQLite
by phaedrus on Monday February 12, 2018 @03:20PM
(#16361684)
A lesson I've learned the hard way, from using Sqlite in many apps I've written, is that if you need any kind of insert performance you can't abstract away the transaction model, and retrofitting transactions onto an application which previously ignored them is not easy either. You really have to design for it up front.
Mainly this is because there is at most one, global transaction per Sqlite database (in other words you can't nest transactions), but also because the optimum window to gather up inserts for a bulk transaction might not match up well with the logical function call structure of you program. I recently learned about the Sqlite SAVEPOINT feature; this might help implement nested-transactions, but cannot help with the second problem.
For example, what if you made a "logging.db" file for a GUI application, and you're grouping writes to a log table in batches of 50 for each INSERT transaction. What happens if your GUI app gets to 48 inserts but then stops receiving user interaction events? You'd probably still want those 48 inserts to show up (eventually) in the logging.db rather than just being "lost" if the app subsequently crashes. So what do you do? Register a timer event to create a watchdog to flush the transaction if enough time goes by? OTOH what if a subroutine of your app is actually at that moment performing a lengthy "real" (logical) transaction and wouldn't want it flushed? Or maybe the application should use different database files for logging versus data where consistency is vital (so that a database can be opened either in "accumulate INSERTs" mode or "real logical transactions" mode)?
My point is not the detail of how to answer those questions; it is just to highlight that these are questions which have to be thought about at a whole-application architecture level.
Re:Appropriate Uses for SQLite
by fauigerzigerk on Monday February 12, 2018 @09:19AM
(#16358678)
Sadly the lack of a decimal type complicates monetary calculations sufficiently for me to stay with postgresql even for small projects that could easily be done with SQLite.
Re:Appropriate Uses for SQLite
by asaph on Monday February 12, 2018 @09:33AM
(#16358766)
Store your monetary values as cents instead of dollars and use integers. Convert to dollars in the presentation layer. My assumption here is that you're dealing in US currency but the same idea applies to any currency; use integers, not decimals.
Re:Appropriate Uses for SQLite
by fauigerzigerk on Monday February 12, 2018 @09:40AM
(#16358807)
Say you have a savings account with an interest rate of 0.125% and any interest has to be converted into another currency for taxation purposes at a rate of 0.88619.
Working around these sorts of problems with integers is just not worth the trouble.
I can understand the convenience if you are setting up a simple online store, but more complicated cases (like the one in your example) absolutely need a proper handling of decimal values.
Re:Appropriate Uses for SQLite
by anarazel on Monday February 12, 2018 @11:27AM
(#16359677)
> Can you explain to me what particular cases postgresql's decimal type cannot handle properly _and_ conveniently?
I entirely agree that numeric is a good solution for this case.
But to try to answer your question nevertheless: I'd say there is potential for storage density and math performance issues. You really need to have a number of numerics for each row, and a lot of rows to make storage density matter. If you do a lot of aggregates over numerics you definitely can notice the CPU overhead of them over say a plain float. But most of the time that doesn't matter.
Re:Appropriate Uses for SQLite
by haimez on Monday February 12, 2018 @10:23AM
(#16359145)
Yes it is. You convert to a big decimal representation, compute the tax/interest and then intentionally round back to an integer format using the appropriate rounding mode.
Re:Appropriate Uses for SQLite
by haimez on Thursday February 15, 2018 @10:40PM
(#16389683)
Yes, it can. The case and point is that you need to control where those moments of imprecision happen, and using fixed point a fixed point (often integer/long) representation is absolutely worth the effort because it's not much effort at all and you DEFINITELY care about when your money calculations involved rounding.
Re:Appropriate Uses for SQLite
by jenscow on Monday February 12, 2018 @11:58AM
(#16359928)
If that's the only reason to use a "real" database, then what you have is a 100MB+ multi-process (and potentially costly) library to perform a few calculations.
(I don't disagree with you, just answering your question)
Re:Appropriate Uses for SQLite
by fauigerzigerk on Monday February 12, 2018 @01:03PM
(#16360458)
Of course there are many other considerations, but there is a class of small finance related multi-tenancy systems for which monetary calculations are key and either a database server or a database library can potentially make sense.
Resource consumption isn't an issue at all. 100MB+ is less than loading an average news website in a browser and it costs almost nothing. The reason why I would have wanted to use SQLite sometimes is that it makes it easy to distribute and run the entire app on premises if needed. People are rightly concerned about losing access to web based software.
Re:Appropriate Uses for SQLite
by haimez on Thursday February 15, 2018 @10:44PM
(#16389696)
There are also also many use cases where you don't want to "store" something at all, just compute the value (eg: a shopping cart that is not yet a purchase), and you don't want to to either 1. duplicate implementations or 2. wait for a network request to your database to complete.
Re:Appropriate Uses for SQLite
by dragonwriter on Monday February 12, 2018 @11:53AM
(#16359885)
You could just store decimal strings and use application-language decimal functions to operate on them, even in queries, since it's fairly trivial to use application-language functions as SQLite (scalar or aggregate) database functions, at least in most decent SQLite drivers I've seen. It's one of the advantages of an in-process database.
Re:Appropriate Uses for SQLite
by BareNakedCoder on Tuesday February 13, 2018 @01:20PM
(#16369376)
Strings don't work well with numeric aggregate SQL functions like SUM or MAX. Also, won't work well with comparisons or sorting: "111.00" is less than "9.99" string-wise (unless you store all numerics with a bunch of leading "0"; but then that takes a lot more space).
Re:Appropriate Uses for SQLite
by fauigerzigerk on Monday February 12, 2018 @09:43AM
(#16358835)
As I said in my reply to asaph, it doesn't work well with interest rates and exchange rates. Also, it simply adds to the mental load in many places (e.g ad hoc queries)
Re:Appropriate Uses for SQLite
by aldanor on Monday February 12, 2018 @10:09AM
(#16359036)
The proper way to handle this is fixed-point representation (whether the DB natively supports this or not). E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges. A typical scaling factor is 10^8, so if the exchange wants to tell you that the price is now 101.27, they send you an int64 value of 10127000000. You may then choose to take it further and store e.g. in 128-bit decimal.
Of course, there's an issue of how to handle mul/div (i.e., rounding/truncation), but that's a separate question -- at least you'd be making decisions yourself rather than relying on how IEEE-754 floats work.
Some DBs like PG, do, as a matter of fact, support decimals natively which makes things quite a bit simpler.
Re:Appropriate Uses for SQLite
by KMag on Monday February 12, 2018 @01:13PM
(#16360545)
Certainly for decimalized exchanges, binary floating point is the wrong choice. (On a side note, it's also the wrong choice for spread sheets, at least as the default. The average freshly graduated accountant can have a reasonable grasp on decimal floating point rounding after a 5 minute explanation, whereas most professional software engineers with a decade of experience have a very weak understanding of IEEE 754 binary floating point.)
> E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges.
That's true of NASDAQ's OUCH, but some (many?) other protocols use decimal floating point.
NYSE's UTP Direct[0] (See section 4.15) Is not fixed point, but actually their own decimal floating point system with a 32-bit significand and single ASCII character exponent ("0" to "6", indicating negated base 10 exponent, not allowing "5").
FIX SBE uses an 8 byte significand and a 1 byte (power-of-10) exponent, if I remember correctly.
It's a bit unfortunate that (as far as I know) there aren't any major messaging formats supporting IEEE 754-2008 decimal64 values.
Re:Appropriate Uses for SQLite
by anarazel on Monday February 12, 2018 @11:00AM
(#16359463)
16383 digits ought to be enough for everyone:
"up to 131072 digits before the decimal point; up to 16383 digits after the decimal point"
Non jokingly: I think precision wise that's really plenty enough for pretty much any halfway realistic usecase (no, don't do public key crypto using SQL datatypes). There's some issues with the numeric / decimal type, but lack of potential precision isn't one of them.
> Ruby provides built-in support for arbitrary precision integer arithmetic. For example:
>
> 4213 -> 1265437718438866624512
>
> BigDecimal provides similar support for very large or very accurate floating point numbers.
My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.
Re:Appropriate Uses for SQLite
by lsaferite on Monday February 12, 2018 @03:21PM
(#16361694)
What is the typical use-case of a unit smaller than the smallest tradable currency unit? Mind you, I understand there are smaller units than the circulated currency (For the USD you have a mill which is 1/10 of a cent).
Re:Appropriate Uses for SQLite
by hajile on Monday February 12, 2018 @10:59AM
(#16359452)
The computer has only integers and floats for purposes of calculation (except for some IBM units implementing IEEE fixed point). In storage though, there are only whole numbers to which we assign meaning.
Store your as a single integer then write a small layer that converts that integer into decimal (that's all a language or DB does anyway). Alternatively, use two entries in the database for each side of the decimal if you want even more precision or store it as a string if you want arbitrary precision.
Re:Appropriate Uses for SQLite
by prewett on Monday February 12, 2018 @01:34PM
(#16360746)
I'm curious how 64-bits would be tight for monetary data. A 64-bit integer is +/- 9e18, which is so big I don't even know how to actually say that number. Given that economies are only in the trillions, you have at least three orders of magnitude left when representing the largest amounts of money on the planet. Anything smaller would be a piece of cake.
Also, seems like the obvious thing to do to store money in an integer would be to store it in pennies. Or if the numbers get too big, do what corporate financial reports do and use thousands.
Re:Appropriate Uses for SQLite
by qznc on Monday February 12, 2018 @04:39PM
(#16362510)
Pennies (2 decimals) is not fine enough. Exchanges and other financial services use 4, 6, or maybe even 8 decimals. With 6 decimals, a few trillion is the limit.
If you implement multiplication and division with integer arithmetic, you need the additional decimals to keep the error small. See https://en.wikipedia.org/wiki/Salami_slicing
Re:Appropriate Uses for SQLite
by eesmith on Monday February 12, 2018 @04:31PM
(#16362426)
The largest Zimbabwean banknote during hyperinflation was Z$100,000,000,000,000 or 1E14. I assume they printed more than 10,000 of those.
I recall something about Oracle sending people down there to help with the bank or perhaps government databases deal with those numbers. I can't find confirmation of that, but https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe says "Computers could not handle the amount of zeros such that other forms of money had to be used to act as normal money (bearer’s cheques)."
Re:Appropriate Uses for SQLite
by fauigerzigerk on Monday February 12, 2018 @04:43PM
(#16362545)
64 bits can be tight if you have to set aside, say, 7 digits for decimal places in order to perform a lot of divisions/multiplications by small numbers, like exchange rates, without rounding errors adding up.
But if you sacrifice 7 places you can no longer represent the market cap of Toyota Motor at the Tokyo stock exchange (24 trillion Yen) or the US GDP (~19 trillion USD).
So if you don't have a data type that can represent all of these quantities as well as the results of any calculations up to the required precision then your code will become a lot more complex and error prone.
Re:Appropriate Uses for SQLite
by fauigerzigerk on Monday February 12, 2018 @11:58AM
(#16359927)
A few words of explanation wouldn't hurt. Are you saying that I shouldn't ever do monetary calculations in the database at all? If so then I have to disagree.
Re:Appropriate Uses for SQLite
by sigzero on Monday February 12, 2018 @04:23PM
(#16362330)
No, they are giving ways to do it outside of the db. You really answered your own bias on this. You want the db to handle it, so you wouldn't use sqlite in the first place.
Re:Appropriate Uses for SQLite
by tracker1 on Tuesday February 13, 2018 @04:19AM
(#16365789)
Could look at Firebird if you want something embedded still. Can even scale to use a server mode. I wrote a distributed system about a decade and a half ago using Firebird, worked pretty well.
Re:Appropriate Uses for SQLite
by andyidsinga on Wednesday February 14, 2018 @06:45AM
(#16375102)
I've used sqlite as an index for S3 objects ..and stored the DB file in S3 itself. It was very practical to use, and a handy pattern to apply to object storage where the number of objects to be indexed for a given time frame was reasonable for sqlite (I had in 100k range).
Also - easy to dump the sqlite db into a SQL text file then import into something like postgres :)
Re:Appropriate Uses for SQLite
by Decabytes on Monday February 12, 2018 @03:58PM
(#16362064)
I love SQLite and am currently working on a project that makes extensive use of it. In the book "The Definitive Guide to SQLite" it says that SQLite can scale to 2TB of data Looking at the website it says
An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes).
That's ludicrous. I wonder if anyone has come close to that in day to day use.
Re:Appropriate Uses for SQLite
by fnord123 on Monday February 12, 2018 @04:24PM
(#16362352)
Almost certainly not seeing as the single hw largest device is currently 12TB. Someone would have to pool together 10+ (and more for redundancy) just to store the single file.
Re:Appropriate Uses for SQLite
by odonnellryan on Monday February 12, 2018 @10:38AM
(#16359266)
SQLite can probably handle a lot of SELECT queries at once, but only one process can be writing to the database at once.
I think it's a hard question to answer, "what's the upper limit on SQLite?"
Better question would probably be, "We have this app, what database should I use?"
I use SQLite for a lot of smaller apps. Applications that have a focus on single-business, low-volume use.
They might do a good amount of computation and data-fetching, but they don't make heavy use of the database.
If you need features Postgres has, use that. If you need features MS SQL has, use that... if you don't need anything special, think about using SQLite :)
Re:Appropriate Uses for SQLite
by andyjpb on Monday February 12, 2018 @12:33PM
(#16360228)
I've benchmarked SQLite on my laptop at just north of 20,000 queries (SELECTs) per second for the workload I was interested in at the time.
However, the limit depends heavily on your workload. If your working set (the data that all the simultaneous queries require) fits in memory then you'll be limited by memory bandwidth or the language you're calling from: GC stalls and query preparation can really hammer the throughput; if you're doing any actual work with the results of the query then I'd hope that time spent there would dominate the data access.
If your data doesn't fit in memory then you'll be limited by the speed of your disk system. If you have a fixed working set then you can increase the performance by adding RAM until the working set fits again.
If you have a streaming workload then you'll never be able to fit it in RAM (by definition) so you'll be limited by the speed of the IO systems involved; either network or disk.
If things are still too busy then stuff will start to queue up. This will cause each individual process to run slower as it'll be contending with the others. This, in turn will cause more incoming work to queue up. At some point the machine will run out of some other resource (memory for incoming connections, network sockets, space in queues, etc). How the system responds to that depends on how it's configured but, traditionally, your system is no longer providing service.
Re:Appropriate Uses for SQLite
by zxcvbn4038 on Monday February 12, 2018 @02:28PM
(#16361264)
SQLite only allows one writer at a time, so depending on your access pattern that might be an issue, but if your site is getting a million requests a day then your only processing an average of 11.5 requests per second, and what percentage of them are writes? Front your app with varnish, nginx (w/cache), or cloudflare and absorb some additional load. Though as far as I can tell www.sqlite.com is running on a single linode instance with no fancy hacks.
Re:Appropriate Uses for SQLite
by karmicthreat on Monday February 12, 2018 @07:02AM
(#16358110)
Every once in a while I have sqlite eat it, and end up with a corrupt db file. About as often as mysql really. I wish I had some more serious automatic recovery in sqlite. I have to have a boot script check the db, export everything to an sql file if the db is corrupt then create a new file.
Re:Appropriate Uses for SQLite
by dijit on Monday February 12, 2018 @07:23AM
(#16358163)
That does not mirror my experience, I hate to be the guy suggesting this but have you considered the fact that you're using it wrong?
What I mean by that is that SQLite really is a replacement for open() and as such is not multi-thread compatible. If you're doing a lot of work that's corrupting you can also turn up the level of binary logging until recovery becomes easy enough.
I'm using SQLite effectively on a few thousand multi-threaded windows machines and I've encountered corruption exactly once- which was when a hardware raid had degraded non-gracefully.
Re:Appropriate Uses for SQLite
by jpeeler on Monday February 12, 2018 @11:16AM
(#16359592)
My understanding is that using SQLite is completely fine in a multithreaded architecture, but you have to ensure that the simultaneous database operations match the correct threading mode:
I also believe that the common way of gaining performance while protecting safety is to use multi-thread mode with connection pooling. The idea being that your application can use different database connections to achieve multiple writes without relying on the overhead of additional locking required within SQLite itself for serialized mode.
Would be interesting if anybody has performance metrics that backs up what I said with data converging on an optimum number of database connections.
Re:Appropriate Uses for SQLite
by karmicthreat on Monday February 12, 2018 @08:04AM
(#16358310)
Oh I guarantee I am doing something wrong. This is running on some crappy sandisk sd cards as well. So it is entirely possible that the corruption I run into is just an issue with the sd card.
In a similar vein, VueJS has a comprehensive exploration/comparison of itself compared to other frameworks[0].
I really appreciate open source maintainers that have the humility to compare their work fairly with others within the domain while still taking pride in their own acheivements.
[0]: https://vuejs.org/v2/guide/comparison.html
For example https://github.com/sapcc/swift-http-import#do-not-use-if -- This particular instance was born out of watching users bending over backwards to use the software even though standard software does the job just fine or better in their concrete case.
So while I have no idea about the status of vue.js regarding that topic, I belief that even if they are just in a normal state it is a huge positive aspect in comparison to riot.js, but the whole issue might be a little subjective too.
SQlite on the other hand doesn't have such a process so, every client has to open the database file itself, process the SQL and close/write the file again.
So the biggest difference is, that if you want to use SQLite, you will have to limit your database operation to one or fewer ;-) processes to not get into a situation where one process has to wait until the other finished writing the database file to the filesystem just to read it again in the next moment. Otherwise your filesystem and the continuing reading and writing of the database file will make the whole thing very slow.
But as long as you have only one process using the SQLite file and keep it open for a while you will see a comparable performance to other database systems. Actually, SQLite can be very fast if you do use it in a sane way.
So the limiting factor is not the degree of concurrency, but the amount of write-activity. Workloads that mostly read and only occasionally write to the database work well with SQLite.
I say this not because SQLite is not good. It's _great_ for what it intends to do which is, as the linked page says, offer an excellent alternative to fopen(). It doesn't really offer an alternative to a full DBMS, though. There may be some gray area between a "heavy fopen()" workload and a "light DBMS" workload where there might be a legitimate case to be made for SQLite over a DBMS, but standard OLTP or OLAP workloads with any degree of concurrency are almost uncertainly outside of SQLite's intended use case.
There was a recent conversation on a similar topic with input from Richard Hipp, SQLite's author, here: https://news.ycombinator.com/item?id=15303662
In a usual quassel (irc bouncer that can, optionally, store logs in SQLite) setup, you’ll have between 1 and 10 writers, constantly writing hundreds of IRC messages a second to SQLite, while you’ll also have readers that need to respond with low latency for queries loading hundredthousands of messages, including those that were just written.
The result is that SQLite gets so slow that the writers queue up so much work that the IRC connection times out.
SQLite is absolutely useless for multiple writers.
I don't really quite agree with this, but would say that with SQLite it's more important be be aware of how concurrency is being used with regards to the database, in comparison to something like MySQL or PostgreSQL. As far as IRC logs, I think the important thing (that would almost certainly hamstring any SQL database that's actually being ACID) is to not use autocommit on inserts, and to not use a transaction for every single insert (which is possibly what's happening). I wrote a plugin for ZNC to do exactly what you're doing above, and by batching my writes (either waiting 15 seconds or until n (where n is maybe 1000 or more) messages are queued), I've gotten superb performance out of SQLite3 running with a WAL journal, with the trade off being a little bit of data might be lost if your bouncer happens to fall down in an unhandled way. I was also able to get really good read performance without hurting raw write performance by leveraging partial indices, which something like MySQL doesn't give you.
With SQLite, it’s basically useless.
In the end, it's a matter of testing. Also, with faster drive options (Optane and whatever is next), it could very well be a decent option. It all depends.
Frankly, for logging, I'd lean towards elasticsearch (ELK) these days, which is about the most write heavy situation you're likely to see. Depending on the scaling Mongo, RethinkDB, ElasticSearch and Cassandra can all be better solutions than RDBMS. I'd still reach for SQLite or PostgreSQL first depending on the situation though.
https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
Making a backup before trying something risky (and then restoring the backup when something screws up) is similarly trivial.
Maybe to some of you it's not worth that much, but it's a legitimate timesaver in my opinion.
But you wouldn't want a single file format on enterprise level systems where enterprise level performance and stability is required. At the very least, you'd want your data and transaction log files to be different files on different disks. Especially when IO throughput is important.
Also, since DBs tend to have important data on them, being able to copy a file (db) and move it anywhere you want isn't much of a selling point on serious systems.
Having said that, sqlite is great for application/client side storage.
It's deeply disappointing to not be able to use SQL on both client and server. Yes, there's Lovefield, which employs its own DSL that has nothing to do with SQL, so you can scrap everything on the server and redo it on the client.
Or just give up and go with NoSQL on client and server, that seems to be what the browser vendors are implicitly pushing developers toward.
SQLite hadn't been kicked to the curb, the WebSQL API whose “standard” was essentially “act like version x of SQLite” was kicked to the curb.
The concept of WebSQL was good, but the standard itself wasn't great, and the absence of any intent to implement from other vendors or intent to improve the standard from those who had embraced it made it a dead-end standard.
Nobody said what was so wrong about that.
I just don’t think there’s any rule that says that we need multiple implementations of thing to make it a standard. Also, we already have a standard for SQL and they could’ve just used the parts of SQLite that were compliant with that.
> I just don’t think there’s any rule that says that we need multiple implementations of thing to make it a standard
There are rules that say that we need multiple implementations of thing to make it a standard:
* The IETF requires it to be accepted as "Internet standard", ie the highest form: https://en.wikipedia.org/wiki/Internet_Standard#Standardizat...
* The W3C strongly desires that proposed standards have multiple, independent implementations to advance to the state of "Proposed reccomendation": https://www.w3.org/2005/10/Process-20051014/tr.html#cfr
> Also, we already have a standard for SQL and they could’ve just used the parts of SQLite that were compliant with that.
That would probably have been the best solution, I agree. And then people would have complained about bloat again, that browsers are now required to expose a SQL engine, that they are too big, etc...
Anyway, there are multiple implementations of SQLite itself. It’s been implemented in JavaScript and .NET and Java as well I believe.
I know right, there are in fact several (which may have been part of the problem). Most SQL database vendors tend to stray slightly (or not so slightly) from the SQL standard that they target; SQLite is no exception.
Just unfortunate that the browser vendors couldn't come together and build a client-side SQL based API rather than punting and implementing a very basic key/value store on top of which someone will eventually reimplement SQLite. It's like 5 or 10 years from now we'll have what we had 10+ years ago, such progress!
It's hard to say which reason was more compelling for Mozilla, but my gut feeling is that they were betting hard on NoSQL and came up with convenient technical justifications afterwards.
Are you thinking of WebSQL? I don't remember a SQLite interface client-side in any browser. I might be wrong, but I thought IndexedDB was originally implemented on top of SQLite in both Chrome and Firefox. I think Chrome moved to a different backing store, but that has no bearing on the API.
[0]: https://github.com/kripken/sql.js/
https://hacks.mozilla.org/2012/02/saving-images-and-files-in...
Well, yes and no. The whole problem with WebSQL was that it effectively meant using SQLite and never anything else that wasn't completely bug compatible.
So the whole problem with WebSQL was that it was, de facto, a web standard built around giving an SQLite interface client-side.
Yes, exactly, but Web SQL uses SQLite as the backing store. IndexedDB is its own thing, a standalone key/value store, nothing to do with SQLite.
Were you writing the same queries on the client and the server? Generally client and server are different moving parts of the same machine, so they don't really share a lot of code anyway. (Perhaps in some cases computations could optionally run on client or server depending on the circumstances, but security concerns limit the opportunities to do that.)
Yes, it's a pain to have to know two different technologies, but it doesn't seem like this often leads to literally needing to scrap code and rewrite it.
Well, yeah, that's the idea, isn't it? These days every language under the sun compiles to javascript, why wouldn't you want shared model, validation, queries, etc.?
> Yes, it's a pain to have to know two different technologies, but it doesn't seem like this often leads to literally needing to scrap code and rewrite it.
Currently working in an Angular 5 + TypeScript frontend with Scala on the backend, a shared nothing architecture, really blows compared to a single typed shared everything stack.
All the stats based queries run on the server? Throw 'em out and manually recalculate on the client, probably literally 100X the amount of code; less efficient and error prone to boot. Same for form validation, model layer (hey, why does this new property exist client-side and not on the server?), etc. -- it's a house of cards.
As much as I love SQLite, I find this little issue always adding an extra bit of work to deal with. Not a blocker, but useful to know. I've found that in some (rare) cases, H2 became a better solution (horrendous start time, slow data load, but ability to use real date and time types reduced errors and improved analyses).
Finally, knowing that SQLite is not right for a situation is great, but it wouldn't hurt for them to mention a potential alternative, if the devs or community know of one.
H2, being java, is in a whole different universe from SQLite...not a valid comparison in my view.
It's dual-licensed under the Mozilla Public License and the Eclipse Public License.
[0] https://www.firebirdsql.org/pdfmanual/html/fbmetasecur-embed...
Another perspective; embeddable py 10MB, electron app 50MB[0], free pascal exe 1.5MB.
So 7MB for a RDBMS seems reasonable, but not comparable to SQLite as you pointed out.
[0]: https://github.com/electron/electron/issues/2003#issuecommen...
If you're determined enough you can turn any datastore into a mess, though I suppose SQLite gives you less tools.
https://excel.uservoice.com/forums/304921/suggestions/105490...
Genuinely curious, what kind of page would require 200 queries? I've written plenty of moderately-complex applications and don't think I've come close to 10% of that on a single (non-crazy dashboard) page.
I should imagine something running on a generic ORM (ie without the optimised queries) would use considerably more (Rails, IIRC, will often do two queries to load a single object.)
(And just to be clear, for my use cases, I have been very happy with SQLite's performance.)
(If you’re thinking, “no dummy, just use caching” then I regret to inform you that cache invalidation and eviction without an info architecture are as hard if not harder. And caching bugs make you look like a clown in front of your bosses. Because the whack-a-mole process never looks professional)
Caching has sucked all the fun out of too many projects I’ve been associated with.
Do cache headers and cache busting first. You can always put a transparent cache in front of your REST endpoints in prod. After debugging the browser caching. Don’t write your own caching implementation. Please.
I've done similar with node.js streams pretty easily.
This can then be displayed to the user via a web server which has read-only access to the measurement db.
https://github.com/kokes/sqlfs
Haven't had much time to finish it, but it's been fun to play with.
I appreciate that there's just a hint of snark there. YAGNI, after all, is one of the most often-forgotten important principles in this industry.
[1] https://github.com/pedrocr/syncer
Mainly this is because there is at most one, global transaction per Sqlite database (in other words you can't nest transactions), but also because the optimum window to gather up inserts for a bulk transaction might not match up well with the logical function call structure of you program. I recently learned about the Sqlite SAVEPOINT feature; this might help implement nested-transactions, but cannot help with the second problem.
For example, what if you made a "logging.db" file for a GUI application, and you're grouping writes to a log table in batches of 50 for each INSERT transaction. What happens if your GUI app gets to 48 inserts but then stops receiving user interaction events? You'd probably still want those 48 inserts to show up (eventually) in the logging.db rather than just being "lost" if the app subsequently crashes. So what do you do? Register a timer event to create a watchdog to flush the transaction if enough time goes by? OTOH what if a subroutine of your app is actually at that moment performing a lengthy "real" (logical) transaction and wouldn't want it flushed? Or maybe the application should use different database files for logging versus data where consistency is vital (so that a database can be opened either in "accumulate INSERTs" mode or "real logical transactions" mode)?
My point is not the detail of how to answer those questions; it is just to highlight that these are questions which have to be thought about at a whole-application architecture level.
Working around these sorts of problems with integers is just not worth the trouble.
https://stackoverflow.com/questions/3730019/why-not-use-doub...
I can understand the convenience if you are setting up a simple online store, but more complicated cases (like the one in your example) absolutely need a proper handling of decimal values.
I entirely agree that numeric is a good solution for this case.
But to try to answer your question nevertheless: I'd say there is potential for storage density and math performance issues. You really need to have a number of numerics for each row, and a lot of rows to make storage density matter. If you do a lot of aggregates over numerics you definitely can notice the CPU overhead of them over say a plain float. But most of the time that doesn't matter.
(I don't disagree with you, just answering your question)
Resource consumption isn't an issue at all. 100MB+ is less than loading an average news website in a browser and it costs almost nothing. The reason why I would have wanted to use SQLite sometimes is that it makes it easy to distribute and run the entire app on premises if needed. People are rightly concerned about losing access to web based software.
Serious question, what's the proper way to handle this case? I'm having hard time to believing it's IEEE-754 floats.
Of course, there's an issue of how to handle mul/div (i.e., rounding/truncation), but that's a separate question -- at least you'd be making decisions yourself rather than relying on how IEEE-754 floats work.
Some DBs like PG, do, as a matter of fact, support decimals natively which makes things quite a bit simpler.
> E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges.
That's true of NASDAQ's OUCH, but some (many?) other protocols use decimal floating point.
NYSE's UTP Direct[0] (See section 4.15) Is not fixed point, but actually their own decimal floating point system with a 32-bit significand and single ASCII character exponent ("0" to "6", indicating negated base 10 exponent, not allowing "5").
FIX SBE uses an 8 byte significand and a 1 byte (power-of-10) exponent, if I remember correctly.
It's a bit unfortunate that (as far as I know) there aren't any major messaging formats supporting IEEE 754-2008 decimal64 values.
[0]https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDir...
"up to 131072 digits before the decimal point; up to 16383 digits after the decimal point"
Non jokingly: I think precision wise that's really plenty enough for pretty much any halfway realistic usecase (no, don't do public key crypto using SQL datatypes). There's some issues with the numeric / decimal type, but lack of potential precision isn't one of them.
From the docs for [`BigDecimal`](http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigD...):
> Ruby provides built-in support for arbitrary precision integer arithmetic. For example: > > 4213 -> 1265437718438866624512 > > BigDecimal provides similar support for very large or very accurate floating point numbers.
My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.
> Ruby provides built-in support for arbitrary precision integer arithmetic.
[I'm also assuming that it's really arbitrary precision and not just 'really big but limited' precision.]
Tracking transactional values for large stock and currency trades would be an example. That said, could use integers to represent arbitrary precision.
Store your as a single integer then write a small layer that converts that integer into decimal (that's all a language or DB does anyway). Alternatively, use two entries in the database for each side of the decimal if you want even more precision or store it as a string if you want arbitrary precision.
Well, that's my point. Postgres does it for me. SQLite doesn't. I'm lazy.
Also, SQLite's largest integer type is 64 bits. That's awfully tight for some use cases.
Also, seems like the obvious thing to do to store money in an integer would be to store it in pennies. Or if the numbers get too big, do what corporate financial reports do and use thousands.
If you implement multiplication and division with integer arithmetic, you need the additional decimals to keep the error small. See https://en.wikipedia.org/wiki/Salami_slicing
I recall something about Oracle sending people down there to help with the bank or perhaps government databases deal with those numbers. I can't find confirmation of that, but https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe says "Computers could not handle the amount of zeros such that other forms of money had to be used to act as normal money (bearer’s cheques)."
But if you sacrifice 7 places you can no longer represent the market cap of Toyota Motor at the Tokyo stock exchange (24 trillion Yen) or the US GDP (~19 trillion USD).
So if you don't have a data type that can represent all of these quantities as well as the results of any calculations up to the required precision then your code will become a lot more complex and error prone.
Also - easy to dump the sqlite db into a SQL text file then import into something like postgres :)
I think it's a hard question to answer, "what's the upper limit on SQLite?"
Better question would probably be, "We have this app, what database should I use?"
I use SQLite for a lot of smaller apps. Applications that have a focus on single-business, low-volume use.
They might do a good amount of computation and data-fetching, but they don't make heavy use of the database.
If you need features Postgres has, use that. If you need features MS SQL has, use that... if you don't need anything special, think about using SQLite :)
However, the limit depends heavily on your workload. If your working set (the data that all the simultaneous queries require) fits in memory then you'll be limited by memory bandwidth or the language you're calling from: GC stalls and query preparation can really hammer the throughput; if you're doing any actual work with the results of the query then I'd hope that time spent there would dominate the data access.
If your data doesn't fit in memory then you'll be limited by the speed of your disk system. If you have a fixed working set then you can increase the performance by adding RAM until the working set fits again.
If you have a streaming workload then you'll never be able to fit it in RAM (by definition) so you'll be limited by the speed of the IO systems involved; either network or disk.
If things are still too busy then stuff will start to queue up. This will cause each individual process to run slower as it'll be contending with the others. This, in turn will cause more incoming work to queue up. At some point the machine will run out of some other resource (memory for incoming connections, network sockets, space in queues, etc). How the system responds to that depends on how it's configured but, traditionally, your system is no longer providing service.
"SQLite on Heroku" [1], essentially: don't do it, switch to postgres.
I suppose it would work with AWS Beanstalk and EBS.
[1] https://devcenter.heroku.com/articles/sqlite3
What I mean by that is that SQLite really is a replacement for open() and as such is not multi-thread compatible. If you're doing a lot of work that's corrupting you can also turn up the level of binary logging until recovery becomes easy enough.
I'm using SQLite effectively on a few thousand multi-threaded windows machines and I've encountered corruption exactly once- which was when a hardware raid had degraded non-gracefully.
https://sqlite.org/threadsafe.html
I also believe that the common way of gaining performance while protecting safety is to use multi-thread mode with connection pooling. The idea being that your application can use different database connections to achieve multiple writes without relying on the overhead of additional locking required within SQLite itself for serialized mode.
Would be interesting if anybody has performance metrics that backs up what I said with data converging on an optimum number of database connections.