In Bayesian, what is the effect of prior on the curvature of posterior? Just wonder about the simplest example, I have a simple Normal prior on my scalar parameter $w$. 
$$P(w|D) \sim P(D|w) P(w)$$
$$P(w) = \mathcal{N}(0,\alpha^2)$$
When I increase $\alpha$ from 0 to $\infty$, how would the curvature of $P(w|D)$ changes?
 A: From your clarification in the comments, it appears that you are interested in the second derivative of the posterior, not the curvature in its strict sense.  Obtaining this is just a matter of basic calculus.

To facilitate our analysis, let's use the alternative notation $\pi_D(w) \propto L_D(w) \pi_0 (w)$ so that we are not using generic function notation.  From your specification we have $\pi_0 (w) = \text{N}(w|0,\alpha^2)$.  With a bit of simple calculus on the normal density, the derivative of the specified prior density is:
$$\begin{equation} \begin{aligned}
\frac{d \pi_0}{dw}(w) = \frac{d \text{N}}{dw}(w|0,\alpha^2) &= - \frac{w}{\alpha^2}\cdot \text{N}(w|0,\alpha^2) = - \frac{w}{\alpha^2}\cdot \pi_0(w).
\end{aligned} \end{equation}$$
Then, using repeated application of the product rule you get:
$$\begin{equation} \begin{aligned}
\frac{d \pi_D}{dw}(w) 
&= \frac{d L_D}{dw}(w) \cdot \pi_0(w)  + L_D(w) \cdot \frac{d \pi_0}{dw}(w) \\[6pt]
&= \Bigg[ \frac{d L_D}{dw}(w) - \frac{w}{\alpha^2} \cdot L_D(w) \Bigg] \pi_0(w), \\[10pt]
\frac{d^2 \pi_D}{dw^2}(w) 
&= \Bigg[ \frac{d^2 L_D}{dw^2}(w) - \frac{1}{\alpha^2} \cdot L_D(w) - \frac{w}{\alpha^2} \cdot \frac{dL_D}{dw}(w) \Bigg] \pi_0(w) + \Bigg[ \frac{d L_D}{dw}(w) - \frac{w}{\alpha^2} \cdot L_D(w) \Bigg] \frac{d\pi_0}{dw}(w) \\[6pt] 
&= \frac{1}{\alpha^2} \Bigg[ \alpha^2 \cdot \frac{d^2 L_D}{dw^2}(w) + \Big( \frac{w^2}{\alpha^2}-1 \Big) L_D(w) - 2w \cdot \frac{dL_D}{dw}(w) \Bigg] \pi_0(w). \\[6pt] 
\end{aligned} \end{equation}$$
Taking the limit as $\alpha \rightarrow \infty$ you get:
$$\begin{equation} \begin{aligned}
\lim_{\alpha \rightarrow \infty} \frac{d \pi_D}{dw}(w) &= \frac{d L_D}{dw}(w) \cdot \pi_0(w), \\[8pt]
\lim_{\alpha \rightarrow \infty} \frac{d^2 \pi_D}{dw^2}(w) &= \frac{d^2 L_D}{dw^2}(w) \cdot \pi_0(w).
\end{aligned} \end{equation}$$
