Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL vs MySQL FriendFeed casestudy
{
"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,
}
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) )
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;
-- 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.