What do you use and why?

I've used sqlite for structured storage by scripts and the like. From a performance standpoint, it's not amazing, but easy to set up and doesn't have resource usage if it's not actually being used.

If I were going to use an always-active daemon, I'd probably default to PostgreSQL, mostly because decades back, the last time I looked at it, it was considered to have some technical advantages over MySQL. And at one point I did some hacking on its scheduler, so I have a bit of a warm feeling for it.

Do you think server hardware is required for most operability or can a good desktop computer and internet speed suffice as long as downtime isn't a problem?

I probably wouldn't think of things as "server" hardware" and "desktop" hardware. That's really a product of vendors trying to segment the market up, which lets them engage in price discrimination, as business buyers tend to be less price sensitive. Maybe you want more memory or a faster form of storage or something.

As to performance, I mean, it depends entirely on your use case. I would guess that the vast majority of database-using software out there has performance be a non-issue.

If you're looking for potential land mines, I'd suggest making sure that your backup system is aware of the database and can take an atomic snapshot, so that you don't get a garbled, being-written copy every time you do a backup.

If you're trying to figure out whether to buy a lot of hardware, though, I can tell you that without knowing what you're doing, I would not go out and buy new hardware just over performance concerns until you've actually tried doing your project on existing stuff and found that it's inadequate. People have run useful, large databases for many decades on much weaker hardware and slower storage than is generally-available in 2024.