But if you just always indent by n tabs, and align with spaces for visual alignment, then it's not a problem. This is what e.g. gofmt does.

(This isn't the best example of what I'm getting at, but it's easy to grasp. A stronger example would be the use of ternary operators with multiple lines of JSX. There are many "micro-syntaxes" or "micro-layouts" that help readability and form common patterns in code bases which don't play well with tabs-for-indents, so when you use tabs, you have to train your team to develop and accept novel versions of these "micro-layouts".)

In other words, instead of:

    def my_func(self,
                param):

    def my_func(
        self,
        param,
    ):

However, regarding "mixing two different invisible characters in the same file" - you can, and should, configure your text editor to display tabs in a visually distinct way from spaces. Kate (my favorite) shows them as chevrons. Geany and Notepad++ show them as arrows. Not only does this make it easier to scan vertically along an indentation level, but doing so also makes it glaringly obvious if a pesky space has snuck in, resulting in a jarring misalignment.

While I agree with this in general, I find making the code nicer to read to be more important that avoiding diff pollution. Especially when that diff pollution can be avoided with a simple "ignore whitespace" in most cases.

That being said, there are other cases where I want to align things and "a specific size indent" doesn't work

SQL

    SELECT this_column,
           that_column
    FROM tablename
    WHERE this_column = 1
      AND that_column IS NOT NULL

    /* 
     * Blah blah blah
     * Note: this is interesting because (enough text to want newline)
     *       and also that blah blah
     */

    SELECT
      this_column, 
      that_column
    FROM tablename 
    WHERE
      this_column = 1
      AND that_column IS NOT NULL

It might not be your preference, but that's always going to be the case with formatting.

With the comments, that's a good point. I'd generally prefer to use double comments for anything that spans multiple lines, but if you're creating a formatter you need to handle any syntax thrown your way. You could try formatting it without the leading space, but that would be very unusual, or keep the leading space and have it as the one exception to the rule. Or just not format comments, I guess.