What is Homology anyway?

Let's take coefficients in a field $k$, for simplicity.

On 2): the singular cohomology of a topological space $X$ is the dual of its singular homology, almost by definition. But if $X$ is a space for which singular cohomology is not the same as sheaf cohomology, then the sheaf cohomology of $X$ need not have a predual. For example, if $X$ is the Cantor set, then the sheaf cohomology of $X$ with coefficients in the constant sheaf $\underline{k}$ is the vector space of locally constant functions from $X$ into $k$. This is a vector space of countable dimension over $k$, so it cannot arise as the dual of anything.

On 1) and 4): part of the point of the six-functor formalism is that it incorporates things like homology automatically. For nice spaces $X$, singular cohomology = sheaf cohomology with coefficients in the constant sheaf, and singular homology = compactly supported sheaf cohomology with coefficients in the dualizing sheaf. Or, in six-functor notation,

Cohomology of $X$ = $f_* f^* k$ and homology of $X$ = $f_! f^! k$ (here $f$ is the projection map from $X$ to a point, and all functors are derived). These constructions are related as follows:

a) If the topological space $X$ is locally nice (so that the constant sheaf satisfies Verdier biduality), then cohomology $f_* f^* k$ is the dual of homology $f_! f^! k$. This is satisfied for many spaces of interest (for example, finite simplicial complexes, underlying topological spaces of complex algebraic varieties, ...)

b) If the topological space $X$ is compact, then homology $f_! f^! k$ is the dual of cohomology $f_* f^* k$. This applies even when $X$ is locally very badly behaved, like the Cantor set.

If $X$ is both compact and locally nice, then both of these arguments apply, and the homology and cohomology of $X$ are forced to be finite-dimensional.

I generally think about the relationship differently than Jacob, probably because I'm coming from an algebraic topology background rather than an algebraic geometry one. I would say that if $\mathcal{E}$ is any $(\infty,1)$-topos, with $f:\mathcal{E}\to \mathcal{S}$ its unique geometric morphism to $\infty$-groupoids (homotopy spaces), then $f_*$ is cohomology of $\mathcal{E}$ with coefficients in some ($\infty$-)sheaf (of spectra, say), and so $f_* f^*$ is cohomology with coefficients in a constant sheaf. The homology of $\mathcal{E}$ with coefficients in some sheaf would use instead $f_!$, the left adjoint to $f^*$: the difference is that such a left adjoint doesn't always exist, only when $\mathcal{E}$ is locally contractible.

If $X$ is a topological space, we can make it into an $(\infty,1)$-topos in multiple ways. One is the slice $\mathcal{S}/X$, where $X$ is regarded as its homotopy type: this is always locally contractible, and in this way we get ordinary algebraic-topological homology and cohomology, as well as homology and cohomology with local coefficients in the classical sense (i.e. locally constant coefficients). Another is $\mathrm{Sh}(X)$, consisting of $\infty$-sheaves on the site of opens in $X$; this is not locally contractible unless $X$ itself is. Thus we can define cohomology with coefficients in an arbitrary sheaf on an arbitrary space, whereas for homology with such coefficients we need the space to be locally contractible --- or else to define the homology as only a "pro-object".

I am not an expert on six-functor formalism, but my understanding is that it includes both algebro-topological homology and algebro-geometric compactly supported-cohomology: the former arises in the Wirthmuller context $f^* = f^!$, while the latter is a generalization of the Grothendieck context $f_! = f_*$ (which is the case when $f$ is proper, i.e. $\mathcal{E}$ is compact). In topos theory there is a fundamental duality between local-connectedness conditions and compactness conditions (see for instance chapter C3 of Sketches of an Elephant), and the two perspectives on homology come from focusing on one or the other of these worlds.

Edit in response to comment: the duality between local connectedness and properness is indeed not obvious from the usual definitions. One way to see it fairly clearly is in terms of their characterizations using Beck-Chevalley conditions. A geometric morphism is "locally $n$-connected" iff every pullback of it (in the $(\infty,2)$-category of $(\infty,1)$-toposes) satisfies the left Beck-Chevalley condition for $n$-truncated objects, and it is "$n$-proper" (or "proper of height $n$") iff every pullback of it satisfies the right Beck-Chevalley condition for $n$-truncated objects. I don't know whether this is in the literature for general $n$; the locally (-1)-connected (a.k.a. open) and locally 0-connected (a.k.a. locally connected) cases and the (-1)-proper (a.k.a. proper) and 0-proper (a.k.a. tidy) cases, for 1-toposes, are in chapter C3 of Sketches of an Elephant, and the $\infty$-proper case is in section 7.3 of Higher Topos Theory.