What is homology anyway? Disclaimer: I don't feel qualified to ask this question and yet it's been troubling me for some time now and I lost my patience and decided to ask to get some kind of answer. If there are any stupid mistakes please treat them as such and try to focus on the main issue raised if at all possible.
As the title suggests I'm struggling with the meaning of "Homology". In particular how are "Homology" and "Cohomology" related. By the end of my question I hope it will be clear what I mean. Let me start with some of the possible interpretations I'm (somewhat) familiar with, and after that let me say what troubles me. (All categories and functors are $\infty$ unless stated otherwise)

*

*Cohomology $\sim \operatorname{Hom}$ — Homology $\sim \otimes$
To make this precise consider the suspension $\infty$-functor sending spaces to their suspension spectra $\Sigma^{\infty}_+ :\mathrm{Spaces} \to \mathrm{Sp}$. The category of spectra is a symmteric monoidal $\infty$-category so for every space $X$ and spectrum $E$ one can
define the $E$-homology of $X$ as the homotopy groups of the smash
product $E_*X\mathrel{:=}\pi_*(\Sigma^{\infty}_+X \otimes_{\mathbb{S}} E)$.
The $E$-cohomology of $X$ in this picture is the homotopy groups of
the mapping spectrum $E^*X\mathrel{:=}\pi_*(\operatorname{Map}(\Sigma^{\infty}_+X,E))$.


*Homology $\sim$ Abelianization
To make this precise one can consider the tangent category to $\mathrm{Spaces}$ which is the fiberwise stabilization of the codomain fibration $\mathrm{Spaces}$. The fiber over a space $X$ will be the category spectra parametrized by $X$. Then one can define the Homology of $X$ as the image of the identity map $X \to X$ under the stabilization procedure. This is the "absolute cotangent complex" $L_X$. One has a kind of shriek pushforward for these parametrized spectra which for the case $X \to  \mathrm{pt}$ sends $L_X$ to $\Sigma^{\infty}_+X$ and one recovers some of the above from this viewpoint (I'm not so sure about this statement suddenly, is this true?). In a sense this is the relative setting for the above.


*Cohomology $\sim \mathrm{limits}$ - Homology $\sim \mathrm{colimits}$
To make this precise start with a local system over a space $X$. Let's take as a definition for a local system a functor from $X$ considered as an infinity groupoid to some category of coefficients (say spectra). Take this local system $L:X \to \mathrm{Sp}$ and define $L$-cohomology of X to be $\operatorname{Lim} L$ (this coincides with the sheaf cohomology definition) and $L$-homology to be $\operatorname{Colim} L$ (giving the same answer as 1 for the case of a constant functor $L=E$).


*Homology $\sim$ dual to Cohomology
This is the most cheeky definition. There are many flavors of this I believe the basic archetype being the Poincaré duality for oriented manifolds $H^i_{\mathrm c}(M) \cong H_{n-i}(M)$. The main idea is to define homology in such a way that one gets "Poincaré duality". For example in Verdier duality for locally compact (sufficiently nice) spaces one can define homology with coefficients in a sheaf $F$ as the compactly supported cohomology with coefficients in the Verdier dual of $F$. For example on a manifold if $F= \mathbb{Z}$ is the constant sheaf then the Verdier dual will be $\operatorname{OR}_M$ the orientation sheaf (perhaps shifted depends on one's conventions). The point is that this definition is concocted so that one always has a duality between homology and cohomology. This can be done in any cohomology theory which has good duality properties (i.e. six functors).
Why am I not satisfied?
Here are my concerns. Some of the interpretations above answer some of the concerns but none of them answer all of the concerns in a satisfactory way:

*

*Lack of convenient relative framework: For sheaf cohomology one has a very convenient framework for working in a relative situation (push/pull) in any context no matter how general. All one needs is a site and one immediately can ask questions about how cohomology behaves in this site, what kind of properties does it satisfy? Does it have 6 functor formalism? If not maybe at least 5 or 4? Does it have any interesting dualities? etc.… For Homology one seems to run into several persistent problems when trying to translate the above interpretations into a relative general setting like this.

*Using duality as a crutch: As much as I like dualities sometimes I feel like we're being a bit unfair to "Homology" treating it like a deformed creature which only has a right to exist as a dual to cohmology when in fact homology is the older brother of the two!

*Asymmetry between co/homology: In cohomology one has sheaves, sections, resolutions etc.… What do we have in homology? I'm kind of wishing that all the homology business is part of a bigger story Cosheaf Homology — Sheaf Cohomology. Unfortunately I have no idea what the words in the left hand side mean or even what they should mean. I just wish there was some way to put homology and cohomology on an equal footing.

*Only locally constant data: This is related to the above point. Why is there no "Constructible Homology" or "Coherent Homology"? Why doesn't Homology deserve these variants?

I hope by now I've made it clear what's my "problem" with my current understanding of Homology. As I said I don't feel like I'm qualified to ask this question so if anyone has any suggestion for an edit or a revision please don't even ask permission just edit away!
 A: 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.
A: For a long time (and still today), I very much shared the confusion of the OP. I think Jacob Lurie gives a very clear take on the standard perspective, but Mike Shulman does have a very valid contrasting point. The issue is that in the types of $6$-functor formalisms that are usually studied say in étale cohomology, there just is no left adjoint to pullback, and cohomology is not the dual of anything. For example, if $S$ is a profinite set, then $H^0(S,\mathbb F_\ell)$ are the locally constant functions $S$ with values in $\mathbb F_\ell$, which is often a countably dimensional $\mathbb F_\ell$-vector space, and hence cannot be the dual of anything.
I've highlighted this in some other MathOverflow questions before, but I wanted to do it here again: In Chapter VII of Geometrization of the local Langlands correspondence, we define for any "small v-stack" $X$ a closed symmetric monoidal category $D_{\blacksquare}(X,\mathbb Z_\ell)$ in such way that for any map $f: Y\to X$, the pullback $f^\ast$ admits a left adjoint $f_\natural$ in addition to the right adjoint $Rf_\ast$. (We do not use the notation $f_!$, as the latter means compactly supported cohomology, which is different from homology.) Moreover, completely general base change and projection formulas hold true. So this is a version of a $6$-functor formalism, allowing constructible coefficients, that puts homology and cohomology on equal footing, and arguably homology is now more fundamental again: For example, it satisfies a projection formula (which cohomology doesn't in general), and cohomology is always the dual of homology, but not the other way around.
What is a small v-stack? One source comes from analytic adic spaces, so for example from rigid spaces over $p$-adic fields, and this is the perspective taken in our work. But actually any condensed set, or in fact condensed groupoid (and we might as well allow a condensed anima), defines a small v-stack, over a fixed geometric point $\mathrm{Spa} C$. So the above applies in particular to say compactly generated weak Hausdorff spaces $X$ (regarded as a condensed set, regarded as a diamond over $\mathrm{Spa} C$, which is a special kind of small v-stack).
This formalism notably allows profinite sets $S$ as above. The problem gets resolved by allowing homology to be a topological group (rather, condensed group); in that case, the measures on $S$ with $\mathbb F_\ell$-coefficients, which is naturally a profinite $\mathbb F_\ell$-vector space, whose (continuous) dual are the locally constant functions $H^0(S,\mathbb F_\ell)$.
I'm in a state of perpetual confusion over how expressive this formalism is; many things usually expressed in terms of a $6$-functor formalism do not have an obvious translation, but usually have some translation into this picture. For example, if $f: Y\to X$ is proper and smooth, then the dualizing complex is given by the inverse of
$$R\pi_{1\ast} \Delta_{f\natural} \mathbb Z_\ell,$$
where $\Delta_f: Y\to Y\times_X Y$ is the diagonal and $\pi_1: Y\times_X Y\to Y$ the projection. Concretely, the fibre of this at a closed geometric point $y\in Y$ is given by the homology of $Y$ with coefficients in $i_{y\natural} \mathbb Z_\ell$, where $i_y: \{y\}\to Y$ is the closed immersion. Here
$$i_{y\natural} \mathbb Z_\ell = \varprojlim_{U\ni y} j_{U!} \mathbb Z_\ell$$
where $U\to Y$ runs over étale neighborhoods of $y$, so the homology of $Y$ with coefficients in $i_{y\natural} \mathbb Z_\ell$ is the limit of the compactly supported cohomologies $R\Gamma_c(U,\mathbb Z_\ell)$. This should be thought of as the compactly supported cohomology of a small ball around $y$, which should indeed be dual to the dualizing complex at $y$.
(While I tend to think about analytic adic spaces for $X$ and $Y$ here, this actually also works if $X$ and $Y$ are manifolds. In that case, one can even replace $\mathbb Z_\ell$ by $\mathbb Z$.)
Also, one can give a very similar treatment replacing analytic adic spaces with schemes. Some things are a bit different though, for example the base change results are not quite as general. (I find it very curious that analytic adic spaces are better than schemes here, and have no good intuitive explanation.)
A: 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.
