django-pindb 0.1.12

TL;DR API

# Try to save without pinning:
foo = Model.objects.all()[0]

# UnpinnedWriteException raised under strict mode, or master pinning occurs under greedy mode.
foo.save()

# read only from the master, allow writes.
pindb.pin(alias)

# Initialize/end the container:
pindb.unpin_all()

with unpinned_replica(alias):
   ... # read from replicas despite pinning state
# or
queryset.using(pindb.get_replica('master-alias'))

with master(alias):
   ... # write to master despite pinning state

Jargon

Master refers to a writable DB. Replica (or slave) refers to a read-only DB whose data comes from master writes. One or more master/slave database sets can help scale reads and avoid lock contention on the master. Typically all reads go to replicas until a write occurs – then all subsequent reads also go to the master to avoid inconsistent reads due to replication lag. Pinning is time-based and round-trips between web requests via cookies. See “design notes” below for more.

Installation

pip install pindb

TL;DR:

1. Set DATABASES_ROUTERS to use a pindb router

2. set PINDB_ENABLED to False under test

3. Define DB masters and replica sets.

4. populate DATABASES with pindb.populate_replicas.

5. Add PinDbMiddleware to your middleware.

6. Integrate with celery (if needed).

7. profile for places to explicitly side-step pinning.

More explicitly:

Add pindb.StrictPinDbRouter or pindb.GreedyPinDbRouter to DATABASE_ROUTERS.

StrictPinDbRouter requires that pinning be declared before a write is attempted. The advantage is that read replicas are used as much as possible. The disadvantage is that your code will need many declarations to explicitly allow and opt out of pinning.

GreedyPinDbRouter will pin to a master as soon as a write occurs. The advantage is that most of your code will just work. The disadvantage is that you will use the read replicas less than possible. You also might encounter more situations where the state of your DB changes behind your back: you might read from a lagged replica, then perform a write (which pins you to the master) based on that old information.

PINDB_ENABLED can be used to disable pindb under test. Each TEST_MIRROR’d alias gets its own connection (and hence transaction), which is problematic under Django’s TestCase, where a master write will not be visible under the replica’s connection.

pindb has an extensive test suite; disabling it under your own test suite is sane/recommended.

If you need to manage more than 1 master/replica set, add PINDB_DELEGATE_ROUTERS for pindb to defer to on DB set selection. This is just another Django database router which should return a master alias; pindb will then choose a master or replica as appropriate for the current pinning state of the returned master.

Define MASTER_DATABASES, same schema as DATABASES:

DATABASES = {
  "unmanaged": {
    # HOST0, etc.
  }
}

MASTER_DATABASES = {
  "default": {
    # HOST1, etc.
  },
  "some_other_master": {
    ...
  }
}

Define DATABASE_SETS, which overrides specific settings for replicas:

DATABASE_SETS = {
  "default": [{HOST:HOST1}, {HOST:HOST2}, ...],
  "some_other_master": [...] # zero or more replicas is fine.
}

Finalize DATABASES with pindb.populate_replicas:

DATABASES.update(populate_replicas(MASTER_DATABASES, DATABASE_SETS))

Add pindb.middleware.PinDbMiddleware to your MIDDLEWARE_CLASSES before any expected database access.

Optionally, throughout your codebase, if you intend to write, declare it as early as possible to avoid inconsistent reads off the related replicas:

pin("default")

That will cause all future reads to use the master.

To use under celery, hook celery.signals.task_postrun to call pindb.unpin_all:

import pindb
from celery.signals import task_postrun

def end_pinning(**kwargs):
  pindb.unpin_all()
task_postrun.connect(end_pinning)

Exceptions and avoiding them

with pindb.unpinned_replica(alias):
  # code which reads from replicas

with pindb.master(alias):
  # code which writes to the DB

Requirements and design notes

We have multiple separate masters (not necessarily sharded). Let’s call a grouping of a master and its replicas a “DB set”.

We would like to have read replicas of these masters, and we would like to read from replicas as much as possible and we would like all writes to go to the master of the set. But we would also like reads to be consistent to writers.

We would like this to be possible for web request cycles but also for units of work like tasks or shell scripts. So we call this unit of work the “pinning context”.

Writes to a given master should continue to read from the master to avoid inconsistency in the replication lag window, so there will be an API for declaring that. Declaring (or otherwise preferring) that a set master is needed is “pinning” and the group of pins for all DB sets is called the “pinned set”.

Code which plans on writing (or needs the very lastest data) should be able to declare that as early as possible to get a fully-consistent view from the master(s).

It should be a clear error if we’ve made a mistake in pinning (that is, writing after reading from a set). The issue here is that if we allow reads (not knowing that a write is coming) that gives us an inconsistency window. For example, a process reads from replica, gets a PK that has been deleted in master, writes to master, fails. Or gets a PK that’s been mutated in master so that it shouldn’t have been processed, etc.

Code which needs to write without pinning the whole container (e.g. a logging table) should be able to side-step the pinning.

We should be able to manage the DB sets in settings with minimal repetition, and it should compose well with multiple settings files.

MASTER_DATABASES = {
  'master-alias': { 'HOST':"a", ...normal settings },
   ...
}

DATABASE_SETS = {
  'master-alias': [{'HOST':'someotherhost',...},],
   # override some of the master settings
}

DATABASES = DATABASES.update(populate_replicas(MASTER_DATABASES, DATABASE_SETS))

DATABASES = {
  'master-alias': { 'HOST':"a", ...normal settings },
  'master-alias-1': { 'HOST':"someotherhost", ...merged settings,
                      TEST_MIRROR='master-alias' },
  ...}

with pindb.unpinned_replica('master-alias'):
       ...

pindb.pin('master-alias')

Coverage

To see coverage of pindb:

$ PYTHONPATH=.:$PYTHONPATH coverage run setup.py test $ coverage html

Example configuration

MASTER_DATABASES = {
    'default': {
        'NAME': 'db1',
        'ENGINE': DB_ENGINE,
        'USER': '...',
        'PASSWORD': '...',
        'HOST': '10.0.1.0',
        'PORT': 3306,
        'OPTIONS': DB_OPTIONS
    },
    'api': {
        'NAME': 'db2',
        'ENGINE': DB_ENGINE,
        'USER': '...',
        'PASSWORD': '...',
        'HOST': '10.0.2.0',
        'PORT': 3306,
        'OPTIONS': DB_OPTIONS
    },
}

DATABASE_SETS = {
    "default": [{'HOST': '10.0.1.1'},{'HOST': '10.0.1.2'}],
    "api": [{'HOST': '10.0.2.1'}]
}

DATABASES = {...}

DATABASES.update(pindb.populate_replicas(MASTER_DATABASES, DATABASE_SETS))


PINDB_DELEGATE_ROUTERS = ["myapp.router.Router"]
DATABASE_ROUTERS = ['pindb.GreedyPinDbRouter']

# default values which you can override:
PINDB_ENABLED = True
PINDB_PINNING_COOKIE = 'pindb_pinned_set'
PINDB_PINNING_SECONDS = 15