It comes down to personal choice, influenced by your environment, knowledge and willingness to invest yourself into finding the optimal solution for you.
I'll try to elaborate on that a bit, answering some of your questions as we go 
Classic DNS does not - full stop (and btw, your are wrong about confidentiality, as you'll see shortly).
Each available extension does only ever address a subset of these areas.
The most prominent option configurable in Pi-hole is DNSSEC.
DNSSEC provides authenticity (i.e. you are indeed communicating to the resolver you think you talk to) and integrity (i.e. no one has tampered with the DNS records you are receiving) by digitally signing DNS records.
It does nothing for confidentiality (i.e.third parties can directly observe and collect your DNS traffic), and privacy is not possible when it comes to the resolver (as that necessarily has to see your requests, so it indeed comes down to a question of trust).
If it would be sound to enable DNSSEC then, why not just enable it in Pi-hole by default?
Well, for DNSSEC to work as intended, your target DNS resolver has to support it as well. In fact, resolver side support is required for any solution extending classic DNS.
So what if your chosen upstream DNS does not provide it at all, or only with notably increased latency? Would you pick another, possibly less trustworthy provider or stick with it and forego DNSSEC?
Another (if minor) parameter is related to the hardware you are using:
A common time frame on the sender and receiver side is required for DNSSEC, making the lack of a dedicated real time clock (RTC) on an RPi a possible source for failure of DNS resolution (if only intermittently and temporarily, until your RPi successfully resyncs with a time server). Can you live with temporary DNS outages?
Also note that there have been occurences of DNS outages due to expired certificates needed for digital singature (something that can never occur with classic DNS).
If you chose to run unbound, BIND or any other full resolver locally, you won't need DNSSEC enabled in Pi-hole, as the local resolver can already handle that.
It will likely be slower, if measurably only.
And no, data isn't sourced locally: Initial lookups will take longer due to recursing multiple DNS servers, whereas subsequent queries to the same domain during its TTL will not accrue any network latency.
However, similar is true for Pi-hole, caching successfully resolved domains, without accrueing the initial impact of a full recursion.
That said, I wouldn't expect a noticable difference in day-to-day usage when visiting my usual websites that I frequent regularly.
.
Finally, note that DNS-over-HTTPS is is distinctively different from DNS-over-TLS.
So, no.
DNS-over-TLS (aka DoT, also corrected my wrong spelling from TCP to TLS) and DNS-over-HTTPS (DoH) share some goals, but they are not identical.
Though not rejecting it per se, I have several concerns about DoH, e.g. as it is employed at application rather than network level, different client applications may resolve the very same DNS names differently, and it promotes further DNS data concentration at a select few DoH resolvers as Cloudflare.
In fact, the link you provided does exclusively make use of one of their tools.
(In the long run, this concentration could mean that your internet plan will become more expensive, as ISPs lose a source of income - it's not uncommon for ISPs to monetarise your DNS history)
For an in-depth comparison, let me refer you once again to DNS Security: Threat Modeling DNSSEC, DoT, and DoH
