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.

Okay, then that's a very different situation and you will need a proper DB for that. Postgres is the best starting point.

Assuming that 30Hz is a normal number for either a game client or a game server to be operating at, 9000 queries per second is 300 queries per frame, which seems like a very mundane figure.

Each player probably doesn't have very much data each -- let's say 1 kilobyte for fun. That's 3000 KB or ~2.9MB. If each query needs to touch 100 bytes of that player's data, that's under 1MB/s of memory bandwidth required, and any respectable system will have well over 1GB/s of raw memory bandwidth available.

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

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.

1) You do not want to use a database server as a game server. Packing all the services on a single machine is okay for development and testing environments, but any proper deployment needs to factor database services into different hosts from game services.

2) You do not want to use a database as your main IPC or state distribution mechanism. It should not be used to communicate player movement updates, or player chat, or anything like that. Databases are used when you absolutely need transactional integrity and durability. What are you doing 3 times a second to 4,000 users that needs durability and transactional integrity?

You do not want to use a database as your main IPC or state distribution mechanism

This. The OP's question smells like an XY Problem to me. _Why_ do you want a database? Ask us for help with the actual problem you're trying to solve.