When to switch from a stateful server to a database

Using a database (whether it’s a traditional SQL database or an OTP database such as Mnesia) makes sense when you want to persist data beyond application restarts. This might be true for parts of your data or all of your data, depending on your application.

I personally tend to just use an SQL database right away because it usually fits my application’s need anyways.
But if that’s not the case, you can always add a persistence layer to your stateful application that takes care of storing data when required and loading it when it gets restarted. With this approach you can leave most of your code unchanged and simply add code for storing/retrieving in/from the DB to your own state management functions.

A database is useful to store dynamic state that can’t be reconstructed, for instance user input. Things that can be rebuilt, or queried from another service, often don’t need to go in DB. See:

Another consideration is deployment. If you have persistent state in your application, you’ve essentially made your application a database. The way you database is very different from the way you deploy an application with no persistent state. There’s a concept called Pets vs Cattle, where Pets are special applications that need a lot of criteria to be true. For example, if you’re using disk based persistence, then if you roll that application, you’ll want it to have access to the same disk on reboot. Cattle, applications without persistent state, can be killed and booted anywhere.

See Phoenix Contexts for reference. You can make your own modules and functions that do reading, querying, writing and deleting and the consumer would be none the wiser on how is the data stored. So you can code according to your requirements from day one really.