refahs.blogg.se - Postgres 14 sharding

#Postgres 14 sharding code#

To make things even more complex, I use Streaming Replication to replicate every database server's data to multiple slave servers. # - or by querying a "name_to_user" table then querying just the # - either by querying *all* database servers (not good) Searching users by name # fetches all relevant users (kings) from all relevant logical shards User = User(name = "Arthur", title = "King") User = User.create(name = "Arthur", title = "King")

#Postgres 14 sharding code#

Ideally, I would love to be able to write Django code such as:įetching an instance # this gets the user object on the appropriate server, in the appropriate schema:įetching related objects # this gets the user's posted articles, located in the same logical shard:Ĭreating an instance # this selects a random logical shard and creates the user there: How can this be achieved as simply as possible in Django ?

Finally, the user table in the appropriate schema is queried.

The schema is simply named something like "shardNNNN", where NNNN is the logical shard ID.

Each logical shard lives in its own PostgreSQL schema (for those not familiar with PostgreSQL, this is not a table schema, it's rather like a namespace, similar to MySQL 'databases').

Instagram uses Pgbouncer at this point to retrieve a pooled database connection to the appropriate database server.

The mapping from physical shard ID to database server is also hard coded.

The mapping from logical to physical shard ID is hard coded (in some configuration file or static table).

The logical shard ID is directly calculated from the user ID (13 bits embedded in the user id).

User ID => logical shard ID => physical shard ID => database server => schema => user table

I want users to be sharded across those database servers using the same sharding logic used by Instagram: I have a Django project based on multiple PostgreSQL servers.