What are some reasons I might consider Postgres over MySQL when choosing a DB platform?
Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).
Postgres 11 – A First Look
81–90 of 193 posts
Re: Postgres 11 – A First Look
#82I love PG. heaps better than the alternatives, for many reasons. My main one, is native IPv6 object support. I found the migration from 9.x to 10.x deeply painful, I think the one-time cost of the dump-restore cycle hurt me far more than I expected. I very much hope the transition to 11 can be done more expeditiously.
For future reference, you can use logical replication to do this online between versions with a minimal window at the end for a final sync when apps are off and not changing data. By that I mean have your 9.6 in prod running as normal, set up your 10/11 newprod and then kick off logical replication for ALL TABLES from prod->newprod. That'll chug along and as long as you have appropriate resources and replication configured correctly, it'll replicate your data over and maintain it while prod is still being used. Then you turn your apps off, let it finish the sync, switch the apps over to the new db and you're good. Obviously there's some missing detail like users, extensions, replication etc, but that's always very specific to an install.
Re: Postgres 11 – A First Look
#83> Now you can quit Postgres by simply typing quit or exit. Previously you had to use Ctrl + D or \q While this is good to help out beginners a bit, people should really learn Ctrl + D. It works practically everywhere and saves tons of time guessing/remembering and even typing the correct incantation for the program you want to exit.
C-d is a great way to accidentally exit more than one shell with remote latency. I only use it then there's no other choice. Most frequent use case is ad-hoc file transfer via terminal paste to remote end running cat > out.txt.
Re: Postgres 11 – A First Look
#84Earlier quoted context omitted.
100% naive question: why is mysql and their similars so popular then? Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
I used both of them, but migrated to Postgres permanently a decade ago. Early on in my career, the biggest selling point for Mysql was the excellent web admin tool "phpMyAdmin" - it really helped get applications off the ground, since the core of most modern systems is the data model. Users could modify data, without your needing to create a UI for that use case. It conveniently used the same stack as the rest of the…
It was the same for me - back when I first learned SQL I really tried to use Postgres but phpMyAdmin was so much better than pgMyAdmin that I gave up. A lesson in the importance of user-friendly UIs. Perhaps some developers can really nail a schema on first go, but for me it usually takes a lot of tweaking before I can really understand what I want and the CLI is not the best place for that, especially if my mind is deep in the code I am trying to write.
Re: Postgres 11 – A First Look
#85Earlier quoted context omitted.
100% naive question: why is mysql and their similars so popular then? Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
> Why? Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language. The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does n…
That's a broad generalization of PHP "coders"
I think it's safe to say we've all seen good code a crap code -- regardless of language.
It's not the wand, it's the magician.
(nb: I've been building software for 20+ years, I use PHP (among others) and knew JOIN and types in PG before I ever saw PHP, I cannot be the only one)
Re: Postgres 11 – A First Look
#86Earlier quoted context omitted.
> Why? Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language. The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does n…
In fairness, some of this is historical baggage that MySQL got stuck with from it's early popularity. mSQL was a free/low cost SQL database in the early 90s. Originally it was an SQL translator built on top of Postgres (that used POSTQUEL). That was too slow (because Postgres had higher system requirements), so a new lightweight engine was developed for it.. and that's what later became MySQL. The point was a lightwe…
[deleted]
Re: Postgres 11 – A First Look
#87What are some reasons I might consider Postgres over MySQL when choosing a DB platform?
Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).
Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message.
I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function.
A MySQL DBA solved that problem in that project I worked on years ago without queries. He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. My friend told me he wants to code that query, no triggers, not even an INSERT OR UPDATE from his PHP code. I replied to go on, he'll learn a lot of things and I hoped this will make him pick PostgreSQL next time.
Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.
Re: Postgres 11 – A First Look
#88What are some reasons I might consider Postgres over MySQL when choosing a DB platform?
With Postgres you don't need MongoDB, InfluxDB, or any other trendy thing, Postgres does it all, and better than all the wannabes.
Re: Postgres 11 – A First Look
#89Earlier quoted context omitted.
100% naive question: why is mysql and their similars so popular then? Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
MySQL was easier to run. Both for beginners where it was packaged with PHP and available with basically every webhost, and for advanced users that took advantage of the simple and reliable replication. There were a lot of problems with it, but the novice users didnt run into them and the advanced users knew how to deal with it. These decades of mainstream use carry a lot of momentum and many large companies continue…
PostgreSQL has been useable Long before 9.x, however I think features of 9.x have made it much more popular as of late.
I think I first started using it at v6 or 7. I liked it could I could run it from a .bat file and spin it up for tests without installing it.
Re: Postgres 11 – A First Look
#90Earlier quoted context omitted.
Have you tried pg_upgrade?
Yes. I still don't understand why it failed. It gave me a huge list of locale related reasons it felt unhappy, and since I hadn't selected a locale config option installing either the prior, or the current (9.4 -> 10.x) I felt pretty wierded out.
Postgres 9.4.0 was released 2014-12-18
The version convention has changed with v10.0 so it should be trivial to change from 10.x to 11.x . In your position I'd be updating 9.4.x -> 9.5.x -> 9.6.x -> 10.x