Hacker News Reader

Comment by wiremine | Hacker News Reader

> The trends for the last 8 years are clear: Oracle and SQL Server are constantly declining in popularity; MySQL is slightly declining; and PostgreSQL is clearly growing in popularity. But while PostgreSQL almost tripled in popularity in these eight years, it is still far behind the other three.

It's great to see Postgres growing, but I often wonder why it isn't growing faster, especially when compared to MySQL. Without getting into a religious flame war, I'm curious what the HN thinks about that?

View on HN

> I'm curious what the HN thinks about that?

People don't need those white-label commodity cPanel or Plesk shared-hosting or VPS services to run SaaS applications anymore - it's far better for everyone[1] when they switch to a major cloud provider, namely AWS, Azure, GCP, etc.

I argue that most web/saas devs today grew up with - and tinkered-with - said white-label services: which means they got their experience with MySQL because it was part of the stock default configuration for all those web-host accounts: with PHP, maybe Perl, and many preinstalled web-applications like phpBB, Wordpress, Coppermine, phpNuke, and so on... and that's probably a solid 15+ years of commonplace web-hosting market saturation (thinking 2001 through 2016, with 2014 being the tipping-point for AWS/Az/GC being the home of SaaS).

You'd be in high-school age-range (14-18?) and get bored of simply running other peoples' programs on your web-space, and you saw these web-applications were written in PHP so you'd follow some series of online tutorials for PHP and when it inevitably leads to databases they assume you have MySQL - and MySQL (at the time) had not only the widest installbase, but also had a far more forgiving SQL engine than anyone else. While PostgreSQL was often only a few clicks away via quick installers built-in to cPanel/Plesk/etc, MySQL was a safer-bet by everyone involved.

...but those days are past: as a personal anecdote: at no-point in the past 10 years have I come across any "serious" PHP web-application software intended for private or on-prem usage since vBulletin or SVN.

[1]Except free-as-in-freedom software advocates...

I think around that time, postgres was handicapped because of performance reasons. It was considered slow because of ACID compliance and MySQL was all the rage with MyISAM.

If only postgres won the marketing battle back then...

> If only postgres won the marketing battle back then...

Be glad they didn't - otherwise their support dept. would be buried under questions from web-hosting newbs.

PG also shipped with EXTREMELY conservative defaults for buffers/etc., so it ran like total ass 'out of the box' for years. A quick change to buffer allocations and things in the config file made it fly, but you had to 'know' that was a thing, where mysql just...didn't need that tweaking.

> People don't need those white-label commodity cPanel or Plesk shared-hosting or VPS services to run SaaS applications anymore - it's far better for everyone[1] when they switch to a major cloud provider, namely AWS, Azure, GCP, etc.

Why?

> at no-point in the past 10 years have I come across any "serious" PHP web-application software intended for private or on-prem usage since vBulletin or SVN.

There's Phabricator [0], which incidentally uses MySQL.

[0] https://phacility.com/phabricator/

Enterprise dba shops have deep expertise with oracle (and sometimes sql server). I have personally run into serious resistance from these teams when I explicitly ask for postgres. They try to warn me that oracle is the recommended option for real production use cases because of the deep in-house expertise and that I am pretty much on my own if I go with postgres. Most people back off at that point.

I've seen this so much, and can empathize with them. When you have a product that took you years to master, the value that you bring to your employer is wrapped up in that product. So it isn't so much that someone is a DBA, they are an Oracle Database software specialist. You've learned all the quirks so that when something goes south, you "have the answer". So naturally you will have a number of reasons why another product isn't as good.

I feel the same way with Unix/Linux. At one point I was told that my organization may be migrating away from Linux, and redeploy everything under Windows. The management layer really couldn't understand that they would lose nearly every Linux admin on staff (that happened to also have deep understanding of their apps and infrastructure). "Why would someone have a problem with switching platforms -- we'll provide you the training" was their answer. Fortunately we were able to come up with a number of technical (and cost related) issues that the project was shot down.

At sufficiently large companies, deep in-house experience with a given DB technology may be roughly equivalent to a startup engineers deep experience on their companies current monolith.

Companies with large and complex db installations often need the complexity for various reasons that are difficult to escape, even if you're working on a green field project.

PG over mysql is obvious IMO. PG over mssql, not so obvious. People often forget the 'management system' part of RDBMS. MSSQL provides an enormous amount of tooling in the typical enterprise install. Analysis services, data tools, etc... provide a lot of value beyond a data store.

Of course if you don't need any of these additional tools or can't afford them, then yeah pg is a great choice.

Cost is a really solid argument against MSSQL. Even at Azure, MSSQL costs ~6x as much as the open-source databases. For even relatively small workloads, it's not hard for the MSSQL license to cost as much as an FTE.

Aside from the raw cost, the licensing model and cost considerations can drive design decisions. In my experience, most shops that use MSSQL do so because nobody on staff has experience with other databases.

Sure, but we're talking about enterprises. Back when I could walk down the hall to the datacenter, paying $30k for the hardware and $60k for licenses was normal. Every time I bought a new server I tried to make a case for pg or mysql, but the cost to replace SSIS, SSAS, or SSRS either with another product or to build a replacement was always more.

> Cost is a really solid argument against MSSQL

If you're a small 2-5 person team, sure. But once dev payroll breaks $1M/yr, paying ~$100k for SQL Server doesn't seem so bad anymore.

That doesn't match the economics at any company I've seen. Dev payroll of $1mm/yr gets you something like 6 devs or fewer in the US.

I have yet to encounter a team that would rather have SQL Server than another developer given the explicit choice between the two (assuming easy migration, etc.).

Are there no equivalents to these tools for PG?

I've only interacted with Analysis Services and Reporting Services for maybe five minutes, but the impression I was left with is that they're extremely clunky, even by Windows standards. I also remember a weird situation with SSL wildcard certificate configuration, where the interface said the configuration wasn't applied, but it actually was.

My client subcontracts this to a supposedly "expert DBA" shop, and they always seem to take ages to do what look like simple things on the surface. (I rarely if ever interact with them, so I don't know whether they're actually competent or not.)

Tools like SSIS, SSAS, SSRS were hard to replace for less money for a very long time. There are products out there now, but they are not cheap either. I still think SSIS was one of the easiest and most flexible to use ETL tools out, and it was included with MSSQLs enterprise license.

> Analysis services, data tools, etc... provide a lot of value beyond a data store

They probably do if you have certain needs, but at lots of places they seem to be the basis for solutions that would be far less complicated (and maintainable with a far more common skillset) without them.

Sorry, OLAP and ETL are real problems that need to be solved, SSAS and SSIS were (and still are in many cases) as good as the next tool for solving them, especially with their enterprise features like security and auditing/logging, which are almost always nonexistent in opensource / homegrown solutions.

Tools like Airflow and Druid are just now getting to where those tools were in 2005 ... unfortunately those tools haven't done much since about 2015 when Azure became the new hotness.

Or the same issue form the other side. It's damn near impossible to hire a good DBA in some areas and so if you find someone who has deep magics with MySQL then you go with MySQL. In a huge complex app they're all good options except for Mongo and so going with what your team knows is far more productive.

> Enterprise dba shops have deep expertise with oracle (and sometimes sql server).

IME, that “deep experience” often means “a very expensive support contract and internal technical staff who walk through decade+ out of date cargo cult rituals by rote without understanding the rationale and why it has been gone longer than they've been employed”.

Have used both extensively. I generally prefer MySQL because I find it easier to manage for a few reasons:

1. MySQL has a simpler permission model, so user management is less of a headache.

2. Connections are cheaper in MySQL so you don't have to use an external connection pooler like pgbouncer.

3. There's more/better documentation on MySQL performance tuning, especially from Percona.

That's really it for me.

Permissions management in Postgres is indeed a pain.

I agree, but can you enumerate a bit more what specifically is painful?

Try the lock down e.g. a user to only allow reads. Not easy, when testing if it‘s working you will see so many „strange“ behaviours e.g. you can still create tables in public namespace, databases created before/after (dont remember which one) your user are accessible etc...

Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier.

> Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier.

I don't think this is quite right. When a new user is created in Postgres, I don't believe any permissions are implicitly GRANTed by default aside from CONNECT. And I believe the only default privileges granted on a given database are to its owner. Is it possible your experience has only been with configurations in which your PG user was either the owner of the database or a superuser?

EDIT: clarification

My statement was not correct, you are right. But every user/role in postgres has by default the rights to create objects in the public schema and to connect to every database [1]. The only way to remove this is to remove the rights on the public role affecting every other user. In this case the mysql concept is better as every new user has by default absolutely no rights.

[1] https://aws.amazon.com/de/blogs/database/managing-postgresql...

In particular, note the justification for the default:

> Keep the default. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users.

And: https://www.postgresqltutorial.com/postgresql-schema/

I agree that the whole PUBLIC thing is pretty confusing, and I doubt anybody would design it the way it is if we were start from scratch.

Note that you can change the set of default privileges, including the grant to PUBLIC. E.g. ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC;

> The only way to remove this is to remove the rights on the public role affecting every other user. In this case the mysql concept is better as every new user has by default absolutely no rights.

This seems a bit contradictory...

A significant annoyance is that permissions cannot be encapsulated within a database but are global to the instance, requiring extra consideration at every step, from the very logic of your migrations, to how you dump/restore.

To the point number two: even though I love to use PostgreSQL, when I hit the point where I need pgBouncer and its transaction mode, things get quite ugly especially if you've been using a lot of prepared statements. Now you need to clean the statements from the connection after every use or get conflict errors if another connection using the same connection from the bouncer wants to declare a statement with the same name.

Or you just use the text protocol and escape your parameters in the app...

Personal guess: because switching DBs is expensive and if it works, why bother?

I personally LOVE pg over all other choices, but I've got some 5-year-old apps sitting on a little mysql DB and no intention to move them. Dbs are small, performance is good enough, cost of instances wouldn't be very different on PG, but cost of moving everything over is probably a week or two of work that would be pretty hard to justify given the current situation.

1. MySQL is offered as the default "open source" database in most discussions. This puts the onus on Postgres to people who go "I need a better database", rather than people going "I need a database" in a "Nobody got fired for choosing MySQL" way.

2. Companies looking for enterprise support don't see anyone as high profile as Microsoft, Oracle, and err, Oracle supporting Postgres.

(Whether ongoing interaction with Oracle is a net positive is of course debatable. At a previous company our only interaction with Oracle was them being inflexible and expensive that ultimate meant we had a Oracle DB on prem serving a AWS hosted service because it would have cost too much to put it in AWS. Note, this was many years ago, before AWS had a compatible offering, and apparently Oracle even have loosened up a bit here since.

MySQL/MariaDB have several relatively easy ways of building multi-master setups. Postgresql doesn’t really have a that great solutions for master-master replication. Our customers go for MariaDB to get master-master replication

We see some customers building new solutions on Postgresql, but for large setups, it’s still MariaDB/Galera or Oracle and there isn’t a big push to drop Oracle.

Postgres doesn't have a great solution for any replication, not just master-master.

Lots of things are just half-baked and very clumsy in Postgres compared to MySQL (MariaDB).

Streaming replication works great in Postgres.

Would downvote if I could, PostgreSQL has Patroni, for example, the newest replication management tool, which works. They got rid of the requirement for an external DCS with the latest major release, so.. it's easy to set up and manage

When PostgreSQL would get a built-in replication tool would increase it's popularity a lot, but I guess the core team wants to focus on the DB, and management tools are left to the others to develop.

I think you just proved my point.

IMO replication is a core feature of a DBMS (like transactions or SQL support), the fact that you need to install obscure third-party tools to bring Postgres into the modern age is bonkers.

At my current gig, we've been burned pretty badly by scalability problems with MySQL, and the prevailing wisdom is that new functionality and services that have to deal with data at scale should be built on Cassandra. My cries of "we wouldn't have this problem with Postgres" have mostly fallen on death ears.

Can you elaborate on the specifics of the scalability problems, and how the different databases do differently on them? It's an interesting subject.

We're having scalability problems with our RDBMS, but it's something RDBMSs in general don't solve.

We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended period. In Postgres this operation is instant no matter the size of the table.

This not the only problem, but it is the most pernicious

MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...

I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op....

Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues.

¹=https://github.com/github/gh-ost

> adding a column is instaneous on MySQL 8

Has been since MySQL 5.6 (released Feb `13).

In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.

Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data.

¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...

²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...

Adding a null column on PostgreSQL is instant. Import your new column data then apply defaults and constraints. This is how people have avoided length locking periods on production databases for years.

I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.

Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios.

For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished.

Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan).

PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.

> PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed

So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks.

I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).

In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)

MyISAM was historically notably faster for simple SELECTs on the primary key and SELECT COUNT(*).

InnoDB had ranged from slightly faster to much slower than Postgres depending on exactly what you’re doing. Once you’re doing more than trivial queries the MySQL optimizer tends to fall apart, often in weird ways with arbitrary thresholds where performance goes from decent to terrible after a table grows by 5%.

I worked at a place that was running SQL Server 2012 in compatibility mode with an older version. The place had a pretty high turn over rate, so a lot of the expertise is in the existing tech rather than the people. It was hard enough to try convince them to upgrade SQL Server (I wanted to leverage row level security to get away from having a dozen copies of what was essentially the same app, but that required SQL Server 2016)

Also, when I brought up postgres for a new project & mentioned it being open source, response was "that means it could vanish any day"

Inertia is real

Wordpress is probably the elephant in the room.

The popularity of PG relative to MySQL is going to be unavoidably skewed by the popularity of WordPress and its tight linkage to MySQL.

OP.

This is a very good and fair question. Postgres is growing a lot, but is it growing enough, according to its true potential? It has everything: incredibly robust and trusted; very large feature set; not under any company's direction; extremely liberal license. Should be conquering the database market, and by far is not!

I don't have an answer. I have potentially, many. Possibly I will blog about this at some time. But I believe that definitely it should be growing more and becoming more relevant than it is right now.

This is no detriment at all to all the fantastic work done by everybody; but just the ambition that Postgres can and should go farther.

Postgres never gave me the ability to quickly and easily build stored procedures that return multiple heterogeneous resultsets with ease. In SQL Server you do it like this:

    CREATE PROCEDURE get_customer_and_orders
      @id int
    BEGIN
      SELECT id, first_name, last_name, email, etc FROM customers WHERE id = @id;
      SELECT id, store_id, created_at, etc FROM customer_orders WHERE customer_id = @id;
    END

I've quickly built entire applications with this tactic as the centerpiece. You can argue that it moves business logic into the database layer and to that I'd say "good", at least for apps that are maintained by IT departments with many strong SQL people and not so many developers. If you know TSQL, you know that you can also do branching, looping and other logic operations within this same procedure - you can even decide to send back 3 resultsets instead of 2 if you want to and the client API allows you to handle whatever it received quite elegantly.

I think that features like this are why many businesses will stay on SQL Server. Also the high quality of tools for SQL Server that have no match in Postgres such as SQL Server Management Studio, SQL Server Data Tools, SQL Server Profiler, SQL Server Integration Services among many other such tools that are extremely well integrated.

My limited experience with smaller companies (and now local government) shares this sentiment. For “small” databases (say, not hundreds or more of terabytes) I’ve experienced using stored procedures as a core of the ETL process.

That being said, haven’t some recent versions of Postgres added support for stored procedures or some variant? I’m curious if we’d seen any changes in performance if we experimented with switching over.

They did, but there is still not a good way to return multiple result sets... I mean you can return cursors, but it's...strange.

This isn't meant to detract from your point, but just a fun anecdote -- my first job out of college was working on a product that had its entire business logic layer built in stored procedures. All of it. Hundreds and hundreds of lines per procedure. It "worked", but man was maintenance a bear. In a way, it was good; ever since I've been very comfortable with SQL. But I can't recommend it.

I wrote a couple apps like that. I'm not sure I recommend it, but those apps still work like clockwork, years later.

The example you gave makes no sense. It would be better expressed as a join. There may be examples where it makes sense, but I can't think of any. In SQL Server, I think of a stored proc as a logical operation that takes action and returns one object. If I want sub-structure within that object, I have the stored proc return one json or xml object.

The example given allows the application layer to instantiate a customer object with a list of order objects in one database query. Yes, you could do that by returning JSON or XML, but why have the extra step of parsing the text? Directly accessing the result set and acting on the object instances will be more efficient.

The example is better expressed as a join.

No it's not. A join means you have to wastefully repeat the same parent fields for every row. That's wasted cycles serializing and deserializing and it's more work on the client.

It's absolutely ridiculous to make a blanket statement about this very useful feature.

I've done the benchmarking on many applications of this tactic. It very often works better with multiple select statements. I'll certainly trust hard performance numbers against an ill conceived opinion any day.

And yet, MARS is not great for troubleshooting or testing (for database people writing SQL) and I have worked at many places where the complexity of many sets doesn't really justify not making two procedures.

Also the freetds guys hate it :) https://www.freetds.org/mars.html

(I am currently converting a MARS app explicitly so it never does utilizes this approach)

Perhaps I'm interpreting your code incorrectly, but this seems like you can do this with PG as well? You just define a custom type and use that type in the "returning" clause? You can easily define a bunch of scalars (customer_id, first_name, ...) and an array (customer_orders[]) inside this type.

That is one way to do it, kinda sucks compared to just returning multiple result sets because you have the overhead of maintaining those custom types for every type of query you're going to need. I don't personally like that solution much for complicated apps which will have a ton of queries written against their database.

I see that as an advantage. What you're really doing is defining the interface for that function, and once the function is in use, you can't just change it willy nilly. Having to alter the type is in some way a "are you sure you want to do that?" layer.

It would be extremely nice if you could just specify that directly in the function and not have that sprawl where it's unnecessary. For a single result set you do exactly that with PG, just wish it would allow you to specify multiple.

As a contractor, I'm glad that MySQL is still around. It's an extremely useful red flag for potential clients.

It is almost guaranteed that if a client is running on MySQL, you can be certain their entire code base is going to be plagued with less-than-best-practices. Not to be too harsh, but MySQL's most prominent use case is for projects that start with "let's just stand-up a DB real quick and we'll sort out the hard stuff later".

Golly, if that doesn't describe my current employ. Everything about the mess I inherited reeks of, "let's just...and sort out the hard stuff later"

I think a lot of people just don't know the new capabilities of PostgreSQL. PostgreSQL's competition isn't MySQL, its the new hybrid DBs like CockroachDb.

DBAs and Engineers that know MySQL will always use MySQL. In corps MySQL is a safe choice (sadly).

MySQL is a bit simpler even though pgadmin is a wonderful tool for learning postgres advantages. For me the difference became clear when needing to plot locations on a globe. PostGIS is super helpful

MySQL has way better UX for command line users. Postgres can't get away from legacy Ingres design, with a postgres user and a horrible command line client. Also, database/schema/tables hierarchy is a penalty over database/tables in MySQL.

psql is easily one of my favorite tools. What makes the CLI "horrible" in your mind?

Also, database/schema/tables is great when doing things like multi-tenancy where you split customers by schema (which also makes horizontal scaling super easy if needed).

I would say the commands are just not intuitive.

For example, when you type quit instead of quitting it instead prints out that you must type a different command to quit

Last time I tested (literally seconds ago) this works.

All of \q, quit, and exit work fine starting psql 11?

Oh good to know!

Ctrl-D works fine for me.

I fail to see how Postgres' hierarchy is any worse than MySQL. You have one more layer of hierarchy which you do not need to consume, with the added feature of atomic renaming any of the layers.

If I'd compare this to MySQL, where you must move tables one-by-one to another schema (nay database) with the new name to 'rename' your database [0], then I'm very happy with postgres' hierarchy structure.

[0] That is, if you're even allowed to, because if you have a trigger on your table, good luck renaming your database. See https://dev.mysql.com/doc/refman/8.0/en/rename-table.html

People go with what they know, the the LAMP stack was a classic for multiple years. And even today PostgreSQL's HA tooling isn't that great ( one can set up MySQL/MariaDB in active/active in a few minutes, and that's impossible with PgSQL, and even active/passive with easy failover requires more work and oftentimes external tooling).

How many people need multi master, though? I'd guess it's much less than 1% of user base

It's not about needing it, it's about the security of knowing that you have proper redundancy that will work if you need it ( e.g. one goes down) without manual intervention.

In my little world, MySQL was popular because we could get enterprise level support directly from MySQL. It was the default choice from that point on. I don't know if that has changed. AWS changed that pretty quickly.

In terms of overall adoption, PostgreSQL doesn't need to catch Oracle or MySQL. The people that enjoy legacy platforms are not going to change (necessarily).

Real work gets done with PostgreSQL. You can't buy something from Amazon.com without using PostgreSQL. The more Amazon talks about that in their ecosystem, the more PostgreSQL becomes the default choice for all new developers within that (very large) ecosystem.

When you combine that with the loud presence of another 800lb Gorilla promoting PostgreSQL (MSFT), the market has spoken.

Reply on news.ycombinator.com