[Postgresql multi tenant] Multiple Databases vs Single Database | Performance Comparison

pg_db_tenants.py

import datetime
import os
import string
from random import randrange
from peewee import Model, DatabaseProxy, PostgresqlDatabase
from peewee import ForeignKeyField, BooleanField, TextField, IntegerField, CharField


NO_OF_DBS = 800
NO_OF_POSTS = 50
NO_OF_SELECTS = 1000

MONOLITH_DB_NAME = 'monolith'

CONTENT = string.ascii_letters

database = PostgresqlDatabase(MONOLITH_DB_NAME)
database_proxy = DatabaseProxy()

rands = [randrange(1, NO_OF_DBS * NO_OF_POSTS) for i in range(NO_OF_SELECTS)]


class ctx:
    schema = None


def timeit(f):
    def wrapped():
        then = datetime.datetime.now()
        for n in rands:
            f(n) == CONTENT
        now = datetime.datetime.now()
        delta = now - then
        return (f.__name__, delta)
    return wrapped


class MTModel(Model):
    class Meta:
        database = database_proxy


class MTPost(MTModel):
    content = TextField()
    published = BooleanField(default=False)


class MOPost(Model):
    site = TextField()
    content = TextField()
    published = BooleanField(default=False)

    class Meta:
        database = database


class SchemaPost(Model):
    content = TextField()
    published = BooleanField(default=False)

    class Meta:
        database = database


def create_dbs():
    for i in range(NO_OF_DBS):
        os.system(f'createdb site{i}')


def destroy_dbs():
    #for i in range(NO_OF_DBS):
    #    os.system(f'dropdb site{i}')
    os.system(f'dropdb {MONOLITH_DB_NAME}')


def populate_dbs():
    for i in range(NO_OF_DBS):
        dbname = f'site{i}'
        database = PostgresqlDatabase(dbname)
        database_proxy.initialize(database)
        MTPost.create_table()
        for j in range(NO_OF_POSTS):
            content = f'{CONTENT}:{j}'
            MTPost.create(content=content).save()


def populate_db_monolith():
    ctx.schema = 'public'
    database.execute_sql(f'set search_path to {ctx.schema}')
    MOPost.create_table()
    for i in range(NO_OF_DBS * NO_OF_POSTS):
        content = f'{CONTENT}:{i}'
        MOPost.create(site=i, content=content).save()


def populate_db_schema():
    for i in range(NO_OF_DBS):
        schemaname = f'site{i}'
        ctx.schema = schemaname
        database.execute_sql(f'create schema {schemaname}')
        database.execute_sql(f'set search_path to {schemaname}')
        SchemaPost.create_table()
        for j in range(NO_OF_POSTS):
            content = f'{CONTENT}:{j}'
            SchemaPost.create(content=content).save()


def select_mt(n):
    db = int(n / NO_OF_POSTS)
    post = (n % NO_OF_POSTS) or 1
    # print(NO_OF_POSTS, db, post)
    dbname = f'site{db}'
    database = PostgresqlDatabase(dbname)
    database_proxy.initialize(database)
    content = MTPost.get(MTPost.id == post).content
    #assert content.endswith(f':{post}')


def select_schema(n):
    db = int(n / NO_OF_POSTS)
    post = (n % NO_OF_POSTS) or 1
    # print(NO_OF_POSTS, db, post)
    ctx.schema = f'site{db}'
    database.execute_sql(f'set search_path to {ctx.schema}')
    content = SchemaPost.get(SchemaPost.id == post).content
    #assert content.endswith(f':{post}')


def select_monolith(n):
    ctx.schema = 'public'
    database.execute_sql(f'set search_path to {ctx.schema}')
    content = MOPost.get(MOPost.id == n).content
    #assert content.endswith(f':{n}')


def prepare():
    destroy_dbs()

    #create_dbs()
    #populate_dbs()

    os.system(f'createdb {MONOLITH_DB_NAME}')
    populate_db_schema()
    populate_db_monolith()


def measure():
    delta_schema = timeit(select_schema)()
    #delta_mt = timeit(select_mt)()
    delta_mo = timeit(select_monolith)()
    print(delta_schema, delta_mo)
    # print(delta_schema, delta_mo, delta_mt)


if __name__ == '__main__':
    prepare()
    measure()