5 ms·
Better JSON in Postgres with PostgreSQL 14
- yhoneycomb 5y agoAs 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.
- atonse 5y agoDoes anyone know why "col->'blah'->'blah2' = 'value';" ignores the index whereas @> uses it?
- jkatz05 5y ago"@>" 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-opclasses.html https://www.postgresql.org/docs/current/gin-builtin-opclasse...
- atonse 5y agoJust feels like it would be nice to have some syntactic sugar to convert these expressions.
- bhaney 5y agoGIN 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.
- chrisacky 5y agoI'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...
- etaioinshrdlu 5y agoMySQL 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...
- yarcob 5y agoPostgreSQL 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.
- jkatz05 5y agoPostgreSQL 14 made improvements to this area around indexing: https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION https://www.postgresql.org/docs/14/btree-implementation.html...
- mattashii 5y agoYes, 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.
- nknealk 5y agoMySQL 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
- mattashii 5y agoConnection 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.
- zeroimpl 5y agoAnybody know if there any performance/query plan differences between the following? WHERE details->'attributes'->>'color' = 'neon yellow' And WHERE details['attributes']['color'] = '"neon yellow"'
- rcv 5y ago> 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.