15 ms·

Postgres 11 – A First Look

390 points by samaysharma 8y ago

kstrauser 8y ago
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!
- mrep 8y ago
  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?
  - johncolanduoni 8y ago
    Probably because compatability with MySQL is more valuable than comparability with Postgres from a product perspective.
  - tejtm 8y ago
    Will make an attempt to answer, hopefully without starting anything. Mysql aimed for lowering bar for non-dba-app-developers. Postgres did not. In some cases it matters, in many it does not, especially if you have the dba skills on hand to mind the gaps.
  - ma2rten 8y ago
    MySQL used to be more popular. It was the M in the LAMP stack, which virtually everyone used to use. It also used to be more scalable, because PG's replication story used to be poor. This is why Google and (I assume) Amazon used MySQL internally. This is all a long time ago, but I suspect this is the reason more people are familiar with MySQL than Postgres at these companies. Keep in mind that Spanner was developed and used internally at Google long before it was publicly announced.
    - mrep 8y ago
      > This is why Google and (I assume) Amazon used MySQL internally. Amazon actually uses a lot of Oracle with some scattering of MySQL.
      - reitanqild 8y ago
        That's weird. But maybe at their scale even Oracle can be made tolerable. That, and they have some serious leverage in negotiations. (I used to deal with Oracle DBs a lot a few years ago but thankfully not anymore.)
      - tfehring 8y ago
        It looks like Amazon's migrating away from Oracle pretty aggressively, however. https://www.cnbc.com/2018/08/01/amazon-plans-to-move-off-oracle-software-by-early-2020.html https://www.cnbc.com/2018/08/01/amazon-plans-to-move-off-ora...
  - manigandham 8y ago
    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 to use it because they've always used it (like Youtube for example). PostgreSQL only recently became much more usable after v9.5 and is now being recognized and growing in popularity.
    - philliphaydon 8y ago
      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.
      - spion 8y ago
        Replication is still a pain.
        scurvy 8y ago
        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).
        cookiecaper 8y ago
        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.
  - rgbrenner 8y ago
    History. A long time ago, in the early 90s, there was a database called msql. It was a little DB that used SQL and ran well on low end systems. It was also free. In fact, it was the ONLY free SQL database out at the time -- that's why it was written. Because of those two advantages (that really only lasted a couple of years), a small ecosystem developed around it. This was the database you used to develop interactive websites in the early/mid-90s. Then MySQL came. MySQL extended mSQL, and kept compatibility with it's ecosystem... and people switched from mSQL to MySQL. Postgres also existed in the 80s and 90s, but it used POSTQUEL, was harder to setup, and had higher system requirements. Not a lot, but enough to discourage use on low end computers. (Remember, Postgres at the time was being developed on university servers, where they had plenty of system resources.) They did address these points in postgre95 (in fact, Postgres adopted SQL as a response to mSQL popularity), but by then it was too late. mSQL and MySQL already had an ecosystem.. and in that brief moment, they lost the race. The idea that it was slower stuck around for many years, even after the improvements in computers, and (less so) optimizations in Postgres made it no longer true. Postgres didn't start being considered again until Go, RoR, et al started to become popular.. displacing the PHP ecosystem. And the biggest reason you see MySQL around so much today is that there's about 25 years of legacy code built around it. Edit: If you're curious what POSTQUEL was like: https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
  - apapli 8y ago
    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 with more experience than I generally prefer PG for a whole lot of reasons. EDIT: I should have googled first before replying. Looks like the syntax is near enough to identical for my simple needs and pgadmin will do what I need. I’m not sure why I had to learn some other weird syntax in 2012 for postgres, perhaps it was just shortcuts. Either way I’m going to seriously consider migrating now. :)
    - akavel 8y ago
      What syntax do you have in mind? Can you give sone example of what you see as easier in MySQL than Postgres? Genuinely curious, I don't have money in that game
      - apapli 8y ago
        I recall having to type /d from the command prompt to show databases (or something like that). I prefer “show databases”.
        mrep 8y ago
        Haha, same. When I first used a postgres database, I got super annoyed trying to figure out how to simply show the databases and tables as I kept trying to use show and describe.
        petepete 8y ago
        The standard way to do this would be to query `information_schema. tables`. PostgreSQL's approach isn't the most intuitive but it's well documented and very powerful. A few aliases could probably help, it's good they've added `quit`/,`exit` this time around.
      - mgkimsal 8y ago
  - dotancohen 8y ago
    > 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 not know the difference between character encodings. What does he care what MySQL does, he knows that he can put strings in if he calls them VARCHAR, and he can pull the right one out with a WHERE. He cares not enough to check that user bios fit in his VARCHAR, and when he learns to JOIN he does not understand which constraints to put in the ON clause. I do have nicer words for the L and A in the stack.
    - rgbrenner 8y ago
      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 lightweight SQL database that ran well on cheap early-90s computers. 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. Postgres on the other hand, was a university project in the 80s and 90s being developed on mainframes. It was always a slow moving, cautious project. Yes, it didn't make the compromises mSQL made, because it didn't have to. It used more memory, more CPU, but was more correct/safe than MySQL. That was the same reason it lost in the 90s. mSQL was out being used by websites and other projects on cheap computers. Postgres was mostly waiting for computer tech to get fast enough so they wouldn't need to compromise their code too much to take it off the mainframe an on to cheaper computers. Postgres only adopted SQL in response to mSQL's popularity. Early Postgres was also harder to setup, another point that wasn't addressed until after mSQL took off. MySQL was popular because of the compromises they made. If mSQL just kept Postgres as the engine, MySQL never would have existed. And if they instead waited (even a couple of years), and did things correctly, they wouldn't have beat Postgres -- which had a decade lead in development and was more advanced than mSQL/MySQL.
  - samcheng 8y ago
    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 software, but I remember spinning up a phpMyAdmin instance even after moving away from PHP as an application language. Postgres still doesn't really have anything like that! The closest that comes to mind is Django's Admin tool.
    - rgbrenner 8y ago
      phpPgAdmin, started ~2002: http://phppgadmin.sourceforge.net/doku.php http://phppgadmin.sourceforge.net/doku.php Basically a port of phpMyAdmin to postgres. I used it for several years around ~2003-5.. it's nearly identical.
      - luhn 8y ago
        I’m a regular Postgres user and fan, but cut my teeth on LAMP. I can’t speak to the current state of the projects, but phpMyAdmin was vastly superior to phpPgAdmin in the 2000s. phpMyAdmin remains the best DB GUI I’ve ever used. (Although I haven’t used any GUI in quite a few years.)
        deleted 8y ago
        [deleted]
    - gbuk2013 8y ago
      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.
    - 8y ago
  - twunde 8y ago
    Historically postgres was slower than MySQL, harder to manage than MySQL, wasn't available on shared hosting and generally speaking wasn't beginner friendly. Because of this a generation of programmers learned MySQL, especially as everything PHP used MySQL. As such the install base for MySQL, was quite large so there were more potential customers for MySQL projects (WordPress, drupal and Joomla all run on mysql, so we're talking about 50% of websites before talking about custom applications)
  - jpalomaki 8y ago
    For the Spanner and Aurora: MySQL has a lot less features. For example on the query side it has been really limited compared to Postgres. Maybe this was a plus in this case. Less features make it essier to optimize.
  - gwn7 8y ago
    Probably accessibility. Because MySQL for some reasons is more accessible[1] to beginners, juniors, average developers, and other people who may prefer a lower barrier of entry over correctness/safety (e.g. full-stack devs, managers, people who don't have much time); who form the bulk of the community combined. It's the same for languages, frameworks, and other tech; as several people noted already. [1] Or maybe "was"; in which case the answer becomes "historical superior accessibility".
  - Keyframe 8y ago
    MyISAM made it popular back then, and the rest of it is inertia.
  - mistrial9 8y ago
    from second-source information: in the late 90s, just about every database product in the world, from tiny to massive, was tested as a backend to various web sites. Scaling was still being invented. MySQL was simply the fastest (only by microseconds) to return a set of strings to a php page, and php was a fast development environment. Neither php nor MySQL had the rigor that many in the database world demanded, and that was ok. In fact that helped to make the MySQL/php combination fast. Once the herds picked MySQL (and php) the economies of scale started, and advantages grew. Meanwhile, the proper and safe competitors were generally disgusted (drama word but not entirely an exaggeration) but who cares about them, maybe even a weird positive to the new breed of web devs who were inventing the future.
  - evanelias 8y ago
    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 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.
  - jitans 8y ago
    There is who cares about his data and who uses MySQL
- alecco 8y ago
  > “build it safe and then make it fast” I have a lot of respect for the PostgreSQL team. But I think they don't care about the second part, or the constraints of the first part make it impossible. A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Also to note, PostgreSQL has too many features for my taste. I get it's the selling point. But that usually correlates with poor performance and code complexity, too. Of course, there's a problem of all modern fast database engines being proprietary. But by not addressing the performance problem, perhaps with an smaller engine lighter in features, open source is left biting the dust. And that's not good for the industry.
  - jacques_chester 8y ago
    > A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Why is this surprising?
    - IshKebab 8y ago
      I assume he means bad in comparison to other DBs.
      - jacques_chester 8y ago
        So do I. But it's a bit like criticising a car for being slow because you didn't push the accelerator pedal. That's not really the car's fault.
mmartinson 8y ago
Thank you pg maintainers, you've made such a wonderful thing.
- sergiotapia 8y ago
  It really is incredible how we can get something as fundamental as a database for free that is so well made and production ready. Can you imagine if there was no postgres? We'd have to use mysql or oracle or gasp mssql.
  - SOLAR_FIELDS 8y ago
    If MSSQL wasn't tied to the Microsoft ecosystem, I don't think it would get such a bad rap - When I was a dev on the Microsoft product stack I found MSSQL quite a joy to use - reasonably fast, pretty powerful admin tools (SSMS) and pretty easy to interface with as long as you stayed within the MS ecosystem. I did find myself missing SSMS specifically when I moved to non MS stack development, since Postgres' 'official' open source options aren't nearly as robust. There are a few proprietary tools that can rival SSMS for Postgres, but then you are back into the realm of proprietariness.
    - nwatson 8y ago
      MSSQL runs on Linux now. There's even a docker image. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...
jorams 8y ago
> 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.
- craigkerstiens 8y ago
  Original author here. Yes, it's great to learn Ctrl + D. But losing out on beginners that aren't familiar in favor of things that are naturally intuitive is a great way to make those who are new to programming and especially databases feel like they're dumb or not capable of it. I still recall the first time I was in vi and I couldn't figure out how to get out of the damn thing, and don't get me started on arrow keys. There are a lot of ways to help others learn things like Ctrl + D, an even better experience might be a 1 second delay with a message informing them of other ways. We could bikeshed all day on which is the more pristine way to exit a program, paint the shed whatever color you want, being intuitive for beginners is never a bad thing.
  - Jedd 8y ago
    You use the word intuitive a couple of times, and suggest there's a natural way to exit interactive cli programs (and it's not ctrl-d). Yet for me, ctrl-d is my usual first way of trying to exit any shell or cli utility. Whether it's natural or not, I couldn't say. Arguably the nipple is the first and only intuitive interface, everything after that is learned. After many years on my/maria I find the backslash shortcuts very counterintuitive to 'show databases | tables' etc. While I think it's great to have both options present, I don't think the demographic of people who work out they want an rdbms, evaluate and select postgresql, work out how to install server and client components, su to the postgres user, and are then likely to abandon in frustration as they can't determine how to exit the cli, is negligible.
    - craigkerstiens 8y ago
      The background of what you learned on is key to whats intuitive or natural. If you came up on an unix based system then yes you'd expect this. If you learned databases via MySQL then you'd expect show database to work. Today though many more are coming to development without an academic or linux background. If you install Postgres via Postgres.app there is a chance you're not at all familiar with a commandline. What is intuitive... yes it's relative to your background. I very much hope that the number quit or exit is intuitive for, those that don't come from a linux or academic background, grows of time. This does become a more valuable tool the more beginners there are, and personally from working with a lot of beginners already suspect it's more valuable than many realize.
ggm 8y ago
I 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.
- RedCrowbar 8y ago
  Have you tried pg_upgrade?
  - ggm 8y ago
    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.
    - tourdownunder 8y ago
      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
- chillydawg 8y ago
  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.
Nelkins 8y ago
I'm excited for hash partitioning. Being able to partition on a uuid is going to be very useful.
brightball 8y ago
Great looking release. I’m really excited to see if the cstore extension can make good use of the parallelism improvements.
- macdice 8y ago
  It looks like someone would need to do the things described in here: https://www.postgresql.org/message-id/flat/CA%2Bz6ocRFEnThhXye3F9_ZjSMDSfOGdnGOt8hnXsVgGmFpNTYFA%40mail.gmail.com https://www.postgresql.org/message-id/flat/CA%2Bz6ocRFEnThhX... There is an easy level "parallel safe" that would allow scans of different cstore partitions in a parallel query, and a harder-to-code "parallel aware" level that would allow parallel scans on one individual cstore_fdw relation. AFAIK no FDW has attempted parallel scans yet, but cstore looks like it may be an ideal case to be first?
kbumsik 8y ago
One thing the OP didn’t mention is that the JIT is not enabled by default. They found that it has performance improvements for only long and complex queries yet. [1] But it can be enabled by a simple configuration command: jit = on. [1]: https://www.phoronix.com/scan.php?page=news_item&px=PostgreSQL-JIT-No-11-Default https://www.phoronix.com/scan.php?page=news_item&px=PostgreS...
- anarazel 8y ago
  It's expected to only have benefits for longer queries. The problem is primarily that the current logic what is likely to be a long query isn't perfect, so can trigger in the wrong moments. Since integrating the feature we'd added an item in the "pre-release item list" [1], to decide whether to enable/disable - but we definitely wanted it enabled during most of the beta test period. This wasn't really a last-minute "whaaaaaaa" thing... [1] https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items
shrumm 8y ago
I’m already loving PG10’s native partitioning support. PG11 closes the loop on a few convenience features like being able to set foreign and primary keys at the master table level. With PG10 I can’t use ON CONFLICT clauses - looking forward to upgrading and sorting that out.
- welder 8y ago
  Check out TimescaleDB, a Postgres extension that auto-creates partition tables for you while PG11 native partitioning you still need to manually run CREATE TABLE. https://news.ycombinator.com/item?id=18026699 https://news.ycombinator.com/item?id=18026699
  - shrumm 8y ago
    would love to but this is a no-go at this point, since I’m on RDS. I think it’s great you’re solving this though - I currently use a cron job to create new partitions.
- LunaSea 8y ago
  I encountered the same limitations as you while implementing partitioning a few months ago and it's possible to use ON CONFLICT if the unique index is set on the partitioned table. So after initialising a new partition, I also create a unique index attached to that partition directly instead of the parent table.
  - shrumm 8y ago
    wow thanks, I never thought to try it, just dismissed it and never dig further. I’ll try this!
deytempo 8y ago
What are some reasons I might consider Postgres over MySQL when choosing a DB platform?
- petre 8y ago
  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.
  - evanelias 8y ago
    > 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.
- sv12l 8y ago
  If data is central to your business, in all likelihood it will be, and you care.
  - calcifer 8y ago
    This is not a useful or constructive comment with its unstated argument of "only idiots use mysql". It's sarcastic flamewar bait.
    - guiriduro 8y ago
      No, its just the TL;DR version of a longer argument demonstrating MySQL's ability to lose, truncate and misinterpret data and ignore commands and thereby create false expectations (like, your data is validated and safe). PostgreSQL takes far greater care over data.
ahartmetz 8y ago
I have a serious problem with Postgres. Every year at FOSDEM, some of the most technically interesting talks are about Postgres, and the room is always far too small so I can't get in.
- adwhit 8y ago
  The AW block is tiny but has so many great talks. All the queues end up merging with each other and jutting out into the foyer. Chaos!
- twic 8y ago
  Sounds like you need to VACUUM ANALYZE a few of the other attendees to make room ...
nathan_f77 8y ago
> Fear column addition no more This section was really surprising to read! I use the strong_migrations [1] gem to catch "NOT NULL column with a default value", because it's such a common mistake. I guess I had assumed that this was just something we had to accept, based on decades of architecture decisions, and the way that Postgres was written. It never occurred to me that it was actually possible to change the default behavior of Postgres and fix the underlying issue. [1] https://github.com/ankane/strong_migrations#adding-a-column-with-a-default-value https://github.com/ankane/strong_migrations#adding-a-column-...
- booleanbetrayal 8y ago
  This feature has me way more excited than I probably should be. Been splitting up relatively simple schema migrations into multiple steps for years now, and definitely welcoming this change.
  - mb4nck 8y ago
    Notably, that feature got written by somebody from Salesforce (I assume on their work time, but not sure), and finished up by a Postgres major contributor. It might be the first major contribution from Salesforce outside of all the stuff Tom Lane did when he was working there.
    - petergeoghegan 8y ago
      Not so. I worked on UPSERT for several years as a Salesforce employee, as well as quite a number of other things. I worked for Heroku, a Salesforce business unit, from 2013-2017.
dkubb 8y ago
I love Postgres, but the only thing I find myself wishing for was better Unicode support in the regex engine. I like to add strong database constraints, and sometimes lean on the regexp engine but I find it's missing a lot of features available in other regexp engines. PCRE compatibility would be ideal, but even just support for the Unicode \p classes would be a big step up.

390 points by samaysharma 8y ago

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!

mrep 8y ago
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?
- johncolanduoni 8y ago
  Probably because compatability with MySQL is more valuable than comparability with Postgres from a product perspective.
- tejtm 8y ago
  Will make an attempt to answer, hopefully without starting anything. Mysql aimed for lowering bar for non-dba-app-developers. Postgres did not. In some cases it matters, in many it does not, especially if you have the dba skills on hand to mind the gaps.
- ma2rten 8y ago
  MySQL used to be more popular. It was the M in the LAMP stack, which virtually everyone used to use. It also used to be more scalable, because PG's replication story used to be poor. This is why Google and (I assume) Amazon used MySQL internally. This is all a long time ago, but I suspect this is the reason more people are familiar with MySQL than Postgres at these companies. Keep in mind that Spanner was developed and used internally at Google long before it was publicly announced.
  - mrep 8y ago
    > This is why Google and (I assume) Amazon used MySQL internally. Amazon actually uses a lot of Oracle with some scattering of MySQL.
    - reitanqild 8y ago
      That's weird. But maybe at their scale even Oracle can be made tolerable. That, and they have some serious leverage in negotiations. (I used to deal with Oracle DBs a lot a few years ago but thankfully not anymore.)
    - tfehring 8y ago
      It looks like Amazon's migrating away from Oracle pretty aggressively, however. https://www.cnbc.com/2018/08/01/amazon-plans-to-move-off-oracle-software-by-early-2020.html https://www.cnbc.com/2018/08/01/amazon-plans-to-move-off-ora...
- manigandham 8y ago
  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 to use it because they've always used it (like Youtube for example). PostgreSQL only recently became much more usable after v9.5 and is now being recognized and growing in popularity.
  - philliphaydon 8y ago
    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.
    - spion 8y ago
      Replication is still a pain.
      - scurvy 8y ago
        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).
        cookiecaper 8y ago
        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.
- rgbrenner 8y ago
  History. A long time ago, in the early 90s, there was a database called msql. It was a little DB that used SQL and ran well on low end systems. It was also free. In fact, it was the ONLY free SQL database out at the time -- that's why it was written. Because of those two advantages (that really only lasted a couple of years), a small ecosystem developed around it. This was the database you used to develop interactive websites in the early/mid-90s. Then MySQL came. MySQL extended mSQL, and kept compatibility with it's ecosystem... and people switched from mSQL to MySQL. Postgres also existed in the 80s and 90s, but it used POSTQUEL, was harder to setup, and had higher system requirements. Not a lot, but enough to discourage use on low end computers. (Remember, Postgres at the time was being developed on university servers, where they had plenty of system resources.) They did address these points in postgre95 (in fact, Postgres adopted SQL as a response to mSQL popularity), but by then it was too late. mSQL and MySQL already had an ecosystem.. and in that brief moment, they lost the race. The idea that it was slower stuck around for many years, even after the improvements in computers, and (less so) optimizations in Postgres made it no longer true. Postgres didn't start being considered again until Go, RoR, et al started to become popular.. displacing the PHP ecosystem. And the biggest reason you see MySQL around so much today is that there's about 25 years of legacy code built around it. Edit: If you're curious what POSTQUEL was like: https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
- apapli 8y ago
  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 with more experience than I generally prefer PG for a whole lot of reasons. EDIT: I should have googled first before replying. Looks like the syntax is near enough to identical for my simple needs and pgadmin will do what I need. I’m not sure why I had to learn some other weird syntax in 2012 for postgres, perhaps it was just shortcuts. Either way I’m going to seriously consider migrating now. :)
  - akavel 8y ago
    What syntax do you have in mind? Can you give sone example of what you see as easier in MySQL than Postgres? Genuinely curious, I don't have money in that game
    - apapli 8y ago
      I recall having to type /d from the command prompt to show databases (or something like that). I prefer “show databases”.
      - mrep 8y ago
        Haha, same. When I first used a postgres database, I got super annoyed trying to figure out how to simply show the databases and tables as I kept trying to use show and describe.
      - petepete 8y ago
        The standard way to do this would be to query `information_schema. tables`. PostgreSQL's approach isn't the most intuitive but it's well documented and very powerful. A few aliases could probably help, it's good they've added `quit`/,`exit` this time around.
    - mgkimsal 8y ago
- dotancohen 8y ago
  > 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 not know the difference between character encodings. What does he care what MySQL does, he knows that he can put strings in if he calls them VARCHAR, and he can pull the right one out with a WHERE. He cares not enough to check that user bios fit in his VARCHAR, and when he learns to JOIN he does not understand which constraints to put in the ON clause. I do have nicer words for the L and A in the stack.
  - rgbrenner 8y ago
    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 lightweight SQL database that ran well on cheap early-90s computers. 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. Postgres on the other hand, was a university project in the 80s and 90s being developed on mainframes. It was always a slow moving, cautious project. Yes, it didn't make the compromises mSQL made, because it didn't have to. It used more memory, more CPU, but was more correct/safe than MySQL. That was the same reason it lost in the 90s. mSQL was out being used by websites and other projects on cheap computers. Postgres was mostly waiting for computer tech to get fast enough so they wouldn't need to compromise their code too much to take it off the mainframe an on to cheaper computers. Postgres only adopted SQL in response to mSQL's popularity. Early Postgres was also harder to setup, another point that wasn't addressed until after mSQL took off. MySQL was popular because of the compromises they made. If mSQL just kept Postgres as the engine, MySQL never would have existed. And if they instead waited (even a couple of years), and did things correctly, they wouldn't have beat Postgres -- which had a decade lead in development and was more advanced than mSQL/MySQL.
- samcheng 8y ago
  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 software, but I remember spinning up a phpMyAdmin instance even after moving away from PHP as an application language. Postgres still doesn't really have anything like that! The closest that comes to mind is Django's Admin tool.
  - rgbrenner 8y ago
    phpPgAdmin, started ~2002: http://phppgadmin.sourceforge.net/doku.php http://phppgadmin.sourceforge.net/doku.php Basically a port of phpMyAdmin to postgres. I used it for several years around ~2003-5.. it's nearly identical.
    - luhn 8y ago
      I’m a regular Postgres user and fan, but cut my teeth on LAMP. I can’t speak to the current state of the projects, but phpMyAdmin was vastly superior to phpPgAdmin in the 2000s. phpMyAdmin remains the best DB GUI I’ve ever used. (Although I haven’t used any GUI in quite a few years.)
      - deleted 8y ago
        [deleted]
  - gbuk2013 8y ago
    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.
  - 8y ago
- twunde 8y ago
  Historically postgres was slower than MySQL, harder to manage than MySQL, wasn't available on shared hosting and generally speaking wasn't beginner friendly. Because of this a generation of programmers learned MySQL, especially as everything PHP used MySQL. As such the install base for MySQL, was quite large so there were more potential customers for MySQL projects (WordPress, drupal and Joomla all run on mysql, so we're talking about 50% of websites before talking about custom applications)
- jpalomaki 8y ago
  For the Spanner and Aurora: MySQL has a lot less features. For example on the query side it has been really limited compared to Postgres. Maybe this was a plus in this case. Less features make it essier to optimize.
- gwn7 8y ago
  Probably accessibility. Because MySQL for some reasons is more accessible[1] to beginners, juniors, average developers, and other people who may prefer a lower barrier of entry over correctness/safety (e.g. full-stack devs, managers, people who don't have much time); who form the bulk of the community combined. It's the same for languages, frameworks, and other tech; as several people noted already. [1] Or maybe "was"; in which case the answer becomes "historical superior accessibility".
- Keyframe 8y ago
  MyISAM made it popular back then, and the rest of it is inertia.
- mistrial9 8y ago
  from second-source information: in the late 90s, just about every database product in the world, from tiny to massive, was tested as a backend to various web sites. Scaling was still being invented. MySQL was simply the fastest (only by microseconds) to return a set of strings to a php page, and php was a fast development environment. Neither php nor MySQL had the rigor that many in the database world demanded, and that was ok. In fact that helped to make the MySQL/php combination fast. Once the herds picked MySQL (and php) the economies of scale started, and advantages grew. Meanwhile, the proper and safe competitors were generally disgusted (drama word but not entirely an exaggeration) but who cares about them, maybe even a weird positive to the new breed of web devs who were inventing the future.
- evanelias 8y ago
  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 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.
- jitans 8y ago
  There is who cares about his data and who uses MySQL
alecco 8y ago
> “build it safe and then make it fast” I have a lot of respect for the PostgreSQL team. But I think they don't care about the second part, or the constraints of the first part make it impossible. A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Also to note, PostgreSQL has too many features for my taste. I get it's the selling point. But that usually correlates with poor performance and code complexity, too. Of course, there's a problem of all modern fast database engines being proprietary. But by not addressing the performance problem, perhaps with an smaller engine lighter in features, open source is left biting the dust. And that's not good for the industry.
- jacques_chester 8y ago
  > A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Why is this surprising?
  - IshKebab 8y ago
    I assume he means bad in comparison to other DBs.
    - jacques_chester 8y ago
      So do I. But it's a bit like criticising a car for being slow because you didn't push the accelerator pedal. That's not really the car's fault.

mmartinson 8y ago

Thank you pg maintainers, you've made such a wonderful thing.

sergiotapia 8y ago
It really is incredible how we can get something as fundamental as a database for free that is so well made and production ready. Can you imagine if there was no postgres? We'd have to use mysql or oracle or gasp mssql.
- SOLAR_FIELDS 8y ago
  If MSSQL wasn't tied to the Microsoft ecosystem, I don't think it would get such a bad rap - When I was a dev on the Microsoft product stack I found MSSQL quite a joy to use - reasonably fast, pretty powerful admin tools (SSMS) and pretty easy to interface with as long as you stayed within the MS ecosystem. I did find myself missing SSMS specifically when I moved to non MS stack development, since Postgres' 'official' open source options aren't nearly as robust. There are a few proprietary tools that can rival SSMS for Postgres, but then you are back into the realm of proprietariness.
  - nwatson 8y ago
    MSSQL runs on Linux now. There's even a docker image. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...

jorams 8y ago

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

craigkerstiens 8y ago
Original author here. Yes, it's great to learn Ctrl + D. But losing out on beginners that aren't familiar in favor of things that are naturally intuitive is a great way to make those who are new to programming and especially databases feel like they're dumb or not capable of it. I still recall the first time I was in vi and I couldn't figure out how to get out of the damn thing, and don't get me started on arrow keys. There are a lot of ways to help others learn things like Ctrl + D, an even better experience might be a 1 second delay with a message informing them of other ways. We could bikeshed all day on which is the more pristine way to exit a program, paint the shed whatever color you want, being intuitive for beginners is never a bad thing.
- Jedd 8y ago
  You use the word intuitive a couple of times, and suggest there's a natural way to exit interactive cli programs (and it's not ctrl-d). Yet for me, ctrl-d is my usual first way of trying to exit any shell or cli utility. Whether it's natural or not, I couldn't say. Arguably the nipple is the first and only intuitive interface, everything after that is learned. After many years on my/maria I find the backslash shortcuts very counterintuitive to 'show databases | tables' etc. While I think it's great to have both options present, I don't think the demographic of people who work out they want an rdbms, evaluate and select postgresql, work out how to install server and client components, su to the postgres user, and are then likely to abandon in frustration as they can't determine how to exit the cli, is negligible.
  - craigkerstiens 8y ago
    The background of what you learned on is key to whats intuitive or natural. If you came up on an unix based system then yes you'd expect this. If you learned databases via MySQL then you'd expect show database to work. Today though many more are coming to development without an academic or linux background. If you install Postgres via Postgres.app there is a chance you're not at all familiar with a commandline. What is intuitive... yes it's relative to your background. I very much hope that the number quit or exit is intuitive for, those that don't come from a linux or academic background, grows of time. This does become a more valuable tool the more beginners there are, and personally from working with a lot of beginners already suspect it's more valuable than many realize.

ggm 8y ago

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

RedCrowbar 8y ago
Have you tried pg_upgrade?
- ggm 8y ago
  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.
  - tourdownunder 8y ago
    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
chillydawg 8y ago
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.

Nelkins 8y ago

I'm excited for hash partitioning. Being able to partition on a uuid is going to be very useful.

brightball 8y ago

Great looking release. I’m really excited to see if the cstore extension can make good use of the parallelism improvements.

macdice 8y ago
It looks like someone would need to do the things described in here: https://www.postgresql.org/message-id/flat/CA%2Bz6ocRFEnThhXye3F9_ZjSMDSfOGdnGOt8hnXsVgGmFpNTYFA%40mail.gmail.com https://www.postgresql.org/message-id/flat/CA%2Bz6ocRFEnThhX... There is an easy level "parallel safe" that would allow scans of different cstore partitions in a parallel query, and a harder-to-code "parallel aware" level that would allow parallel scans on one individual cstore_fdw relation. AFAIK no FDW has attempted parallel scans yet, but cstore looks like it may be an ideal case to be first?

kbumsik 8y ago

One thing the OP didn’t mention is that the JIT is not enabled by default. They found that it has performance improvements for only long and complex queries yet. [1] But it can be enabled by a simple configuration command: jit = on. [1]: https://www.phoronix.com/scan.php?page=news_item&px=PostgreSQL-JIT-No-11-Default https://www.phoronix.com/scan.php?page=news_item&px=PostgreS...

anarazel 8y ago
It's expected to only have benefits for longer queries. The problem is primarily that the current logic what is likely to be a long query isn't perfect, so can trigger in the wrong moments. Since integrating the feature we'd added an item in the "pre-release item list" [1], to decide whether to enable/disable - but we definitely wanted it enabled during most of the beta test period. This wasn't really a last-minute "whaaaaaaa" thing... [1] https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items

shrumm 8y ago

I’m already loving PG10’s native partitioning support. PG11 closes the loop on a few convenience features like being able to set foreign and primary keys at the master table level. With PG10 I can’t use ON CONFLICT clauses - looking forward to upgrading and sorting that out.

welder 8y ago
Check out TimescaleDB, a Postgres extension that auto-creates partition tables for you while PG11 native partitioning you still need to manually run CREATE TABLE. https://news.ycombinator.com/item?id=18026699 https://news.ycombinator.com/item?id=18026699
- shrumm 8y ago
  would love to but this is a no-go at this point, since I’m on RDS. I think it’s great you’re solving this though - I currently use a cron job to create new partitions.
LunaSea 8y ago
I encountered the same limitations as you while implementing partitioning a few months ago and it's possible to use ON CONFLICT if the unique index is set on the partitioned table. So after initialising a new partition, I also create a unique index attached to that partition directly instead of the parent table.
- shrumm 8y ago
  wow thanks, I never thought to try it, just dismissed it and never dig further. I’ll try this!

deytempo 8y ago

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

petre 8y ago
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.
- evanelias 8y ago
  > 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.
sv12l 8y ago
If data is central to your business, in all likelihood it will be, and you care.
- calcifer 8y ago
  This is not a useful or constructive comment with its unstated argument of "only idiots use mysql". It's sarcastic flamewar bait.
  - guiriduro 8y ago
    No, its just the TL;DR version of a longer argument demonstrating MySQL's ability to lose, truncate and misinterpret data and ignore commands and thereby create false expectations (like, your data is validated and safe). PostgreSQL takes far greater care over data.

ahartmetz 8y ago

I have a serious problem with Postgres. Every year at FOSDEM, some of the most technically interesting talks are about Postgres, and the room is always far too small so I can't get in.

adwhit 8y ago
The AW block is tiny but has so many great talks. All the queues end up merging with each other and jutting out into the foyer. Chaos!
twic 8y ago
Sounds like you need to VACUUM ANALYZE a few of the other attendees to make room ...

nathan_f77 8y ago

> Fear column addition no more This section was really surprising to read! I use the strong_migrations [1] gem to catch "NOT NULL column with a default value", because it's such a common mistake. I guess I had assumed that this was just something we had to accept, based on decades of architecture decisions, and the way that Postgres was written. It never occurred to me that it was actually possible to change the default behavior of Postgres and fix the underlying issue. [1] https://github.com/ankane/strong_migrations#adding-a-column-with-a-default-value https://github.com/ankane/strong_migrations#adding-a-column-...

booleanbetrayal 8y ago
This feature has me way more excited than I probably should be. Been splitting up relatively simple schema migrations into multiple steps for years now, and definitely welcoming this change.
- mb4nck 8y ago
  Notably, that feature got written by somebody from Salesforce (I assume on their work time, but not sure), and finished up by a Postgres major contributor. It might be the first major contribution from Salesforce outside of all the stuff Tom Lane did when he was working there.
  - petergeoghegan 8y ago
    Not so. I worked on UPSERT for several years as a Salesforce employee, as well as quite a number of other things. I worked for Heroku, a Salesforce business unit, from 2013-2017.

dkubb 8y ago

I love Postgres, but the only thing I find myself wishing for was better Unicode support in the regex engine. I like to add strong database constraints, and sometimes lean on the regexp engine but I find it's missing a lot of features available in other regexp engines. PCRE compatibility would be ideal, but even just support for the Unicode \p classes would be a big step up.