[kangoo1707]

At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason, dependency reason, boss-likes-MySQL reason...). They are all handicapped by MySQL featureset, and can't update to 8 yet. If they had used Postgres some years ago, they would get:

- JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres)

- Window functions (available in MySQL 8x only, while this has been available since Postgres 9x)

- Materialized views, views that is physical like a table, can be used to store aggregated, pre-calculated data like sum, count...

- Indexing on function expression

- Better query plan explanation

[Macha]

Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing (you see GROUP_CONCAT shenanigans being used instead).

For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.

[colanderman]

Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned to remain that way to serve that purpose. If you can, prefer using views to enhance readability (and testability as a bonus). PostgreSQL views (unlike those in MySQL) do not prevent optimization across them.

[zkomp]

No, CTEs are not planned to remain a barrier, this is already fixed in the next version which is in feature freeze right now.

https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

[jeltz]

My favorite feature of PostgreSQL 12 except perhaps REINDEX CONCURRENTLY, but I am very biased since I was involved in both patches (both were large projects involving many devs and reviewers). It is awesome to finally see both land.

[paulddraper]

This is the best news I've heard all week.

[munk-a]

I have tried to express my joy at this news to my less SQL literate co-workers... that failed so I wanted to let it out here. This is the best news, I am overjoyed!

[colanderman]

Oh wow, that is news to me! A welcome change.

[hans_castorp]

Which is very often a good thing. I have tuned more than one query by moving a sub-query/derived table into a CTE.

What bothers me more, that a CTE prevents parallel execution, but I think that too is fixed with Postgres 12

[evanelias]

> Indexing on function expression

MySQL 5.7 fully supports this. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... and https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda...

> JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres)

Actually MySQL 5.6 doesn't support this, but 5.7 does, quite well: https://dev.mysql.com/doc/refman/5.7/en/json.html

[hans_castorp]

Indexing a generated/computed column is not the same as creating an index on an expression. If you want to support several different expressions you need to create a new column each time.

Additionally, an ALTER TABLE blocks access to the table. Indexes can be created concurrently while other transactions can still read and write the table.

But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.

[evanelias]

> If you want to support several different expressions you need to create a new column each time

Yes and no. Generated columns in MySQL can optionally be "virtual". An indexed virtual column is functionally identical to an index on an expression.

> Additionally, an ALTER TABLE blocks access to the table.

It depends substantially on the specific ALTER and version of MySQL. Many ALTERs do not block access to the table in modern MySQL; some are even instantaneous.

> But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.

What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

[hans_castorp]

> What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

You create a single index, e.g:

create index on the_table using gin(jsonb_column);

And that will support many different types of conditions,

e.g.: check if a specific key/value combination is contained in the JSON:

where jsonb_column @> '{"key": "value"}'

this also works with nested values:

where jsonb_column @> '{"key1" : {"key2": {"key3": 42}}}'

Or you can check if an array below a key contains one or multiple values:

where jsonb_column @> '{"tags": ["one"]}' or where jsonb_column @> '{"tags": ["one", "two"]}'

Or you can check if all keys from a list of keys are present:

where jsonb_column ?& array['key1', 'key2']

All those conditions are covered by just one index.

[evanelias]

Interesting, thanks! Indeed, there isn't an equivalent feature in MySQL yet.

Out of curiosity, how commonly is this used at scale? I'd imagine there are significant trade-offs with write amplification, meaning it would consume a lot of space and make writes slow. (vs making indexes on specific expressions, I mean. That said, you're right -- there are definitely use-cases where making indexes on specific expressions isn't practical or is too inflexible.)

[noir_lord]

I'm stuck on 5.7 because previous dev used the worst sprocs I've seen (no exaggeration) and until I've ripped them all out I daren't move to 8, it was on 5.5 when I started but with much effort I got it tested enough to reasonably confident that 5.7 would work.

It's an excruciating process though.

[hans_castorp]

Actually window functions were introduced in Postgres 8.4