The database that refused to die: How Postgres survived its own creators FEATURE Today Postgres is one of the most widely used database systems, but its launch and subsequent development were inauspicious to say the least. If it weren’t for a league of exceptionally devoted open source contributors, it probably would be another forgotten also-ran just like Ingres, the database system on which it was …
I didn't know the connection between Postgres today and the Ingres work described in The Ingres Papers. Basically having the code BSD licensed was probably responsible for its renaissance.
I remember at the time ADTs were a big thing in teaching compsci students software design and programming before OO became de rigeur. Still a pretty useful component of their education if only for clearly delineating between a representation and the thing represented.
Indeed, and the comparison with MySQL ignores the cancerous dual-licensing that was part of the model. MySQL as a database has improved under Oracle's stewardship, but they've also made it very clear that it's a gateway drug to full-fat Oracle licences.
As long as you have Postgres, you don't need to ask the lawyers anything. This also makes it easier for commercial vendors to offer "added value" add-ons as part of the support contracts that many businesses will be happy to sign.
Another thing, I get the impression MySQL used to be popular as a feature-lite almost database that was good enough for a lot of intended use-cases, and had better performance than proper databases.
Now, we have Postgres as proper database, which also has better performance than MySQL, and hardware is so much faster now that performance considerations aren't quite so important.
MySQL is understandable for its original task: great write performance with support for SQL. But it was never suitable for the kind of work that relational databases do and, as usual, write performance takes a hit when have integrity checks. But it was well marketed and came with a Windows version and GUI tools, making it the "goto" tool for many young programmers and it helped spawn generations of awful CRUD systems that actively discourage developers from writing queries themselves.
A long time ago Postgres was known as the "correct" SQL database with ACID compliance and MySQL was known as the "fast" database without ACID compliance. Correct or fast you can't have both. All the PHP scripts used MySQL because it was better at serving up pages fast.
But it turns out people really want ACID compliance so MySQL acquired a different backend storage engine called InnoDB which had it. But MySQL was confusing since now there are two engines boasting difference characteristics. Eventually the original ISAM engine got dumped but InnoDB won. But that was acquired by Oracle and then MySQL was acquired by Sun, and Sun acquired by Oracle and suddenly MySQL front and back is owed by Oracle. People had an issue with that and then MariaDB was forked from MySQL.
Meanwhile Postgres was chugging along being correct and getting more performant and new features with each release. I think the lack of drama and confusion has meant it has basically won between the two. Virtually every cloud service favours postgres, probably because they don't want Oracle lobbing some kind of sueball at them if they built off MySQL and the licence is also more permissive.
The first RDBMS I came across was Ingres and used Windows4GL to code a X Windows (and Microshaft windows as well, used pretty much the same 4GL code just compiled it on a Windows box) UI... and have always wondered why other systems were so popular when they were(are?) so flakey.
Back in those far off days, switch off an Ingres server and, unless you were really unlucky, everything just resumed when the power came back. Doing the same to Oracle was a completely different experience - one had to hope that the backup last night worked. Having done both, I have the scars to prove it!
I did see, with some joy, that our printer accounting/management software shifted from SQLServer runtime (or whatever its called these days - Microsoft Database? who knows?) to Postgres recently.
I used many different relational databases in the 90's and the best feature of Ingres was - as you say - that it was mostly a case of 'install it and forget it'. Oracle at the time, while very feature rich, required constant monitoring not to run out of space in any of the myriad of different partitions it had.
The downside was that by the late 90's Ingres was looking VERY outdated, feature wise but embedded in an application is was nice and trouble free.
On a related note, a friend worked as a developer for Ingres (ASK), Informix and Postgres (at Berkeley) at various times. ASK was where all the database nerds ended up. When Computer Associates purchased ASK in 1994 you couldn't find a larger clash of cultures. My friend recounts that after the announcement of the takeover, Oracle had a recruitment van outside ASK within the hour. Sybase had one there a couple of hours after that. By the end of the day, of the ~200 strong development team, only about 20 were left.
There were no vans outside the ASK/Ingres building in Alameda, but there was a ad-hoc party of people outside cheering as people walked out of the building having chosen not to sign the California-not-enforceable non-compete agreements.
Ingres missed the boat for three reasons. (1) SQL; it had to write a new version of that to transition from Quel and it absorbed a massive amount of resource that could better have gone to (2), locking and concurrency strategy. Ingres used exclusive page locks which blocked readers while updates were in flight for everything on the page. It was correct, but slow. There were plans to do row-locks that weren't implemented at the time of the Demise. Eventually CA did it, but way too late. It never had any plans to address the write-blocks-read issue. At the same time, Oracle did a very clever thing with "Consistent Read" which was a way of getting multi-version concurrency at low cost. When you need an old version to satisfy a query, you'd resurrect it by applying UNDO from the logs, and do it on demand. When the rate of reference is low, it doesn't cost much. This was a club with which Oracle beat-up on Ingres at sales opportunities; (3) Oracle used financial clout to strangle Ingres. Once it was profitably extending its lead, Larry made a policy that Oracle would -never- be undersold by Ingres on any deal, and AIUI, would pretty much give Oracle licenses away to win deals, and expect to make it up on support revenue later. This crushed the Ingres sales channel, leading to both the acquisition by ASK and then CA.
Ingres did have ADTs, but a different implementation from Postgres which was developed simultaneously.
Postgre's recovery strategy was not terrible, but they didn't work it out. It was multi-versioned from the start, I think, which solved the write-blocks-read issue. But the snapshot/point-in-time recovery stuff was underthought.
There was an attempt to resuscitate Ingres post-CA by a company that became Actian, but I don't think that went well. It did get open-sourced for a while, but last time I looked it was hard to get. It probably could have competed with Postgres as open source except for the lack of community, and the multi-version update-blocks-reader problem may still be unsolved.
I first used Ingres in 1979, from a BSD2.something on a PDP-11/34a running UNIX edition 7 for a database course at Uni. It was tremendously slow. One user would make everything else crawl, two would pretty much stop the system.
A few years later, I was running my 'own' PDP-11/34a, but this time, I had SMD disks and 2MB of memory (PDP-11/34a should only be able to address 256KB, but this one, modified by SYSTIME, had 22-bit memory addressing bolted on, making it a strange beast that was a non-I&D -11 that could, in theory, have the maximum 4MB of memory).
Jiggling the code a bit, and compiling up Ingres for a system with a maximum of 56KB per process required it to be broken down into 6 different processes for different, talking together through pipes (no shared memory on UNIX edition 7). But unlike my first experiences, there was sufficient memory to prevent it swapping, and I could also increase the number of system buffers by moving them out of the kernel space that allowed more files to be opened simultaneously. With this still modest system, I could get 4 or 5 basic QUEL sessions against the demo db running at acceptable speed.
The version of BSD 2 I had at that point had an overlay linker and loader on it, but I never managed to get Ingres to compile and run using this, but I reckon that if I had been able to, performance would have been better still.
I know the community here is not particularly fond of docker / containers but I find it very convenient and PostgreSQL lacking sensible in-place upgrade between major versions is very annoying... I would love for it being added. You can do it within major version with minor version bumps so I should be doable with major versions as well without having to deal with two versions being available at the same time (which is a huge PITA in docker / k8s...
What's the point about Docker? You can easily use it with Postgres? Or are you talking about it in terms of the lack of in-place upgrades?
My understanding is that anything that touches the indexes will generally be faster through a backup/restore approach which may be more important for many users. I've normally had little problem with upgrades but did hit two real problems going from Postgres 13 to 17 – one was datatypes and the other was a change in the signature of a PostGIS extension – now that wasn't fun to try and fix on 180GB system!
You are running PostgreSQL 13, you want to switch to 14. You can simply use newer version because it will refuse to start.
In ternera, when NOT running in docker when you upgrade PostgreSQL via system package manger it will download newer version, run pg_upgrade (which requires version _from_ which you are upgrading still being present) and after the upgrade remove older version. With docker it's not possible as you only have current version in the image so PG_upgrade fails.
And while there are workarounds (export / import or using custom, alternative docker images that contain both versions) it's somewhat inconvenient.
So (I'm your docker compose for example) changing image from postgresql:13.0 to postgresql:13.1 will work just fine with in-place upgrade, switching to postgresql:14 will throw an error...
I'm not suggesting upgrading between very far apart versions but upgrades between successive major versions would be nice.
I'm assuming from your posts that you have several (many?) different systems/applications using separate Postgres databases. And these are deployed in Docker containers for various reasons, not least that it is cheaper than VMware?
Choosing containers imposes some constraints resulting from the fact that they are meant to be immutable and replaced when needing change, not updated on the fly so they no longer match their containerfile definition.
I'm assuming / guessing at your scenario a bit, but suggest you:
a) Create a new version of your app container, using the new version of Postgres but don't deploy it.
b) Create an "upgrade instance" of Postgres - which could be containerised or not - just for you to run as admin with old and new Postgres present. Stop the old container in K8s and run the upgrade yourself against the database to be upgraded using this "upgrade instance".
c) Then, if the upgrade went with no errors, allow K8s to start up the new version container(s).
Repeat the above for each application in turn.
Yes, it's quite popular to have a docker compose with related services (app + db + others if needed). Most of the time you would just stick with the required PostgreSQL version when you started and stick with it only bumping APP image version (which then would run schema migrations if needed).
However, at times, it would be nice (of required because of compatibility) to be able to upgraded the database (to next major version) as well and it's not as simple as just changing the image tag like in the APP case, becase PostgreSQL doesn't support that... (it works fine with MySQL though so you could switch from 5.1 to 8.0 and during next docker compose down / start and recreation of the container, new version of MySQL would detect older version of the files and would run then upgrade on it's own.
We selected PostgreSQL for commercial use in around 2000, since while we were prepared to use something commercial we /really/ didn't want sales calls every month or to find details of what we were doing leaking. After the SOLID Database Server ("with Bonsai Tree Technology(tm)") vanished into IBM the options were fairly sparse: MySQL didn't have adequate support for transactions, and we /really/ didn't want to tell customers to whom we were demonstrating our service that we used something with such a tacky name: most of them still had IBM mainframes.
The version at that time was IIRC 6.5, and while sniffing around the source (which we purchased from the .org on a CD) I found hooks for tertiary storage, i.e. offloading seldom-used data to a tape library or similar. That code- removed shortly afterwards- was written by Sunita Sarawagi, presumably as part of her PhD at Berkeley ("Query Processing in Tertiary Memory Databases") before joining IBM.
I remember way back when PostreSQL first came out in the research days when I was at the University of Saskatchewan in Saskatoon, SK, Canada, back around '84-'85 in third year when the "Business Programmer Students" started working with it on the student systems. You knew very well that if you saw someone log in to that particular 400-series class on our VAX-780's, you may as well log out and come back some other time, because two measly users of PostgreSQL were enough to completely saturate the capabilities of a VAX.
It wasn't until a few years later, around '89-'90, that I had exposure to Oracle's 5-series releases on HP hardware. I mostly wrote software the fed the database test data results, but I had to know enough about the database to make sure the data I fed it was "clean" for it's use. Around '91 I actually started doing paid programming in SQL, evaluating, of all things, the early releases of SybaseASE, which itself was based on Ingres technology, like PostgreSQL was.
Over the years, there really weren't any major commercial databases I didn't touch for months or years at a time on projects, but I still stick to PostgreSQL for my own projects, and have for over 20 years...
> I remember way back when PostreSQL first came out in the research days when I was at the University of Saskatchewan in Saskatoon, SK, Canada, back around '84-'85 in third year when the "Business Programmer Students" started working with it on the student systems. You knew very well that if you saw someone log in to that particular 400-series class on our VAX-780's, you may as well log out and come back some other time, because two measly users of PostgreSQL were enough to completely saturate the capabilities of a VAX.
You've reminded me of the capacity sizing guidelines I (we) had for VMS back in the late '80s: 0.5KB per standard user; 1.5KB per Ingres application user.
Yes, those are kilobytes (but they were green screen users).
Tell that to kids of today etc etc
Back when Informix incorporated the Illustra stuff i went on a course to cover it. The ADTs were implemented by writing your own code which would then be linked into the database engine. Running customer code inside the engine that was powering your business-critical applications never seemed like a good idea. Then I came across somebody who'd contrived to crash the engine with client code...
Does PCI need TDE? I'd be surprised; generally it requires specific controls, but doesn't specify technology. So it may require "encryption of sensitive data at rest". And TDE may be a potential solution to this requirement. But I'd be surprised if PCI specifically specifies TDE (after all, mainframes may not have TDE but use pervasive encryption which is closer to block level).
Now, personally, I argue (Ugh, I wrote this 9 years ago; https://www.sweharris.org/post/2017-06-04-encryption-rest/ ) that TDE is inadequate because it doesn't protect against attacks at the database layer. If I get DBA permissions or even just app account permissions then I can get access to all the data. I argue that the better solution is _application_ level encryption. Now you've got protection against disk theft, rogue/compromised SA accounts and rogue/compromised DBA accounts. You may still be vulnerable at the app layer (rogue/bad developers; bugs; etc) but only the apps that need the unencrypted data would ever get to see it.
PG is/was the go-to DBMS for analytics products such as Redshift, Netezza and Greenplum.
In each case PG is deployed in a scale-out MPP architecture pioneered by Teradata in the ‘80s.
Sharding and columnar storage are offered on Greenplum, and maybe other PG-derived analytic offerings.
I’m sure Bruce M knows this given he works for EDB that offer a Greenplum-derived product called Warehouse PG!
The PCI security control mentioned as missing is actually encryption at OS Level. I.E. you can read the datasets if you logon with correct permissions at OS level.
This does not mean the RDBMS has to encrypt, only that it's files require an encryption key label permission.
Db2 mostly uses OS level encryption. That's fine. If Postgres does that it's fine too.
F
