Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What’s so exciting about Postgres?

changelog.com

Post reply on HN
Print view View original

81–90 of 137 posts

Re: What’s so exciting about Postgres?

#81

Post by setr »

rbanffy wrote: post #12

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.

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

Re: What’s so exciting about Postgres?

#82

Post by acdha »

nomad543 wrote: post #77
pmiller2 wrote: post #60

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

Your example is outdated, MySQL doesn't truncate by default in later versions. Defaults can easily be changed. Choosing one database over another because of defaults is not a good strategy.

The point still stands, however — here's an incomplete list of everything you have to worry about with a MySQL database which you don't need to worry about with most other databases:

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mo...

(I believe there are also still issues with Unicode, too)

I ran into a fun one a few weeks back: code which had been running for years failed on an AWS Aurora cluster because they'd defined a field as BIGINT and were inserting UUID_SHORT() values into it. For some reason, Aurora always generates values with the first bit set and so they learned the hard way that that field needed to be declared as unsigned.

Yes, the defaults can be changed but many, many people do not know this and will only learn why they need to after they have something blow up painfully. Often, even people who know this in theory will forget about it at some point when setting up a new one. It's the same reason why the industry is trying to get away from services like MongoDB / ELK listening on 0.0.0.0 with no password or having a default password which can easily be scanned. For something as widely installed as MySQL, even a 10% chance of oversights will mean a LOT of collateral damage.

Re: What’s so exciting about Postgres?

#83

Post by johannes1234321 »

gigatexal wrote: post #14
craigkerstiens wrote: post #8

Earlier quoted context omitted.

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 I'm fairly familiar with Postgres, but have not hea…

Case insensitive search is accomplished with ILIKE no?

How well does ILIKE work with indexes? In MySQL the collation (which includes case- and accent-independency options) is known to the index.

Re: What’s so exciting about Postgres?

#84

Post by redis_mlc »

pmiller2 wrote: post #60

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

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

Re: What’s so exciting about Postgres?

#85

Post by redis_mlc »

acdha wrote: post #69
pmiller2 wrote: post #60

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

> 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. This is incredibly important: if you use MySQL, unless you are extremely pedantic about using safe defaults and a safe-by-default ORM you will almost certainly end up in…

> I've even seen a few cases where people had data loss which had either gone unnoticed

Maybe under MyISAM, but InnoDB has been the default storage engine since 2010.

Re: What’s so exciting about Postgres?

#86

Post by Koshkin »

bachmeier wrote: post #78
robertlagrant wrote: post #52

Earlier quoted context omitted.

It's not, because it is a real DB. It's just one for single user access only.

> Think of SQLite not as a replacement for Oracle but as a replacement for fopen(). https://sqlite.org/about.html

Well sure, it’s better be thought of as a replacement for fopen() than one for Oracle. (Unless you were thinking of buying Oracle in order to use it on your desktop.) An even better way would be to compare it with MS Access, for example.

Re: What’s so exciting about Postgres?

#87

Post by acdha »

redis_mlc wrote: post #85
acdha wrote: post #69

Earlier quoted context omitted.

> 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. This is incredibly important: if you use MySQL, unless you are extremely pedantic about using safe defaults and a safe-by-default ORM you will almost certainly end up in…

> I've even seen a few cases where people had data loss which had either gone unnoticed Maybe under MyISAM, but InnoDB has been the default storage engine since 2010.

Under InnoDB, because we’re not talking about file corruption but cases where MySQL would silently and irreversibly discard data rather than throwing an error. For example, inserting a value which is too large for the target data type - it truncates the value so that value is lost unless you have another copy or way to reconstruct it.

Re: What’s so exciting about Postgres?

#88

Post by hodgesrm »

pmiller2 wrote: post #60

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

I think you are getting lost in the details. Databases are inherently complex and there are sharp edges as well as tricks you need to learn for each one. Beyond that row stores like MySQL and PostgreSQL are essentially interchangeable. The biggest technical success factor is whether you understand the DBMS well enough to use it effectively. The biggest business success factor is licensing: MySQL is GPLv2 and PostgreSQL is BSD.

Specific features are somewhat interesting but at this point not many teams are starting from scratch on MySQL or PostgreSQL. It's generally the case you already have experience on one or the other or both.

Re: What’s so exciting about Postgres?

#89

Post by pmiller2 »

redis_mlc wrote: post #84
pmiller2 wrote: post #60

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

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

Here's the deal: I am not presenting my opinion as objective fact, nor do I actually care that you're a DBA. If anything, the fact that you are a DBA makes your opinion less relevant to me as an application developer. Simply put, you do not share my pain, and the people who have upvoted me, while your comment is currently gray as I write this, have.

The example was intended to show a scenario in which MySQL silently loses data. There are certainly situations in which this sort of data loss is acceptable. I've been involved in scenarios where it was not acceptable, I was surprised by the behavior, and I ended up with a bunch of missing data.

The problem here is that I was surprised by my database. That should literally never happen. The correct behavior in this specific case is for the database driver to raise an exception or warning (exactly which should probably be a configurable option, defaulting to an exception). That way, if an exception gets raised in my application, at the very minimum, I have a traceback in my logs to tell me something went wrong, which allows me to fix the issue sooner rather than later.

As for your existence proof that "MySQL's behavior is ok," the fact that MySQL has a bigger installed base literally proves nothing. I guarantee you there are folks out there who are experiencing such behavior and being surprised by it, just like I was. There are people out there for whom this specific behavior is not a problem. And, there are people out there who are length checking every bit of text they try to stuff into a VARCHAR(n) or CHAR(n) field using MySQL, because they started with MySQL and they're basically stuck with it, unless they want to do major surgery on their app.

Re: What’s so exciting about Postgres?

#90

Post by hashtagmarkup »

edgyquant wrote: post #64
hashtagmarkup wrote: post #23

Earlier quoted context omitted.

> If the Javascript crowd had to manage data storage, nothing persistent would work reliably. Wait until this guy learns what the JS in "json" column types means, and how the largest webcrap sites rely on them for persistence.

Json has nothing to do with the persistence part.

If you need to persistently store frontend state in the backend, would it not be easier to use the native frontend data format?

It's not entirely about persistence... it's about the ease and reliability of the persistence.

Post reply on HN