Jay Taylor's notes

back to listing index

Django Sharding notes

[web search]
Original source unknown
Tags:
Clipped on: 2013-02-07
Modify settings.py to automatically enable dblink extension:

Use 'DATABASE_OPTIONS', as per:

     https://docs.djangoproject.com/en/dev/releases/1.2/#specifying-databases

     https://docs.djangoproject.com/en/1.1/ref/databases/


e.g.:

    # Automatically initialize dblink capability extension.
    DATABASE_OPTIONS = {
        'init_command': 'CREATE EXTENSION IF NOT EXISTS dblink',
    }




Nested distributed filters:

            contactsFilter = Contact.objects.all_with_deleted() \
                .filter(number=self.fromNumber)

            receiptsFilter = Receipt.objects \
                .filter(contact__in=contactsFilter) \
                .only('message_id')

            oneDayAgo = datetime.utcnow() - timedelta(days=1)

            # NB: This is a good example of nested distributed filter.
            userMessages = UserMessage.objects \
                .filter(distributed=True, id__in=receiptsFilter) \
                .filter(Q(sent__gt=oneDayAgo) | Q(scheduled_at__gt=oneDayAgo)) \
                .order_by('-sent')


Results in:

SELECT "id", "user_id", "text", "hash", "created_at", "modified_at", "scheduled_at", "sent", "link", "shortlink_id", "direction", "threadId", "deleted", "typeId", shard FROM (
     SELECT *, 'shard_2' AS "shard" FROM dblink('dbname=db2 user=jay host=localhost sslmode=require', 'SELECT "id", "user_id", "text", "hash", "created_at", "modified_at", "scheduled_at", "sent", "link", "shortlink_id", "direction", "threadId", "deleted", "typeId" FROM "main_usermessage" WHERE ("id" IN (SELECT U0."id" FROM "main_receipt" U0 WHERE U0."contact_id" IN (SELECT U0."id" FROM "main_contact" U0 WHERE U0."number" = ''+16505750996'' )) AND ("sent" > ''2013-02-07 20:57:30.771185''  OR "scheduled_at" > ''2013-02-07 20:57:30.771185'' )) ORDER BY "sent" DESC') AS t("id" bigint, "user_id" bigint, "text" character varying(1500), "hash" character varying(32), "created_at" timestamp without time zone, "modified_at" timestamp with time zone, "scheduled_at" timestamp without time zone, "sent" timestamp without time zone, "link" character varying(30), "shortlink_id" bigint, "direction" character varying(14), "threadId" bigint, "deleted" boolean, "typeId" bigint)
     UNION
     SELECT *, 'shard_3' AS "shard" FROM dblink('dbname=db3 user=jay host=localhost sslmode=require', 'SELECT "id", "user_id", "text", "hash", "created_at", "modified_at", "scheduled_at", "sent", "link", "shortlink_id", "direction", "threadId", "deleted", "typeId" FROM "main_usermessage" WHERE ("id" IN (SELECT U0."id" FROM "main_receipt" U0 WHERE U0."contact_id" IN (SELECT U0."id" FROM "main_contact" U0 WHERE U0."number" = ''+16505750996'' )) AND ("sent" > ''2013-02-07 20:57:30.771185''  OR "scheduled_at" > ''2013-02-07 20:57:30.771185'' )) ORDER BY "sent" DESC') AS t("id" bigint, "user_id" bigint, "text" character varying(1500), "hash" character varying(32), "created_at" timestamp without time zone, "modified_at" timestamp with time zone, "scheduled_at" timestamp without time zone, "sent" timestamp without time zone, "link" character varying(30), "shortlink_id" bigint, "direction" character varying(14), "threadId" bigint, "deleted" boolean, "typeId" bigint)
     UNION
     SELECT *, 'shard_1' AS "shard" FROM dblink('dbname=sendhub user=jay host=127.0.0.1 sslmode=require', 'SELECT "id", "user_id", "text", "hash", "created_at", "modified_at", "scheduled_at", "sent", "link", "shortlink_id", "direction", "threadId", "deleted", "typeId" FROM "main_usermessage" WHERE ("id" IN (SELECT U0."id" FROM "main_receipt" U0 WHERE U0."contact_id" IN (SELECT U0."id" FROM "main_contact" U0 WHERE U0."number" = ''+16505750996'' )) AND ("sent" > ''2013-02-07 20:57:30.771185''  OR "scheduled_at" > ''2013-02-07 20:57:30.771185'' )) ORDER BY "sent" DESC') AS t("id" bigint, "user_id" bigint, "text" character varying(1500), "hash" character varying(32), "created_at" timestamp without time zone, "modified_at" timestamp with time zone, "scheduled_at" timestamp without time zone, "sent" timestamp without time zone, "link" character varying(30), "shortlink_id" bigint, "direction" character varying(14), "threadId" bigint, "deleted" boolean, "typeId" bigint)
     UNION
     SELECT *, 'shard_4' AS "shard" FROM dblink('dbname=db4 user=jay host=localhost sslmode=require', 'SELECT "id", "user_id", "text", "hash", "created_at", "modified_at", "scheduled_at", "sent", "link", "shortlink_id", "direction", "threadId", "deleted", "typeId" FROM "main_usermessage" WHERE ("id" IN (SELECT U0."id" FROM "main_receipt" U0 WHERE U0."contact_id" IN (SELECT U0."id" FROM "main_contact" U0 WHERE U0."number" = ''+16505750996'' )) AND ("sent" > ''2013-02-07 20:57:30.771185''  OR "scheduled_at" > ''2013-02-07 20:57:30.771185'' )) ORDER BY "sent" DESC') AS t("id" bigint, "user_id" bigint, "text" character varying(1500), "hash" character varying(32), "created_at" timestamp without time zone, "modified_at" timestamp with time zone, "scheduled_at" timestamp without time zone, "sent" timestamp without time zone, "link" character varying(30), "shortlink_id" bigint, "direction" character varying(14), "threadId" bigint, "deleted" boolean, "typeId" bigint)
     ) q0 ORDER BY "sent" DESC