I love the direction. It makes a lot of sense to me. We'll need to be careful to not trash performance when we adopt this. Leaving my high-level feedback now and will come back to look at details.

Reviewed 13 of 34 files at r1, all commit messages.
Reviewable status: complete! 0 of 0 LGTMs obtained (waiting on @postamar)

pkg/sql/catalog/descs/getters.go line 33 at r1 (raw file):

// ByIDGetter looks up immutable descriptors by ID.
type ByIDGetter interface {

I'm modestly worries that this interface approach is going to end up hurting from a performance perspective, at least, eventually. I worry that we're going to end up allocating frequently. I do get the value in doing this. I wonder if ultimately the approach we'll want to take is to return the concrete struct which implements this to the callers and have all the various forms actually share the same underlying pointer. Consider:

type ByIDGetter getterBase

You can then implement ByIDGetter the interface and have all the fields of a getterBase without exposing any of its methods or what not. Then, to convert between the various implementations, you can cast the values without ever incurring the need to allocate the object on the heap.

Keep an eye on the various benchmarks we have related to resolving descriptors as you make progress around these parts. This code ends up being rather performance critica.

pkg/sql/catalog/descs/getters.go line 578 at r1 (raw file):

// TODO(postamar): clean up inconsistencies, enforce sane defaults.
type getterFlags struct {
	context      contextFlags

the code might read more cleanly if you embed the contextFlags

I saw that you categorize all the flags into: context-related, layer-related, and desc-specific. This is very great! My question is that it seems you also introduced withoutStorage and withoutCommittedAdding flags bc they don't exist in the present tree.CommonLookupFlags. Why do you want to have these two new flags?

This pleases me very much. In an ideal future world, we'll remove the Txn arg from the *descs.Collection functions and then can encapsulate that thing as an interface (it's always a pointer on the heap anyway). I think if we did that, it'd go a very long way towards making the catalog access more composable. What is currently the getters would be the layer to import and not descs.