Recursive Postgres Queries(blog.timothyandrew.net)
blog.timothyandrew.net
Recursive Postgres Queries
http://blog.timothyandrew.net/blog/2013/06/24/recursive-postgres-queries/
7 comments
How do you handle ordering of list elements? (say I have a "position" column that subcategories need to be sorted by)
also: Nested Sets http://en.wikipedia.org/wiki/Nested_set_model
Saved me a couple of times.
Saved me a couple of times.
I've dealt with recursive queries many many times. For legacy production systems I stick to Oracle's CONNECT BY statement and half joins to keep performance okay. Most of the tables I deal with typically have 80 million rows with a fanout of 1:2.5 and a average depth of ~3.
A better solution is to create a temporary table to insert results in as you go if you can't afford extra DB results and perform additional inserts to that table in order to effectively take advantage of shared memory on the server side. The second better result is to effectively flatten out the rows as a closure table.
A better solution is to create a temporary table to insert results in as you go if you can't afford extra DB results and perform additional inserts to that table in order to effectively take advantage of shared memory on the server side. The second better result is to effectively flatten out the rows as a closure table.
I don't know Ruby, but does Benchmark.ms clear buffers and flush cache? If no, the second query has a large advantage, sometimes you can get similar performance improvements without changing query...
Good point! I've flipped the queries around.
The difference is lesser, but still fairly significant (30x).
Thanks for pointing this out.
The difference is lesser, but still fairly significant (30x).
Thanks for pointing this out.
This is absurd. Is all this just to eliminate the n+1 queries problem? There are already solutions for this in any ORM.
When loading a survey into an ActiveRecord object, why not just preload the associations? You can then iterate through the children and subchildren of survey in Ruby. Either Survey.includes(categories: { subcategories: questions }).find(1) (one query) or Survey.preload(categories: { subcategories: questions }).find(1) (four queries) would do the trick.
No need for convoluted Postgres-only queries.
When loading a survey into an ActiveRecord object, why not just preload the associations? You can then iterate through the children and subchildren of survey in Ruby. Either Survey.includes(categories: { subcategories: questions }).find(1) (one query) or Survey.preload(categories: { subcategories: questions }).find(1) (four queries) would do the trick.
No need for convoluted Postgres-only queries.
This is a somewhat bad example of when to use recursive queries in PostgreSQL, but they're useful a lot of places Activerecord's preload can't hack it and trees. Examples:
* Recursive queries can update, insert, or delete rows. This is a huge win over doing it in Activerecord with large data sets. I've done updates and inserts in under an hour that testing with Activerecord indicated would quite literally take days. It's not really black magic, but you have to actually learn SQL instead of simple selects to use this properly.
* Pg's query optimizer with a recursive query & internal data handling is much higher performance than anything Activerecord can portably produce (or produce at all) even if you're lucky enough not to have to retrieve most of your results. Preloading is fine for small data sets, but by the time you hit a few hundred thousand rows you're going to start feeling it. This is one of those places where SQL as set handling and Activerecord as ORM diverge more than just conceptually.
* Pulling up rows more than a few step away from the parents (especially through other tables) tends to move a lot of unnecessary data over the network with preloading and can produce ridiculous joins.
* "WITH RECURSIVE" can do things Activerecord can't without multiple costly queries round-tripping their results to the client, getting processed there, and fed back into more queries. http://www.postgresql.org/docs/current/static/queries-with.h...
* Recursive queries can update, insert, or delete rows. This is a huge win over doing it in Activerecord with large data sets. I've done updates and inserts in under an hour that testing with Activerecord indicated would quite literally take days. It's not really black magic, but you have to actually learn SQL instead of simple selects to use this properly.
* Pg's query optimizer with a recursive query & internal data handling is much higher performance than anything Activerecord can portably produce (or produce at all) even if you're lucky enough not to have to retrieve most of your results. Preloading is fine for small data sets, but by the time you hit a few hundred thousand rows you're going to start feeling it. This is one of those places where SQL as set handling and Activerecord as ORM diverge more than just conceptually.
* Pulling up rows more than a few step away from the parents (especially through other tables) tends to move a lot of unnecessary data over the network with preloading and can produce ridiculous joins.
* "WITH RECURSIVE" can do things Activerecord can't without multiple costly queries round-tripping their results to the client, getting processed there, and fed back into more queries. http://www.postgresql.org/docs/current/static/queries-with.h...
Super cool, but it seems like this is throwing technology at a problem that could be solved in a much simpler way.
Since you will rarely (if ever?) be looking up a single question in a survey, storing the whole set as a JSON array-of-arrays makes much more sense. Then you can look up the set of questions with a dead-simple SELECT.
Since you will rarely (if ever?) be looking up a single question in a survey, storing the whole set as a JSON array-of-arrays makes much more sense. Then you can look up the set of questions with a dead-simple SELECT.
I'm assuming you'd want to link an answer to a particular question. And have referential integrity.
JSON is great if you don't need foreign keys to catch integrity problems.
JSON is great if you don't need foreign keys to catch integrity problems.
I agree that recursive queries overcomplicate things greatly. Store the whole thing as a semi-typed JSON document, consider adding a thin caching layer to avoid re-parsing it on every hit (perhaps with some kind of sticky load balancing if you are running multiple processes -- I don't know how the app setup he uses work).
Plus, depending on who your users, the amount of options you'll want your questions/answers to have will steadily increase, making it more suitable to a semi-typed JSON tree (I say that based on a decade's commercial experience in this field -- and at the very beginning I was doing it via Perl CGI scripts).
Plus, if you really really wanted, with PG 9.3+ you an actually do queries within the JSON.
One tip: make sure everything has a system-generated unique ID.
Plus, depending on who your users, the amount of options you'll want your questions/answers to have will steadily increase, making it more suitable to a semi-typed JSON tree (I say that based on a decade's commercial experience in this field -- and at the very beginning I was doing it via Perl CGI scripts).
Plus, if you really really wanted, with PG 9.3+ you an actually do queries within the JSON.
One tip: make sure everything has a system-generated unique ID.
I'm not familiar with JSON in Postgres, but thanks for the tip. Definitely something we can look into.
This is basically a tree like data structure.
Did they look at http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html and see if it can be adapted to their problem?
Did they look at http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html and see if it can be adapted to their problem?
nested sets are not generally faster. for anything which is not "get all direct and indirect children", they are slower than common table expressions (recursive queries). in databases supporting cte's, that is.
Please, please, never do this.
Programming like this in SQL is a maintainability nightmare. Can you even imagine how difficult it would be to modify this to add in new features? I've worked on projects where people have done 'clever' tricks like this, and it always ends in terrible awful terrible. The SQL itself is also non-portable which makes it even worse.
Follow the advice of Justin in the comments there and rather denormalize - which basically means caching the results of deep tree queries at the top level where they can be easily and simply understood.
EDIT: If you don't like the idea of denormalizing, another simple alternative is to create a separate lookup table/model which you can use to easily map back the items to the correct parent.
Programming like this in SQL is a maintainability nightmare. Can you even imagine how difficult it would be to modify this to add in new features? I've worked on projects where people have done 'clever' tricks like this, and it always ends in terrible awful terrible. The SQL itself is also non-portable which makes it even worse.
Follow the advice of Justin in the comments there and rather denormalize - which basically means caching the results of deep tree queries at the top level where they can be easily and simply understood.
EDIT: If you don't like the idea of denormalizing, another simple alternative is to create a separate lookup table/model which you can use to easily map back the items to the correct parent.
I make heavy use of SQL in my daily work. A lot of our business logic lives in the database as views, triggers and various functions.
I actually find that taking this logic away from the app code and into the database is one of the best architectural decisions we took.
I think the primary issue some people have with doing that is that they don't know working with databases well enough, and consider them a dumb place to store data, and nothing more. This is what makes it difficult to maintain.
One thing that I do agree with is that the SQL code isn't portable - but its the same as your Ruby/PHP/NodeJs/whatever code not being portable. I don't see that as an issue.
I actually find that taking this logic away from the app code and into the database is one of the best architectural decisions we took.
I think the primary issue some people have with doing that is that they don't know working with databases well enough, and consider them a dumb place to store data, and nothing more. This is what makes it difficult to maintain.
One thing that I do agree with is that the SQL code isn't portable - but its the same as your Ruby/PHP/NodeJs/whatever code not being portable. I don't see that as an issue.
From a software engineering perspective, this is entirely backwards.
Databases offer a great way of looking at your data.
However they have very poor stories around version control, rollout, rollback, separating metadata from data, and creating multiple parallel environments for production, qa, and every dev engineer. Yes, I know that database vendors claim otherwise, but compared to what is standard for most programming environments these days, it is a major limitation.
But it doesn't stop there. If you are lucky enough to get a busy site, you will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel. The more work you push down to that, the sooner you'll hit that bottleneck, and the harder it is to fix once you do. When you've experienced, as I have, the joys of periodic outages because the database choked at a million dynamic pages/day, you'll value moving logic out of the database, the ability to put caching in front of it, etc.
Now to all of this the database vendors have a trump card, what happens if multiple applications access the database? If your business logic is there, then you're fine, otherwise you're reimplementing that logic multiple times! The thing is that in the 15 years that I've seen people making that argument, I've not encountered a lot of organizations opening up a business critical transactional database for random applications. And in practice it is easier and saner to put an RPC layer on top of of your existing app, in which case you get the business logic that way.
I guess I should somewhere note that I spent several years of my life with my primary job being to write fancy SQL. I was also the go to person on the software development side for "get us to scale the database better". I do not criticize databases out of a lack of knowledge of how to use them. I criticize them based on a large amount of experience with how to make the most effective use of their capabilities.
Databases offer a great way of looking at your data.
However they have very poor stories around version control, rollout, rollback, separating metadata from data, and creating multiple parallel environments for production, qa, and every dev engineer. Yes, I know that database vendors claim otherwise, but compared to what is standard for most programming environments these days, it is a major limitation.
But it doesn't stop there. If you are lucky enough to get a busy site, you will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel. The more work you push down to that, the sooner you'll hit that bottleneck, and the harder it is to fix once you do. When you've experienced, as I have, the joys of periodic outages because the database choked at a million dynamic pages/day, you'll value moving logic out of the database, the ability to put caching in front of it, etc.
Now to all of this the database vendors have a trump card, what happens if multiple applications access the database? If your business logic is there, then you're fine, otherwise you're reimplementing that logic multiple times! The thing is that in the 15 years that I've seen people making that argument, I've not encountered a lot of organizations opening up a business critical transactional database for random applications. And in practice it is easier and saner to put an RPC layer on top of of your existing app, in which case you get the business logic that way.
I guess I should somewhere note that I spent several years of my life with my primary job being to write fancy SQL. I was also the go to person on the software development side for "get us to scale the database better". I do not criticize databases out of a lack of knowledge of how to use them. I criticize them based on a large amount of experience with how to make the most effective use of their capabilities.
In general, "ordinary" software has these advantages for the simple reason that it's ephemeral. The software starts with a clean universe each time you kill it and relaunch. That makes lots of things much, much easier.
Databases don't get to do that. The data is persistant between instances, between sessions and between copies. It never goes away and the database must always consider all other considerations secondary. If you want to change the object model of a Ruby program, you change the source and relaunch. If you want to change the schema of a database, you will need to provide the database instructions on what you want to do with all the existing data that is now inconsistent.
If you look at the hoops people jump through in always-on software, as opposed to "restart with #e33cf4" software, it's similarly gnarly.
Relational databases are kissing cousins with heavy-duty type systems. They make you do homework and eat your vegetables to forestall classes of error lots of people have never encountered and so discount to zero. Then it's decided that they aren't agile and fwoosh, out they go.
Databases don't get to do that. The data is persistant between instances, between sessions and between copies. It never goes away and the database must always consider all other considerations secondary. If you want to change the object model of a Ruby program, you change the source and relaunch. If you want to change the schema of a database, you will need to provide the database instructions on what you want to do with all the existing data that is now inconsistent.
If you look at the hoops people jump through in always-on software, as opposed to "restart with #e33cf4" software, it's similarly gnarly.
Relational databases are kissing cousins with heavy-duty type systems. They make you do homework and eat your vegetables to forestall classes of error lots of people have never encountered and so discount to zero. Then it's decided that they aren't agile and fwoosh, out they go.
Absolutely.
Which is why I find it works well to have code flow from dev to production, and databases flow the other way. With all changes starting as well-tested patches that are then applied in production and propagated elsewhere.
Which is why I find it works well to have code flow from dev to production, and databases flow the other way. With all changes starting as well-tested patches that are then applied in production and propagated elsewhere.
Why can't you version control database code/schema? I can do it fine using Rails migrations. I've also been looking at http://sqitch.org/ as well.
Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?)
Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?)
Why can't you version control database code/schema? I can do it fine using Rails migrations. I've also been looking at http://sqitch.org/ as well.
If you're willing to use a third party tool to manage the database, and ONLY use that tool, then you can solve version control/migration. I've used and written such tools myself.
However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc. And once you have random things in your database that your tool knows nothing about, you're on your way to maintenance headaches.
Furthermore third party tools like this generally want your site to be offline during database migrations. But if you've got a production site going 24x7, then you really want to figure out how to roll out upgrades without downtime. This is much, much easier if you can upgrade database and website independently of each other, with the database going first. That important detail tends to be poorly handled in my experience. (Though things change and I'd love to hear if that had improved.)
Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something)
Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?)
You can. The difficulty lies in keeping them synchronized with what is in your repository. And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.)
These problems are all solvable. I've solved them. But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git).
If you're willing to use a third party tool to manage the database, and ONLY use that tool, then you can solve version control/migration. I've used and written such tools myself.
However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc. And once you have random things in your database that your tool knows nothing about, you're on your way to maintenance headaches.
Furthermore third party tools like this generally want your site to be offline during database migrations. But if you've got a production site going 24x7, then you really want to figure out how to roll out upgrades without downtime. This is much, much easier if you can upgrade database and website independently of each other, with the database going first. That important detail tends to be poorly handled in my experience. (Though things change and I'd love to hear if that had improved.)
Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something)
Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?)
You can. The difficulty lies in keeping them synchronized with what is in your repository. And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.)
These problems are all solvable. I've solved them. But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git).
However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc.
Why would this happen? Database changes should be treated just like any other code update.
Furthermore third party tools like this generally want your site to be offline during database migrations.
They do? Why?
Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something)
I don't see why this is a problem at all (or worth mentioning).
The difficulty lies in keeping them synchronized with what is in your repository.
This is why you use migrations/tools/etc for managing database updates.
And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.)
I'm not sure how this is related.
But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git).
I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code.
Why would this happen? Database changes should be treated just like any other code update.
Furthermore third party tools like this generally want your site to be offline during database migrations.
They do? Why?
Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something)
I don't see why this is a problem at all (or worth mentioning).
The difficulty lies in keeping them synchronized with what is in your repository.
This is why you use migrations/tools/etc for managing database updates.
And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.)
I'm not sure how this is related.
But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git).
I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code.
> I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code.
I couldn't agree more. It boggles my mind how many people are resistant to storing this stuff in version control. It is code! Treat it like code! The exact same thing applies to configuration management. Your chef cookbooks should live in your git repository right next to your application code, and right next to your database code. That's the only possible way for every node in your source history graph to be functional in isolation. If your database changes independently of your version control, you simply cannot check out an arbitrary commit and expect anything to work.
I couldn't agree more. It boggles my mind how many people are resistant to storing this stuff in version control. It is code! Treat it like code! The exact same thing applies to configuration management. Your chef cookbooks should live in your git repository right next to your application code, and right next to your database code. That's the only possible way for every node in your source history graph to be functional in isolation. If your database changes independently of your version control, you simply cannot check out an arbitrary commit and expect anything to work.
Use case for you based on a real example. Early Monday morning, your site goes down under load. You really want a DBA to be able to hop on it, identify that the cause is, for instance, a poorly executing query, and lock a decent query plan in place.
Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that.
Secondly when it comes time to update your schema, the easiest solution is to simply lock tables and do updates. However locking the table can involve a forced outage. If, however, you limit yourself to very specific kinds of updates, then you can update your schema without major outages. Now rolling forward is cheap. Rolling it back is very, very expensive. Tools generally just try to do what you asked, and don't know how to say, "Really?"
If you pay attention to this, then you want to be able to just roll forward. And you want to be able to roll your database forward independently of the web server. Now you have the option of rolling the database forward, then rolling out a new version of the web server code. Should you have an emergency problem, roll the web server code back, and leave the database on the new version. (This only works if the database is "dumb".)
One strategy to support this is to let the DBAs be in charge of the schema, separately from your development tree. Developers are allowed to create patches that alter the database on dev, and will submit those patches to the DBAs. If the DBAs do not object to the change, they are in charge of putting it on QA or production. (If the DBA does object, of course, it goes nowhere.)
And now the pattern is that DBAs maintain the official definition of the database. DBAs are willing to apply proven patches to production, and can make QA a clone of production whenever they want. In emergencies, DBAs can fix problems directly on production. (You try to have this happen as little as possible.) All developer changes to the database schema have to be blessed by DBAs. Database patches need to be applied in production separately from and strictly before release.
I've seen a number of different strategies for handling database schemas and code. This is the one that, in my opinion, worked best in practice.
Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that.
Secondly when it comes time to update your schema, the easiest solution is to simply lock tables and do updates. However locking the table can involve a forced outage. If, however, you limit yourself to very specific kinds of updates, then you can update your schema without major outages. Now rolling forward is cheap. Rolling it back is very, very expensive. Tools generally just try to do what you asked, and don't know how to say, "Really?"
If you pay attention to this, then you want to be able to just roll forward. And you want to be able to roll your database forward independently of the web server. Now you have the option of rolling the database forward, then rolling out a new version of the web server code. Should you have an emergency problem, roll the web server code back, and leave the database on the new version. (This only works if the database is "dumb".)
One strategy to support this is to let the DBAs be in charge of the schema, separately from your development tree. Developers are allowed to create patches that alter the database on dev, and will submit those patches to the DBAs. If the DBAs do not object to the change, they are in charge of putting it on QA or production. (If the DBA does object, of course, it goes nowhere.)
And now the pattern is that DBAs maintain the official definition of the database. DBAs are willing to apply proven patches to production, and can make QA a clone of production whenever they want. In emergencies, DBAs can fix problems directly on production. (You try to have this happen as little as possible.) All developer changes to the database schema have to be blessed by DBAs. Database patches need to be applied in production separately from and strictly before release.
I've seen a number of different strategies for handling database schemas and code. This is the one that, in my opinion, worked best in practice.
Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that.
I'm assuming that Oracle lets you do this via some sort of a sql file/script? I'd think the process would be:
1. Create SQL file that locks the query plan
2. Test it
3. Check into version control
4. Using migration tool, apply to production database
PostgreSQL lets you do all sorts of updates without locks, fwiw, and that's what I'm most experienced with. And all postgresql administration can be done via text files that can be checked into version control.
I'm assuming that Oracle lets you do this via some sort of a sql file/script? I'd think the process would be:
1. Create SQL file that locks the query plan
2. Test it
3. Check into version control
4. Using migration tool, apply to production database
PostgreSQL lets you do all sorts of updates without locks, fwiw, and that's what I'm most experienced with. And all postgresql administration can be done via text files that can be checked into version control.
I'm assuming that Oracle lets you do this via some sort of a sql file/script?
Not that I know of. When I took the SQL tuning class where I learned about it, they had an interactive admin panel for tracking down, identifying, and fixing query plans.
But I'm not a DBA, I haven't used Oracle in a few years, and I do not know what all is available there.
Not that I know of. When I took the SQL tuning class where I learned about it, they had an interactive admin panel for tracking down, identifying, and fixing query plans.
But I'm not a DBA, I haven't used Oracle in a few years, and I do not know what all is available there.
That would be interesting - but I wonder if it makes sense.
@btilly: what is the name of the thing with locking queries you referred to?
@btilly: what is the name of the thing with locking queries you referred to?
Any upgrade script that does certain operations will lock tables as it goes. See your database documentation for details. This can include rebuilding indexes, removing columns, modifying existing columns, etc. Furthermore when you've got both stored procedures and tables changing, you have to get the order of operations correct. I'm not aware of any automated tool that can figure out the dependencies and do so correctly. (Mind you I have not looked recently.)
There's nothing terribly complicated about recursive queries and common table expressions, and for the use cases in which they're commonly used (hierarchical data being one of those) they perform significantly better than all of the popular SQL alternatives in almost every case.
I was referring to putting business logic in your database, and not any particular query technique.
Sometimes it just saves you huge amounts of time and code to do something in the database though. For instance, if you need to join against a remote data source, you can:
1. Write a bunch of code to pull the data from both sources and join it in the application. You have to pick a join algorithm at the beginning, and if your data changes you may need to rewrite it later.
2. Add a remote table in the database and just do the join there. The database can use statistics to pick a join algorithm for you.
I'm sure there are cases where you'd pick #1. But in many cases, the database offers a feature that allows you to implement a solution and call it "done" in minutes or hours where an application-based solution would drag on for days or weeks and carry a larger maintenance burden.
1. Write a bunch of code to pull the data from both sources and join it in the application. You have to pick a join algorithm at the beginning, and if your data changes you may need to rewrite it later.
2. Add a remote table in the database and just do the join there. The database can use statistics to pick a join algorithm for you.
I'm sure there are cases where you'd pick #1. But in many cases, the database offers a feature that allows you to implement a solution and call it "done" in minutes or hours where an application-based solution would drag on for days or weeks and carry a larger maintenance burden.
Please go to https://news.ycombinator.com/item?id=5934136 and look at the final paragraph.
Yes, I'm well aware of what databases can do for me, and have made a good living getting them to jump through hoops to do their thing.
However part of knowing how to use a tool well is knowing its limitations. And from what I've learned, a web-based CRUD application is usually better off with business logic in the web layer, and not in the database.
Also I have absolutely no idea why you brought up joining against remote data sources. However I've had to do it against multiple databases over the years. And in my experience with Access, Sybase, Oracle, PostgreSQL and MySQL, I found that for all but the simplest cases, #1 was preferable. For each of those databases I can name a case where I started with #2 with clear issues, and wound up with #1 working well.
Why? Because if you understand query performance, you can make #1 work well. And databases tend to do a horrible job of understanding what's going on with remote data sources and picking good query plans for them. Which can cause #2 to suck in hard to fix ways. (Plus with PostgreSQL specifically there were security issues that made the DBA unwilling to open up one database remotely to the other. He was willing to let my application access both, but didn't want to allow other applications to be able to do it.)
That said, most people who need access to remote data are in a simple circumstance. And I know a lot more about query performance than your average software developer. So if #2 can work for you, you should go that route.
Yes, I'm well aware of what databases can do for me, and have made a good living getting them to jump through hoops to do their thing.
However part of knowing how to use a tool well is knowing its limitations. And from what I've learned, a web-based CRUD application is usually better off with business logic in the web layer, and not in the database.
Also I have absolutely no idea why you brought up joining against remote data sources. However I've had to do it against multiple databases over the years. And in my experience with Access, Sybase, Oracle, PostgreSQL and MySQL, I found that for all but the simplest cases, #1 was preferable. For each of those databases I can name a case where I started with #2 with clear issues, and wound up with #1 working well.
Why? Because if you understand query performance, you can make #1 work well. And databases tend to do a horrible job of understanding what's going on with remote data sources and picking good query plans for them. Which can cause #2 to suck in hard to fix ways. (Plus with PostgreSQL specifically there were security issues that made the DBA unwilling to open up one database remotely to the other. He was willing to let my application access both, but didn't want to allow other applications to be able to do it.)
That said, most people who need access to remote data are in a simple circumstance. And I know a lot more about query performance than your average software developer. So if #2 can work for you, you should go that route.
"will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel"
In context, the alternatives available are something like:
1. Issue one recursive query 2. Issue many non-recursive queries 3. Use some kind of denormalized structure that is tuned to the one specific recursive query in question, but might perform badly on other queries.
#3 can work if you are focusing on the optimization of a single query and can pick a structure tuned to that one query. You can even parallelize it, because you can know the partitioning scheme that will work best for that one query. But outside of those constraints, or if the query profile changes over time, it does not work well. You end up needing to duplicate the data into many different structures to accommodate many different queries.
And I don't see how #2 has any advantage over #1 performance wise. Add caching layers or other performance hacks as needed.
In context, the alternatives available are something like:
1. Issue one recursive query 2. Issue many non-recursive queries 3. Use some kind of denormalized structure that is tuned to the one specific recursive query in question, but might perform badly on other queries.
#3 can work if you are focusing on the optimization of a single query and can pick a structure tuned to that one query. You can even parallelize it, because you can know the partitioning scheme that will work best for that one query. But outside of those constraints, or if the query profile changes over time, it does not work well. You end up needing to duplicate the data into many different structures to accommodate many different queries.
And I don't see how #2 has any advantage over #1 performance wise. Add caching layers or other performance hacks as needed.
For further context, I'm responding to someone who is advocating putting your business layer in your database.
I'm not against using tools that exist to make accessing your database more efficient - including recursive queries. I'm against making your database responsible for more of your application than it needs to be.
I'm not against using tools that exist to make accessing your database more efficient - including recursive queries. I'm against making your database responsible for more of your application than it needs to be.
In my experience: most people that don't know proper DB design and SQL will do two things:
- Over-utilize stored procedures, views, etc.
- Implement many obscure "hacks"
There is nothing wrong with using built-in database functionality, but it can be over done.
- Over-utilize stored procedures, views, etc.
- Implement many obscure "hacks"
There is nothing wrong with using built-in database functionality, but it can be over done.
I agree with you that in this particular instance, this is probably not the optimal solution to the problem at hand. I would argue, however, that you are significantly overreaching by implying that one should never take advantage of advanced features of your RDBMS. While the OP's problem was rather easy to solve in the application layer, there are many problems where that approach is absolutely unreasonable.
As an example, I'm currently working on a project that involves modeling a directed cyclic graph in the database. Trying to pull the logic out into the application layer is simply unfeasible. There is no performant way to do it, period. However, it's easy enough to work with it in the database, either by using a function that performs a recursive query, making a materialized view out of that same query, or using triggers to maintain the transitive closure in a table proper.
You also seem to have a pretty narrow perspective of the situations in which RDBMSs are used. As it happens, there are a lot more use cases than 'persistence layer for web apps', and in many of those use cases the idea of not using advanced features of your RDBMS would be absolutely ludicrous.
I do agree with one complaint made in another reply to your post, which is that version control, testing, etc. are much more difficult when your logic is residing in the database. There are a lot of solutions out there for that, though (migrations alone solve many of the headaches), so it's really not a knockdown argument. As for portability, the SQL itself is as portable as any SQL is. WITH RECURSIVE was added to the standard in '99.
As an example, I'm currently working on a project that involves modeling a directed cyclic graph in the database. Trying to pull the logic out into the application layer is simply unfeasible. There is no performant way to do it, period. However, it's easy enough to work with it in the database, either by using a function that performs a recursive query, making a materialized view out of that same query, or using triggers to maintain the transitive closure in a table proper.
You also seem to have a pretty narrow perspective of the situations in which RDBMSs are used. As it happens, there are a lot more use cases than 'persistence layer for web apps', and in many of those use cases the idea of not using advanced features of your RDBMS would be absolutely ludicrous.
I do agree with one complaint made in another reply to your post, which is that version control, testing, etc. are much more difficult when your logic is residing in the database. There are a lot of solutions out there for that, though (migrations alone solve many of the headaches), so it's really not a knockdown argument. As for portability, the SQL itself is as portable as any SQL is. WITH RECURSIVE was added to the standard in '99.
> The SQL itself is also non-portable
?
CTE is part of SQL:1999 and most RDBMS support it: http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer...
Or are you using some odd custom definition of the word "portable"?
?
CTE is part of SQL:1999 and most RDBMS support it: http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer...
Or are you using some odd custom definition of the word "portable"?
CTEs are portable, but the sorting piece of the solution relies on PostgreSQL's arrays, which are not portable. Even with CTEs, I think doing sort-by-path in a portable way is going to be ugly (arrays make it very nice.)
There's a better way: create a view backed by the recursive query. This way if they ever wish to move away from Postgres (although why would they?), they can denormalise manually.
Yep. For a portable solution there are a number of way of storing the hierarchy so that it's efficient to select a specific subtree.
One way I've done it before is to have a field that contains the path (eg node1/sub_n/sub_n2 etc) and another field containing the ordering (in my case I did it across the whole tree). You can then use simple single selects to get ancestors / descendants / subtrees / siblings / all sorts of things you need (eg where path like '/path/to/subtree/%s'). Obviously not totally efficient but easy to work with and fine for small trees.
The trade-off is that the operations to manipulate the tree are more fiddly - they make for a great little brain teaser though :)
One way I've done it before is to have a field that contains the path (eg node1/sub_n/sub_n2 etc) and another field containing the ordering (in my case I did it across the whole tree). You can then use simple single selects to get ancestors / descendants / subtrees / siblings / all sorts of things you need (eg where path like '/path/to/subtree/%s'). Obviously not totally efficient but easy to work with and fine for small trees.
The trade-off is that the operations to manipulate the tree are more fiddly - they make for a great little brain teaser though :)
Recursive CTEs are a portable solution; they are part of the SQL:1999 standard and are supported by current versions of Postgres, DB2, Oracle, SQL Server, Firebird, etc.
The only two widely used SQL-using relational databases I can think of off the top of my head that don't support them are SQLite and MySQL.
(PostgreSQL arrays aren't portable, though in other DBs I've seen strings used for similar purposes, though that is truly an abomination.)
The only two widely used SQL-using relational databases I can think of off the top of my head that don't support them are SQLite and MySQL.
(PostgreSQL arrays aren't portable, though in other DBs I've seen strings used for similar purposes, though that is truly an abomination.)
Common Table expressions are part of the SQL:1999 standard. Most RDBMS can handle them.
Absolutely. We aren't using this code in production. It's sitting on a branch.
I just thought recursive queries were really cool...and I had an example to illustrate it with. So hey, why not?
I just thought recursive queries were really cool...and I had an example to illustrate it with. So hey, why not?
"Programming like this in SQL is a maintainability nightmare."
How does it compare to the application code required to do the same thing?
How does it compare to the application code required to do the same thing?
[1] http://www.postgresql.org/docs/9.3/static/ltree.html