thanks for bringing up those very valid questions. Generally speaking every developer should choose the best tool(s) to tackle his/her development challenges.
Let's start in reverse order of your questions, the development APIs.
Informix is an object-relational, SQL database which follows the SQL99 standard and is supported by all the major programming APIs, some of them community supported and some provided and supported by IBM. Here is an incomplete overview about the available APIs (I guess some of them need to be compiled for the RPi with the Informix ClientSDK which comes with the Informix RPi server):
One of the strengths of Informix 12.10 is the optimized integration of time series data (for e.g. sensor data), Geospatial data, 'classic' relational data and unstructured JSON data in one hybrid database engine. All of those data types can be easily accessed either via SQL, MongoDB API and/or the REST API depending on a developer's requirement.
Unlike in MySQL you don't need to mix and match different DB/storage engines to achieve a specific behavior. So if you need HA for your application its built-in. If you need multi-site data replication its also built-in. If you need support for complex clusters over thousands of nodes, its built-in. There are no restrictions on how to combine the different technologies.
You can even join structured relational data with JSON documents and eg. time series and Geospatial data.
Informix can be configured for resource constraint environments like eg. a Raspberry Pi. If required the install footprint (w/o data) can be easily below 100 MB, maybe even below 50 MB.
The memory utilization is quite often dependent on how efficiently a database stores its data on disk, since those data pages are eventually loaded into memory.
In the case of time series/sensor data for example, the Informix approach of storing such data will provide you with storage savings on disk of about 60 - 80% (+/-, depending your data structures) compared to a pure relational approach like in MySQL. That in return means that you have more sensor data readings in one data page and hence either much more readings in the same amount of database RAM (cache) or you can reduce the memory usage to cache the same amount readings w/o any performance issues.
Also: using less storage space on disk for those time series data means significantly less I/O and CPU activity for operations on those time series data compared to a 'traditional' relational approach which is definitely very beneficial for those less powerful HW environments like a Raspberry Pi.
Simply speaking: Informix is very efficiently utilizing the precis RPi resources in such use cases.
Unfortunately I don't have a head to head comparison between MySQL and Informix in that respect, but I recently did an Informix relational vs. Informix time series comparison on a platform similar to the RPi (although ARM v7 based). Buy just moving time series data from a relational Informix table to an Informix time series, I have been able to speed up queries on the same kind of data by factor 3-7!.
The more time series based data you need to handle on a RPi or any other platforms, the more a developer will appreciate the Informix capabilities in that respect.
I nearly forgot to mention that Informix has been optimized over the last years to especially handle the tough requirements of deep embedded, low resource, zero admin environments. Informix has for example a buit-in low memory handler to automatically react on low memory situations. BTW, due to those features customers like Cisco have deep embedded Informix in many of their solutions and I believe that they do have an install base of more than 100k Informix instances worldwide, most of them invisible to their customers (Details can be found here: http://www-01.ibm.com/software/sw-libra ... 58W44.html
Speaking of a pub and a beer: if you might be in the Munich area, give me a 'ring' (or a PM) and we could have a more in depth-discussion, very likely together with some colleagues from the Munich Informix lab and other technical teams. Alternatively I could get you in touch with a great colleague and Informix time series specialist in the greater London area.
If other Raspberry Pi developers who are reading this post might be in the Munich area and would like to chat with some colleagues from the technical Informix team(s) and/or me, just send me a PM.
- Alexander (Twitter: AlexKoeMuc)
PS: Since you are mentioning MQTT in your signature, have you already had a chance to take a look at IBM's Bluemix and IBM's IoT Foundation? Both are making intensive use of MQTT and they did integrate Informix with a focus on time series data. Links: http://www-01.ibm.com/software/bluemix