Postgres 11 – A First Look • Hacker News Board

Re: Postgres 11 – A First Look

#151

Post by evanelias » Sat, Sep 22, 2018, 3:23 PM UTC

rgbrenner wrote: post #67

dotancohen wrote: post #53

Earlier 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…

> The compromises to make mSQL fast were inherited by MySQL, and they can't be easily changed without breaking the ecosystem that was developed around mSQL/MySQL.

This doesn't sound accurate to me. Which compromises are you referring to?

Historically, MySQL's major source of criticism related to leniency of type safety / automatic data conversion -- which is unrelated to performance. It's also essentially a solved problem with the advent of strict sql_mode. MySQL made this the default in 2015, but it has been available (and recommended as a best practice) since 2004.

Performance in MySQL, as a general topic, greatly depends on the storage engine. Relative to Postgres, MySQL's pluggable storage engine API is both a blessing and a curse -- it permits use of alternative engines that perform significantly better for specific workloads, at the cost of substantial administrative complexity.

Performance-wise, Postgres generally has the lead for things like number of supported index types, join strategies, query planning for very large queries (and OLAP workloads in general). These may or may not matter for you, depending on your workload.

Re: Postgres 11 – A First Look

#152

Post by duckerude » Sat, Sep 22, 2018, 3:50 PM UTC

tofflos wrote: post #33

1stranger wrote: post #6

Earlier quoted context omitted.

Practically any interactive shell. python or bash for instance. It's everywhere.

Except Powershell. :(

It does on Linux.

Re: Postgres 11 – A First Look

#153

Post by evanelias » Sat, Sep 22, 2018, 3:56 PM UTC

mrep wrote: post #37

kstrauser wrote: post #31

A million things like this are why PostgreSQL is the only relational database I consider for, well, pretty much anything. Their approach of “build it safe and then make it fast” has been paying off in spades for a couple of decades now. Thanks for everything you do, psql maintainers!

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?

The question of MySQL's popularity comes up often in HN Postgres threads. The answers naturally skew towards being a bit one-sided, as most people clicking on a Postgres article tend to be Postgres users currently (and sometimes former MySQL users in years past). This can result in comparisons of modern-day Postgres to 5+ year old versions of MySQL.

That said, and with a disclaimer that my own bias leans the other way (as I have a lot more MySQL experience than Postgres experience) although I try to be impartial about these things:

* Spanner: I've heard this second-hand and might be incorrect, but as I understand it, Google's original internal MySQL team was shuttered in ~2009 and some of those folks may have transferred over to the Spanner team. I wouldn't say that Spanner is particularly based off of MySQL anyway though, but I don't have enough familiarity to say that conclusively.

* Aurora: AWS now offers a Postgres-based Aurora as well. As for why the built the MySQL one first, I'd assume that was likely a business decision based on mysql-vs-postgres RDS usage at the time.

* Those two aside, there are definitely major products based around Postgres. AWS Redshift is one example. Or look at CockroachDB, which chose wire-protocol compatibility with Postgres. (There are some examples the other way too; e.g. TiDB, which chose wire-protocol compatibility with MySQL).

* Regarding why MySQL has historically been popular, there are a lot of factors. I'd say the biggest one for large users has been replication; some of my older HN comments delve into that more, https://news.ycombinator.com/item?id=16880663 for one example. For smaller users, ease-of-use has been important. The other discussions in this thread hint at this, especially around silly simple things like the "exit" command.

Re: Postgres 11 – A First Look

#154

Post by evanelias » Sat, Sep 22, 2018, 4:05 PM UTC

petre wrote: post #50

deytempo wrote: post #45

What are some reasons I might consider Postgres over MySQL when choosing a DB platform?

JSON support, ability to query it, JSONB support, real spatial extensions, timescale extensions, consistent behaviour, online backups. We now have to migrate from MySQL to Postgres because of these features. Instead of MySQL 8 we will be probably usng Pg 11. Now we are using MariaDB.

> JSON support, ability to query it

MySQL 5.7 has this, and MySQL 8 improves on it further. Note that MariaDB's implementation is substantially different than MySQL's here.

> consistent behaviour

This one's a bit vague :) But generally using strict sql_mode and innodb_strict_mode go a long way towards alleviating this common complaint.

> online backups

Online physical backups can be created with Percona's xtrabackup, which is open source. Or for online logical backups, there's always mysqldump, or third-party concurrent options like mydumper.

> real spatial extensions, timescale extensions

Yeah these ones I definitely see Postgres having a substantial edge, if they're important to your application or workload.

Re: Postgres 11 – A First Look

#155

Post by lkbm » Sat, Sep 22, 2018, 4:28 PM UTC

jorams wrote: post #3

> 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.

I'd propose that "quit" should do two things:

1. Print "You can also quit using Ctrl + D"

2. Quit.

But I also believe that only doing step 2 is better than neither. psql should prioritize psql's usability over educating users on Unix standards.

Re: Postgres 11 – A First Look

#156

Post by scurvy » Sat, Sep 22, 2018, 4:43 PM UTC

spion wrote: post #136

philliphaydon wrote: post #89

Earlier quoted context omitted.

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.

Replication is still a pain.

Indeed. MySQL replication topologies with GTID are super easy to maintain and run. Does Postgres still require "extra" tools like pg_rewind to do a simple operation like turning a former primary server into a new replica?

Some of the MySQL backup/restore stuff is a lot more straightforward (innobackup vs WAL shipping).

Re: Postgres 11 – A First Look

#157

Post by cookiecaper » Sat, Sep 22, 2018, 4:46 PM UTC

phaer wrote: post #102

edoceo wrote: post #85

Earlier quoted context omitted.

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)

Yes, but the proportions between "bad", lets rather call them inexperienced, "coders" and experienced coders varies strongly in different ecosystems. PHP and JavaScript both democratized programming, as BASIC did years before, but that also meant that more tutorials and howtos for PHP were written by people relatively new to programming compared to say howtos on Haskell or C.

It's an "Eternal September" effect that never really wears off. I'm growing increasingly concerned that this is descending upon Python now. The internet as a whole is a wide enough system that it was able to withstand its Eternal September (though it's arguable that newsgroups weren't), but any specific language ecosystem, I fear for the moment it hits "mainstream".

Re: Postgres 11 – A First Look

#158

Post by cookiecaper » Sat, Sep 22, 2018, 4:50 PM UTC

scurvy wrote: post #156

spion wrote: post #136

Earlier quoted context omitted.

Replication is still a pain.

Indeed. MySQL replication topologies with GTID are super easy to maintain and run. Does Postgres still require "extra" tools like pg_rewind to do a simple operation like turning a former primary server into a new replica? Some of the MySQL backup/restore stuff is a lot more straightforward (innobackup vs WAL shipping).

GTIDs are relatively new and require some porting work to implement on pre-existing databases. MySQL replication is a massive disaster without them. Like most things in PostgreSQL, there is a bit of extra legwork to get things set up right up front, but when they work, they work.

MySQL replication with binlog_pos was a total crapshoot. The reason innobackupex exists (and anything from Percona, to be honest) is because MySQL's default options were so unworkable. The Postgres equivalent is probably something like barman.

Re: Postgres 11 – A First Look

#159

Post by mixmastamyk » Sat, Sep 22, 2018, 5:14 PM UTC

dtech wrote: post #94

apapli wrote: post #52

Earlier quoted context omitted.

I’m no expert on this topic and have used both PG and MySQL. My preference is MySQL purely because the syntax is easier, or at least consistent with what I learned at uni in the late ‘90s. I also like MySQL workbench, although I’m sure there is an equivalent for PG I haven’t needed to look for one in recent years. If PG allowed me to use the same syntax as MySQL I’d probably switch, purely because experts and those w…

MySQL workbench is still 10.000 miles ahead of PgAdmin. About three years ago the latest iteration PgAdmin 4 came out, which I tried on and off for a few months but was so buggy it was unusable for me. It might have gotten better since then. It's a shame, psql is awesome once you know enough to use it, but GUIs is how people start using RDMSes, and it's about the only thing that MySQL is clearly superior to PostgreSQ…

Ever tried dbeaver?

Re: Postgres 11 – A First Look

#160

Post by scurvy » Sat, Sep 22, 2018, 5:16 PM UTC

cookiecaper wrote: post #158

scurvy wrote: post #156

Earlier quoted context omitted.

Indeed. MySQL replication topologies with GTID are super easy to maintain and run. Does Postgres still require "extra" tools like pg_rewind to do a simple operation like turning a former primary server into a new replica? Some of the MySQL backup/restore stuff is a lot more straightforward (innobackup vs WAL shipping).

GTIDs are relatively new and require some porting work to implement on pre-existing databases. MySQL replication is a massive disaster without them. Like most things in PostgreSQL, there is a bit of extra legwork to get things set up right up front, but when they work, they work . MySQL replication with binlog_pos was a total crapshoot. The reason innobackupex exists (and anything from Percona, to be honest) is becau…

Hyperbole much?

MySQL replication was workable before GTID. GTID simply made things a lot easier. GTID was hard to work into existing DB's at places like Facebook, but it we managed to get things going without a DBA in several multi-TB databases (sans downtime).

GTID is over 4 years old at this point. That's not relatively new in the tech world. Especially with the pace of things like Kubernetes and containers.

There are 3rd party additions to Postgres, too. pg_rewind was written by eBay (?) to address the obvious shortcomings of repointing primaries and replicas.