YSQL Sample Program - user messages/actions table

ysql_user_msgs.py

# Dependencies:
# On CentOS you can install psycopg2 thus:
#
#  sudo yum install postgresql-libs
#  sudo yum install python-psycopg2

import psycopg2;
import time

conn = psycopg2.connect("host=localhost dbname=yugabyte user=yugabyte port=5433")
conn.autocommit = True

cur = conn.cursor()

start_time = time.time()
cur.execute("""DROP TABLE IF EXISTS user_actions""");
now_time = time.time()
print "Dropped (if exists): user_actions table"
print("Time: %s ms ---" % ((now_time - start_time) * 1000))

start_time = time.time()
cur.execute("""
      CREATE TABLE user_actions(
         id integer,
         msg_id integer,
         msg    text,
         PRIMARY KEY(id HASH, msg_id ASC)
      )
  """)
now_time = time.time()
print "Create user_actions table."
print("Time: %s ms ---" % ((now_time - start_time) * 1000))

cur.execute("""PREPARE insert_user_action(int, int, text) AS
                 INSERT INTO user_actions(id, msg_id, msg) VALUES ($1, $2, $3)""")

num_users = 2
num_msgs = 20000

for user_id in range(num_users):
  now_time = time.time()
  for msg_id in range(num_msgs):
     cur.execute("""EXECUTE insert_user_action(%s, %s, %s)""",
                       (user_id, msg_id, "msg-"+str(msg_id), ));
  delta_insert = (time.time() - now_time)*1000
  print("inserted: %d rows; %s ms" % (num_msgs, delta_insert))

for i in range(5):
  now_time = time.time()
  cur.execute("select count(*) from user_actions where id=0");
  row = cur.fetchone()
  delta_select = (time.time() - now_time)*1000
  num_rows=row[0]
  print("select count(*) of messages for one user=%d rows; time: %s ms" % (num_rows, delta_select))

for i in range(5):
  now_time = time.time()
  cur.execute("select msg from user_actions where id=0 order by msg_id ASC limit 1");
  row = cur.fetchone()
  delta_select = (time.time() - now_time)*1000
  msg_txt = row[0]
  print("retrieve first message: %s, time: %s ms" % (msg_txt, delta_select))

for i in range(5):
  now_time = time.time()
  cur.execute("select msg from user_actions where id=0 order by msg_id DESC limit 1");
  row = cur.fetchone()
  delta_select = (time.time() - now_time)*1000
  msg_txt = row[0]
  print("retrieve last message: %s, time: %s ms" % (msg_txt, delta_select))

for i in range(5):
  now_time = time.time()
  cur.execute("select msg from user_actions where id=0 and msg_id >= 5 limit 2");
  row = cur.fetchone()
  delta_select = (time.time() - now_time)*1000
  msg_txt = row[0]
  print("retrieve 2 messages with msg_id >= 5: %s, time: %s ms" % (msg_txt, delta_select))