What is the precise relationship between pyknoticity and cohesiveness?

The work on analytic geometry is all joint with Dustin Clausen!

Your main question seems a little vague to me, but let me try to get at it by answering the subquestions. See also the discussion at the nCatCafe. Also, as David Corfield comments, much of this had been observed long before: https://nforum.ncatlab.org/discussion/5473/etale-site/?Focus=43431#Comment_43431

1. Yes, I think cohesion does not work in algebraic or $p$-adic contexts. The issue is that schemes or rigid-analytic spaces are just not locally contractible.

2. Cohesion does not seem to have been applied in algebraic or $p$-adic contexts. However, I realized recently (before this nCatCafe discussion), in my project with Laurent Fargues on the geometrization of the local Langlands correspondence, that the existence of the left adjoint to pullback ("relative homology") is a really useful structure in the pro-etale setting. I'm still somewhat confused about many things, but to some extent it can be used as a replacement to the functor $f_!$ of compactly supported cohomology, and has the advantage that its definition is completely canonical and it exists and has good properties even without any assumptions on $f$ (like being of finite dimension), at least after passing to "solid $\ell$-adic sheaves". So it may be that the existence of this left adjoint, which I believe is a main part of cohesion, may play some important role.

3. As I already hinted in 2, this relative notion of cohesiveness may be a convenient notion. In brief, there are no sites relevant in algebraic geometry that are cohesive over sets, but there are such sites that are (essentially) cohesive over condensed sets; for example, the big pro-etale site on all schemes over a separably closed field $k$. So in this way the approach relative to condensed sets has benefits.

All of these questions sidestep the question of why condensed sets are not cohesive over sets, when cohesion is meant to model "toposes of spaces" and condensed sets are meant to be "the topos of spaces". I think the issue here is simply that for Lawvere a "space" was always built from locally contractible pieces, while work in algebraic geometry has taught us that schemes are just not built in this way. But things are OK if instead of "locally contractible"(="locally contractible onto a point") one says "locally contractible onto a profinite set", and this leads to the idea of cohesion relative to the topos of condensed sets.

Let me use this opportunity to point out that this dichotomy between locally contractible things as in the familar geometry over $\mathbb R$ and profinite things as codified in condensed sets is one of the key things that Dustin and I had to overcome in our work on analytic geometry. To prove our results on liquid $\mathbb R$-vector spaces we have to resolve real vector spaces by locally profinite sets!

We have a case of relative cohesion used in an algebraic geometric setting discussed at the nLab. The entry for differential algebraic K-theory interprets

• Ulrich Bunke, Georg Tamme, Regulators and cycle maps in higher-dimensional differential algebraic K-theory (arXiv:1209.6451)

via cohesion over the base $Sh_\infty\left(Sch_{\mathbb{Z}}\right)$, ∞-stacks over a site of arithmetic schemes.

See also Urs Schreiber's entry, differential cohesion and idelic structure, and arithmetic elements of his research proposal, Higher theta functions and higher CS-WZW holography.