$N(p)$ can grow to $\infty$ however fast. Indeed, let $g\colon(0,\infty)\to(1,\infty)$ be any (say) strictly increasing continuous function, with $g(\infty-)=\infty$.
We want to show that for some nonnegative function $f$ such that $f\in L^p$ for all $p>0$ we have
\begin{equation*}
N(p)\ge g(p); \tag{1}\label{1}
\end{equation*}
here and what follows, by default $p>0$ is any large enough number.\begin{equation*}
N(p)\ge g(p). \tag{1}\label{1}
\end{equation*}
Note that
\begin{equation*}
G(p):=g(p)^p>1
\end{equation*}
is also strictly increasing and continuous in $p$. Let
\begin{equation*}
h_p:=\frac1{G(p)}\in(0,1)
\end{equation*}
and
\begin{equation*}
a(x):=\frac2{G^{-1}(1/x)};
\end{equation*}
everywhere here, by default, $x>0$ is any small enough number$x\in(0,1)$.
Note that $a(x)\downarrow0$ as $x\downarrow0$.
Moreover, without loss of generality, $G$ is increasing so fastthe condition that $a$$g$ is varying so slowlyincreasing means exactly that
\begin{equation*}
b(x):=a(x)\ln\tfrac1x
\end{equation*}
beis decreasing in $x$.
Letting now \begin{equation*} f(x):=x^{-a(x)}=e^{b(x)} \end{equation*} and recalling that $a(x)\downarrow0$ as $x\downarrow0$, for each real $p>0$ we have $f(x)^p\le x^{-1/2}$ for all $x$ in a right neighborhood of $0$. Also, $f$ is bounded outside any right neighborhood of $0$. So, $f\in L^p$ for all $p>0$.
On the other hand, recalling that $b$ is decreasing, we have \begin{equation*} N(p)^p\ge \int_0^{h_p} f^p =\int_0^{h_p} e^{pb(x)}\,dx \ge h_p e^{pb(h_p)}=1/h_p=G(p)=g(p)^p, \end{equation*} so that \eqref{1} follows, as desired.