8 ms·
Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a pa
by paulryanrogers 2y ago
Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a painful workaround based on triggers that amplified writes.
Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL.
At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of downtime.
- api 2y agoI've always wondered why Postgres is so insanely popular. I mean it has some nice things like very powerful support for a very comprehensive subset of SQL functionality, but most apps don't need all that. It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it.
- stickfigure 2y agoWhat's the alternative? MySQL? No transactional DDL, immediate fail.
- jes5199 2y agoI worked for a company that migrated from mysql to postgres, but then got big enough they wanted to hire fulltime database experts and ended up migrating back to mysql because it was easier to find talent
- bastawhiz 2y agoDunno if that says much about Postgres, but it says a lot about the company
- deleted 2y ago[deleted]
- appendix-rock 2y ago[dead]
- icedchai 2y agoUgh. I worked with MySQL earlier in my career (until about 10 years ago.) All the companies since have been Postgres. All my personal projects are Postgres. I can't imagine going back.
- cosmotic 2y agoIt's not just DDL that isn't transactional, there's a whole bunch of other things that aren't. And they break the transactionality silently. It's like an obstical course where bumping into something might be fatal.
- evanelias 2y agoWhat specific non-DDL things are you referring to here? Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES. This is all documented very clearly on https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html. Hardly an "obstical course".
- stickfigure 2y ago"Aside from missing his head, the patient appears to be in fine shape."
- evanelias 2y agoThat hardly seems equivalent. Why do you need to e.g. reconfigure replication inside of a transaction in the first place? The lack of transactional DDL is a totally valid complaint, but the non-DDL stuff is just a total head-scratcher to me. Aside from DDL, implicit commits have literally never impacted me in my 21 years of using MySQL.
- stickfigure 2y agoSorry - I was trying to make light of the discussion. DDL is so important that it's silly to talk about the other stuff.
- cvalka 2y agoTiDB
- justin_oaks 2y ago> It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it. How software "feels" is subjective. Can you be more specific?
- threeseed 2y agoThe command line experience is old school style i.e. to show tables. \c database \dt Versus: use database show tables
- dventimi 2y agoThat's psql.
- fhdsgbbcaA 2y agoIt’s also faster to type.
- eYrKEC2 2y agoNot after you have to google, "What's the equivalent of `show tables` in postgres?", because the psql command names are completely arbitrary.
- fhdsgbbcaA 2y agoWhich you need to do exactly once.
- tpmoney 2y ago> I've always wondered why Postgres is so insanely popular. In no particular order, my preference for postgres is driven by: * Date / time functions that don't suck * UTF-8 is really UTF-8 * 99% of a backup can be done live with nothing more than rsyncing the data directory and the WAL files * Really comprehensive documentation * LTREE and fuzzy string match extensions * Familiarity from using it for years MySQL/Maria I'm sure is fine, but it's one of hose things where it's just different enough and I haven't encountered a compelling use case for changing my preference.
- fhdsgbbcaA 2y agoUTF-8 is what made me switch. It’s insane MySQL has something called UTF-8 that isn't really UTF-8, but do have a type UTF8MB4 that actually is correct. This means if you use UFT-8 in MySQL, you can’t use emoji for example.
- bastawhiz 2y agoAnd the fact that adding real utf-8 support limited (limits?) the length of strings that can be indexed
- evanelias 2y agoPostgres limits btree keys to 2704 bytes, which is actually slightly smaller than MySQL's limit of 3072 bytes, assuming the default InnoDB storage engine. That said, when using utf8mb4 in an index key, MySQL uses the "worst case" of each character being 4 bytes. So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set. For practical purposes, this doesn't cause much pain, as it's generally inadvisable to use complete long-ish strings as a key. And there are various workarounds, like using prefixes or hashes as the key, or using binary strings as keys to get the full 3072 bytes (if you don't need collation behaviors).
- bastawhiz 2y ago> So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set. This is exactly what I mean. 768 characters for an index is woefully bad. And for no obviously great reason: you can just index the encoded UTF-8 text. This was literally reason why a former company (who will remain nameless) refused to add Unicode support. It's not even an imagined problem.
- DonHopkins 2y agoBecause it's not tainted and cursed by Oracle, like MySQL (and Oracle).
- immibis 2y agoThat's what MariaDB is for, right? I'm surprised to hear people recommend the Oracle fork of MySQL (still called MySQL because they own the trademark) rather than the original project (now called MariaDB)
- fzeindl 2y ago* transactional DDL * comprehensive transaction model using different modes * PostGIS and lots of other great extensions * supports most of the current SQL standard and is clear on interpretation of edge-cases in the documentation * support for writing stored procedures in any major programming language * many useful functions regarding dates, sets, ranges, json, xml, ... * custom datatypes * extremely thought-out and standardized approach to development: if a feature is included it generally works well in interaction with everything else * syntax, semantics and performance are all very predictable * great documentation Regarding MySQL / MariaDB: MySQL optimized for performance first. Until 2010 the standard-storage-engine MyISAM didn't even support transactions. PostgreSQL always focused on correctness and stability and then made sure everything performed.
- arkh 2y ago> * custom datatypes Good in theory. But last time I checked the main libs to connect to pgsql, everything you get back from the database are strings. So you need something in your app to convert those strings to the equivalent data structures.
- ttfkam 2y agoYou're thinking only in terms of application. Types in the db save storage space, allow for better validation than plain strings, can be correlated cleanly with other columns with the same type, etc. Yes, more drivers and libraries should support the more expansive data type list, but even just within the database itself there are multiple advantages.
- moogly 2y ago> I've always wondered why Postgres is so insanely popular Real answer: no licensing cost
- Scramblejams 2y ago> I've always wondered why Postgres is so insanely popular. Just another anecdote: MySQL lost data for me (2004). I spent some time evaluating the projects and Postgres’ development process seemed much more mature — methodical, careful, and focused on correctness. Boring, which I loved. I didn’t need whatever perf advantage MySQL had so I switched to Postgres and never looked back. And then the Oracle drama and Monty’s behavior around it — not saying he was wrong or right, but it was the opposite of boring — just reinforced my decision. I like to play with new tech in various spots of the stack, but for filesystems and databases I go boring all the way.
- vbezhenar 2y agoFor me Postgres is 100% predictable and reliable. It's neither clunky nor arcane in my experience. I don't need to think about it, I just SQL it and that's about it. It quietly works in the background. At some scale there might be some issues, but there is always known path to solve things.
- paulryanrogers 2y agoI've never lost data with PostgreSQL. MySQL had enough data loss bugs and foot guns that I ran into a few of them.
- darth_avocado 2y ago> At least when you cannot spare more than a few minutes of downtime. I think it boils down to this. We used to constantly be running the obsolete pg version until it became an emergency mostly because upgrading with the tooling available at the time was very painful. Today however, we stay relatively up to date. Once you figure out the data replication, you can almost do blue green deployments on databases with almost no down time.
- slotrans 2y ago"Not as robust as MySQL"? Surely you're joking.
- Propelloni 2y agoIt is hard to live down a reputation ;) MySQL was immortalized as the database in every LAMP stack. And just like PHP it improved considerably since then.
- p_l 2y agoAlso for licensing reasons for a long time there was surviving contingent of MySQL 3.23 in LAMP hosting.
- johannes1234321 2y agoWhile that change from LGPL to GPL affected only the client library (server always was GPL(+commercial)) and the MySQL company relatively quickly reacted with a FOSS exception to the GPL and by providing a reimplementation of the client library under PHP license (mysqlnd) to serve that market. (I joined MySQL shortly after that mess, before the Sun acquisition)
- p_l 2y agoRandom hosting providers that were major place for having your baby steps on LAMP stack didn't necessarily grok licensing much
- johannes1234321 2y agoThey also didn't like updating software - to likely that update to PHP or MySQL or something broke some bad script by a customer, who'd complain to the host.
- sgarland 2y agoThey’re not wrong. If you’ve ever spent meaningful time administering both, you’ll know that Postgres takes far more hands-on work to keep it going. To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound, etc.
- aorloff 2y agoA shudder went through my body hearing that, I had forgotten all about Slony.
- cenamus 2y agoIs slony some sort of language joke? Slon is czech (probably slavic in general) for elephant. (which may be actually derived from the turkish aslan, for lion, but somehow the animal got mixed up)
- newaccount74 2y agoI think so. The PostgreSQL logo is an elephant, and the community calls him "Slonik", probably derived from the Russian word for elephant. There is also a node.js driver that is called "slonik"
- anticensor 2y agoNot really, the older form of Turkish aslan is actually arslan.
- cenamus 2y agoI just read that some time ago https://en.wiktionary.org/wiki/Reconstruction:Proto-Slavic/slon%D1%8A#Etymology_1 https://en.wiktionary.org/wiki/Reconstruction:Proto-Slavic/s...
- pella 2y agoMySQL vs. PostgreSQL - Jepsen Analyses https://jepsen.io/analyses/mysql-8.0.34 https://jepsen.io/analyses/mysql-8.0.34 ( MySQL 8.0.34 2023-12-19 ) HN: https://news.ycombinator.com/item?id=38695750 https://news.ycombinator.com/item?id=38695750 "MySQL is a popular relational database. We revisit Kleppmann’s 2014 Hermitage and confirm that MySQL’s Repeatable Read still allows G2-item, G-single, and lost update. Using our transaction consistency checker Elle, we show that MySQL Repeatable Read also violates internal consistency. Furthermore, it violates Monotonic Atomic View: transactions can observe some of another transaction’s effects, then later fail to observe other effects of that same transaction. We demonstrate violations of ANSI SQL’s requirements for Repeatable Read. We believe MySQL Repeatable Read is somewhat stronger than Read Committed. As a lagniappe, we show that AWS RDS MySQL clusters routinely violate Serializability." --------------- https://jepsen.io/analyses/postgresql-12.3 https://jepsen.io/analyses/postgresql-12.3 ( PostgreSQL 12.3 2020-06-12 ) HN: https://news.ycombinator.com/item?id=23498781 https://news.ycombinator.com/item?id=23498781 "PostgreSQL is a widely-known relational database system. We evaluated PostgreSQL using Jepsen’s new transactional isolation checker Elle, and found that transactions executed with serializable isolation on a single PostgreSQL instance were not, in fact, serializable. Under normal operation, transactions could occasionally exhibit G2-item: an anomaly involving a set of transactions which (roughly speaking) mutually fail to observe each other’s writes. In addition, we found frequent instances of G2-item under PostgreSQL “repeatable read”, which is explicitly proscribed by commonly-cited formalizations of repeatable read. As previously reported by Martin Kleppmann, this is due to the fact that PostgreSQL “repeatable read” is actually snapshot isolation. This behavior is allowable due to long-discussed ambiguities in the ANSI SQL standard, but could be surprising for users familiar with the literature. A patch for the bug we found in serializability is scheduled for the next minor release, on August 13th, and the presence of G2-item under repeatable read could be readily addressed through documentation."
- Symbiote 2y agoI think you're describing versions before 9.2, which was released 12 years ago.