Waiting for PostgreSQL 14 – SEARCH and CYCLE clauses

> Fantastic work to all the postgres committers, cheers all around.

Not to forget the postgres contributors: only a portion of the people who write code for postgres are 'committers'. I do agree that the committers do amazing work and play an essential role in postgres' overall ecosystem, but we should not forget that a not insignificant part of postgres' code, code reviews and bugfixes is provided by non-committers.

The authors of paper "Graph Processing in RDBMSs" surveyed graph features in 2017 among 4 popular systems:

http://sites.computer.org/debull/A17sept/p6.pdf (page 6, Table 1)

They found that only Oracle supported the CYCLE keyword, DB2 SQL Server, and Postgres (version 9 at the time) didn't. SQL Server now has a graph extension so that might have changed. MySQL has introduced support for WITH RECURSIVE in 2017 but it does not support the CYCLE keyword.

I know that Oracle supports the CYCLE and the SEARCH clause in recursive CTEs

What advantages do Oracle databases have over PostgreSQL or even SQLite?

I would not trust Oracle software after reading this:

https://news.ycombinator.com/item?id=18442941

> the cost/time of retrieving a possibly large amount of data for that computation

in this case doing it in application space is really problematic because you can't formulate the next query until you see the result of the first - so you will be iterating over and over with sequential queries in your bread or depth first search - really problematic. So while there are many things where it's neutral technically and more a matter of design aesthetic / principle where things go.... in this case it's solving something that just cannot be done sensibly in application space (at least, without large and complex caching logic ...).

Is there anything wrong with doing all your work in the database, other than the version control/deployment complications?

I’m of the opinion that:

1. If it can be efficiently done in the database, that’s where your business logic actually is.

2. Treating the database as some external foreign thing is generally a bad idea. Your application doesn’t (generally) exist without it.

3. Tools should first-class data stores in general so this distinction is more about federation than network/socket boundaries.

Look at pgsync or pgloader

https://news.ycombinator.com/item?id=22676112

There is also interesting work around using filesystem volume snapshots or clones for database cloning, but that requires infrastructural changes… I think there was a startup around that but I can’t find the name :)

https://www.sedlakovi.org/blog/2019/03/fast-postgres-snapsho...

Yeah i see value but tend to agree it’s catering to an interesting segment that i don’t understand either.

More generally, I get concerned when too much functionality ends up in the db as it’s often the hardest thing to scale, so I’d prefer to spread those cycles out elsewhere. I feel this is likely to be something someone relies on and causes a production db to get pretty warm at a bad time.

If you have a mix of "normal" relational data and some hierarchical data then it absolutely makes sense to stick with a relational database.

And why use a full blown graph database if just want to model (and query) a strictly hierarchical structure? e.g. categories that can contain categories or similar things.

And it's part of the SQL standard, so it makes sense to add it to be more compliant with it.

if i have the data in the DB, why would i bother to load it to memory if i can just query it to get what i want...?

it's a generic DB which now started to provide DFS/BFS - you could argue that B-trees are way more advanced than this.

Replicating state to multiple places can add quite much complexity to application development and operations.

There are a few reasons why doing things like this in the RDBMS is a good thing for many use cases.

First, there is a ton of already built tooling for dealing with an RDBMS. This alone is why I think every project should default to using an RDBMS and only move if a good case can be made.

Second, like it or not, sql is the defacto language for querying the data. After 20+ years of writing software, I've used many languages, but sql (of course it has evolved) has been a constant across RDBMS. I've seen many service layers and front ends change over the years, all while sitting on the same RDBMS. Given the speed at which tech moves, it's pretty amazing when you think about it.

Finally, for things like graphs or json, it's often the case that I only need handle it in addition to my existing relational datastore. In some cases it might make sense to stand up an entire system for the use case (like a solr server for document searching), but many times I just need to store a bit of json or I have one or two graphs that need to be represented. In these cases, standing up an entire separate system adds unneeded complexity.

I use recursive queries like these at work, for hierarchical accounts/groups/locations/equipment/etc. where each level can have permissions that cascade down or not. Loading the hundreds of rows into memory necessary to calculate what any particular user can see is very costly in terms of network time, particularly for deeply nested stuff. Much easier to do the legwork in SQL and return the relatively tiny result set. There are certainly other tools that could handle these relationships more gracefully, but they also come with the questions of code integration time/backups/redundancy/scaling/etc, which postgres already has good answers for

Thing is, if the primary source of truth is in the database, often it's beneficial to do the computation as close to the data as possible, as the bottleneck is likely to be I/O, not computation.

"just load the data into memory" requires the database to provide to the client all the data that might be needed for that algorithm, which is more load on the DB server than scanning the (much smaller) subset of the data that actually gets traversed during the algorithm directly on the database itself and providing just the results, as that requires less I/O. That's the reason why it's often more efficient (even if we care only about the server-side load) to do filtering, aggregation and summarization for tabular data in the DB instead of loading all the data into a client and calculating it there, and the same applies for graph data.

Its all well and good to sit there and say "get a dedicated graph database".

But there are two problems with that :

(a) In many cases, running a graph database will require getting involved with the monstrosity that is managing Java (since that is what most of them are written in). Managing Java is all well and good if you (or your team) have experience. For mere mortals its a nightmare to manage.

(b) In the case of pretty much all graph databases, you only get basic features in the free / open-source version. If you want access to any genuinely useful features, you have to pay for "enterprise" with the associated $$$$$$$$$$.

Meanwhile Postgres gives everyone a solid database, with all the features included out of the box, no pay to play.

So what if you need to - shock horror - actually learn how a database works and write some proper SQL queries. The reason so many people have horror stories about SQL databases is because they let some inept developers loose on their RDBMS who treat a database like a black box dumping ground with zero thought about table design, index design, SQL queries or anything else.

Rant over. ;-)

These iterative queries convert hierarchical (not graph) data into relational data. That means queries can perform aggregations and joins against other relations, which is obviously useful in a relational database.

It's a case of "I want to keep all my data on the same place" because nobody has the attention span to manage a relational database, a document database, an hierarchical database, and a key-value database (did I miss any?). All that while you lose atomicity, because your data is now distributed.

Also, data often changes in form, and you don't want to rewrite everything to get the data from a different place just because its type changed.

Are you aware of https://pgrouting.org/ ?

As it turns out, Postgres is a very effective/efficient graph database, and is used as such quite often.

But to address a more core issue:

> if you only have a database, everything looks like a SQL query

Postgres, here, is just implementing SQL standard features. And SQL is supposed to be the be-all end-all Standard Query Language.

SQL makes no claims about being suited only for querying relational databases. (It uses a lot of relational terminology, but that’s because relational algebra is the strict superset of other querying models, in the same sense that a Turing machine is the strict superset of other automata. If you want a fully-general query language that can be used to query anything, it’s going to need to have relational algebra somewhere in it, at least for the cases where your query is about relations and can’t be expressed in any more compact/abstract way.)

Graph databases could—and probably should!—expose SQL-based query parsers for their querying engine, along with the bevy of other more graph-focused languages they support. (Yes, SQL isn’t as efficient of a syntax for making graph queries than e.g. Datalog, but SQL can still express said queries; and a query parser can turn those SQL-expressed graph queries into the very same query plans it’d build from a Datalog query.)

And, if you want to be able to query a graph database using SQL, you need features like this in SQL. Or rather, to reverse that: given that the SQL committee wants SQL to be “the” standard for querying, they’re going to add features like this to SQL to ensure that it is a viable query language for things like graph databases to expose.

That being said, you can think of this move by PostgreSQL less as “Postgres trying to be a graph database” and more as “Postgres ensuring that, when porting your standard SQL query—which you may have very well written originally to talk to an SQL-speaking graph database—that you don’t have to rewrite the query, but can continue to use standard SQL features.” Think of it like a “compatibility feature” allowing Postgres to “host” this foreign model.

(Which is not to say Postgres is not good at being a graph database. This “compatibility” is first-class! As I mentioned above.)

—————

P.S.: The deeper issue is that nobody other than database maintainers is really aware of what exactly is in the SQL standard; probably because it’s a proprietary document that you have to pay for access to.

On top of that, there’s also no secondary source that lays out what the SQL standard defines, vs. what various DBMSes actually implement. There’s no CanIUse.com for SQL features.

Really, there’s not even anybody out there making blog-posts about newly-standardized not-yet-universally-supported SQL features, the way people make blog-posts about newly-standardized not-yet-universally-supported JS features.

Instead, all most people know about SQL is the particular dialect their DBMS of choice implements; and they only become aware that certain features are in SQL at all, when their DBMS-of-choice implements those features.

It’s all faintly ridiculous, and reeks of the siloed enterprise distribution model of the 1980s. (Remember back before POSIX was a thing, when shell scripts were often written to assume the flavour of Unix the writer was familiar with—usually the one created+maintained by the vendor of their mainframes and workstations? Somehow, despite having the POSIX equivalent—the SQL standard—we’re still stuck in that culture in database land.)

At my company we used Neo4J, that sucked and it was too expensive. So we moved to OrientDB, that also sucked. So we moved the graph database to Postgres like all our other databases, and it works great now.

> Is it just me or is this a severe case of "if you only have a database, everything looks like a SQL query"?

Honestly I’m starting to feel like if it looks like tool flexibility is a hammer, all you see is a tool belt. Databases should be able to do search. If you need to pipe tools (and likely duplicate storage), probably one of your tools is dropping the ball or you’re being reflexive about your tools.

I didn't down vote you, but I suppose that if you are looking for others to respond with why, you could start by explaining why you have that perspective yourself. Else, what is anyone to respond to?