Monday, May 31, 2010

Recursive queries

"We deal with an enormous set of information, and some threads have literally thousands of repies. Pulling this down into memory isn’t a problem when 99% of threads only have a hundred replies, but when they start to peak we end up wasting a lot of time. Recursion within PGSQL allows us to easily pass off this work to the database (which just so happens to be able to do it faster than we ever could), and save us a ton of network traffic and web processing time in the process.

To give you an idea of the effect this will have (pending everything works out), we’ve seen savings of up to 500% on some large datasets, just for the SQL processing time (returning 25 results, rather than 1000). This doesn’t even represent the cost in our application layer. Yes, you heard that right, the SQL statement is 5x more performant at the database level alone." - from here: Recursive_queries in PostgeSQL. Real example from Disqus (cloud discussions).

P.S. I can confirm that Disqus shows a good perfomance in our mashups (see Geo talk, for example).

No comments: