In most of the times when effect size is reported, it seems to me that there is a clear inverse proportionality with p-value. I know that effect sizes bring information that is independent from significance, which is obvious when one considers the extreme cases of a very small and very large sample size - when large effects are difficult to found significant, and small effects can be found significant respectively. What would help is to see a plot of effect size as a function of p-value, with N as a curve parameter, but I haven't been able to find such a plot. Can anyone suggest where to find / how to create such a plot, or otherwise shed light on the problem?
Take a look at the formulae for your favorite hypothesis test and notice that the $p$-value depends not only on the effect size, but also on the sample size(s). Furthermore, different tests use different notions of effect size. These two reasons are why no such plot as you have requested can be created for the general case.
What measure of effect size are you using? People who report correlation coefficients tend to report their "significance", when they mean "the significance level of the test that r =/= 0". I suspect that this happens with other measures too, but have not worked with them sufficiently to be sure that it is common.
If this is the case you are talking about, then of course there is a strong correlation 1. It is much "easier" to be certain that a correlation 2 of 0.8 is different from zero, than that a correlation 2 of 0.1 is different from zero.
Note that this is somewhat of a side effect of limitations on the side of statistical knowledge and computational power available to some scienitifc traditions (such as the psychometry of the late 20th century). If you start doing different tests, their significance needs not be correlated to the strength itself.
1 denotes a correlation between effect size and statistical significance of the effect size, 2 denotes a correlation between your variables of interest, or a measurement of effect size. I had to use this unortodox notation to prevent confusion in the answer.
Of course you understand that effect sizes are the preferred metric relative to p-values with large amounts of data in an analysis. The obvious reason for this being that significance is very sensitive to sample size -- with large data, everything is "significant." Effect sizes help decide whether something matters. That said, there is a cult of significance among the technically semi-literate. This is one of the biggest problems with peer-review in publishing.
A key question should be the choice of effect size measure. There are many as the term "effect size" does not have a single meaning and overlaps strongly with measures of feature relative importance. Ulrike Groemping's papers are one of the best sources for a review of this literature. Pairwise correlations are not an appropriate metric since they are not conditional.
The many machine learning workarounds for modeling large numbers of features (e.g., random forests, bags of little jacknifes, etc.), once summarized, would generate the kind of information you seek to create such a plot. One barrier to this graphic will be the fact that most packages report significance only out to several decimal places, preferring to roll up smaller values with a "<0.0001" symbol.
What would be interesting would be to see how the relationship between p-values and effect size changes as a function of the metric used.