7 ms·

The major news are: - SQL/PGQ - A Graph Query Language - JSON improvements (a JSON type, simplified notations) Peter Eisentraut gives a nice overview here: h

The major news are: - SQL/PGQ - A Graph Query Language - JSON improvements (a JSON type, simplified notations) Peter Eisentraut gives a nice overview here: https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fin...

wslh 3y ago
Basic question: is it correct to assume that having PGQ involves a big change in the database engine?
- zozbot234 3y ago
  It's just a different language and a simple "property" layer over the existing data. No changes to the internals are necessary.
  - WorldMaker 3y ago
    No changes are required by PGQ but some engines may wind up using PGQ as a signal to bring in new types of indexes or disk storage formats to back it. It may be interesting to see if real world usage of PGQ pushes the database engines to do interesting things.
- pphysch 3y ago
  AFAICT the idea is that you are not directly querying the tables as a graph, but you construct a graph "view" from the tables, and then query that graph using PCQ.
  - zubiaur 3y ago
    Correct. The one RD I know that has implemented this "graph view" concept is Oracle's. They did it first with PGQL extensions on top of say 19c. Interesting, but the execution seemed a bit unpolished. Now with 23c, they are adopting PGQ and one should be able to interface through the regular JDBC connectors. Will see how it shapes up.
justinclift 3y ago
As a thought, it might be better to use the https:// https:// link to Peter's overview. :)
- MarkusWinand 3y ago
  Fixed. I wonder why Google sent me to http...
bokchoi 3y ago
Thanks! Lots of little neat improvements in there like accessing JSON values using dots and array syntax: SELECT t.j.foo.bar[2], ... FROM tbl t ...
Zpalmtree 3y ago
I like the DISTINCT / NOT DISTINCT unique NULL option, I was wanting this feature just a few weeks ago
- MarkusWinand 3y ago
  That particular one is already available in PostgreSQL 15. https://modern-sql.com/caniuse/unique-nulls-not-distinct https://modern-sql.com/caniuse/unique-nulls-not-distinct
sverhagen 3y ago
I'm a pretty average SQL user, but I've heard expert consultants say before that they could do many more things with SQL databases that developers like me would have maybe grabbed a different tool for, like a graph database. So this then makes me wonder, once there's even broader adoption through PGQ, is that going to be a killer for niche databases like Neo4j, in favor of, say, Postgres?
- pphysch 3y ago
  "Kill" is a strong word, as Postgres's solid JSON support technically obsoleted MongoDB for most use cases, but Mongo is still around for various reasons. I suspect if Postgres had a solid implementation of SQL/PCQ it would be a similar story for Neo4j.
- derefr 3y ago
  Graph databases are about as different from RDBMSes storage-wise, as column-stores are from row-stores. It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node. Using a graph DB with many underlying KV-store nodes, you can have a single graph spread over many machines representing e.g. Facebook's social graph, and run a query which "chases around" edges between vertices that live on different nodes, to solve that query, while ensuring that as little of that has to happen as possible — both by rebalancing vertices so that data is sharded at low-connection-degree points in the graph; and by consolidating the steps of queries that occur on the same node into single batch queries, such that the whole thing becomes (close to) a single map/reduce step. There's nothing in Postgres that knows how to do that; if you had e.g. a graph stored in a Citus hypertable, and did a recursive CTE over it to do graph search, then you'd get pretty dang bad perf.
  - MarkusWinand 3y ago
    > It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node. A problem everbody would love to have but pretty much nobody actually has.
    - 3y ago
pphysch 3y ago
See also [1] for how this (might) relate to PostgreSQL In particular it is nice to see that a core dev views JSON dot accessing and PCQ as "sensible" future additions to Postgres. [1] - https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023 https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...
thanatos519 3y ago
PGQ looks neat - create a "property graph" from a relational model, then query it via Cypher-like expressions. The best or the worst of both worlds, depending on implementation quality.
ksec 3y ago
I wonder when or even if MySQL will adopt any of these.
dang 3y ago
Discussed here: SQL:2023 is finished: Here is what's new - https://news.ycombinator.com/item?id=35562430 https://news.ycombinator.com/item?id=35562430 - April 2023 (153 comments)
bionhoward 3y ago
Which SQL DBs support these features now? Who is almost there? I’m definitely excited to try it!
mariuz 3y ago
And here is the article on the status of SQL:2023 support in PostgreSQL https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023 https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...

wslh 3y ago

Basic question: is it correct to assume that having PGQ involves a big change in the database engine?

zozbot234 3y ago
It's just a different language and a simple "property" layer over the existing data. No changes to the internals are necessary.
- WorldMaker 3y ago
  No changes are required by PGQ but some engines may wind up using PGQ as a signal to bring in new types of indexes or disk storage formats to back it. It may be interesting to see if real world usage of PGQ pushes the database engines to do interesting things.
pphysch 3y ago
AFAICT the idea is that you are not directly querying the tables as a graph, but you construct a graph "view" from the tables, and then query that graph using PCQ.
- zubiaur 3y ago
  Correct. The one RD I know that has implemented this "graph view" concept is Oracle's. They did it first with PGQL extensions on top of say 19c. Interesting, but the execution seemed a bit unpolished. Now with 23c, they are adopting PGQ and one should be able to interface through the regular JDBC connectors. Will see how it shapes up.

justinclift 3y ago

As a thought, it might be better to use the https:// https:// link to Peter's overview. :)

MarkusWinand 3y ago
Fixed. I wonder why Google sent me to http...

bokchoi 3y ago

Thanks! Lots of little neat improvements in there like accessing JSON values using dots and array syntax: SELECT t.j.foo.bar[2], ... FROM tbl t ...

Zpalmtree 3y ago

I like the DISTINCT / NOT DISTINCT unique NULL option, I was wanting this feature just a few weeks ago

MarkusWinand 3y ago
That particular one is already available in PostgreSQL 15. https://modern-sql.com/caniuse/unique-nulls-not-distinct https://modern-sql.com/caniuse/unique-nulls-not-distinct

sverhagen 3y ago

I'm a pretty average SQL user, but I've heard expert consultants say before that they could do many more things with SQL databases that developers like me would have maybe grabbed a different tool for, like a graph database. So this then makes me wonder, once there's even broader adoption through PGQ, is that going to be a killer for niche databases like Neo4j, in favor of, say, Postgres?

pphysch 3y ago
"Kill" is a strong word, as Postgres's solid JSON support technically obsoleted MongoDB for most use cases, but Mongo is still around for various reasons. I suspect if Postgres had a solid implementation of SQL/PCQ it would be a similar story for Neo4j.
derefr 3y ago
Graph databases are about as different from RDBMSes storage-wise, as column-stores are from row-stores. It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node. Using a graph DB with many underlying KV-store nodes, you can have a single graph spread over many machines representing e.g. Facebook's social graph, and run a query which "chases around" edges between vertices that live on different nodes, to solve that query, while ensuring that as little of that has to happen as possible — both by rebalancing vertices so that data is sharded at low-connection-degree points in the graph; and by consolidating the steps of queries that occur on the same node into single batch queries, such that the whole thing becomes (close to) a single map/reduce step. There's nothing in Postgres that knows how to do that; if you had e.g. a graph stored in a Citus hypertable, and did a recursive CTE over it to do graph search, then you'd get pretty dang bad perf.
- MarkusWinand 3y ago
  > It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node. A problem everbody would love to have but pretty much nobody actually has.
  - 3y ago

pphysch 3y ago

See also [1] for how this (might) relate to PostgreSQL In particular it is nice to see that a core dev views JSON dot accessing and PCQ as "sensible" future additions to Postgres. [1] - https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023 https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...

thanatos519 3y ago

PGQ looks neat - create a "property graph" from a relational model, then query it via Cypher-like expressions. The best or the worst of both worlds, depending on implementation quality.

ksec 3y ago

I wonder when or even if MySQL will adopt any of these.

dang 3y ago

Discussed here: SQL:2023 is finished: Here is what's new - https://news.ycombinator.com/item?id=35562430 https://news.ycombinator.com/item?id=35562430 - April 2023 (153 comments)

bionhoward 3y ago

Which SQL DBs support these features now? Who is almost there? I’m definitely excited to try it!

mariuz 3y ago

And here is the article on the status of SQL:2023 support in PostgreSQL https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023 https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...