from the eating-it-with-fava-beans-and-a-nice-Chianti dept.
Postgres is eating relational:
Even as NoSQL databases keep booming, the relational party is very far from over. But among the relational crowd, one database keeps growing in popularity at the expense of its more established peers. Yes, I'm talking about PostgreSQL. The real question isn't why developers like PostgreSQL. There are plenty of reasons. No, the real question is why developers like it so much right now.
The PostgreSQL renaissance is several years old now, something I've written about repeatedly. The reasons for its popularity? There are several, as consultant Tanel Poder neatly summarizes:
1. Rich set of features 2. Extremely extensible (extensions, hooks) 3. Open source 4. 'Permissive' open source license
[...] At any rate, no one questions how good PostgreSQL is nor the part it plays in the industry trend that favors general-purpose databases. This isn't exactly news. What is news is the rush to modernize—and PostgreSQL's role in it.
[...] So even if another database model might actually be better for their use case, the "easy button" is to go PostgreSQL. As ex-AWS engineer Dave Cuthbert notes, "Far more apps are using relational [databases] because it was the only hammer they had."
OK, so the guy likes Postgresql. But what relational database do you or your company use, and why was the choice made? Has it lived up to its promises, or have you found that some things don't quite work as well as they might? What would you recommend today?
(Score: 4, Insightful) by Thexalon on Thursday October 27 2022, @10:57AM (5 children)
I have to admit, when I hear the phrase "more established peers" used to describe Postgres, I'm confused. Postgres got its start at Berzerkely back in the late 1980's, has always been aiming to be a competitor to commercial SQL databases, and having a feature set that makes it a credible alternative.
As far as it beating out Oracle and SqlServer, that doesn't surprise me in the least, because it's better feature-wise and much cheaper. Also potentially relevant here is that cloud providers make it very easy to pick, although I doubt it's that much harder to pick than the others. I could imagine the rising trend being as simple as "AWS picked Postgres as its default".
The only thing that stops a bad guy with a compiler is a good guy with a compiler.
(Score: 5, Funny) by Rosco P. Coltrane on Thursday October 27 2022, @11:50AM (3 children)
That's not too hard: however good their database product may be, encoding and retrieving data in a text file using Emacs with a braille terminal is a more appealing prospect than being an Oracle customer by a country mile.
(Score: 3, Funny) by JoeMerchant on Thursday October 27 2022, @12:36PM
Reminds me of one of my first "computer consulting" gigs. I was about 14 and a friend of my mother was given a new IBM PC at her job but zero instruction as to how to use it. This was the DOS booting from 5.25" floppy generation.
The only software they gave her was the base OS, which included some absolutely horrible editor, as user unfriendly as vi, but less capable than notepad, nano is tremendously advanced by comparison.
All I could show her for how to "use" her new computer was to boot up and edit/save documents using that editor. She was eternally grateful because everyone else who had one of these behemoths installed on their desk couldn't do more than boot it up and type on the command line, getting errors when they pressed enter.
Україна досі не є частиною Росії Слава Україні🌻 https://news.stanford.edu/2023/02/17/will-russia-ukraine-war-end
(Score: 0) by Anonymous Coward on Thursday October 27 2022, @03:33PM (1 child)
How do you type "Escape-Meta-Alt-Ctrl-Shift" in braille?
(Score: 0) by Anonymous Coward on Friday October 28 2022, @09:02PM
How do you type "Escape-Meta-Alt-Ctrl-Shift" in braille?
With a size 14 army boot.
We use Oracle, because if we didn't the project might actually work, and then we, the "consultants", would be out of a job!
--
You have the right to remain dead.
(Score: 2) by inertnet on Thursday October 27 2022, @01:20PM
Oops, I just posted something similar about the origin of Postgres before reading your post.
(Score: 5, Funny) by Rosco P. Coltrane on Thursday October 27 2022, @11:44AM (2 children)
My company uses Excel. It's relational in the sense that you have to know someone who can work one of the many obscure formulae in the sheets without breaking everything.
The choice was made out of ignorance and stupidity, then we kept at it out of inertia.
(Score: 2) by Freeman on Thursday October 27 2022, @01:45PM
Once a boulder gets going down hill, it's hard to stop it and you might not want it to.
Joshua 1:9 "Be strong and of a good courage; be not afraid, neither be thou dismayed: for the Lord thy God is with thee"
(Score: 2) by krishnoid on Thursday October 27 2022, @08:57PM
Bear in mind spreadsheets are a least common denominator, and you can get help for using it -- particularly in non-technology based companies -- from many, many sources. People can use it at multiple levels -- click in a cell and type in it, through importing CSVs and entering formulas into cells, through writing your own functions and automation. It's basically got an easy learning curve, and is fully usable at every part along that curve. Start with SQLite and you need to learn SQL; start with a server-based relational option like PostgreSQL or MariaDB and you need someone who's tech-savvy enough to act as a database administrator.
So I'd say Excel or Google Sheets or Libreoffice Calc are fine choices to start with. You can always upgrade from there without too much work once the needs or relational requirements or data sizes outweigh the inertia.
(Score: 4, Interesting) by JoeMerchant on Thursday October 27 2022, @12:27PM
As the answer to:
>what relational database do you or your company use,
clearly shows: SQLite
>and why was the choice made?
We are not serious multi user database developers. The thinking is: if we ever need more than SQLite offers, the upgrade path is pretty smooth. But for the 95% of our database development that never ends up needing more than SQLite offers, it's super low overhead as compared with "more capable" alternatives.
Україна досі не є частиною Росії Слава Україні🌻 https://news.stanford.edu/2023/02/17/will-russia-ukraine-war-end
(Score: 4, Insightful) by loic on Thursday October 27 2022, @12:45PM (1 child)
Why PostgreSQL? Because it is open source, so it is free for cloud deployment and it suck less than Mariadb.
(Score: 5, Informative) by bart9h on Thursday October 27 2022, @05:14PM
I also has excellent documentation.
I found this Don't Do This [postgresql.org] wiki article particularly useful, and applied all it's tips when designing the schema for my company's database.
(Score: 5, Informative) by inertnet on Thursday October 27 2022, @01:17PM (1 child)
More established? It's the original. PostgreSQL is a direct descendant of the very first relational database, Ingres, developed at Berkeley university. It's even in its name: post-Ingres.
(Score: 3, Interesting) by Thexalon on Thursday October 27 2022, @02:58PM
The impression I get is that they're thinking about "established" in terms of market share, not code, features, decades of bugfixes and performance enhancements, etc.
I mean, I can answer why I like Postgres: It does what I need it to do, and does it well. It can handle very large data sets, it can be backed up sanely, it can be replicated, it does all the joining and integrity checks you'd expect of any relational database. And it's free in both the "free speech" and "free beer" senses. What's not to like?
The only thing that stops a bad guy with a compiler is a good guy with a compiler.
(Score: 3, Interesting) by bzipitidoo on Thursday October 27 2022, @03:39PM (4 children)
I was on a project to migrate from Informix to MySQL, back before MySQL went proprietary and MariaDB was forked. The trouble was that the planners had way too rosy a view of how easy that would be. They barely even knew the term "migrate", until the DBA they'd hired tried to warn them. Thought it'd be as easy as dumping all the data from Informix to a flat file, maybe tweaking things a little bit, then reading it all into MySQL. Nope! The customer's system was heavily dependent upon various Informix utilities for which MySQL had no analog, and their own custom made and messy utilities that tied into Informix. To make it all work, they'd also screwed with the OS (HP-UX), suppressing system signals. One program checked for the existence of a bunch of directories, and if not present, would create them, but they'd made a mess of that. It would create one directory and crash. Rather than fix that, they merely wrapped it in a script that ran it n times, where n was the number of directories plus 1. If you wanted to exit the program, you might have to work the exit dialog n times, but you'd eventually get it to stay exited.
The project was a failure. By the time the far too ambitious 3 month deadline passed, the team had accomplished a little but was still learning how deep that rabbit hole went, untangling messes like that directory creation code, and finding out how much more work would have to be done.
(Score: 2) by JoeMerchant on Thursday October 27 2022, @05:30PM
>wrapped it in a script that ran it n times, where n was the number of directories plus 1
In the voice of Larry the Cable Guy (aka Tow Mater):
GIT'R'DONE!
Україна досі не є частиною Росії Слава Україні🌻 https://news.stanford.edu/2023/02/17/will-russia-ukraine-war-end
(Score: 2) by JoeMerchant on Thursday October 27 2022, @05:35PM (2 children)
>that directory creation code
I used to create directories with scripts... now I use "systemd-tmpfiles --create" with a .conf file in /usr/lib/tmpfiles.d/
Yes, it has the mark of the systemd beast, but it's also dead reliable - remaking directories and setting permissions on every boot when necessary, such as when said directories exist on a volatile file system, which is what got me looking for the solution in the first place. As a bonus, you can set it to clear out old files - as you would want for log files.
Україна досі не є частиною Росії Слава Україні🌻 https://news.stanford.edu/2023/02/17/will-russia-ukraine-war-end
(Score: 2) by bzipitidoo on Saturday October 29 2022, @04:23PM (1 child)
First I've heard of this feature. Why is systemd-tmpfiles better than a bash script or even a perl script? A script could replicate the config file approach, pretty easily mapping a list of directory names in a config file to instructions to delete, then create and set permissions. And it'd be more portable.
(Score: 3, Insightful) by JoeMerchant on Saturday October 29 2022, @07:47PM
If you've hooked your bash/perl/whatever into the boot sequence, after the RAM drives have been created, but before anything might want to use your folders on said volatile storage, then, sure - that works. You'll just have to add functionality to create the needed folders, set ownership and permissions, and (the reason for the systemd utility in the first place) when it's a temporary storage folder on a non-volatile storage: read through all the files in the folder and judge them vs an age threshold and delete the expired ones. The .cfg file for our system has about 20 entries in it presently, may add 4 or 5 more over the coming months, so I'd rather not be calling the script(s) 20 times, so I'd probably end up with a .cfg file approach myself.
It's open source, you _can_ do anything. My professional estimate of getting all that crap right is that it would take a day or two (and, in our software dev system would require a specification, likely unit tests, V&V test protocol, documented evidence of passing the protocol - which amounts to about 7-10 more days of various people's time over the upcoming dev cycle), whereas learning to use the existing tool, which is maintained by "others," and deploying it for the needs in our system was a couple of hours, and the tool falls under the Ubuntu system umbrella so doesn't generate all the additional documentation testing and documentation requirements (If I were to be realistic about it, I would have said documentation about 4 more times.)
Choice is yours. You _can_ re-code the entire kernel, if that's your kink. Our previous implementation (the one that has been used in thousands of copies deployed in the field for years now) just created the folders once during the initial system setup scripts, and that's been 100% successful so far, but... we do want to move certain things to truly volatile storage rather than continue to erase them before shutdown (for data security, identity protection, etc.) and that's when I stumbled upon this tool which is really so much better. for my situation.
Україна досі не є частиною Росії Слава Україні🌻 https://news.stanford.edu/2023/02/17/will-russia-ukraine-war-end
(Score: 1, Insightful) by Anonymous Coward on Thursday October 27 2022, @05:12PM
The developers of IBM System R collectively give you the bird.
(Score: 5, Interesting) by bloodnok on Thursday October 27 2022, @06:09PM (1 child)
To answer why now, I think it's better to ask why not before.
And I think the answer to this is simply fashion and inertia. When MySQL was the darling of developers, I was often told how easy and fast it was. I was using Oracle at the time and experimenting with Postgres. MySQL was certainly easy, but it was so full of gotchas that I couldn't take it seriously. Postgres in those days was not as fully featured as it is now, but it was still pretty damn fast, totally reliable, and although maybe not quite as easy as MySQL, was way easier to use than Oracle.
The problem then was that developers just believed that MySQL was good and didn't even consider Postgres. By the time Postgres 7 came out, there were very few use cases where MySQL (or even Oracle) was better, but still the perception persisted.
Developers thought it was fast, that it was good, and that it was cheap. They were wrong on all counts but the myth persisted.
It was only good if you ignored all those, well documented, gotchas.
It was only cheap if you didn't factor in the administration of regularly rebuilding indexes, dealing with failed backups and more.
And it was only fast if you ran exactly the same query multiple times, without the database being updated. Cached queries were very fast, but if that is your use case you should be asking yourself why you are running the same query all the time.
I once consulted for a MySQL house that was having performance problems. They were joining 2 largish tables with fairly simple outer joina. The joins simply would not use the right indexes and hundreds of full table scans were being performed. I could not get the queries to perform. Eventually in desperation I copied the data to my laptop and threw them into a brand new untuned Postgres instance. The queries ran over 100 times faster on my laptop than in MySQL on enterprise hardware. The second time the queries ran in MySQL, due to caching, they were blindingly fast, but since the Postgres versions on my laptop only took about 500msecs, the advantage was not huge and the cost of the first uncached queries was too much for the customer to bear. They switched to Postgres and never looked back. In doing so, they also reduced their administration overhead as they no longer had to rebuild indexes every few days.
It has taken Postgres years to overcome the inertia of developers who believed MySQL to be good. It wasn't a matter of fact, but of fashion and faith. Although I often blame PHP for MySQL's early popularity (if you thought PHP was good enough, why would you not thing the same of MySQL?), I now think it was just that developers are lazy and subject to the same blinkered attitudes that infects the rest of humanity. My own blinkered attitudes and laziness only properly kick in when I have some evidence that I really am going to find life easier with tool X rather than tool Y: I take my laziness very seriously.
__
The Major
(Score: 1) by GloomMower on Friday October 28 2022, @03:04PM
I agree. And it is hard, when you got someone in love with MySQL there is no real talking them out of it. Slowly PostgreSQL gets put in when finally there is no more talking that can be done.
(Score: 2) by stormreaver on Thursday October 27 2022, @10:19PM
I've been using PostgreSQL since about 1996/1997. Back then, getting it running on Linux was a more manual task than it is now. Back then, it took me about 20 minutes to get an instance running. Nowaday, it takes about 60-70 seconds to configure it and start it. All the hard work is done by the packaging system. All I have to do to get a basis system running now is to allow all interfaces and set the allowed subnet. The rest takes care of itself.
I continue to use PostgreSQL (both personally and professionally) because it's fast, free, highly reliable, highly configurable, and bursting at the seams with useful features that are lacking in most other relational databases. We have replaced Oracle and SQLServer databases with PostgreSQL with no regrets. PostgreSQL runs our ArcGIS system faster than Oracle did, and on the same hardware. I have another mission-critical system that has tables with hundreds of millions of rows, referenced by multiple other tables with tens of millions of rows, with query times to extract data subsets anywhere from small fractions of a millisecond (warm queries) to low double digit (cold queries) milliseconds.
It's very easy and logical to administer, too. It's a big win on all fronts.
(Score: 2) by arslan on Friday October 28 2022, @12:38AM
Its free, I used to use MySQL a lot until Oracle made a move on it so I looked at Postgres and never looked back.
Also, I've started moving some of my mongodb workload over as the json document support have been getting better and better and some workloads don't really need to use mongo and in fact some is problems that require a mix of relational and document works really well with postgres.
The fact that they are also offered by major cloud platforms like aws is also a big reason and the fact that vendors like Oracle insist on licensing their product in the worst possible ways on cloud platforms other than their own is just asking for folks to not use their own product.
(Score: 2) by ilsa on Friday October 28 2022, @03:55AM
I can't speak to why it's popular now, except for maybe my romanticized wish that people finally woke up and realized how much better it is compared to other options. Off the top of my head, I can give three reasons:
-It's the only OSS database that provides something resembling an enterprise-level feature set. Proper acid compliance, stored procedures, high performance, etc. MySQL/MariaDB has caught up in a lot of ways but it's still behind. MySQL/MariaDB's priority was always about "easy", not "good", and it didn't take much before you started hitting limitations and complications.
-The base installation is secure by default. You can't even connect to it from another machine unless you explicitly update the appropriate config file to allow that access, and even then you need to specify how you want people to connect with controls for SSL, ip ranges, users, etc. MySQL's security is laughable by comparison.
-If you need to get really fancy, EnterpriseDB provides an Oracle compatibility layer with pl/sql support.
Actually... maybe that's it. I wonder how many of these people are actually moving off of Oracle, and postgres is the most logical place to jump to. Whenever someone recommends a high-end database of calibre similar to Oracle, Postgres is always the first option I give. I really hate that question actually, cause virtually nobody _actually_ needs (or understands) the absurd amount of functionality and flexibility Oracle provides. They just think "I need a powerful DB!" and they think Oracle is the benchmark.