Unusually for me, I have spent the past couple of weeks swimming in a database sea. Specifically PostgreSQL 9.3: the relational database that deserves a lot more press than it has ever received.
I lay the blame for that at the feet of Tim O'Reilly, and early Web developers. We are going back to the mid 1990s here. Many local and regional printing companies thought they would own the Web development business because, the predominant commercial Web sites of the time, in any market segment, were static: they were divided roughly equally between online brochures and catalogues. Which was what printers did, so they expected to rule by a rather natural segue, powered largely by having graphic artists, etc., on staff.
It turned out that having graphic artists on staff wasn't enough. Interactivity demanded running code (both server-side and client-side), not just HTML markup. A server-side database became a basic requirement, and it turned out that old-school graphic artists were not all that knowledgeable about coding and database administration.
Into that breach stepped Tim O'Reilly, and his introduction of the term 'LAMP stack'. Linux, Apache, MySQL, PHP. This hugely boosted the popularity of both Linux and the Apache Web server. It was free! ISPs could offer it on the cheap!
One downside to this idea, was MySQL, which achieved that vital initial surge in popularity simply because some graphic artist, likely still affiliated with a printer, or some random person associated with an equally random ISP, could actually make it run. Sort of; it was buggy and primitive.
It stayed that way for a long time. Not ACID-complaint, horrible SQL compliance, didn't reliably enforce constraints, etc. A seemingly unending list of serious issues. But hey, it was easy to get it running! PostgreSQL was also available, and has historically been far superior, but it demanded more care and feeding, as serious databases or wont to do. The horrible name helped not at all.
I, OTOH, had a need for reliability, was already operating in an environment that used Linux and Apache (though not PHP -- a subject for another post) and had no problem with the systems administration aspect. To this day, in any project that needs a hugely competent database, I default to PostgreSQL. Is it perfect? No. I have never seen perfect software, and do not expect to.
Enough History. What Am I doing?
I currently have five projects in-work that require a database back-end. I am planning to migrate all of them to at least 9.2. It is possible that in one or more cases, postgres will not meet the need. In which case, it will be abandoned. Having the best historical results with a database is not the same thing as being wedded to the damned thing forever.
The way that this breaks down is that one is personal. We might toss that one on those grounds, except that it is the project that uses more postgres features than any. The only thing it is not subject to is a sensitive dependence on ingestion rates. I am going to leave it in, with some misgivings; it is famously hard to avoid using features that are available in your database, but subject you to lock-in. 'Serial' v 'Autoincrement' may be the canonical example.
Overall, I care about, ordered by overall requirements:
- Integrity (very much including security)
- Auditability (not the same thing as security)
- Ingestion rate
- Rate of change (in both code and documentation requirements)