Although SQLite's memory overhead is tiny by the standards of modern desktops/servers/phones, it's still much too big for 8-bit microcontrollers like the Arduino. If you want to build something that costs tens of cents instead of tens of dollars, SQLite is way too big.
For example, https://www.sqlite.org/about.html claims that SQLite can run in "very little heap (100KiB)"; this project targets an Atmel ATmega2560 which has only 8KiB of RAM.
(I doubt "5 times smaller than SQLite" is a useful comparison; the limiting factor is probably RAM consumption, as opposed to executable code which can be stored in cheaper non-volatile memory.)
I thought hard what to put in the subject (limited length, you know) - code size or working RAM size, but figured if I write "can run queries in 1KB", people won't get it ;-).
Since the size comparison to SQLite in the title has made the thread mostly be an argument about size comparisons and SQLite, let's try the other phrase and see if we fare better.
(Submitted title was "LittleD – SQL database for IoT, 5 times smaller than SQLite").
128K/256K of builtin flash is a midline for microcontrollers now. A 16Mb SPI flash chip costs $0.5, and many newer devices have some external flash. Of course, nobody precludes you to manage all that space manually, bit by bit.
In our lab, we have used both SD-cards and flash chips built in to devices to make storage a possibility. When you need or want to do anything complicated, such as a projection or a join, IImplementedItDB tends to become a headache.
LittleD targets devices the size of an Arduino, while SQLite requires at least some sort of operating system offering a filesystem interface. The feature set of LittleD is also reduced as it supports only simple queries.
The corresponding paper [1] claims "SQLite requires at least 200 KB of code space and cannot run on popular platforms such as the Arduino." For comparison, Arduinos based on ATmega microcontrollers only offer up to 256 KB of flash memory at maximum.
SQLite definitely does not require a filesystem interface, or even standard OS interface. It does require some kind of storage, though, but that could be direct flash, RAM, etc.
I have spent a lot of time with it on ARM Cortex-M-class devices on small RTOSes or even bare metal, and you can adapt it to almost anything by plugging in new VFS interfaces (http://www.sqlite.org/vfs.html).
It does require at least 10s of kb of RAM as a heap to do pretty much anything, as others have stated.
You can adapt, or have you adapted? I looked at SQLite internals, and decided that I'll skip that adaptation for now, seeing if someone else would do that. LittleD's author says that he looked into adapting SQLite, and that's how he decided to write LittleD.
How little memory have you managed? They claim 100KiB, but if it could be even half that then there are some tremendous opportunities to use it. I'd love to hear more about your work adapting SQLite for Cortex-M-class devices.
The ATmegas only go up to 16KBytes RAM, with 4KBytes or less being usual. As a Harvard architecture device, none of your binary is memory-mapped, which can make porting existing code a bit annoying. sizeof(int)==2, which doesn't help either (however sizeof(void *)==2 as well, so there is at least that).
It's rather unusual by today's standards. Certainly quite different from an ARM.
...though still the latest and sanest of that crop of micro's, IMHO. Designed in 1996, it was once marketed as "the first new 8-bit architecture designed in 20 years", I believe :-)
LittleD is designed to run on devices the size of (higher-end) Arduinos. So, it's difference of not being able to use a database and being able to use it. Reliability is a different problem, which is addressed for deeply embedded devices is slightly different ways than for bloated servers (e.g. by employing flash memory properties, or monitoring power supply in realtime).
Yes, currently you need to build it yourself to do size comparisons. Generally, this HN post is in argument to LittleD's author that people care about his project, and that he should care about its potential users (and contributors!) and provide better disclosure of LittleD's capabilities and traits, not requiring everyone to dig deep to get basic facts.
It's not a tradeoff you are making, the fact is that your Arduino only has program space measured in the dozens KiB. If it doesn't fit, it doesn't fit.
But that's not the biggest problem. SQLite, like most programs, relies on having a C library like glibc available. This is pretty much the intermediary person to the operating system - you can ask it for memory, it will allocate memory for you, you can ask it to open a file, it will facilitate that. Most programs assume it's always present and so the dependency isn't properly abstracted. This is a problem when you don't have an operating system.
Notice also that you can't just go and implement or stub everything that glibc does. To do that, you would just end up having to add an operating system.
(This particular rabbit hole goes much deeper. A lot of programs sadly don't depend on a C library, they depend on glibc in particular. This causes massive problems for systems that run Linux yet can't accomodate glibc, like OpenWRT. They use an alternative C library that works fine 99% of the time, and for the remaining 1% it will cause programs to silently misbehave or crash because of an implicit dependency on whatever glibc does. I had this problem with the hwclock utility which started segfaulting when OpenWRT switched to musl libc)
As others have noted, the resource constraints associated with many of the most cost-effective 8/16/32-bit AVR or ARM devices simply cannot run on SQLite. I even looked at making it smaller before starting LittleD, but I deemed that a Herculean task.
SQLite itself requires at least 100KiB of Heap space, sometimes many times more memory than already available: http://www.sqlite.org/about.html
Back in 2004, I wrote code for the Intermec ck1 barcode scanner. It ran uClinux and had a stack size limited to 4K. That thing could only very barely run sqlite 2 (after applying a few patches to it works with the small stack size) and I could never get SQLite 3 to work.
Granted. That was 10 years ago, but it was also quite a beefy device for its time. So while naturally the devices have become quite more powerful in the last 10 years, current IoT devices are also much more compact than the old ck1.
As such I would say it's conceivable that you don't have the memory to easily run current SQLite on any of these devices.
Also mind that software doesn't stay the same either. As an example, LMDB (smaller, key-value DB) in its initial docs and slides bragged 30K of object size. Build current version, you'll get 60K, and there doesn't seem to be config options to just make it smaller. So either use older version, which is of course scary (who knows how many bugs was fixed later), or spend quite a lot of time tearing it apart, with unclear maintenance perspectives. Having something ROM and RAM optimized from the beginning seems like pretty good solution (if someone grew brave to do it).
It is if the space savings gets your product sales due to lower unit price with big $$$ resulting. That's why there's a market for 8-16bit processors. Most developers don't want it so much as need it because getting job done for $1 a CPU is better than using $10-$100. ;)
EDIT to add two links by Ganssle to explain it better:
Those articles are over a decade old. The size of silicon between an 8 or 16 or 32 bit ALU contained in an MCU is miniscule. Most of the space is taken up by peripherals and this is where Microchip has historically thrived but this is no longer so. There are a number of 32 bit MCUs that are cost competitive with 8 bit parts.
And many of these still don't have enough RAM to make SQLite workable. That said, I look forward to the day when SQLite is the smallest database we will need; its just up for debate when that will occur.
I recently was invited to give a talk about LittleD at the University of Michigan. While there, I got to talk with the one of the lead researchers working on the world's smallest computer, this thing: http://www.eecs.umich.edu/eecs/about/articles/2015/Worlds-Sm...
The basic computer has so little memory (~3KB total for code/data IIRC) to make anything sophisticated impossible, but there are extensions that keep the thing much smaller than a typical embedded device (like orders of magnitude smaller) but providing enough for there to be interesting work.
That's really neat. So tiny a fly could trip over it haha. Many of the advances in process nodes are making complex hardware small & efficient enough to do stuff like this. However, I still think there's worthwhile gains in R&D where we just throw more limited hardware in there w/ dedicated functions. We're seeing that with 8-bits + smart peripherals but I think there's more to do.
My last idea was several hundred 8-bit processors in a multi-core config that could handle many data-processing problems. Turns out it wasn't entirely original:
Doubted about its marketability but supports my assertion these little CPU's & such can go much further on modern nodes. Just gotta experiment. Your work is another example.
We'd need a price list for the other thing. Can you get 32's with decent memory & low watts for a few bucks a CPU in low volume? Aside from that, you still think they're dead and useless with the new articles?
I'll add that many chips from old nodes survived for decades due to stability. The new process nodes have all kinds of issues and break faster. So, there is that to consider if the application is long-term, safety-critical, or security-critical. Many in high assurance design stayed with old stuff (esp on SOI) because manufacturing bugs were less and interference/wear issues are lower.
Yes, the tiny Freescale, Cypress, etc can be obtained for less than a dollar in a reasonable quantity [1]. The microcontroller market is changing pretty rapidly of late, with the number of products getting more and more broad. For your high reliability stuff, you'd probably want to look at the cortex-r series, which is built for the type of use cases you describe.
Appreciate the confirmation. Outside embedded, one use for these people aren't appreciating is offloading interrupts or security checks of I/O devices. Along with sensors/mgmt stuff you can trust more. Cost being so low let's one do physical partitioning of domains instead of software.
Also, thanks for the mention of Cortex-R as I hadn't heard of it. A quick glance at their description looks good. About $8 on low end to $50 on high-end. Getting quite cheap indeed for what 8-bits would be required for. Way cheaper than the old champ (RCA 1802 MCU) is today ($150 w/ 1k units).
Ok. So, I'll probably not have to go to 8-bit or drop large $$$ if I take-on certain projects. Good to know. Dirt cheap chips with large ecosystem, lock-step, real-time, and MPU's... it's like the golden era in tech for embedded start-ups, eh? :)
The charts on the EDN article are flipped, 8 bit usage is dropping. Time is from right to left. ;)
My argument is that the economic cost of production between 8 and 32 bit MCUs is negligible. There is a _need_ for market segmentation, etc. But the cost of the package makes up for the cost in gates. It really is a wash. Much of what we still see in 8 bit designs is because of (no insult intended) 8 bit designers. I love me some 8 bit RISC, but AVR is having to fully embrace ARM even when it has its own AVR32 ISA.
I don't think they are dead and useless, but they are definitely on the out for most designs. Totally agree on old stable designs. Engineering tolerances are "better" now. Something to be said for the Brooklyn Bridge and DC3s.
"The charts on the EDN article are flipped, 8 bit usage is dropping. Time is from right to left. ;)"
I saw that. They still had almost 10% of a huge market, though. My point was Ganssle's points of them not gone for most cost-sensitive markets seemed still accurate.
"My argument is that the economic cost of production between 8 and 32 bit MCUs is negligible. There is a _need_ for market segmentation, etc. But the cost of the package makes up for the cost in gates."
That does seem to be true. Especially package vs gates.
"I don't think they are dead and useless, but they are definitely on the out for most designs."
I won't argue there. 32-bit space has improved significantly on cost and efficiency. Dropping down process nodes helped a lot, there. ;)
(In other words, how big is SQLite and what are the size restrictions for IoT devices – I haven't built one before)