Better JSON in Postgres with PostgreSQL 14 | Hacker News Reader

Better JSON in Postgres with PostgreSQL 14

blog.crunchydata.com

I've been 99% MySQL user, project reasons.. everytime I see a release announcement I'm amazed and have feature envy. This isn't a troll comment, but what does mysql do better than postgres? Is it just syntax-favour. I actually Googleéd and ask colleague 10-15 days ago if postgres had json support for indexing root attributes...

MySQL can handle many more connections per instance than postgres. Often when you have a high-transaction database in prod with postgres, you need something like pgbouncer to handle connection pooling or you'll have a bad time.

I agree though that postgres has some fantastic features.

edit: I think they may have addressed some of this in a recent version? I'm basing my knowledge on postgres 12

Connection scaling in postgres works better with the more recent versions. Still, you might indeed want to implement a connection pool for your PG instance if you have large swathes of mostly idle connections due to the single-backend-per-connection constraint of postgresql.

mysql notably has index-oriented tables / persistently clustered tables. For some workloads, that is a blessing (it saves you from needing another copy of your data in unique join tables for the unique constraint), but for others its a curse (the PK columns are duplicated in all indexes, so multi-column PK + multiple indexes is quite inefficient).

Other than that, MySQL (since 8.0) has no stable feature releases. As in, after 8.0.0 new features were added in 8.0.x-releases, making it difficult to reason about what you can and cannot do in a given database cluster based on the major version number alone. This too is a blessing and a curse: you get new features faster, but you don't get bugfixes for a database engine with stable and well-defined featureset.

>but what does mysql do better than postgres

Not MySQL itself but something like Vitess [1]. I believe MySQL 9.0 should be close.

[1] https://vitess.io

MySQL doesn't need the auto-vacuumer to be tuned, as it doesn't have one. I've had Postgres servers crash due to excessive stale rows...

PostgreSQL is vulnerable to certain workloads. For example, if you keep appending something to a text column, you'll use up a lot of disc space since PG will copy the row each time and keep deleted copies around until autovacuum comes along.

Of course, if you are doing this then you are using your database wrong -- but it's still something that's easy to run into.

PostgreSQL 14 made improvements to this area around indexing:

https://www.postgresql.org/docs/14/btree-implementation.html...

Yes, but that doesn't cover the problem of bloat in the TOAST tables due to excessive churn of large attributes that was mentioned in the parent post.

I always thought it was cool that MySQL has different table engines. Postgres doesn't have this (unless I missed something). Sure, there are products based on Postgres that have this, but vanilla Postgres itself doesn't have this option.

Postgres added pluggable storage a couple years ago, but it takes time to develop new storage engines: https://news.ycombinator.com/item?id=19578562

Anybody know if there any performance/query plan differences between the following?

    WHERE details->'attributes'->>'color' = 'neon yellow'

And

    WHERE details['attributes']['color'] = '"neon yellow"'

> While I wouldn't recommend simply using the subscript format everywhere in your application due to it not always leveraging indexes...

I assumed that the subscript format was basically preprocessed into the arrow format, but this kind of implies that it's not. It's too bad the point isn't more clear here as I'm curious as well.

As someone who’s working on a project that I’m prototyping with just json, this is so exciting for me. I didn’t want to get bogged down with learning a database unless I absolutely had to. If my project takes off I’m glad I have the option of easily migrating to Postgres.

Does anyone know why "col->'blah'->'blah2' = 'value';" ignores the index whereas @> uses it?

"@>" is one of the operators for the GIN index, which is what Postgres uses for the JSONB type.

Ref: https://www.postgresql.org/docs/current/gin-builtin-opclasse...

Just feels like it would be nice to have some syntactic sugar to convert these expressions.

GIN indices in general support the @> operator but not the = operator, unrelated to JSON.

You can create a regular btree index on a particular json path and get an index usable for = operations on that path.

Reply on news.ycombinator.com