PostgreSQL vs MySQL FriendFeed casestudy

entitiy_example.json

{
    "id": "71f0c4d2291844cca2df6f486e96e37c",
    "user_id": "f48b0440ca0c4f66991c4d5f6a078eaf",
    "feed_id": "f48b0440ca0c4f66991c4d5f6a078eaf",
    "title": "We just launched a new backend system for FriendFeed!",
    "link": "http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c",
    "published": 1235697046,
    "updated": 1235697046,
}

python_sample.py

import psycopg2
import psycopg2.extras
import binascii
import datetime
conn = psycopg2.connect("...")
cur = conn.cursor()
psycopg2.extras.register_hstore(cur)
user_data = {
     #Notice smart usage of \x.... for postgresql compatible hex format so that I can ::bytea it later on
     "id": "\x71f0c4d2291844cca2df6f486e96e37c", 
     "user_id": "\xf48b0440ca0c4f66991c4d5f6a078eaf",
     "feed_id": "\xf48b0440ca0c4f66991c4d5f6a078eaf",
     "title": "We just launched a new backend system for FriendFeed!",
     "link": "http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c",
     #Notice the variables as strings, I can ::integer it while indexing
     "published": "1235697046",
     "updated": "1235697046",
}

cur.mogrify("INSERT INTO entities (id, updated, body) VALUES(%s, %s, %s)", (bytearray("71f0c4d2291844cca2df6f486e96e37c".decode("hex")), datetime.datetime.now(), user_data) )

sample_entity.sql

SELECT 'id=>71f0c4d2291844cca2df6f486e96e37c,
	user_id=>f48b0440ca0c4f66991c4d5f6a078eaf,
	feed_id=>f48b0440ca0c4f66991c4d5f6a078eafr, 
	title=> "We just launched a new backend system for FriendFeed!", 
	link=> "http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c", 
	published=> 1235697046, 
	updated=> 1235697046'::hstore;

schema.sql

 -- MySQL version of FriendFeed entities table

CREATE TABLE entities (
    added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    id BINARY(16) NOT NULL,
    updated TIMESTAMP NOT NULL,
    body MEDIUMBLOB,
    UNIQUE KEY (id),
    KEY (updated) 
  ) ENGINE=InnoDB;

 -- PostgreSQL version of FriendFeed entities table
CREATE TABLE entities (
    added_id BIGSERIAL NOT NULL,
    id BYTEA NOT NULL,
    updated TIMESTAMP NOT NULL,
    body HSTORE NOT NULL,
    CONSTRAINT entities_pk PRIMARY KEY (added_id),
    CONSTRAINT entities_id UNIQUE (id)
);

CREATE INDEX entities_update_idx ON entities (updated);