Hello everyone. I'm looking for a lightweight database. I'm targeting 2000-3000 player base with each player requiring 2-3 queries per second on average. I've been using Oracle previously but it's making me kill myself each time I click 'Start the service' or run 'SQL Developer' so if there's a lightweight database which would be sufficient for my needs I'd like to move onto that.

What sort of 'lightweight' do you mean? 2-3 queries per second is low enough that almost any technology would work. If it doesn't have to be shared across processes, Sqlite is fine. But if you need a standalone database, they're usually built for robustness, which often implies being a bit 'heavy'.

Postgresql is the best free all-rounder. MySQL is also okay. And there are a ton of other options if you don't care about using SQL.

Thanks for the answer. I meant 2-3 queries per second per player meaning 4000-9000 queries per second in total. Will look into Postgresql.

What response times are acceptable? I don't think there's a lightweight SQL database that would handle that ok if the queries require a lot of work, but you can look into NOSQL databases if Oracle makes you gag.

So, do you even need a database at all? Just make a dumb array in memory and keep the entire dataset loaded in RAM at all times :D

Would that mean I'd need to create structs instead of tables and write functions to insert/select/etc. records into/from those structs? Would this cause an issue as the number of records grow high? Not necessarily with player data but with item data as they can grow very very high.

What response times are acceptable? I don't think there's a lightweight SQL database that would handle that ok if the queries require a lot of work, but you can look into NOSQL databases if Oracle makes you gag.

It should be able to execute 4000-9000 queries in half a second so that I can run the rest of the logic during the remaning half. I didn't run any tests while using oracle but I assumed it was quite fast as I was using procedures and binding parameters for the most part.

Would that mean I'd need to create structs instead of tables and write functions to insert/select/etc. records into/from those structs? Would this cause an issue as the number of records grow high? Not necessarily with player data but with item data as they can grow very very high.

Depends on details, but probably yes.

Relational database systems are for big complex systems with enormous volumes of data. Simple requests take on the order of 10ms (about the same as a full graphics frame) and complex data requests can take much longer. They can work for reliable persistence, but their main purpose is relational queries.

Game need a lot of data in memory. That data might be stored in a database, but shouldn't be queried as the data is run. Data like user account information and inventories and such may be requested, but note that because of the time required for requests, on the order of a full graphics frame, you cannot rely on database round-trips for gameplay data. You can load data from a database, but that shouldn't be your typical data flow.

Servers can use a relational database for a write-through or write-back persistence method. That is, you notify the server of some changes and the server eventually writes a copy out to the database. Most likely the server will keep a live copy in memory for quite some time to give faster access to the data, eventually dropping it from the cache when it is no longer used and something else is needed.

For what you describe you will probably be using plain old arrays of data, possibly keeping them sorted. When you load and save the game you might store it to a database, but the common access for gameplay code will be to a plain old array container, depending on your language probably a C++ vector or a C# List or a Java ArrayList.