13 ms·

What’s so exciting about Postgres?

118 points by jerodsanto 6y ago

RickHull 6y ago
Title has a typo: What's so exciting about Postgres
sandinmyjoints 6y ago
> It was really funny, Uber switched from MySQL to Postgres, and then Postgres to MySQL. So they’ve gone back and forth a couple of times, but when they’ve swapped to Postgres, they had to figure out – they had all this app logic that relied on things being case-insensitive, because the database just doesn’t respect case search. This suggests to me that the guest here maybe doesn't know about collations? But that doesn't seem likely (possible?) given his stated experience and the roles he's held. So what gives?
- dainiusse 6y ago
  Uber once switched Postgres->MySQL because of human error and polytics for “taking remediation action”.
- willvarfar 6y ago
  I read that as Uber discovered their code relied on MySQL default case insentivity. Migrating to Postgres might have been tricky as collation is a very recent addition in Postgres.
  - sandinmyjoints 6y ago
    That could be. It was the "because the database just doesn’t respect case search" part that struck me -- to say that MySQL doesn't respect case search is false, and the only scenario I can think of where it can even appear to be the case is if you're new to MySQL and you've made a big assumption without even googling it. Or maybe if you're new to string comparison (and therefore programming?) in general.
- coding123 6y ago
  I learned that about postgres collations and case sensitivity about 6 years back, and it was within 3 months of my first heavy use of postgres. So I wonder if this person was just experiencing their first usage of postgres too? And I've used relational databases for more than 25 years.
- craigkerstiens 6y ago
  Guest here. The context is Uber was on MySQL which by default doesn't respect (or at least in their version and setup at the time) case sensitivity. To MySQL craig is the same as Craig when matching. Their app for search relied on this. The solution for them was the citext data type to create a case insensitive string - https://www.postgresql.org/docs/13/citext.html https://www.postgresql.org/docs/13/citext.html I'm fairly familiar with Postgres, but have not heard of a collation that tackles case insensitivity without tweaking the system itself? Postgres you can tweak this at the system level, but I've not seen it in the wild and as per the docs you're now non deterministic. For MySQL it appears you can change this, but seems a surprising default for them they expected case insensitivity. Yes, another option could have been indexing and searching on lower or upper casing, but they wanted the minimal change to their app. There was once a conference talk they gave about their migration process from MySQL->Postgres, but I'm not immediately able to find the video online so it may have been removed.
  - Arnavion 6y ago
    I'm not familiar with pg, but the choice of case sensitivity is a fundamental property of collations in other DBMSes like MSSQL. Web search does make it look like pg only got case-insenstivity-via-collation in v12 (2019), and also requires you to make your own collation with case-insensitivity since it doesn't have any built-in ones with it.
  - gigatexal 6y ago
    Case insensitive search is accomplished with ILIKE no?
    - johannes1234321 6y ago
      How well does ILIKE work with indexes? In MySQL the collation (which includes case- and accent-independency options) is known to the index.
      - spacemanmatt 6y ago
        You can stipulate the collation in the query if you need something non-default.
- fuhrysteve 6y ago
  Great practical note by Josh Berkus on why Uber left Posgresql. Basically: runaway table bloat because Uber had a usecase that postgres doesn't address as well as InnoDB. https://www.postgresql.org/message-id/5797D5A1.5030009%40agliodbs.com https://www.postgresql.org/message-id/5797D5A1.5030009%40agl...
  - api 6y ago
    The whole VACUUM paradigm is the biggest thing that bugs me about pgsql. The fact that it can actually freeze things always worries me. Can’t this happen constantly in the background like modern GCs?
    - chousuke 6y ago
      It does, though? That's what autovacuum is, unless I'm missing something. It's just not always enough without tuning, just like GC.
      - api 6y ago
        We have had a lot of problems with frequently updated tables. Is auto tuning really that hard? Or is pg’s philosophy not to “auto” anything?
    - spacemanmatt 6y ago
      I think you might be pleasantly surprised to get current on how vacuum works in current editions. Vacuum used to be a bigger issue for pg.
Animats 6y ago
Databases shouldn't be exciting. They should just work. The main reason webcrap works is because the databases underneath work. If the Javascript crowd had to manage data storage, nothing persistent would work reliably.
- msla 6y ago
  > Databases shouldn't be exciting. They should just work. A complicated thing which just works is exciting.
  - bigbubba 6y ago
    One man's exciting is another's snorefest. A lot of people would probably laugh at the suggestion that anything involving computers could be exciting.
    - systemvoltage 6y ago
      It is unproductive to talk about subjective opinions of excitement. The GP was referring to "Databases shouldn't be exciting" - not in the traditional sense of the word, but a metaphor that hinges on "excitement" being new features, frivolous decorations, unnecessary tangents, losing vision of what a database should do, frantic patching and heated bikeshedding discussions. It wasn't meant as "Ooooo I am excited about a database".
      - bigbubba 6y ago
        I think you're restating my point.
- systemvoltage 6y ago
  It is often downvoted to talk about the "javascript crowd" on HN, but I've worked in software industry long enough to unequivocally and with certainty say that it totally lives up to the cliches. It never gets old to scold people that are not serious, disciplined and principled in their endeavors that affect the rest of the world. Javascript community absolutely deserves the avalanche of criticisms they face. It is the reason why the modern web sucks, and frankly - I wish folks at Postgres would develop a new web standards including browsers that are not governed by corporations and can be implemented in a couple of weeks. Not trivializing what goes into building browsers, but making the whole standard small enough so any one can build a browser during a sabbatical.
rbanffy 6y ago
I started using PostgreSQL because, at the time, MySQL wouldn't do subqueries. The fact it silently truncated VARCHAR data to the size of the column and didn't give out an error didn't help much. Overall, my impression is that the project's philosophy is to do the Correct Thing, even if it's slow.
- Ralfp 6y ago
  Few years back I've had friend explain the difference between MySQL and PostgreSQL like this: - MySQL: do it fast, then make it right - PSQL: do it right, then make it fast
- setr 6y ago
  I had switched after learning that CHECK constraints were parsed, but ignored -- I've yet to be as offended by a program that at that instant Apparently supported now but what the hell; I don't think they even threw a warning about it
- Delk 6y ago
  Years back, the conventional wisdom seemed to be that MySQL used to be faster, and PostgreSQL more correct. Over time, according to that same conventional wisdom, MySQL got more correct than it used to be and PostgreSQL got faster than it used to be.
udev 6y ago
I like the this whole class of questions "What's so exciting/good about <SOMETHING>?" Online (forums, etc) this type of question usually elicits informative answers, and probably in face to face communication too.
anonymous324 6y ago
PostgreSQL isn't exciting. It's boring, with roots in the Berkeley Ingress project from the mid 80s. Which is why 10 years ago, so many HNers went with trendy NoSQL data stores instead, even though they lost data. Data loss is very exciting, and PostgreSQL is very boring. This preference for what's exciting and trendy over what's old and proven is a large reason why so much software today sucks.
- Someone1234 6y ago
  That seems unfair. NoSQL was trying out new ideas in data storage. It was exciting to try out new or re-imagined core concepts, and some of those young projects had teething issues. But several are still around and remain popular, but they're popular for certain niches they excel at (and those niches were largely discovered through trial and error). In the SQL-sphere a lot of people skipped Postgres because MySQL had, at the time, the momentum in the free/cheap relational database space. Between then and now Progres has grown more elegantly than MySQL, and people are rightfully looking to it.
  - anonymous326 6y ago
    > NoSQL was trying out new ideas in data storage Was it? Or was it just rehashing old ideas made obsolete by relational databases? > https://en.wikipedia.org/wiki/IBM_Information_Management_System https://en.wikipedia.org/wiki/IBM_Information_Management_Sys... > Initial release 1966; 54 years ago > https://en.wikipedia.org/wiki/Category:NoSQL https://en.wikipedia.org/wiki/Category:NoSQL
  - treeman79 6y ago
    MySQL was also a lot faster in the early days. Plus it “just worked” not sanely mind you. But you had a lot less problems with type mismatches, up/downcast, etc. Well unless you wanted quality data. But back then we were just happy that it didn’t bother us with “minor” details.
    - hoistbypetard 6y ago
      It was a lot faster, and sometimes the odds that your data might live long enough for those integrity issues to matter seemed very remote. Around 2001, the conventional wisdom was that it was worth the risk to take the performance victory, keep your hosting bill down, use mysql, and build some other approach to data integrity. (If memory serves, the integrity downsides were bigger then, too...) The math is pretty different now. I'm not sure it's fair to hang all of it on Oracle, but it feels like that acquisition derailed some MySQL progress that might've made it a close call today. Now, I use Postgres unless I have a very specific, very compelling reason not to.
shay_ker 6y ago
Surprised they didn't cover transaction id wraparound, which has caused more than a few public outages at scale. IIRC some Postgres folks have a plan to fix it? Maybe Craig knows?
- anarazel 6y ago
  > Surprised they didn't cover transaction id wraparound, which has caused more than a few public outages at scale. There have been some significant improvements in the last couple years - most importantly the introduction of the freeze map in 9.6. But also quite a sprinkling of other incremental improvements. It can obviously still be a problem, but it's not as pronounced as it once was.
  - shay_ker 6y ago
    can you elaborate?
    - anarazel 6y ago
      > can you elaborate? On what exactly? The freeze map bit I referenced above is the following commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd31cd265138019dcccc9b5fe53043670898bc9f https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough infrastructure to avoid vacuuming pages where every tuple on the page is already frozen. So, replace the notion of a scan_all or whole-table vacuum with the less onerous notion of an "aggressive" vacuum, which will pages that are all-visible, but still skip those that are all-frozen. This should greatly reduce the cost of anti-wraparound vacuuming on large clusters where the majority of data is never touched between one cycle and the next, because we'll no longer have to read all of those pages only to find out that we don't need to do anything with them. This means that an anti-wraparound vacuum (automatically started, even when autovacuum is disabled) is cheaper than it used to be. Still not necessarily cheap, as indexes still need to be scanned (but see below). Some of the additional changes were (reverse chronological order): * 2020-03-28 - "Trigger autovacuum based on number of INSERTs" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b07642dbcd8d5de05f0ee1dbb72dd6760dd30436 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This is important because it will reduce the cost of an eventual index wraparound, as there will be less work in a later anti-wraparound vacuum * 2020-01-20 - "Allow vacuum command to process indexes in parallel." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40d964ec997f64227bc0ff5e058dc4a5770a70a9 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... As index processing can be expensive (no equivalent to scanning only changed parts of table), processing them in parallel can greatly reduce the time for a vacuum. Note that this isn't yet done by autovacuum. * 2019-04-04 - "Allow VACUUM to be run with index cleanup disabled." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a96c41feec6b6616eb9d5baee9a9e08c20533c38 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can be extremely useful for manual vacuum when getting close to a wraparound, since the index processing step is not necessary to stave of wraparound. * 2018-04-04 - "Skip full index scan during cleanup of B-tree indexes when possible" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=857f9c36cda520030381bd8c2af20adf0ce0e1d4 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can make vacuums for pretty clean tables vastly cheaper. There's also a number of changes that make indexes smaller / less likely to bloat. That in turn makes vacuuming more efficient. E.g. * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d861bbb702f8aa05c2a4e3f1650e7e8df8c8c27 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd299df8 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Edit: formatting (gah, why is HN formatting so limited)
axegon_ 6y ago
Apart from being the most mature relational database, I would argue the code that runs it. It's pure poetry. I am yet to see code this clean anywhere. Just opening any source code file at random[1] will show you exactly what I mean. [1] https://github.com/postgres/postgres/blob/master/src/backend/catalog/catalog.c https://github.com/postgres/postgres/blob/master/src/backend...
- systemvoltage 6y ago
  Let's not get carried away. It is very much debatable which database is "most mature". Here is some discussions about how Postgres compares with Oracle DB (if $ is not an issue), it is often said that Postgres is a Cessna whereas Oracle DB is like a Fighter Jet: https://news.ycombinator.com/item?id=24582937 https://news.ycombinator.com/item?id=24582937
  - axegon_ 6y ago
    I've never had the opportunity to test Oracle DB at large, just some minor playing around with 12c many many years ago so I don't have an accurate view. That said, given my experience with other Oracle products and the fighter jet analogy... De omnibus dibutandum est...
  - ivolimmen 6y ago
    I have been working with postgres for multiple years and in one project I needed to work with oracle. I actually ran into a bug in oracle db and they did not listen to us because we where a small company. We where working for a very large customer and asked that company to complain on our behalf at oracle. They they finally fixed it. Project was a large GIS project. I never had a say in the choice of database otherwise I would have chosen postgres. Oracle regularly crashed on complex spacial queries. At some point I left te company just to stop trying to fix that product. I think the only way of fixing the problem was a rewrite of the product that would use postgres. I hope I never work with oracle products in my life again.
- deleted 6y ago
  [deleted]
mxyzplk 6y ago
It works reliably and is not at the mercy of Oracle - so it's pretty much the winner for relational storage, unless you need something different (doc db, time series db...).
pmiller2 6y ago
This was a really good article, and thank you @jerodsanto for submitting it. I learned probably 6-7 big things about Postgres reading it that I didn't know before. I won't list out those things, because, I'm sure it wouldn't be relevant to most people reading this comment. :). But, there are a couple things I noticed that I do want to mention: > There were these edge cases in there, where it’s kind of like less safe. And Postgres was there, and it had a reputation of being just rock-solid, consistent, and more strict with your data > "... I never had that moment where I’m like “Oh, Postgres, you screwed me over.” This is a huge deal, and, IMO, the reason to choose Postgres over MySQL. MySQL has some horrendous defaults. You can make things a little better, but, AFAIK, you can't even get to where Postgres starts out of the box. Most of that consists of MySQL being extremely permissive with what it allows you to do in your SQL. Many of those things are non-standard, and some are quite unsafe. Postgres not only prioritizes safety, it keeps the application developer honest. It can be a little complain-y, but I'd rather have my database complain than do stuff that could lose data. One great example is when you try to stuff more characters into a text field than it can accommodate. By default, MySQL just truncates the string silently; Postgres just doesn't allow it. > PostGIS is a huge one. It’s a whole geospatial database. This is the other point I want to call out. I've had the pleasure of working with PostGIS before. The main thing I'd want to say about PostGIS is that, although there are issues working with geojson and GIS in general, PostGIS doesn't exacerbate any of them. Generally speaking, PostGIS is about as pleasant to work with as a geospatial database can be.
- redis_mlc 6y ago
  > I'd rather have my database complain than do stuff that could lose data. One great example is when you try to stuff more characters into a text field than it can accommodate. By default, MySQL just truncates the string silently; Postgres just doesn't allow it. That's your opinion, but the truth is not necessarily black and white. Postgres will probably lose more data in your case when the application developer doesn't handle the exception for a text field too long, and loses the whole row. (If you're an application developer, you should audit your code now.) A well-written application using Postgres would have to truncate the data before insert, achieving the same result as having MySQL auto-truncation, but with more initial and on-going maintenance effort in the case of Postgres. And the existence proof that MySQL's behavior is ok is that MySQL is the most widely-used relational databases, and powers most Internet sites. Source: DBA.
postgresqlrock 6y ago
Features I miss in PostgreSQL: - Support large number of connections (without external pooling tools) and stop starting a new process per connection (consumes huge amount of resources). Other DBMS works with threads instead of processes and don't have any issues. - Easy to configure high availability with multiple nodes Less important: - Graph capabilities directly in PostgreSQL without extentions or something else - Running a scheduling tasks directly in PostgreSQL without external tools (for example a script that removes old data from database). - Supporting Temporal Tables But overall I like PostgreSQL more than Oracle or SQL Server, even if I miss the above features.
cxcorp 6y ago
Wow, what a great episode! Probably one of the info-loaded podcasts I've listened to. Definitely going to be using psql's \e command with $EDITOR set to VS Code.
snthd 6y ago
>[unintelligible 00:08:02.01] "is to go hack on" >[unintelligible 00:09:07.07] "lemming status of developers" >[unintelligible 00:19:16.22] "laying in" >[unintelligible 00:27:29.18] "tables" >[unintelligible 00:30:14.16]] "recompete"
- boredpenguin 6y ago
  > Our transcripts are open source[0] on GitHub. Improvements are welcome. Looks like you (or anyone else) can directly contribute these corrections [0] https://github.com/thechangelog/transcripts/blob/master/podcast/the-changelog-417.md https://github.com/thechangelog/transcripts/blob/master/podc...
golergka 6y ago
> There’s a professor from the University of Moscow that – I’m not sure if he still teaches, or used to teach on astrophysics, and then hacks on Postgres for fun. I'm pretty sure he's talking about Oleg Bartunov! He's one of the best speakers about Postgresql at Russian Highload conference: https://www.youtube.com/watch?v=uhvqly8MtoI&list=PLH-XmS0lSi_xqSyUtHzdYuEGDPrwGPpnk https://www.youtube.com/watch?v=uhvqly8MtoI&list=PLH-XmS0lSi...

118 points by jerodsanto 6y ago

Title has a typo: What's so exciting about Postgres

> It was really funny, Uber switched from MySQL to Postgres, and then Postgres to MySQL. So they’ve gone back and forth a couple of times, but when they’ve swapped to Postgres, they had to figure out – they had all this app logic that relied on things being case-insensitive, because the database just doesn’t respect case search. This suggests to me that the guest here maybe doesn't know about collations? But that doesn't seem likely (possible?) given his stated experience and the roles he's held. So what gives?

dainiusse 6y ago
Uber once switched Postgres->MySQL because of human error and polytics for “taking remediation action”.
willvarfar 6y ago
I read that as Uber discovered their code relied on MySQL default case insentivity. Migrating to Postgres might have been tricky as collation is a very recent addition in Postgres.
- sandinmyjoints 6y ago
  That could be. It was the "because the database just doesn’t respect case search" part that struck me -- to say that MySQL doesn't respect case search is false, and the only scenario I can think of where it can even appear to be the case is if you're new to MySQL and you've made a big assumption without even googling it. Or maybe if you're new to string comparison (and therefore programming?) in general.
coding123 6y ago
I learned that about postgres collations and case sensitivity about 6 years back, and it was within 3 months of my first heavy use of postgres. So I wonder if this person was just experiencing their first usage of postgres too? And I've used relational databases for more than 25 years.
craigkerstiens 6y ago
Guest here. The context is Uber was on MySQL which by default doesn't respect (or at least in their version and setup at the time) case sensitivity. To MySQL craig is the same as Craig when matching. Their app for search relied on this. The solution for them was the citext data type to create a case insensitive string - https://www.postgresql.org/docs/13/citext.html https://www.postgresql.org/docs/13/citext.html I'm fairly familiar with Postgres, but have not heard of a collation that tackles case insensitivity without tweaking the system itself? Postgres you can tweak this at the system level, but I've not seen it in the wild and as per the docs you're now non deterministic. For MySQL it appears you can change this, but seems a surprising default for them they expected case insensitivity. Yes, another option could have been indexing and searching on lower or upper casing, but they wanted the minimal change to their app. There was once a conference talk they gave about their migration process from MySQL->Postgres, but I'm not immediately able to find the video online so it may have been removed.
- Arnavion 6y ago
  I'm not familiar with pg, but the choice of case sensitivity is a fundamental property of collations in other DBMSes like MSSQL. Web search does make it look like pg only got case-insenstivity-via-collation in v12 (2019), and also requires you to make your own collation with case-insensitivity since it doesn't have any built-in ones with it.
- gigatexal 6y ago
  Case insensitive search is accomplished with ILIKE no?
  - johannes1234321 6y ago
    How well does ILIKE work with indexes? In MySQL the collation (which includes case- and accent-independency options) is known to the index.
    - spacemanmatt 6y ago
      You can stipulate the collation in the query if you need something non-default.
fuhrysteve 6y ago
Great practical note by Josh Berkus on why Uber left Posgresql. Basically: runaway table bloat because Uber had a usecase that postgres doesn't address as well as InnoDB. https://www.postgresql.org/message-id/5797D5A1.5030009%40agliodbs.com https://www.postgresql.org/message-id/5797D5A1.5030009%40agl...
- api 6y ago
  The whole VACUUM paradigm is the biggest thing that bugs me about pgsql. The fact that it can actually freeze things always worries me. Can’t this happen constantly in the background like modern GCs?
  - chousuke 6y ago
    It does, though? That's what autovacuum is, unless I'm missing something. It's just not always enough without tuning, just like GC.
    - api 6y ago
      We have had a lot of problems with frequently updated tables. Is auto tuning really that hard? Or is pg’s philosophy not to “auto” anything?
  - spacemanmatt 6y ago
    I think you might be pleasantly surprised to get current on how vacuum works in current editions. Vacuum used to be a bigger issue for pg.

Animats 6y ago

Databases shouldn't be exciting. They should just work. The main reason webcrap works is because the databases underneath work. If the Javascript crowd had to manage data storage, nothing persistent would work reliably.

msla 6y ago
> Databases shouldn't be exciting. They should just work. A complicated thing which just works is exciting.
- bigbubba 6y ago
  One man's exciting is another's snorefest. A lot of people would probably laugh at the suggestion that anything involving computers could be exciting.
  - systemvoltage 6y ago
    It is unproductive to talk about subjective opinions of excitement. The GP was referring to "Databases shouldn't be exciting" - not in the traditional sense of the word, but a metaphor that hinges on "excitement" being new features, frivolous decorations, unnecessary tangents, losing vision of what a database should do, frantic patching and heated bikeshedding discussions. It wasn't meant as "Ooooo I am excited about a database".
    - bigbubba 6y ago
      I think you're restating my point.
systemvoltage 6y ago
It is often downvoted to talk about the "javascript crowd" on HN, but I've worked in software industry long enough to unequivocally and with certainty say that it totally lives up to the cliches. It never gets old to scold people that are not serious, disciplined and principled in their endeavors that affect the rest of the world. Javascript community absolutely deserves the avalanche of criticisms they face. It is the reason why the modern web sucks, and frankly - I wish folks at Postgres would develop a new web standards including browsers that are not governed by corporations and can be implemented in a couple of weeks. Not trivializing what goes into building browsers, but making the whole standard small enough so any one can build a browser during a sabbatical.

rbanffy 6y ago

I started using PostgreSQL because, at the time, MySQL wouldn't do subqueries. The fact it silently truncated VARCHAR data to the size of the column and didn't give out an error didn't help much. Overall, my impression is that the project's philosophy is to do the Correct Thing, even if it's slow.

Ralfp 6y ago
Few years back I've had friend explain the difference between MySQL and PostgreSQL like this: - MySQL: do it fast, then make it right - PSQL: do it right, then make it fast
setr 6y ago
I had switched after learning that CHECK constraints were parsed, but ignored -- I've yet to be as offended by a program that at that instant Apparently supported now but what the hell; I don't think they even threw a warning about it
Delk 6y ago
Years back, the conventional wisdom seemed to be that MySQL used to be faster, and PostgreSQL more correct. Over time, according to that same conventional wisdom, MySQL got more correct than it used to be and PostgreSQL got faster than it used to be.

udev 6y ago

I like the this whole class of questions "What's so exciting/good about <SOMETHING>?" Online (forums, etc) this type of question usually elicits informative answers, and probably in face to face communication too.

anonymous324 6y ago

PostgreSQL isn't exciting. It's boring, with roots in the Berkeley Ingress project from the mid 80s. Which is why 10 years ago, so many HNers went with trendy NoSQL data stores instead, even though they lost data. Data loss is very exciting, and PostgreSQL is very boring. This preference for what's exciting and trendy over what's old and proven is a large reason why so much software today sucks.

Someone1234 6y ago
That seems unfair. NoSQL was trying out new ideas in data storage. It was exciting to try out new or re-imagined core concepts, and some of those young projects had teething issues. But several are still around and remain popular, but they're popular for certain niches they excel at (and those niches were largely discovered through trial and error). In the SQL-sphere a lot of people skipped Postgres because MySQL had, at the time, the momentum in the free/cheap relational database space. Between then and now Progres has grown more elegantly than MySQL, and people are rightfully looking to it.
- anonymous326 6y ago
  > NoSQL was trying out new ideas in data storage Was it? Or was it just rehashing old ideas made obsolete by relational databases? > https://en.wikipedia.org/wiki/IBM_Information_Management_System https://en.wikipedia.org/wiki/IBM_Information_Management_Sys... > Initial release 1966; 54 years ago > https://en.wikipedia.org/wiki/Category:NoSQL https://en.wikipedia.org/wiki/Category:NoSQL
- treeman79 6y ago
  MySQL was also a lot faster in the early days. Plus it “just worked” not sanely mind you. But you had a lot less problems with type mismatches, up/downcast, etc. Well unless you wanted quality data. But back then we were just happy that it didn’t bother us with “minor” details.
  - hoistbypetard 6y ago
    It was a lot faster, and sometimes the odds that your data might live long enough for those integrity issues to matter seemed very remote. Around 2001, the conventional wisdom was that it was worth the risk to take the performance victory, keep your hosting bill down, use mysql, and build some other approach to data integrity. (If memory serves, the integrity downsides were bigger then, too...) The math is pretty different now. I'm not sure it's fair to hang all of it on Oracle, but it feels like that acquisition derailed some MySQL progress that might've made it a close call today. Now, I use Postgres unless I have a very specific, very compelling reason not to.

shay_ker 6y ago

Surprised they didn't cover transaction id wraparound, which has caused more than a few public outages at scale. IIRC some Postgres folks have a plan to fix it? Maybe Craig knows?

anarazel 6y ago
> Surprised they didn't cover transaction id wraparound, which has caused more than a few public outages at scale. There have been some significant improvements in the last couple years - most importantly the introduction of the freeze map in 9.6. But also quite a sprinkling of other incremental improvements. It can obviously still be a problem, but it's not as pronounced as it once was.
- shay_ker 6y ago
  can you elaborate?
  - anarazel 6y ago
    > can you elaborate? On what exactly? The freeze map bit I referenced above is the following commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd31cd265138019dcccc9b5fe53043670898bc9f https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough infrastructure to avoid vacuuming pages where every tuple on the page is already frozen. So, replace the notion of a scan_all or whole-table vacuum with the less onerous notion of an "aggressive" vacuum, which will pages that are all-visible, but still skip those that are all-frozen. This should greatly reduce the cost of anti-wraparound vacuuming on large clusters where the majority of data is never touched between one cycle and the next, because we'll no longer have to read all of those pages only to find out that we don't need to do anything with them. This means that an anti-wraparound vacuum (automatically started, even when autovacuum is disabled) is cheaper than it used to be. Still not necessarily cheap, as indexes still need to be scanned (but see below). Some of the additional changes were (reverse chronological order): * 2020-03-28 - "Trigger autovacuum based on number of INSERTs" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b07642dbcd8d5de05f0ee1dbb72dd6760dd30436 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This is important because it will reduce the cost of an eventual index wraparound, as there will be less work in a later anti-wraparound vacuum * 2020-01-20 - "Allow vacuum command to process indexes in parallel." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40d964ec997f64227bc0ff5e058dc4a5770a70a9 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... As index processing can be expensive (no equivalent to scanning only changed parts of table), processing them in parallel can greatly reduce the time for a vacuum. Note that this isn't yet done by autovacuum. * 2019-04-04 - "Allow VACUUM to be run with index cleanup disabled." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a96c41feec6b6616eb9d5baee9a9e08c20533c38 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can be extremely useful for manual vacuum when getting close to a wraparound, since the index processing step is not necessary to stave of wraparound. * 2018-04-04 - "Skip full index scan during cleanup of B-tree indexes when possible" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=857f9c36cda520030381bd8c2af20adf0ce0e1d4 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can make vacuums for pretty clean tables vastly cheaper. There's also a number of changes that make indexes smaller / less likely to bloat. That in turn makes vacuuming more efficient. E.g. * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d861bbb702f8aa05c2a4e3f1650e7e8df8c8c27 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd299df8 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Edit: formatting (gah, why is HN formatting so limited)

axegon_ 6y ago

Apart from being the most mature relational database, I would argue the code that runs it. It's pure poetry. I am yet to see code this clean anywhere. Just opening any source code file at random[1] will show you exactly what I mean. [1] https://github.com/postgres/postgres/blob/master/src/backend/catalog/catalog.c https://github.com/postgres/postgres/blob/master/src/backend...

systemvoltage 6y ago
Let's not get carried away. It is very much debatable which database is "most mature". Here is some discussions about how Postgres compares with Oracle DB (if $ is not an issue), it is often said that Postgres is a Cessna whereas Oracle DB is like a Fighter Jet: https://news.ycombinator.com/item?id=24582937 https://news.ycombinator.com/item?id=24582937
- axegon_ 6y ago
  I've never had the opportunity to test Oracle DB at large, just some minor playing around with 12c many many years ago so I don't have an accurate view. That said, given my experience with other Oracle products and the fighter jet analogy... De omnibus dibutandum est...
- ivolimmen 6y ago
  I have been working with postgres for multiple years and in one project I needed to work with oracle. I actually ran into a bug in oracle db and they did not listen to us because we where a small company. We where working for a very large customer and asked that company to complain on our behalf at oracle. They they finally fixed it. Project was a large GIS project. I never had a say in the choice of database otherwise I would have chosen postgres. Oracle regularly crashed on complex spacial queries. At some point I left te company just to stop trying to fix that product. I think the only way of fixing the problem was a rewrite of the product that would use postgres. I hope I never work with oracle products in my life again.
deleted 6y ago
[deleted]

mxyzplk 6y ago

It works reliably and is not at the mercy of Oracle - so it's pretty much the winner for relational storage, unless you need something different (doc db, time series db...).

pmiller2 6y ago

This was a really good article, and thank you @jerodsanto for submitting it. I learned probably 6-7 big things about Postgres reading it that I didn't know before. I won't list out those things, because, I'm sure it wouldn't be relevant to most people reading this comment. :). But, there are a couple things I noticed that I do want to mention: > There were these edge cases in there, where it’s kind of like less safe. And Postgres was there, and it had a reputation of being just rock-solid, consistent, and more strict with your data > "... I never had that moment where I’m like “Oh, Postgres, you screwed me over.” This is a huge deal, and, IMO, the reason to choose Postgres over MySQL. MySQL has some horrendous defaults. You can make things a little better, but, AFAIK, you can't even get to where Postgres starts out of the box. Most of that consists of MySQL being extremely permissive with what it allows you to do in your SQL. Many of those things are non-standard, and some are quite unsafe. Postgres not only prioritizes safety, it keeps the application developer honest. It can be a little complain-y, but I'd rather have my database complain than do stuff that could lose data. One great example is when you try to stuff more characters into a text field than it can accommodate. By default, MySQL just truncates the string silently; Postgres just doesn't allow it. > PostGIS is a huge one. It’s a whole geospatial database. This is the other point I want to call out. I've had the pleasure of working with PostGIS before. The main thing I'd want to say about PostGIS is that, although there are issues working with geojson and GIS in general, PostGIS doesn't exacerbate any of them. Generally speaking, PostGIS is about as pleasant to work with as a geospatial database can be.

redis_mlc 6y ago
> I'd rather have my database complain than do stuff that could lose data. One great example is when you try to stuff more characters into a text field than it can accommodate. By default, MySQL just truncates the string silently; Postgres just doesn't allow it. That's your opinion, but the truth is not necessarily black and white. Postgres will probably lose more data in your case when the application developer doesn't handle the exception for a text field too long, and loses the whole row. (If you're an application developer, you should audit your code now.) A well-written application using Postgres would have to truncate the data before insert, achieving the same result as having MySQL auto-truncation, but with more initial and on-going maintenance effort in the case of Postgres. And the existence proof that MySQL's behavior is ok is that MySQL is the most widely-used relational databases, and powers most Internet sites. Source: DBA.

postgresqlrock 6y ago

Features I miss in PostgreSQL: - Support large number of connections (without external pooling tools) and stop starting a new process per connection (consumes huge amount of resources). Other DBMS works with threads instead of processes and don't have any issues. - Easy to configure high availability with multiple nodes Less important: - Graph capabilities directly in PostgreSQL without extentions or something else - Running a scheduling tasks directly in PostgreSQL without external tools (for example a script that removes old data from database). - Supporting Temporal Tables But overall I like PostgreSQL more than Oracle or SQL Server, even if I miss the above features.

cxcorp 6y ago

Wow, what a great episode! Probably one of the info-loaded podcasts I've listened to. Definitely going to be using psql's \e command with $EDITOR set to VS Code.

snthd 6y ago

>[unintelligible 00:08:02.01] "is to go hack on" >[unintelligible 00:09:07.07] "lemming status of developers" >[unintelligible 00:19:16.22] "laying in" >[unintelligible 00:27:29.18] "tables" >[unintelligible 00:30:14.16]] "recompete"

boredpenguin 6y ago
> Our transcripts are open source[0] on GitHub. Improvements are welcome. Looks like you (or anyone else) can directly contribute these corrections [0] https://github.com/thechangelog/transcripts/blob/master/podcast/the-changelog-417.md https://github.com/thechangelog/transcripts/blob/master/podc...

golergka 6y ago

> There’s a professor from the University of Moscow that – I’m not sure if he still teaches, or used to teach on astrophysics, and then hacks on Postgres for fun. I'm pretty sure he's talking about Oleg Bartunov! He's one of the best speakers about Postgresql at Russian Highload conference: https://www.youtube.com/watch?v=uhvqly8MtoI&list=PLH-XmS0lSi_xqSyUtHzdYuEGDPrwGPpnk https://www.youtube.com/watch?v=uhvqly8MtoI&list=PLH-XmS0lSi...