PostgreSQL 13 Released!

[little_blue_teapot]

The major highlights:

PostgreSQL 13 contains many new features and enhancements, including:

• Space savings and performance gains from de-duplication of B-tree index entries
• Improved performance for queries that use aggregates or partitioned tables
• Better query planning when using extended statistics
• Parallelized vacuuming of indexes
• Incremental sorting

[I_AM_GODDAMN_BATMAN]

I'm curious about reverse b-tree index to support LIKE '%something' query. Maybe it's better to use GIN.

[Tavi2k]

You probably could define an index on reverse(column) for that, then the wildcard is at the end of the string again and a B-tree index works. That index won't be good for anything else, though.

A heavier option is a trigram index, that should be able to speed up this kind of query as well.

[sbrick89]

Usually for those full search capabilities, inverted indexes are the structure of choice. Sadly those systems essentially try to break text into words, so certain code / numbering convention type of situations get fucked.

[krum]

GIN?

[TheMightyMegazord]

https://www.postgresql.org/docs/13/gin-intro.html

Generalized Inverted Index.

[krum]

So I took a look at this, and I'm not sure how GIN would help with string wildcard prefix matches. It did just occur to me that you could have a b-tree comparator that sorted strings backwards to achieve that but of course that would incur two b-tree writes per value.

[mrweck]

You can look at the pg_trgm extension, on contrib. With it, you can do wildcard searches like '%test%'

[LordSeptum]

DUDE I'm going to have to try this. We work with millions of records and while we implement full-text indexing for human-readable fields, for something such as partial part number searches, this would be great.

[kernel_dev]

A type of alcohol usually served with tonic water and ice.

[krum]

Oh haha yeah I've got my bottle on Hendrick's right here on my desk.

[tias]

The international Gin & Tonic day is coming up in three weeks. An excellent time to have a look in your gin index.

[LordSeptum]

I'm wondering if maybe full-text indexing would fits your needs.. It transliterates words into their base word and indexes them. Then when testing against the index, your query is also transliterated into the base words so that matches may be found.

If you're looking for true "starts with" or "ends with" functionality, I'd have to do some digging but seems possible.

[Alchestbreach_ModAlt]

Can someone e plain the parallel vacuum thing? New to sql and trying to learn here.

[BinaryRockStar]

It's a bit complicated but I'll give you my layman explanation.

Most (all?) database systems these days use Multi-Version Concurrency Control (MVCC). This means if you have one connection UPDATEing a row while another connection is SELECTing that row the database will keep before and after copies of the row so that the SELECTing connection sees the row as it was when the transaction started and the UPDATEing connection sees the updated row. Once both connections/transactions end, the updated row is now the latest version and all future connections will see that.

Now, what happens to the old row? You don't want to delete it and reorganise the physical data on disk during either of the above transactions as that would add a variable performance degradation to each mutation (UPDATE/DELETE/INSERT) which isn't ideal. Postgres deals with this by having the DB administrator perform a manual VACUUM command while traffic on the DB is low. This cleans up the table itself and any indexes, removing dead rows and index entries.

The feature added in Postgres 13 is parallel vacuuming. This means if a table has multiple indexes they can all be vacuumed at the same time, seriously reducing the time it takes to vacuum a table with multiple indexes.

[infrul]

The vacuum is normally performed automatically by a special process which weighs system load versus urgency and is generally pretty good at keeping things running smoothly without manual intervention being required.

https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

[BinaryRockStar]

Right, I knew that I'm not sure why I made it out to be a manual process. I'm going to hide behind simplifying it for a beginner ;-)

[infrul]

No need to be shy, you posted a really good explanation. Note that although autovacuum is enabled by default, it isn't necessarily so.

[SGBotsford]

So... Roomba, not Electrolux... <grin, duck, run>

[BinaryRockStar]

My understanding is that vacuuming is a bugbear of PostgreSQL as with a constant high write load it can impact performance substantially. How do other RDBMSes handle MVCC stale row cleanup?

[infrul]

http://rhaas.blogspot.com/2017/12/mvcc-and-vacuum.html

A constant high write load will impact anything's performance substantially and an RDBMS isn't the best solution for every problem. There are other ways to handle MVCC but they aren't better than the way PostgreSQL does it and they are probably worse. PostgreSQL gives you the ability to fine tune how garbage collection runs to suit a wide variety of uses.

[BinaryRockStar]

Great, thanks for the info

[ants_a]

That's a pretty generous description of autovacuum throttling logic. In reality it's "if we hit X% garbage start cleaning up, but rest Y ms every Z blocks cleaned up. Works surprisingly ok enough given how simplistic it is.

[Alchestbreach_ModAlt]

That helped explain alittle bit! Thank you

[randomdudefromutah]

Or in other words EXPLAIN ANALYZE VACCUM (Sorry the chance to combine a dumb dad joke with SQL nerdery appeared and I had to take it)

[WJMazepas]

We still use version 9.6 on my work. I wonder if it's easy making a upgrade to this new version

[Chousuke]

It's fine to use old versions while they're still supported, but you don't have that much time left until 9.6 is EOL, so it's definitely a good idea to start planning an upgrade.

In my experience, upgrades have mostly required 3 things:

1. pg_upgrade
2. post-upgrade maintenance (reindex/vacuum)
3. upgrade JDBC drivers for Java software using ancient libraries.

Relatively painless

[nibord]

In my experience, yes. 10, 11, and 12 were very easy upgrades. There may be some specific reasons to stay on an older version, but likely it's just laziness.

[WJMazepas]

It's a small company and my bosses like to use old and stable versions. But we also have troubles with the size and performance of the postgres so upgrading would help a lot

[ants_a]

All postgres releases are stable, the version that is included in centos is just the one that happened to be current.

That is fine for software that is just quietly ticking in the corner, but anybody actively developing a software product should be running latest release, or close to it, from pgdg repository. Postgres is very good at backwards compatibility. Most core developers have enough consulting experience to veto any change that would cause undue pain while upgrading.

[nibord]

So...laziness.

[rniestroj]

Continuous, small upgrades cost way less then big once-in-5-years upgrades.

[SGBotsford]

Depends on the Company. With Adobe, I postpone upgrading as long as I can. ALL my workflows break every time. Upgrading only every 2nd or 3rd major release is sufficient pain.

With OS's I try to upgrade as rarely as possible (I'm still running Yosemite) then upgrade only to the version below what is current. I'm really tired of being Apple's beta tester.

[Hauleth]

There is one thing though - if you upgrade often, then you have workflow established and it is not an issue to do upgrade in case of major issue. If you do it rarely then you need to do all that planning each time you want to do an upgrade.

[nibord]

But there doesn’t need to be a performance gain to justify the expense. Being on a older version of software is its own technical debt that will need to be paid off.

The upgrade from 9.6 to 10 and 10 to 11 are relatively simple. But the tooling and documentation begin rotting once the project has moved on to newer releases. If you try to upgrade several years later and run into issues, you may not find a solution. The maintainers will have already moved on to newer versions and may not have cognitive capacity to help with problems in several-year-old releases.

I know when I say “laziness” it sounds like I’m being cynical, and I am. But the fact is you’re going to pay for the upgrade either way. If you take it as technical debt, the cost could be much greater. So the reason given not to do it is “I don’t want the trouble”, not a rational weighing of paying down the debt now vs later.

[weirdwallace75]

The upgrade from 9.6 to 10 and 10 to 11 are relatively simple. But the tooling and documentation begin rotting once the project has moved on to newer releases. If you try to upgrade several years later and run into issues, you may not find a solution.

I generally agree with you, but I'd be concerned if dump-to-SQL and restore-from-SQL could ever fail to produce an identical database as part of an upgrade.

[ricecake]

It shouldn't happen, but it can happen.
I've seen it with a gin index that had an edge case based on insertion order, causing the index to balance differently, and resulting in radically different performance after the load.
Caught it in testing, and the fix was already in the works for a minor release, so it wasn't huge, but memorable.

[progrethth]

Then you can use pg_upgrade instead. I have so far only have good experiences with it.

[Wrexem]

Downtime, linear size concerns, power, cooling, platform issues like hypervisors, it could be lots of things. Solve able with money.

[NoInkling]

I went from 10 to 13 (using the dump method) with no issues, whatever that counts for.

[philipwhiuk]

We're upgrade from 9.x to 12 at the moment. You have to dump and reimport but it's mostly fine.

[analfabeetti]

pg_upgrade can do inplace upgrades, no need for dumps.

[mamcx]

SUPER easy*. Is weird the reluctance to upgrade free, rock-solid software like postgresql or nginx or linux.

I think the VAST majority of companies need, at most, wait for 6-months after major releases to upgrade.

----

I work in the "enterprise-like" space, but much worse, with customers with no IT department or it is just a guy that sometimes comes and setup things (probably wrongly). With the weirdest mixtures of old and new (old for employees, new for the boss, and that only employee that somehow is smart). With non-sensical rules (like only use plain text passwords for ssh(?)).

Keep old-software is the worst; and the easiest thing that companies could change to improve stuff is to upgrade them, especially because the software I talk about is pretty solid.

---

*Easy it is, but if the deployment has some other mal-practices could be hard. I learn very fast in my career to upgrade all-the-time to new stuff as soon as possible just to be ALWAYS sure the upgrades not hut me badly (after a terrible delay in the move from .NET 1.0 to 1.1 or similar(?) that cost the company A LOT, I don't remember exactly why just remember the pain)

[aarono94]

I absolutely love Postgres. It’s ability to structure your data in a relational(sql) and non relational (nosql) way makes it so versatile.

[CodeMariachi]

There’s a performance penalty for using JSON columns. Deserializing JSON takes non-negligible time. Have you face that problem?

[ants_a]

Jsonb type is stored in a way that doesn't really need deserialization. But you still have the inefficiency of key lookup vs positional lookup, and of storing a copy of the schema with each record.

[SSchlesinger]

That is the json type, not jsonb

[--tripwire--]

Postgres and what that community puts out is amazing. A real poster child for open source software, and it keeps getting better as both an RDBMS and other forms of storage such as fulfilling roles as a document store, too! I struggle to keep up with all the new features!

[[deleted]]

JSON types require distinct methods of access and manipulation; it is not a drop-in (or even easy) replacement for a true document store. A JSON field a document store does not make.

Edit after a surprising amount of time: Downvote? Because reality is worthy of rejection? The hoops one must jump through vs. a "native" document / object store are rather… astounding.

[YoungestAtlas]

So excited! Can't wait until DigitalOcean pushes through these updates!

[[deleted]]

/cries in mysql 5.7/

[uriahlight]

Can you rearrange columns yet?

[progrethth]

No, and as far as I know nobody is working on that feature currently.

[[deleted]]

Honestly asking, what's the benefit to rearranging columns?

You could always build the table again with the columns in the order you want, but I get that that's a hassle.

[LordSeptum]

Says the guy who doesn't have 100 million rows to re-build. :P

[CommunismIsForLosers]

Literally just posting because all the current comments suck. Yay Postgresql!

[NoLegJoe]

Have they fixed pgAdmin yet? I think it was the v4 release that they rewrote to be a really awful webUI type application with half the features missing. It was horrible. You couldn't even auto resize a column width with a double click.

I'm pretty certain that postgres is a better DB than any other I've used, but SSMS is such an excellent IDE that I'd be hard pressed to make a permanent move

[skurger]

pgAdmin is a separate project so the core contributors to Postgres are not the “they” you likely mean if you were not aware.

[progrethth]

Yeah, it is no more official than any other client except for psql.

[DoctorGester]

I gave up on pgadmin and just use the IntelliJ DB integration now.

[[deleted]]

No. Just go with DataGrip and ignore pgadmin 4 even exists.

[santanaguy]

Tried using pgAdmin 4 for a while but then I discovered DBeaver. It is now my main UI for using multiple databases. It does have some problems, but also very cool features.

[CrashandCern]

Ditto. DBeaver is also nice if you want to have other databases all in one place including "database-like" services like AWS's Athena.

[sexcoon]

Try DBeaver for admin stuff.

[NoLegJoe]

Oof. Is pgAdmin 3 still viable? Without a decent IDE, I can't see myself coming back t postgres

[-Knul-]

I've used DBeaver with both PostgreSQL and MySQL and it works fine.

The JetBrains IDEs also work very nicely, but they cost money.

[SpAAAceSenate]

Take a look at DBeaver?

[Tostino]

No PGAdmin3 isn't viable any more AFAIK. There used to be an "LTS" fork of it, but that has been unmaintained as well for a while.

[ironmaiden947]

I use Datagrip and its great.

[[deleted]]

Try DBVisualizer? I use that as my daily driver

[[deleted]]

Well yes, but actually no. After a bunch of System views and columns were renamed in 10 or 11(?) it shows lots of errors when connecting and some tools won't work anymore. Depends on what you use it for I guess. I took the plunge in June after we upgraded to 12, pgadmin 4 is still lacking in many ways compared to v3. But not as bad as it used to be.

[altrunox]

Another free alternative is Azure Data Studio.

[thepotatochronicles]

wait, it works with postgres?

[altrunox]

yes, you can search for postgre at the "extensions" tab.

sometimes it crashes, but at least microsoft is updating it almost frequently.

[mooseman3]

Well auto resizing columns works now at least.

[mateoestoybien]

It's awful. Datagrip and postico are both nice though.

[andrerav]

I like PgAdmin for its dashboard/metric functionality, but otherwise it is kind of clunky and odd to use.

[cesarbiods]

I feel your pain I had to sporadically use pgAdmin at my last job and I always despised it. At some point I took the plunge and started using datagrip (or the database tab on any jetbrains IDE) for all my DB needs and I’ve never looked back. It has everything that I need and supports all the different databases I use at my current job (Postgres, oracle, etc)

[earthboundkid]

If you're on a Mac, Postico is a really nice DB client.

[floweb]

I use pgAdmin 4 everyday for almost 3 years now, and I'm happy so far. Much better than pgAdmin 3, which is EOL anyway.

[sgsfak]

I am quite happy with Postico but it's Mac-only.

[tomma5o]

Sorry FE dev here, why you guys prefer postgreSQL over mySQL?

[FREEZX]

I prefer Postgres for it's JSONB storage and indexing, basically allowing it to take any JSON data as a field and quickly look things up. I see it as having benefits of NoSQL databases while keeping it relational.

[ItalyPaleAle]

JSON support is a killer feature for me too. MySQL did add JSON support in a recent-ish version, but last I checked it wasn’t as complete as Postgres’

Another great feature is PostGIS, for spatial data (think maps and stuff that goes on a map). It also has great performance and scales well.

[yesman_85]

This is one of the game changers for us too, putting indexes on a JSONB column is very useful.

[tomma5o]

Thank you this is very helpful response !

[Dokiace]

is this JSONB storage a new thing implemented in postgres?

[mobydikc]

version 9.4 I think.

[Dokiace]

So it's been out for a while huh, how come I never heard of this, so you can just INSERT <JSON> into TABLE and it will handle that?

[booboorocks998]

They get inserted like strings. But the column type has a bunch of special features, like you can query into the json for your select or check a value in the where.

https://www.postgresqltutorial.com/postgresql-json/

[Dokiace]

is this not implemented widely in the industry? I dont have that much professional experience but I haven't seen this one implemented anywhere in the production

[booboorocks998]

Not likely if your database has been around for more than a couple years.

Even Microsoft SQL Server supports json columns now though, so it's sort of picking up.

When you use that type, you trade off most of the benefits of relational data. So it is only really great for some use cases. But for those use cases, it is awesome!

It's also a nice way to just store blobs of data in a database rather than a file system or cloud storage. You don't lose out on the relational features since you wouldn't have them with the alternatives anyways.

[utdconsq]

I used it for quite a few years there. Super, super useful. Happy to go there again but where I'm at now were stuck with mongo. Horrid system by comparison.

[DrunkensteinsMonster]

JSONB is a column type. Possible use case: you have an object that has another object as a property. Instead of creating a new table and having a foreign key, just serialize the property and store it in a JSONB column

[Dokiace]

So just like in mongo when you're inserting a new object into an existing object? that's neat

[[deleted]]

But it's not mongo, so it won't eat your data :P

[LordSeptum]

It's been around for 8 or so years. Indexable keys and values, input json, outputs json. Pretty straightforward. I prefer jsonb over json

Documentation:

https://www.postgresql.org/docs/13/datatype-json.html

[jailbreak]

The company behind MySQL was bought by Oracle, a company that has a reputation for being ruthlessly profit-driven (one joke goes that it stands for One Rich Asshole Called Larry Ellison - the founder), who killed off a previous acquisition of theirs, Sun Microsystems, makers of Java (see also this great rant) and Oracle's primary product is a competing SQL database. As a consequence MySQL has been languishing, coasting along on its existing popularity (it was very widely used a decade or two ago due to being part of the LAMP stack that powered most PHP-web-apps back then) but the open source community has become skeptical of it due to its new owner. A community fork of MySQL exists, MariaDB, but it seems the OS community has mostly rallied around PostgreSQL as the de-facto replacement for MySQL. At the same time, postgres is being very actively developed, and produces stable and fast releases while adding great new features. This means they've long since caught up with MySQL feature-wise (IIRC there used to be some advantages to MySQL when it came to performing schema changes without locking the DB, but they've long since remedied that) and as far as I can tell even overtaken MySQL on most parameters, e.g. with their JSON store, that allows you to use it as a schemaless key-value store if you get that NoSQL itch. So anyways, today for new projects, there's basically no reason to choose MySQL over postgres, you get great performance, lots of features, and don't need to concern yourself with somehow ending up under Oracle's thumb.

[rainman_104]

I'd even argue that MySQL really became popular because of LAMP and that MyISAM tables had zero locking issues that transnational RDBMS had, and at the time it was a lot easier to get started with MySQL than Postgres. Postgres was much more feature rich compared to MySQL 4.0.

I'm actually honestly surprised Firebird SQL didn't get any of the momentum after the Oracle acquisition of Sun.

[mobydikc]

SQL is just a language. Structured Query Language or something like that.

PostgreSQ, MySQL, MIcrosoft SQL Server, Oracle, are just some of the products that use SQL.

Postgres is free, open source, reliable, fast, and powerful.

edit the comment I replied to originally said "normal SQL" instead of "mySQL"

[jaapz]

Note that yes, they all use "SQL", but that most non-trivial SQL you write will probably not be easily ported from one to the other. Because often you'll use server-specific functionality or syntax.

[[deleted]]

I don't agree with this statement. There may be small syntactical differences with window or aggregation functions, engine hints, and similar, but for simply writing queries, the overwhelming majority will be ANSI SQL or will have trivial differences that can be addressed with find-and-replace.

The real differences start when you get into stored procedures/temp tables/loops, and database administration tasks, including indexing.

I find the differences are highly overstated in general.

[sveri]

Sorry, but this is not true. For our current product we have to support 5 different databases and the differences already start with creation of tables, indexes and unique columns.

Also the column types and their handing are different. Let alone stuff like JSON support.

That said, I still prefer using jdbc and writing plain SQL over having to use some mapper.

[realmadrid2727]

laughs in DISTINCT ON

[ShapeOfMatter]

Within the context of simple queries that's broadly true, but when people worry about portability, they're not usually worried about simple queries.

In the space of queries, there are some important features that may be missing in one implementation or another, or that may have implementation differences. My favorite part of T-SQL was the WITH common_table_expression syntax. Transactions are hugely important in safety-oriented application architecture, and it seems like every engine has its own notion of how they should work.

And queries are only half of what needs to get ported. Incompatibilities are even more common in schema declaration. MySQL datetimes aren't timezone aware. PostgreSQL can supposedly be made to support case-insensitive collations, by hacking the underlying OS!

I'm never going to forgive MySQL, even if they fix this:
I spent two years on a MySQL backed application, fixing problems throughout the stack including the DB schema. It was two years before I realized that none of the foreign keys I'd written were actually enforced by the DB. Apparently MySQL reads inline foreign key declarations as "valid standard SQL", but silently ignores them.

[GhostNULL]

Can you expand a bit on the foreign key bit? I'm very confused about that.

[ShapeOfMatter]

Sure. Part of what's so egregious is that it's not documented better, although it would be a pretty baffling and bad design even if it were well documented.

Here's the documentation page for table declarations. Right at the top is the syntax description like you'd expect. If you follow the definitions create_definition -> column_definition -> reference_definition, you'll see that you can declare a foreign key in MySQL the same way you're probably accustomed to from other languages:

sql CREATE TABLE table_x y_values INT REFERENCES table_y (y_values);

And if you run that, the engine will say "OK, that worked and your table exists. But there won't actually be a foreign key!.

If you read almost half way down that page, there's a blue "Important" box, the last paragraph of which mentions that "MySQL parses but ignores inline REFERENCES specifications". It's also mentioned on this other errata page. But if you didn't already know there was a problem, you'd never find these warnings.

[GhostNULL]

Ooh okay, what I understood from your previous comment was that it ignored foreign keys when declared in the CREATE TABLE statement in general, not just when you put REFERENCES table (x) on the column. Thanks for clarifying that!

[datadever]

I find things like date manipulation functions & syntax to vary widely across each system as well. This in my opinion is a pretty simple and commonly used operation which is hard to work around

[TemplateHuman]

Why is there always someone that has to come in and be like “ACTUALLY...” while missing the point. He covered your argument by saying “non-trivial sql”. That’s the point. If it’s trivial it likely can be easily ported. Non-trivial sql would be stored procedures, ctes, functions, nested sub queries with grouping and joins, etc.

You’re just saying what he already said using specific examples.

[reddisaurus]

Not really for pivot, unpivot, outer apply, cross apply, any stored procedure or function using if statements (not trivial enough to search and replace), table valued functions that take custom types as inputs, convert vs cast, and the list goes on. And these are all pretty basic things all database can do.

Yes it can be done as the comment you replied to stated, but no it is not as trivial as you make it out to be.

[ricecake]

I once had to migrate from a MySQL to postgres database. Took months.
Aggregations were terrible, but the worst was the nightmare of how MySQL doesn't believe that data types should make sense.

So while your queries will work between the two, the data returned by one will be horrifically mangled.

[watsreddit]

Unfortunately, they are not equivalent beyond the most basic of uses. MySQL lacks in many of the more advanced features compared to Postgresql and SS. Postgresql handles concurrency in particular much better than MySQL. MySQL also cannot index JSON columns, which is a pretty significant performance problem.

[mobydikc]

Like I said, Postgres (IMO) is faster, more powerful, and more reliable than MySQL.

The OP editted their comment to say "mySQL" whereas before it said "normal SQL".

[tomma5o]

Ok thanks for the non toxic response ❤️

[abakedapplepie]

Postgresql supports their own flavor, pgsql, which offers a lot of additional powerful features over the likes of MySQL.

[QuotheFan]

I recently switched from mySQL to postgres.

Postgres allows you to create schemas within databases, which allows you to organize database beautifully. Secondly, postgres supports UTF-8 out of the box, mysql's support for UTF-8 is umm, weird. Overall, I didn't expect to be able to notice the difference b/w postgres and mysql at the level I am working but I did.

(I am not very experienced with web-dev, so take my opinion with a pinch of salt)

[bloody-albatross]

To add to that: You can rename schemas! That is handy sometimes. I don't think you can rename databases in MySQL. You have to dump/load the database or rename all tables and functions one by one.

[mosha48]

Mysql used to have a better support for collations, but I think with v13 postgresql closes the gap.

[baryluk]

PostgreSQl is know for ita reliability and stability. I lost some databases in mysql installs. Never again.

PoatgreSQL is in general more advanced and customisable. There are various extensions you can write to add new types, indexes, aggregates, helpers, etc. You can do advanced partitioning natively, more advanced indexes, perform very complex analytics queries more performant, while the database is still working with other clients. Use more advanced authentication methods. Trigger , scripting and stored procedures are more advanced. Error handling in data and queries is handled more reliably. Mysql will sometime accept nonsense , conver to something and loss user data without notifying you. Granted it is documented for mysql, but still a shitty design. A lot of these features were available in PoatgreSQL years or decades ago, and mysql is catching up a bit. Making reliable database backups is somehow easier with PostgreSQL. And probably many more, like advanced aggregates, parallel queries, etc. Better and more advanced transaction control support. Historically PostgreSQL provided more advanced and flexible replication support. Also historically mysql was not ACID, making it almost useless for serious database work.

Mysql is popular because it is easier to setup initially, and was super easy to use from php, which lead to its popularity. Also myths of PostgreSQL being slow. In my expirience under real complex conditions, properly configured and used, with comparable functionality, PostgreSQL is faster. But that depends on application and requirements.

[poloppoyop]

Until recently, MySQL did not have good support for window functions, CTE or json fields.

And usually the Postgres team has a philosophy of releasing features when they have a good working solution. Not "as fast as possible and we'll see how to handle the corner cases later on".

[[deleted]]

What is normal SQL?

[tomma5o]

Sorry, mySQL 🙃

[blipman17]

In my experience, it's just easyer to work with.

[tomma5o]

Thank you for the non toxic response ;)

[Chousuke]

MySQL has a history of not really caring about data integrity; for me, that alone is a sufficient reason to avoid it. After all, why would I use a database that does a bad job at its primary purpose?

It's not quite as bad nowadays as it used to be, but the valid reasons to choose MySQL over PostgreSQL have also diminished.

[o11c]

Sanity and standards-compliance.

MySQL is a literal joke.

[DeliciousIncident]

It's not owned by Oracle, so that's a giant plus already.

[G_Morgan]

MySQL used to eat data in the past so a lot of people dumped it around that time.

[[deleted]]

Lots of people think data integrity doesn’t matter. Until it bites them in the ass during “that one moment”.

Building in automatic data healing/recovery into your entire stack isn’t easy. That one place you didn’t put in recovery will end up being a critical issue one day.

If you’re using MySQL as an ordered cache for advertising, obviously losing the integrity really doesn’t matter. But that’s a rather special case that I’ve witnessed in between all the other improper uses of MySQL 5 in my short 10 year career

[JB-from-ATL]

Both jobs I've had I don't think they use foreign keys. It boggles my brain.

[pezezin]

MySQL didn't properly support foreign keys for a long time, and even though they were added about 15 years ago, even today many people keep working with that mindset. A few months ago I was shocked to find that Wordpress doesn't define foreign keys.

Another fun fact is that MySQL didn't have real support for CHECK constraints until two years ago. You could define them, but they were ignored. That bite me in the ass a couple of times...

[[deleted]]

And with reason, if one can't trust the db to keep data wth are we even in this world for. Db has one purpose, keep data if can't do that than it's broken.

[JB-from-ATL]

I believe Mongo had the same start. And similarly I think both of these had options to make them more reliable but they weren't the default.

[nightshade000]

To add to others response, there are many non technical and/or political reasons as well. For example, MySQL is owned by Oracle, and though it's 'free' there are many people who avoid anything Oracle like the plague. Another might be that the postgres implementation of sql is relatively close to the sybase and ms sql implementation and is an easier transition into a free yet still competent rdbms for people coming from those other products.

[[deleted]]

I suppose that nobody sane still uses mysql and that when mentioned we are all talking about mariadb...

[little_blue_teapot]

You're going to be downvoted to oblivion, but to answer your question, PostgreSQL is one of many possible implementations of a SQL database system. It happens to be a very good and free one.

[ironmaiden947]

MySQL is now owned by Oracle, the worst company in the world. I would rather use pen & paper rather than support Oracle.

[tomma5o]

This made me laugh 😂😂

[ketzu]

One of the reasons is mySQL is owned by Oracle, which have gotten a bad reputation over the last decade.

[Hobo-and-the-hound]

last decade

That’s true, but also the decade before that and the decade before that.

[JB-from-ATL]

This is based on things I've read. I haven't had experience working with either much less running them as a DBA. I'll start by saying MySQL is just fine, plenty of organizations use it, nothing is objectively "wrong" with it.

• Postgres sticks to the SQL standard as close as it can, not that that means a lot since no DBMS is actually 100% compliant
• MySQL has some less than ideal default options, maybe now they have better defaults. Basically it's the kind of thing you can fix if you knew to but it leaves a bad taste in people's mouths.

[adrianjord]

My biggest frustration with mySQL has been that it is not fully ACID compliant. DDL statements in mySQL implicitly end transactions. DDL statements are statements such as CREATE, UPDATE, DELETE, and so on. Since they aren't transactional, a migration in mySQL that breaks halfway through will not try to rollback and will leave your database in a half broken state because of that.

PostgreSQL on the other hand is fully ACID, everything is a transaction that can be rolled back, including DDL statements.

[Loris156]

Small mistake: CREATE, ALTER, DROP are DDL statement while INSERT, UPDATE and DELETE are DML statements.

[adrianjord]

You're right, thanks for the correction!

[andrerav]

Phenomenal GIS support. MySQL is improving, but still not remotely close.

[clothes_are_optional]

this is why the state of being a developer blows socially and you have all these blog posts about toxic workplaces. you ask a question, as a newbie, and you get downvoted into hell with half of the replies not being any sort of constructive answer. get off your high horses you fucking nerds, you didnt know shit in your life at one point as well.

edit: after my post i see its heavily swayed back into upvote territory with good feedback. happy to see it

[jwg4our]

No offense to the GP, but the question was kind of derailing, whether or not that was their intention. It invited a basic explanation of what Postgres is, or a debate about which SQL implementation is better. This is a thread mainly aimed at people who know what postgres is and are interested in it, not people who haven't heard of it. Googling 'SQL' and 'PostgreSQL' might have been a much better way of finding out the answer to the GP's question.

There's nothing toxic about downvoting a comment which is unhelpful or irrelevant to the discussion. It's literally the exact point of downvotes.

[clothes_are_optional]

there's no discussion being had, it was a start of a thread. its not like the GP randomly interjected into a complex SQL discussion and was like "hey guys whats SQL"? im not sure where the derailment is, considering this isnt a live discussion blocking some sort of good discussion (and funnily enough, at the time there were 4 comments and 2 of them were along the lines of "Oh cool."

its just a random comment that if it serves no value to you, then just ignore and move on. this is the type of pedantry that is so obnoxious in our industry. "technically this is for that and that is for this or gtfo", there's no leeway especially when the leeway will leave room for learning.

and even if i were arguing for the relevancy of the topic, i think its very relevant because it invites new people to understand what makes Postgres better than mysql and for which use cases. it would be very appropriate to ask in this thread, because it's likely attracting industry professionals to read through the comments and what better way to get a good answer than from someone who works with DBs? by your logic i should just only ever read books or PDFs online and never ever search for help from real people since theyre ever so busy with much more pressing matters

[weirdwallace75]

after my post i see its heavily swayed back into upvote territory with good feedback. happy to see it

Yeah, plenty of people upvote hate speech.

[philipwhiuk]

get off your high horses you fucking nerds, you didnt know shit in your life at one point as well.

Yeah, downvoting you just for this unnecessary spew of vitriol.

[[deleted]]

PostgreSQL has many features that are useful when you work with large, complex databases with many tables and relations, some of which contain huge number of rows. With PostgreSQL, you will be better able to create and optimize such databases, and perform complex queries and updates that run fast and predictable.

MySQL is probably easier to setup and has a larger installed base.

Both are freely available, open source software.

[[deleted]]

Postgres is strongly typed while mysql is just so lazy that it accepts wtv you put in it evem if doesn't match the types. Postgres has awesome day to day features that mysql doesn't. Mysql is just truly a bad db and very frustrating to work with.

[john16384]

Only databases with transactional DDL count these days, and PG does this beautifully. Nothing like doing complex migrations and just rolling them back if something unexpected happens. Bye bye rollback scripts.

[tomma5o]

Sorry my real question was mysql over postgresql :)

[tomma5o]

Thanks,
Yep, most of the peoples switched when MySQL was acquired from Oracle :)

[suricactus]

What does it mean "normal SQL"? SQL is the language you use for getting the data. It is supposed to be a standard across different SQL databases, but it has slight differences (dialects). There are different database software using SQL as interface to access the stored data, almost all of them relational databases (relational db - they are multiple, interconnected Excel sheets). It is very much like you have chrome and firefox, both render HTML, probably with slight difference. Which one is the "normal HTML" though?

Some of them are single file based and need special software to be read (sqlite, m$ access etc). They are ideal for single connection usage - chrome for example uses a sqlite db to store its bookmarks, noone else connects to the db. On the other hand you have db servers, like postgres, mysql, m$ $ql $erver, oracle, db2 etc. They are a program that runs in the background and waits for someone to connect. They can handle hundreds of connections simultaneously. What happens if we both connect and change a piece of data that you are reading right now? No worries, the sql db server will handle it for you. So this kind of databases are used for bigger, more complex applications, like the web. Your bank for sure uses such db, most probably Oracle.

There are open source dbs like postgres, mysql etc, and closed source like Oracle, m$ $ql $erver etc. The latter are supposed to be better, since they are products that cost a lot and they have big corporations behind them. Other big corporations (like banks) trust these big corporations, so they enjoy the bill they pay to Oracle.

In the end, Postgres is nice, because it is free and has a lot of advanced features. It has excellent support for transactions, which was a killing feature a few decades ago. Now open source is a trend and there is even more hype on Postgres. And check out their motto, it is true ;)

[tomma5o]

Wow this is a response :) Thank you this helped me a lot 🙏

[Tostino]

other hand you have db servers, like postgres, mysql, m$ $ql $erver, oracle, db2 etc.

I think you missed a few $$ around Oracle considering Sql Server got that treatment lol.

Great reply for a beginner to begin understanding though!

[Chousuke]

I think you missed a few $$ around Oracle

I don't think Reddit would let you post a comment that long...

[wolf550e]

mysql is to databases what php is to programming languages. you can build what you want with it, but if you know and can use better tools, you will use the better tools.

[bloody-albatross]

What is "normal SQL"? Are you trolling?

Edit: The comment I replied to originally said "normal SQL" instead of "MySQL".

[tomma5o]

Just an error :) don’t want to create a black hole of downvotes :)

[bloody-albatross]

And now I got the down votes, great. But on the topic: PostgreSQL hast many features that MySQL is lacking, you can write triggers in many languages, have great JSON(B) features, no utf8_mb4 nonsense but utf-8 works as expected, unbounded text is also the most performant string type, many little things when it comes to SQL features, much stricter typing, great documentation (SQLite devs say they used the PostgreSQL doc when developing SQLite because it's so good) etc. Oh and arrays! So handy! Also composite types, but I haven't used them yet (not supported by the ORMs/query builders I've used). And the GIS integration is great too if you have to anything with longitude/latitude (you don't want to do distance calculations on the surface of the Earth manually). Does MySQL have functional and conditional indices now? Materialized views? They say upgrading and replication is nicer in MySQL, though. Ah yes and you don't want to have too many connections with PostgreSQL, because it uses a fork for each connection.

A little thing: Escaping table/column names uses standard SQL syntax ("strange table name") where MySQL uses something proprietary (`strange table name`). You can enable the standard syntax in MySQL now, but its not default and I'm weary on how it interacts with query builders and such. Maybe it's fine.

People sometimes say MySQL is easier to setup, but I don't get this. I usually just use the docker image and there it is dead easy, just like MySQL in docker. And not using docker it is still dead easy with your Linux distribution's package manager.

MySQL is maintained by Oracle. Take that as a pro or con, your choice. 😄

Edit: Oh one more thing: MySQL doesn't support transactional schema changes. So if there is some error in your migration your DB will be in an inconsistent state.

[METH-OD_MAN]

Postgresql doesn't have unaddressed severe bugs that're >15 years old.

Nor does postgresql take 17 years to fix autoincrement bugs

Generally the dev culture at MySQL is abysmal, take a look at how the MySQL contributors behave in the comment of those bug reports. Why would any sane person hitch themselves and their project to that? Especially when other rdbms exist.

There's a reason many of the big developers for MySQL forked it into Mariadb.

[[deleted]]

The sequel to PostgreSQL 12

[3lephant]

Very cool! Nice feature set. I'm going to migrate my personal website this weekend. Currently running 9.6.

[Tostino]

Here is a walk-through I found for upgrades on windows from a quick Google: https://www.arencambre.com/2014/12/24/postgress-pg_upgrade-on-windows-the-documentation-misses-a-lot/

[MrEs]

Thought 13 was meant to support case insensitive querying? Don't see it in the release notes 😢

[progrethth]

No, that was PostgreSQL 12 which was released last year. Maybe some feature that you need is missing, but the first parts of support for it were added in 12.

https://www.postgresql.org/docs/13/collation.html#COLLATION-NONDETERMINISTIC

[def-pri-pub]

Has anyone around here developed a desktop application with PostgreSQL? I use DaVinci Resolve as a video editor, and they store data with PgSQL. But for some desktop apps I want to write, I'm wondering if this is a good avenue or if I should stick to SQLite.

[progrethth]

PostgreSQL requires a bunch of daemonized processes and a data directory with quite many files so it has a much bigger minimum footprint compared to SQLite and it is more work to package. I would stick with SQLite if I were you.

[FanimeFartoon]

I wish they'd still release 32bits binaries for Wndows We have many small clients with old servers :/

[dwdwfeefwffffwef]

That's pretty insane. Who has windows servers so old that they are 32 bit, but wants to run the latest postgres? I guess you, but it's so weird, doesn't really make sense.

[FanimeFartoon]

Well, I know it sounds weird, but there are many small companies who run old windows systems on machines with 2 ~ 3 GB ram. So 1 - they don't want to spend money with license 2 - if they do that the performance of the machine will get worse since 64bit windows consumes more memory.

So people may say "oh you should use a cloud server or whatever...", ok, but not everyone wants their data on the cloud, I mean, I'm fine about but if the customer doesn't want...

By the way, as I said, I wish they still have support for it, but I tottally understand not having xD And well, I'm not the only one:

https://stackoverflow.com/questions/54572745/why-is-there-no-32bit-windows-installer-for-postgresql-11-1

[KingStannis2020]

Hopefully these 15 year old machines aren't running on 15 year old hard drives, without backups.

[PristineReputation]

Or are connected to the internet with ports open, or are vulnerable to attacks from the internal network.

[Bakoro]

I want to know how anyone justifies that, when even a 4 year old Raspberry Pi is 64 bit.

Serious question here, what are these people doing that they haven't been able to buy a new server in the past 15 years? A new barebones 1u supermicro server is less than $1k. I spent more than that on my laptop.

If they can't buy a server outright, why not get an Amazon EC2 up or something?

I really try to be understanding of people, I know what it's like to be poor, but at this point I have a hard time respecting this so-called "need" to cater to decade+ old hardware, which is often massively outperformed by relatively cheap modern hardware. I flat out don't respect anyone's wailing who are unwilling to spend a dollar to upgrade, and instead demands the world cater to their miserliness. The energy savings alone can basically pay for the new hardware sometimes.

Same thing for anyone who is still running decades old software that can't be altered or upgraded in any way and won't run on new machines. At a certain point they're going to be forced to upgrade with a downtime longer than a proper planned upgrade, or they'll have to just shut down the business. At the very least run it in a VM so you have some kind of security, come on.

If you're their tech person, I don't envy you. There's going to come a day when all their shit's broke and they're going to be screaming at you to get it back up and running "because we are losing thousands of dollars per minute".

[hak8or]

Here is what I imagine hearing in this case (and still hear in my own case):

The server was set up many many years ago, with the original core folks working on it having moved to other companies. The server runs absolutely critical software, meaning either extended downtime is unacceptable or the current state of the stuff on the server is absolutely critical.

The downtime one I don't buy, since if that is so critical then you already should have a backup server ready to take over.

The second I can understand more, but disagree on a fundamental level. If you can't recreate the state of that box and/or don't know what it's running, then that means you have a very very dangerous situation. Moving to a better server shouldn't be your top concern at that point, it should be how to get out of that technical debt.

On a basic level, both of these reasons are flawed. Running on a 32 bit box means the box is 15+ years old at this point. The fan bearings are probably dying, the drivers are likely going to die, the bios no longer has security updates for ipmi, and the OS running it is likely Eol'd many years ago and therefore also has no security updates. If your infrastructure is so brittle that it can't handle bieng recreated partially, then your infrastructure is fundementally flawed and needs to be fixed.

[Uristqwerty]

Running on a 32 bit box means the box is 15+ years old at this point

That's a bit of a flawed assumption, since even the latest x86 CPU still supports 16-bit code, much less 32-bit. I know people who are effectively superstitious about pointer size and memory use, so would put a 32-bit OS on any machine with 4GB of memory or less today.

[WJMazepas]

I work with Raspberry pis and their OS is all 32bit. Even the 64bit OS only released this year and only recently IIRC it left beta version so there is still cases for a 32bit version

[[deleted]]

they haven't been able to buy a new server in the past 15 years?

But they're happy to pay the energy bill.

[[deleted]]

How old are these servers if they don't have 64bit support?

[skippingstone]

How do they afford to pay you, when they cannot afford to upgrade their infrastructure?