I have a full node, and have written some limited python programs with bitcoinrpc. I want to start doing slightly more ambitious things now, e.g. visualisations across the entire blockchain (possibly analysing all transactions) and need to slice and dice the data in ways that make me instinctively want to have it queryable in a db (e.g. bigquery). Then again there's already over 800m transactions and so the performance of querying a table of transactions might not be any better than querying via bitcoinrpc.
I suppose my question is whether anyone who's made similar decisions in the past has any guiding principles for thinking about when it makes sense to dump large parts of the blockchain into your own db instead of query it directly via your node? E.g. I'm assuming the bitcoin api services (e.g. blockcypher) aren't just running a bunch of full nodes and querying them directly when new requests come in but instead have structured the data in a way that makes that much more efficient? A related question I have is whether Bitcoin was even designed with maximally-efficient-blockchain-querying in mind?