[HN Gopher] SQL:2023 has been released
___________________________________________________________________
SQL:2023 has been released
Author : MarkusWinand
Score : 180 points
Date : 2023-06-01 17:05 UTC (5 hours ago)
(HTM) web link (www.iso.org)
(TXT) w3m dump (www.iso.org)
| sdflhasjd wrote:
| Ah, a sequel for SQL
| Alifatisk wrote:
| Good one
| xucheng wrote:
| A related question: what is the state in term of supporting the
| SQL standard among the popular RDBMS? It seems that almost all
| database engines use their own custom syntax.
| MarkusWinand wrote:
| This is one of the questions I try to answer at https://modern-
| sql.com/
| bafe wrote:
| Your website is great and I regularly check it to see what's
| new in various implementations. Unfortunately it seems that
| many databases don't support many modern SQL features yet.
| Any ideas as to why?
| jsmith45 wrote:
| I can say that none of Oracle, Sybase, or Microsoft Sql Server
| really aim at conforming to the standard. While they will often
| try to use standard syntax for new features if such syntax
| exists, there is tons of old non-conforming syntax that there
| seems to be no real effort in addressing, even by adding new
| options, etc. Some of these mean really common features deviate
| significantly from what the standard requires.
|
| PostgreSQL does mostly aim at conforming to the standard. They
| will invent new syntax when needed, But compared to the those
| previously mentioned, Postgres seems to prefer to stick closer
| to the standard whenever possible, including adding standard
| syntax for existing features whenever possible.
|
| PostgreSQL does have some places where there is deliberate non-
| conformance (beyond just incompletely implemented features).
| They document many deliberate deviations (other than
| unimplemented or partially implemented features) and if they
| think they can be fixed in the future or not:
| https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard .
| Looking at the list I'd say only one especially likely to bite
| a developer is the default escape character for LIKE clauses,
| or the non-standard trailing space behavior for character(n)
| datatypes (but who used fixed length character datatypes
| instead of varchar or text?). And obviously not yet implemented
| features could bite people, but many such features are optional
| to implement anyway, so...
|
| I cannot speak about MySQL or MariaDB, due to insufficient
| familiarity.
| cpdean wrote:
| I like SQL and all but I really don't care to follow ISO
| releases. They're hundreds of dollars and nobody actually
| implements the whole thing. I get way more excited about database
| releases.
|
| Does anyone else find value in what's in an ISO standard?
| lolinder wrote:
| > They're hundreds of dollars
|
| This isn't SQL-specific, but this is 100% the problem for me.
| There's such a big culture gap between the way that we do
| things in most of the tech world and ISO, and one of the
| biggest clashes is this weird $180 PDF thing.
|
| If I want to implement a new standards-compliant HTML parser, I
| can hop right onto whatwg.org and view the complete standard
| instantly [0]. It's massive and complicated, but it's freely
| accessible to anyone interested.
|
| In contrast, if I want to implement an ISO 8601-compliant date
| parser, ISO wants me to buy their PDF for CHF166 (~$180 USD).
| This spec is for a standard that is orders of magnitude less
| complex, and they're charging through the nose for it.
|
| I'm unclear what makes the difference between a standard that
| can be maintained by a community for the benefit of everyone
| and a standard that needs to be locked behind a paywall.
|
| [0] https://html.spec.whatwg.org/
| justinclift wrote:
| Ugh, it's CHF 208.00 (about US$230.00).
|
| ---
|
| As @rgbgraph points out below, the price is actually several
| times that. There are several parts to the standard, and that
| US$230 is _per part_.
| munk-a wrote:
| I have always despised them for putting a paywall in front of
| the SQL standard - it's been there since at least 2010 and
| probably quite a bit earlier. The good news is that nobody
| actually needs to read it since you need DB specific
| information anyways... but it'd be nice to have access to it
| (especially when I was working on RDBMS neutral dialect to
| support multiple backends). Still, if you ever have a question
| about documentation postgres[1] does an absolutely amazing job
| detailing everything in its dialect and their dialect tends to
| be the most "neutral" imo.
|
| 1. https://www.postgresql.org/docs/current/index.html
| nologic01 wrote:
| they say the Standard contributes to SDG 9...
|
| "Inclusive and sustainable industrialization, together with
| innovation and infrastructure, can unleash dynamic and
| competitive economic forces that generate employment and
| income"
|
| Not clear how an arbitrary levy as this can contribute to an
| SDG goal. It is the definition of exclusion. A bright new thing
| can use the $230 to get several good database books.
|
| Surely ISO has enough corporate stakeholders that can defray
| the cost of running the standardization process.
| MarkusWinand wrote:
| Luckily pretty much nobody needs the standard documents. It's
| actually my aim at https://modern-sql.com/ to make the relevant
| information more accessible -- in particular including support-
| matrices ("Can I Use").
| sosodev wrote:
| I've certainly needed them before :(
| fhoffa wrote:
| I love this, thanks!
|
| How we could we add Snowflake to the compatibility matrices?
|
| It's popular enough to deserve it, IMHO.
|
| https://db-engines.com/en/ranking
| rekabis wrote:
| As a web developer: your site is clean and crisp, with an
| almost brutal simplicity which makes it very attractive.
| Honestly, I like it a lot. It could use one or two very
| subtle tweaks in the super-fine details, but for a technical
| information-dissemination site, it's bloody awesome.
|
| My only issue is with the width. While whitespace between the
| sides and the centre content is very useful, this isn't the
| 1990s anymore with its 1024x768 monitors. You _can_ make the
| centre column responsive to the overall width of the screen.
| Doing so can also give you a lot more room to do things, and
| make it easier to read. Your line-height is already great,
| and is perfect for text blocks a good half again wider if not
| twice as wide. Even on my vertical monitors, which are only
| 1500px wide, that centre column is pretty much
| claustrophobically narrow.
| MarkusWinand wrote:
| The rationale behind the width is more the ease of reading
| than anything else:
| https://en.wikipedia.org/wiki/Line_length
|
| But thanks for the feedback. There is a rework pending
| anyway.
| munk-a wrote:
| One piece of good news is that you just need to drop #page-
| wrapper's width (or adjust to preference) the rest of the
| CSS all appears to be sensibly responsive.
| patrec wrote:
| Just in case anyone who isn't familiar with Markus's work
| gets a slightly w3schools vibe when clicking the above link:
| his book SQL Performance explained[1] is probably the single
| most useful book a backend developer can read.
|
| [1] https://sql-performance-explained.com/
| LispSporks22 wrote:
| Cool nice site man. I bought the book a while back and really
| enjoy the articles.
| chrismorgan wrote:
| I've never had access to the SQL standard, but in things like
| HTML and CSS I know I reach for the specs _extremely_
| regularly, and that when working with SQL (mostly PostgreSQL
| or SQLite, including sometimes having to support both) I get
| frustrated at the general poor state of SQL documentation
| (and certainly a lot of this is because of engine diversity
| and), and have often wished I had access to the SQL Standard
| (even though I know engine diversity significantly lowers its
| value for users of particular databases--my ideal would
| probably be a version of the spec augmented with engine
| support and links to each engine's documentation of the
| matter). Certainly the table of contents for this spec sounds
| delightful.
|
| Not all specs are particularly accessible (e.g. ECMAScript is
| often hard to follow if you haven't spent a fair bit of time
| around it--it's mostly natural-language code that works with
| a ECMAScript virtual machine), but most of the time, I would
| _much_ rather have the actual spec over someone's digested
| summary that covers what _they_ thought was important, but
| regularly lacks details important for my situation. Some
| specs are absolutely magnificent. The HTML Standard is my
| very favourite as both a piece of spec work and as reference
| material.
|
| Seriously, specs are _really_ good stuff and it makes me sad
| how people often ignore them because they assume they'll be
| inscrutable. (Similar remarks apply to legislative texts.
| They're normally pretty easy to understand, and you find all
| kinds of sometimes fun and sometimes useful gems that potted
| summaries exclude.)
| paulddraper wrote:
| The good news (not really) is that the SQL standard isn't
| really followed.
|
| So unless you are writing a database yourself, the DMBS
| documentation is going to be more relevant.
| MarkusWinand wrote:
| I'm studying the SQL standard for years now and compared to
| other standards that I know (XSLT, a little CSS, decades
| ago POSIX, C and C++) the SQL standard is really hard to
| make sense of. You might overestimate the value of having
| access to it.
|
| Having that said: free would be better.
| nurettin wrote:
| I have not read any of 9075, but 14882 is surprisingly
| comprehensive, and wg21 regularly publishes prerelease
| versions for free.
| momirlan wrote:
| agreeing here Markus, for all practical purposes all one
| needs to know are the features supported by a particular
| product, not the possible features. big fan of your site
| !
| rgbgraph wrote:
| For one part. IIRC, there's 11 "new" parts this year.
|
| Which is even more hilarious, considering the ISO is already
| being funded by the tax dollars of member countries.
|
| A bit like having to pay a journal to get access to research
| papers: ridiculous.
| justinclift wrote:
| Oh, wow. Yeah, that's even worse.
|
| Trying to throw together a list of them all, I'm only finding
| 5 though:
|
| * https://www.iso.org/standard/76583.html
|
| * https://www.iso.org/standard/76584.html
|
| * https://www.iso.org/standard/76585.html
|
| * https://www.iso.org/standard/76586.html
|
| * https://www.iso.org/standard/76587.html
|
| Any idea about the others?
| rgbgraph wrote:
| * Part 1: https://www.iso.org/standard/76583.html
|
| * Part 2: https://www.iso.org/standard/76584.html
|
| * Part 3: https://www.iso.org/standard/84803.html
|
| * Part 4: https://www.iso.org/standard/76585.html
|
| * Part 9: https://www.iso.org/standard/84804.html
|
| * Part 10: https://www.iso.org/standard/84805.html
|
| * Part 11: https://www.iso.org/standard/76586.html
|
| * Part 13: https://www.iso.org/standard/84806.html
|
| * Part 14: https://www.iso.org/standard/76587.html
|
| * Part 15: https://www.iso.org/standard/84807.html
|
| * Part 16: https://www.iso.org/standard/79473.html
| justinclift wrote:
| Thanks. The pricing for this stuff seems to be truly
| crazy.
|
| Wonder if Elsevier is involved somewhere? It's how they
| do things too.
| gigatexal wrote:
| It really is utter bullshit that we have to buy these standards.
| What are the business models of these standards bodies anyway?
| jacobsenscott wrote:
| Selling the standards documents.
| awestroke wrote:
| If only they could start allowing queries to begin with "FROM
| tbl". It would allow for for much more helpful autocomplete.
| Also, DELETE or SELECT should really be on the very last line of
| the query. Seems like these changes could be done without losing
| backwards compat
| jchw wrote:
| One thing that has always agitated me about SQL is that although
| it's standardized, and the standard seems to encompass a shit-
| ton, in practice a lot of SQL engines don't really seem to have
| any meaningful interoperability for practical uses among the
| world's most popular database engines.
|
| For example, OK, I realize auto-incrementing IDs are not the most
| important thing in the world, and arguably not even a good
| approach in many cases. But sometimes you want them, and
| helpfully almost every database engine I know of has some kind of
| support for this, even if the semantics may differ. It's a super
| basic thing to want a unique ID that roughly counts upward on a
| table. You might have specific needs about re-using numbers and
| whatnot, but the general idea is very simple.
|
| However: in practice, there is not an excellent way to do it that
| I can see. The closest thing I could find is `GENERATED BY
| DEFAULT AS IDENTITY` which, well, works. However, none of
| SQLite3, MSSQL, nor MariaDB support this to my knowledge.
|
| This is relentlessly annoying.
|
| Is it the standards fault, or the implementations? I honestly
| can't say. However, I definitely find this annoying, since I was
| really hoping that by this time, we'd at least have a nice clean
| subset of standard SQL you could count on anywhere, for popular
| database engines. Unfortunately, it's not quite there yet,
| necessitating ugly hacks to this day.
|
| I assume this new standard doesn't really change anything on this
| regard, since it's a desync with implementations that is a
| problem, and it does not seem the standards committee really
| cares too much about this kind of thing. (I could be wrong,
| though, as I am saying this based on feel and not evidence.)
| dmux wrote:
| SQLite does support auto-incrementing:
| https://www.sqlite.org/autoinc.html. Is your gripe with the
| lack of "GENERATED BY DEFAULT AS IDENTITY" syntax specifically?
| jchw wrote:
| Yes, sorry. My gripe is that we don't have a single general
| way to say "I don't really care about the specifics, I just
| want a number that goes roughly upward" for all database
| engines. SQLite is interesting in that it supports at least
| two distinct sets of semantics for auto-incrementing rows,
| but neither of them are the standard syntax...
| richardwhiuk wrote:
| The gripe is that there's not a standard say of defining this
| in SQL.
|
| SQLite SQL != MySQL SQL != ISO SQL
| mdaniel wrote:
| > Is it the standards fault, or the implementations?
|
| My mental model is that it's a mixture, but my life experience
| has been that a "standard" without a test harness or (at bare
| minimum) a reference implementation is just a bunch of navel
| gazing. For SQL specifically, that problem is even worse given
| the number of existing engines that move faster than the
| specification, so in the absence of leadership they just make
| stuff up
|
| Natural language is also a _catastrophically horrible_
| specification mechanism, since your black /blue is my
| white/gold
| setr wrote:
| The way I see it is the fundamental problem is that SQL is not
| a proper composable language, and the standard never defines
| such a thing. So every feature added to an RDBMS is done so as
| an _extension to the language_ , rather than an update to a
| standard library like any sane modern programming language. SQL
| as a language still operates with the mentality of COBOL -- if
| anything reusable is going to be provided, it will be provided
| by the RDBMS manufacturer. The user is only expected to produce
| highly business-specific logic for their own needs. (RDBMS's do
| often offer standard PL language support, but these typically
| hook into database internals, are intended for highly specific
| scenarios, cannot be transferred to any other RDBMS, and are
| generally meant for writing stored procs -- non-reusable
| functions)
|
| As a result, for databases to compete on features, they must
| arbitrarily extend the SQL language standard; these
| modifications to the language then get backfilled into the
| standard, and runs headfirst into backwards compatibility, and
| suddenly no one agrees on really what the feature should
| precisely be so they it becomes an optional part of the
| standard, which really just means that it isn't standardized.
|
| In any sane language, you wouldn't need different databases to
| add specific support for GENERATED BY DEFAULT AS IDENTITY, and
| especially not for 15 different syntax's used in 15 different
| databases to specify kinda sorta not really the same thing --
| it would simply be a function, one you could write yourself, or
| provided by the standard library. It wouldn't be up to the
| RDBMS to offer support beyond _actual_ language features -- it
| 'd just be up to you to update your libraries.
| la_fayette wrote:
| PGQs match syntax seems interesting and reminds me to writing
| sparql. I wonder if any RDBMS will support this?
| bafe wrote:
| All great features, but unfortunately most SQL DBs still miss the
| implementation of features from SQL:2016 like MATCH_RECOGNIZE. I
| wonder what's the purpose of an ever growing standard when most
| implementations only support a small subset of it, and often with
| nonstandard syntax and semantics
| aerzen wrote:
| Where would one find a pirated mirror of this standard? Or the
| 2019 one?
|
| Asking for a friend, of course.
| MarkusWinand wrote:
| 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-fin...
| dang wrote:
| Discussed here:
|
| _SQL:2023 is finished: Here is what 's new_ -
| https://news.ycombinator.com/item?id=35562430 - April 2023 (153
| comments)
| sverhagen wrote:
| 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?
| derefr wrote:
| 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.
| zozbot234 wrote:
| > There's nothing in Postgres that knows how to do that
|
| Postgres uses foreign data wrappers and table partitions to
| achieve sharding of relational databases over many nodes.
| It's certainly possible to make the FDW layer smarter wrt.
| being able to distribute certain queries to the shards,
| including some recursive queries that happen to be of
| practical interest.
| WorldMaker wrote:
| The PGQ part of SQL is designed in such a way that an RDBMS
| can certainly optimize its storage of graph tables
| (eventually). ("Graph tables" used by PGQ have DDL setup,
| per the Eisentraut overview: CREATE PROPERTY GRAPH
| graphname...) If Postgres embraces PGQ it would be easy to
| imagine they would also adopt some graph database style
| storage to back its indexing and query planners.
| dathinab wrote:
| if you are Facebook scale, yes. But then you anyway will
| use a dedicated graph database.
|
| But a lot of graph databases have sizes where they are
| fully replicated on each node and some small internal
| optimizations for the graph are good enough. Given the
| design of the graph query language (and what you can query,
| hint: not everything arbitrarily) there is no reason
| postgres can't gain some additional optimizations to do
| that effectively as long as it's not facebook scale.
|
| SQL now having syntax for querying graph means Graph
| databases can support SQL, at least a subset of it, in a
| standardized way.
| MarkusWinand wrote:
| > 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.
| jupp0r wrote:
| > A problem everbody would love to have
|
| Except the people who do have it and need to keep their
| business running off of one postgres instance.
| throwaway894345 wrote:
| You can have data that fits on one machine and still run
| multiple instances of postgres in a failover
| configuration, which will probably cover just about
| everyone (depending on your filesystem, disk for a single
| instance is essentially infinite, so I'm not actually
| sure what bottleneck would motivate you need to scale
| beyond this configuration).
| threeseed wrote:
| There is a reason the world moved on from failover
| architectures.
|
| a) At some point you will have more data or more users
| than one instance can handle. And instead of simply
| adding another node you need to throttle usage in order
| to do a rolling upgrade. Which is far easier said than
| done and involves impact to the business.
|
| b) With distributed databases you are constantly testing
| that everything works in Dev, Test etc environments. With
| failover you really only test it every now and again
| usually before you deploy to Production. And in most
| companies which are hopeless the testing will be
| guaranteed to be inadequate.
|
| c) Vendors lie. They promise that failover will just work
| but in my experience it very often doesn't. Which is
| another reason why b) is such important to validate their
| claims.
| throwaway894345 wrote:
| I mean, I don't _like_ that Postgres is not infinitely
| scalable, but the whole point is that (a) is not
| generally true--most companies could probably get by with
| a single machine 's worth of data, or rather if they have
| more than one machine's worth of data, those systems
| probably aren't talking to each other such that they need
| to be on the same box. Regarding (b) and (c), do you not
| need to test failure conditions for distributed databases
| (this isn't rhetorical, I've only ever used cloud
| providers offerings)?
| threeseed wrote:
| I consult for a lot of companies and I never heard of or
| seen a database that wasn't horizontally scaled.
|
| It's not for scalability reasons it's for high-
| availability.
|
| Which as cloud adoption has increased and server uptime
| has decreased is even more important.
| jahewson wrote:
| Why shard when you can just replicate?
| lmm wrote:
| Because replica failover is rarely seamless (and often
| doesn't actually work at all, IME).
| mikepurvis wrote:
| Instinctively that's surprising... replica failover
| should be far simpler technically, shouldn't it?
| Spooky23 wrote:
| Some of these arguments and "common knowledge" things are
| getting old. Everybody scaled up twenty year ago - hell
| Amazon used to brag that they used an HP Superdome or
| whatever.
|
| Anyone with dogmatic opinions about this stuff need to be
| taken with a grain of salt. If you scale out PeopleSoft,
| your accounting system will exceed the value of your
| company. If you're worried about webscaling your random
| app, that's more wasting time navel gazing than
| accomplishing anything! :)
| czx4f4bd wrote:
| I wonder if there's been any observable correlation between
| JSON support in the major SQL databases and the decreased (or
| increased?) adoption of NoSQL document databases like
| MongoDB. It would be interesting to do some bulk analysis on
| GitHub commits to compare their use over time.
| hn_throwaway_99 wrote:
| Just one bit of personal experience, but for me it was a
| significant reason. In most cases you want objects to have
| highly structured data (e.g. for joins and queries) and in
| other cases you just want "a bunch of semi-structured
| stuff". Sure, DBs always had blobs and text, but JSON is
| really what you want a lot of the time.
|
| There's also a good article by Martin Fowler about how
| "NoSQL" was really "NoDBA" for a lot of folks, and I
| _definitely_ saw that dynamic. JSON fields can also be a
| good middle ground here, where a DBA can insure good
| "structural integrity" of your schema, but you don't need
| to go through the hassle of adding a new column and schema
| update if you're just adding some "trivial" bit of data.
| tracker1 wrote:
| The canonical example for me, is when you want to
| store/use additional payment processor details for a
| transaction... If it's direct CC, PayPal, Amazon Payments
| etc. Relationally you only really care that the amount of
| the transaction was sent/received/accepted. But you may
| want to store the additional details, without a series of
| specific tables per payment processor. If you need to see
| the extra details that can still be done at runtime.
|
| Another good example is for generalized classified ads,
| different categories may have additional details, but you
| don't necessarily want to create the plethora of tables
| to store said additional details.
| throwaway894345 wrote:
| Honestly, I pretty much always want structure. The
| reasons I've opted for NoSQL are almost always that cloud
| providers offer it for practically free while managed SQL
| databases are wayyyy more expensive. The nice thing about
| JSON is that it's a lot more ergonomic, but not because
| of the lack of typing--I would absolutely use a database
| that let my write reasonable type constraints for JSON
| columns. (I realize that you're talking about why _most
| people_ use NoSQL and I 'm remarking about why _I_ use
| NoSQL).
|
| Some other controversial thoughts: SQL itself is a really
| not-ergonomical query language, and also the lack of any
| decent Rust-like enum typing is really unfortunate. I
| know lots of people think that databases aren't for
| typing, but (1) clearly SQL aspires toward that but gives
| up half way and (2) that's a shame because they have a
| lot of potential in that capacity. Also while you can
| sort of hack together something like sum types / Rust
| enums, it's a lot of work to do it reasonably well and
| even then there are gaps.
| munk-a wrote:
| Every ecosystem I've ever worked in has had good tooling
| for managing DB migrations (and in some cases I've been
| the one to add it). It's trivial to write a migration to
| ALTER TABLE bar ADD COLUMN foo and I think keeping
| structure explicit is generally quite beneficial for data
| safety even if you're not doing fancy things. DBAs are
| great but most companies simply don't need one - you can
| just get by with some pretty rudimentary SQL and skill up
| as needed.
|
| Assuming you've got good integration test coverage of the
| database schema alterations end up taking a minuscule
| amount of time and if you lack test coverage than please
| reconsider and add more tests.
| tracker1 wrote:
| I think when you really need/want a DBA is when you're at
| a point where either you need redundancy/scale or have to
| remain up. Most developers aren't going to become that
| familiar with the details of maintenance and scale for
| any number of different database platforms. I think MS-
| SQL does better than most at enabling the developer+dba
| role, but even then there's a lot of relatively
| specialized knowledge. More so with the likes of Oracle
| or DB2.
| hn_throwaway_99 wrote:
| Completely disagree. The issue is not about really about
| how hard or easy it is to run migrations (every project
| I've worked on has also used migration files), it's that,
| depending on the data, it can just be a total waste of
| time.
|
| Sibling comment, "is when you want to store/use
| additional payment processor details for a transaction",
| is a great example IMO. I've dealt with card processing
| systems where the card transaction data can be reams of
| JSON. Now, to be clear, there _are_ a lot of subfields
| here that are important that I do pull out as columns,
| but a lot of them are just extra custom metadata specific
| to the card network. When I 'm syncing data from another
| API, it's awesome that I can just dump the whole JSON
| blob in a single field, and then pull out the columns
| that I need. Even more importantly, by sticking the API
| object blob in a single field, unchanged, it guarantees
| that I have the full set of data from the API. If I only
| had individual columns, I'd be losing that audit trail of
| the API results, and if, for example, the processor added
| some fields later, I wouldn't be able to store them
| without updating my DB, too.
|
| Before JSON columns were really standard, saw lots of
| cases where people would pull down external APIs into
| something like mongo, then sync that to a relational DB.
| Tons of overhead for a _worse_ solution where instead I
| can just keep the source JSON blob right next to my
| structured data in postgres.
| sverhagen wrote:
| You would have to tell the decreased adoption of NoSQL due
| to JSON support in major SQL databases apart then from the
| decreased adoption of NoSQL due to the hype being over...
| threeseed wrote:
| MongoDB remains the 5th most popular database: https://db-
| engines.com/en/ranking
|
| And there are four major reasons still to choose MongoDB
| over something like PostgreSQL.
|
| a) PostgreSQL has terrible support for horizontal
| scalability. Nothing is built-in, proven or supported.
|
| b) MongoDB has superior ability to manipulate and query the
| JSON.
|
| c) MongoDB is significantly faster for document-attribute
| updates.
|
| d) MongoDB has better tooling for those of us that prefer
| to manage our schema in the application layer.
| paulddraper wrote:
| By the time you need to shard PostgreSQL (billions of
| records?), you have lots and lots of resources to
| overcome that difficulty, a la Notion.
| threeseed wrote:
| You horizontally scale for high availability as well as
| scalability.
|
| And primary-secondary failover in my experience is rarely
| without issues.
|
| There is a reason almost every new database aims to be
| distributed from the beginning.
| lmm wrote:
| If you want to be high-availability then you need
| sharding or something like it from day 1. There's still
| no first-class way of running PostgreSQL that doesn't
| give you at least a noticeable write outage from a
| single-machine failure.
| pphysch wrote:
| "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.
| quantified wrote:
| A big problem (IMHO) with graph databases is building "the"
| graph model, and the fact that it's easy to be faced with
| problems that don't suit a graph database. Something as
| simple as returning the distinct set of values for an
| attribute and count of vertices containing each value require
| going outside the graph model, so aren't composable very well
| in a property graph system. (There are other graphs besides
| property graphs, they will have their time someday.)
|
| What you really want is to apply graph processing to data as
| it is. The SQL 2023 additions are a step in the right
| direction. I need to find a good detailed description of the
| constraints and semantics to assess how good it is.
| bionhoward wrote:
| Which SQL DBs support these features now? Who is almost there?
| I'm definitely excited to try it!
| wslh wrote:
| Basic question: is it correct to assume that having PGQ
| involves a big change in the database engine?
| zozbot234 wrote:
| It's just a different language and a simple "property" layer
| over the existing data. No changes to the internals are
| necessary.
| WorldMaker wrote:
| 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 wrote:
| 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 wrote:
| 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.
| pphysch wrote:
| 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-...
| justinclift wrote:
| As a thought, it might be better to use the https:// link to
| Peter's overview. :)
| MarkusWinand wrote:
| Fixed. I wonder why Google sent me to http...
| bokchoi wrote:
| 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 ...
| ksec wrote:
| I wonder when or even if MySQL will adopt any of these.
| thanatos519 wrote:
| 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.
| Zpalmtree wrote:
| I like the DISTINCT / NOT DISTINCT unique NULL option, I was
| wanting this feature just a few weeks ago
| MarkusWinand wrote:
| That particular one is already available in PostgreSQL 15.
|
| https://modern-sql.com/caniuse/unique-nulls-not-distinct
| gatvol wrote:
| Not a standard if access requires payment.
| tofflos wrote:
| Seems you can play with SQL/PGQ at
| https://blogs.oracle.com/database/post/get-started-with-prop....
| nologic01 wrote:
| Is Oracle's PGQL (e.g. 2.0) more or less the same as SQL/PGQ?
|
| It might be interesting to have a comparison of where major
| databases stand (or plan to be) with respect to SQL/PGQ
___________________________________________________________________
(page generated 2023-06-01 23:00 UTC)