4 ms·
> 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 do
by 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.
- evanelias 2y agoWhy did you need to index fairly long strings in their entirety in a way that preserves collation behaviors? And why is a 768 character limit woefully bad, but a 2704 character limit is totally fine?
- bastawhiz 2y agoA URL, for instance, can't be safely stored in 768 characters, but it can be stored safely in 2704. If you then wanted to sort those URLs so that all URLs for each domain and path within that domain are adjacent, you need an index. Especially if you want to paginate over them with a cursor. Doing that without an index on the raw value is a royal pain in the ass. Hell, even just being able to sort user-submitted strings up to a kilobyte. Why up to a kilobyte? Some users have strings that are kind of long. If I have to define a second column that's the truncated prefix, that's just a silly waste of space because MySQL decided to use utf-32 under the hood.
- evanelias 2y ago> A URL, for instance VARBINARY is typically ok for that I'd think? Then you can utilize the full 3072 byte limit for the key, since there's no character set applied. > even just being able to sort user-submitted strings up to a kilobyte As a software engineer, I completely agree. But as a DBA, I am obligated to make a "tsk" sound and scowl disapprovingly!
- crazygringo 2y ago
- sgarland 2y ago> This means if you use UFT-8 in MySQL, you can’t use emoji for example. I for one have always viewed this as a perk.
- fhdsgbbcaA 2y agoA database that doesn’t give you back what you put into it is never a perk. It literally can’t handle storing and retrieving the data.
- sgarland 2y agoI don’t want to see emoji in my database. The customer is only right in matters of taste, not engineering.
- ttfkam 2y agoUhh… not wanting to see emojis is a matter of taste, not engineering.
- fhdsgbbcaA 2y agoOk so if you are doing sentiment analysis of user product reviews you want to silently truncate emoji because you don’t like them? That’s a good idea how?
- homebrewer 2y agoI won't defend that utf8 brain damage, but the defaults are sane since 2018 — you don't need to set the encoding, it's set to proper utf8 out of the box. MySQL 8 cleaned up a lot of this legacy stuff.
- fhdsgbbcaA 2y agoGood to hear they saw the light but after I switched to Postgres I never had a single regret. In a competitive market where people make very long term engineering decisions based on stability and reliability you can’t fuck up this badly and survive.
- sgarland 2y agoMySQL does have ON UPDATE for its DATETIME, though; something that Postgres inexplicably still lacks.
- fanf2 2y agoIsn’t ON UPDATE related to foreign keys and independent of the data type? https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK https://www.postgresql.org/docs/current/ddl-constraints.html...
- paulryanrogers 2y agoMaybe they're thinking of TIMESTAMP in MySQL, which IIRC would auto update its value on any update to the row. Which was useful for uodated_at like columns. Though I think they later limited it to only the first TIMESTAMP column in a table.
- sgarland 2y agoNo, it works for both [0] types. The first TIMESTAMP thing you’re referring to is that if a specific variable isn’t set, the first TIMESTAMP column automatically gets auto updates applied on creation and update, unless you explicitly defined it to not. This was the default behavior in 5.7, but has since been changed. [0]: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html https://dev.mysql.com/doc/refman/8.0/en/timestamp-initializa...
- ttfkam 2y agoTransactional DDL!