Tiene ventajas paginar en la aplicación, ya que haces una única consulta a la base de datos y además, si las clases como datatable, recordset, etc lo permiten, las páginas se pueden definir con solo configurar una propiedad, pero todo tiene un costo, como comentas, problemas para gestionar grandes cantidades de registros, o leer el total de los registros sin necesidad en caso de que la información buscada por el usuario se encuentre en las primeras páginas.
La alternativa es paginar dentro de la base de datos, tendrás que ejecutar una consulta por cada página pero es más eficiente porque se gestionan cantidades pequeñas de registros y no lees registros innecesarios una vez que el usuario encuentra la información que busca.
Código:
with clientes as (
select
row_number() over (order by id_cliente) as page,
id_cliente,
nombre
from tu_tabla
)
select id_cliente, nombre
from clientes
where nombre like '%un_nombre%'
and page between 1 and 100
En este caso, ejecutas la consulta por cada página, la cantidad de registros por página es la diferencia entre los valores del between que debes incrementar para obtener los registros de la página siguiente (por ejemplo: page between 1 and 100, page between 100 and 200, etc).
En general, paginar en la aplicación es favorable cuando se trata con pocos registros, o cuando los registros tienden a ser estáticos y los guardas en cache para mejorar el rendimiento, en caso contrario, lo mejor es paginar en base de datos.
Saludos