New answers tagged scalability
Supplementing to @MaxVernon's answer, there are some optimizations that you can do : Use different filegroup for staging tables and make sure the filegroup is on a different physical drive (or LUN). Use Instant File Initialization as well as have a proper autogrowth setting. I have a handy script to analyze autogrowth events. Since you will be emptying the ...
Putting those staging tables on their own filegroup allows you to do piece-meal restores. This helps get the database up-and-running more quickly in the event of a disaster recovery effort. Having those tables in their own filegroup will allow you to place them on their own set of disks which may increase performance.
I assume you want to count the number of immediate childrent for each node in the tree. One easy solution is to do that with a sub-select: WITH RECURSIVE cte (id, message, author, path, parent_id, depth) AS ( SELECT id, message, author, array[id] AS path, parent_id, 1 AS depth FROM comments WHERE ...
Top 50 recent answers are included