Relational database: in-RAM partitioning? Theoretical structure discussion

I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point of doing “Yet Another MMO Server” is to test out some of these concepts in production-level code.

I'm hoping someone here might have some practical experience, however, with PostgreSQL partitioning models, and be able to “lend” some expertise to this project, however.

Overview

There's a TL;DR version below.

• The core OS structure is a cluster of Linux instances, possibly on a cloud system. Stubs for monitoring overall system performance and spinning up new instances using an external API are planned for later implementation; for testing purposes, we're looking at probably using Amazon, but we are leaving the door open to make that a pluggable module for e.g. RackSpace and other providers, or even doing some naughty things with reconfiguring a pool of physical, “hot standby” servers on a private rack.

• The main MMO logic is based on a “pure” Entity-Component-System model. Entities “are” long integer ID's; Components are relational data records or sets (lists) of records (JOINable references); Systems are functions. Systems provide metadata about which Components they need access to, and method←→data locality across the cluster is based on a planner that anticipates these accesses, trying to keep running the same system(s) on the same host(s). That is: systems which access the same component of the same entity will tend to be on the same machine.

• The relational data store is backed by a PostgreSQL database. This was chosen based upon being Free Software as well as providing “better” (for our purposes) SQL/ACID services than MySQL in a few ways. Let's assume this is an immutable (it's already been argued about a lot).

• The server host instances represent a single game world, occupying a contiguous coördinate space: there are no discontinuities (e.g. levels; st

That's a long question.

First off, my current project (I'm the database guy, there are MMO engine experts to deal with that) is a form of MMORPG based on an off-the-shelf engine. Volumes would be like Eve Online" or "World of Tanks" volumes.

Now for an orthogonal short answer:

• separate DB and Engine completely

Don't mix and match because of hardware optimisations

• hardware: DB and engine servers will be way different specs

• design your database normally

There is a whole lot more of course, but I'd suggest you're over-thinking the problem and shooting yourself in the foot. I'm simply applying the same techniques to my MMO that I used in Investment Banking because IMO most high volume systems should converge to the similar architecture

StackExchange Database Administrators Q#14736, answer score: 3