I was able to accomplish what I wanted to do, albeit imperfectly, by just using larger dots in the layer style and rendering with an additive Gaussian blur.
To add that blur, I did the following (QGIS 3.10):
Set the symbol size to however large you want the blur footprint to be. (I chose 10mm).
Expand the Layer Rendering section.
Tick 'draw effects'.
You need points in both sides of the road to interpolate. In the process we will get also a buffer to clip the raster.
Create a buffer of your lines with a proper radius for a road (let's assume that 30m is a proper radius).
Dissolve the buffers.
Densify by interval the dissolved buffer, by a proper distance to get enough nodes (let's assume that 10m ...
this should do the trick -- just replace your tables and columns where needed
FROM tablea a
CROSS JOIN lateral
FROM tableb b
ORDER BY b.geom <-> a.geom
GROUP BY a.id,a.geom
the idea is to take table a (outer table) 1 record at a time and find the 4 ...
Unfortunately, if there are clustering functions in Postgis, there is still no density functions. But you can create your own, and luckily somebody already did: https://trac.osgeo.org/postgis/ticket/2894#no1
I think that you need to use a distance based interpolation method (IDW) with a strong power decay, so that the values fade quickly within your no_data regions.
Then, it is a simple heatmap and the color ramp should be based on the interpolated values.