Olá.

O quanto é realmente utilizado em grandes projetos?

Nessa página https://storybook.js.org/showcase/projects você consegue visualizar várias implementações.
Alguns destaques:

• Microsoft: https://microsoft.github.io/vscode-webview-ui-toolkit
• Adobe: https://opensource.adobe.com/spectrum-web-components/storybook
• IBM: https://react.carbondesignsystem.com/?path=/docs/getting-started-welcome--welcome
• Jetbrains: https://jetbrains.github.io/ring-ui/master/index.html

E realmente relevante ou gasta muito tempo em vão?

Quanto a relevância, depende do conteúdo dele.
O storybook é uma ferramenta para implementação, manutenção e distribuição de um design system.
Se bem utilizado ajuda muito a economizar tempo de desenvolvimento, já que se torna a "fonte de verdade" da parte visual de um produto. Design Tokens, Fontes, Padrões, Boas Práticas...tudo pode ser documentado nele. Também fornece uma galeria de componentes com documentação de API, atributos, casos e contextos de uso.
Além disso estabelece um vocabulário único que ajuda a equipe a se entender quando conversar. Um mesmo componente pode possuir nomes diferentes dependendo de quem implementou. Colocar um nome padrão evita que integrantes da equipe percam tempo descrevendo o que eles querem dizer.

Desenvolver um componente fora do contexto de um cenário ou produto específico ajuda a pensar melhor na API do componente. Um conjunto de componentes com uma API padronizada melhora a experiência do desenvolvedor, reduz a curva de aprendizado e acelera a entrega das demandas pelo time. Também facilita a criação de testes automatizados focados no componente e nos cenários que ele deve atender.

É possível utilizar em todo o projeto ou apenas nos componentes mais básicos?

Sim é possível. Mas isso pode limitar o uso do storybook a apenas aquele projeto. Se o objetivo é criar uma identidade visual para uma gama de produtos da empresa, ter componentes específicos de um produto no design system pode torná-lo mais complexo sem um retorno prático para o produto.