Compare RPostgres and RPostgreSQL Package

R is a great language for R&D. It's fast to write prototypes, and has great visualisation tools. One of constraints of R is it stores the data in system memory. When the data becomes too big to fit in the memory, we asked the user has to manually split the dataset and then aggregate the output later. This process is inefficient and error prone for a non-technical user.

I started an R development project to automate this split-aggregate process. A viable solution is to store the whole data in PostgreSQL, and let R to fetch one small chunk of the data at a time, do the calculation, and then save the output to PostgreSQL. This solution requires frequently data transferring between these two systems, which could be a bottleneck in performance. So I did a comparison of two R packages that interface R and PostgreSQL.

RPosrgreSQL

is supported and developed in the Google Summer of Code 2008 program. It is currently out of development. The last publication is in 2013.

RPostgres

is a new package which provides similar functionality to RPostgreSQL but rewrite using C++ and Rcpp. The development is led by Kirill Müller.

Based on my testing, the RPostgres package is about 30% faster than RPostgreSQL.

The testing set-up is quite simple: I write an R script to send data to and get data out from a remote PostgreSQL database. It logs how long each task takes to complete in R. To avoid other factors that can affect the speed, it repeats this process 20 times and use the minimal run-time as the final score. The dataset transferred between R and PostgreSQL is a flat table with three columns and the number of rows varies from ten thousand to one million.

The run-time in seconds are plotted against number for rows for each package and operation.

Here is a summary of what I observed:

1. RPostgreSQL is slower than RPostgres. For getting data out, it's 75% slower, which is massive! For writing, difference is closer, it's about 20%. When combine both scores together, it is about 33% slower.
2. Particularly, it's slower to read than to write for RPostgreSQL package, the ratio is about 1.5. While as it's quicker to read than to write for RPostgres, the ratio is about 0.8. This is an interesting observation.
3. Both package has a nice feature - the reading/writing time linearly depends on the number of rows. This makes the time estimation reliable. I would be confident to say that for 2 millions rows, it takes RPostgres package about 6 seconds to read.

I don't why which part of implementation makes the RPostgres faster. I guess its the usage of C++ and the magical Rcpp package.

Here is the script just in case you want to your own tests.

library(data.table)                     
library(ggplot2)
library(microbenchmark)
library(RPostgreSQL)
library(DBI)   
                                        # config for PostgreSQL database
host.name <- NULL
database.name <- NULL
postgres.user <- NULL
postgres.passwd <- NULL
postgres.port <- NULL
temporary.table.name <- NULL

                                        # config for testing
nrows <- seq(10 * 1e3, 1 * 1e6, length = 10)
repeats <- 20


                                        # open PostgreSQL connection
pg.RPostgreSQL <- dbConnect(dbDriver("PostgreSQL"),
                           host = host.name,
                           dbname = database.name,
                           user = postgres.user,
                           password = postgres.passwd,
                           port = postgres.port)
pg.RPostgres <- dbConnect(RPostgres::Postgres(),
                         host = host.name,
                         dbname = database.name,
                         user = postgres.user,
                         password = postgres.passwd,
                         port = postgres.port)

ReadWriteWarpper <- function(pg.connection) {
                                        # helper function 
    write <- function() dbWriteTable(pg.connection, temporary.table.name, dt, overwrite = TRUE)
    read <- function() dbReadTable(pg.connection, temporary.table.name)

    var <- list()
    for (n in nrows) {
                                        # create a dataset
        dt <- data.table(x = sample(LETTERS, n, T),  # character
                        y = rnorm(n), # double
                        z = sample.int(n, replace=)) # integer

                                        # read and write once first.
        write()
        read()

                                        # run and log run-time
        res <- microbenchmark(write(),
                             read(),
                             times = repeats)

                                        # parse 
        var[[as.character(n)]] <- data.table(num_row = n,
                                            operation = res$expr,
                                            time = res$time)
    }

                                        # aggregate and return
    rbindlist(var)
}

                                        # run
df0 <- ReadWrite(pg.RPostgres); df1 <- ReadWrite(pg.RPostgreSQL)
df0$pacakge <- "RPostgres"; df1$package <- "RPostgreSQL"
df <- rbind(df0, df1)
plot.df <- df[, min(time) / 1e9, .(num_row, operation, package)]

## generate plot
plot.df[, operation := gsub("\\(|\\)", "", operation)]
ggplot(plot.df, aes(x=num_row, y=V1, col = package)) +
    geom_path() +
    geom_point() +
    facet_wrap(~operation) +
    theme_bw() +
    labs(x="Number of rows",
         y="Run time (sec)"
         )

How to Create a Screencast GIF in Emacs

I've always wanted to create a GIF using Emacs to demonstrate some features, it just looks so cool. I finally got a chance after attending the Leeds Code Dojo. The final exercise is bit unusual; we have to write a basic expression evaluation program without using the eval function in whatever language we choose. The first problem we had was to figure out the order of sub-expression to evaluate. For example, in (5 * (2 + 1) ) expression, we know we firstly add 2 to 1 to get the 3, and then multiply 3 by 5. It sounds trivial but it is actually hard to write a program to do that.

I used regular expression¹ to locate the most inner expression to evaluate, then replaced the expression with its evaluating result, and continued these two steps until there was no expression².

The above GIF shows each step in a expression evaluation program written in Emacs Lisp.

This post show how to make GIF in Emacs on Ubuntu system.

sudo apt-get install recordmydesktop mplayer imagemagick

(package-install 'camcorder)

(with-current-buffer "Demo_Buffer"
  (insert "Start to demo: "))

(defun yt/camcorder-show-off ()
  (interactive)
  (goto-char (point-min))
  (insert "going to show you something cool, don't blink your eyes.")
  (sleep-for 2)
  ;;;; apply some functions
  (insert "\nExciting isn't?"))

(define-key camcorder-mode-map [f5] 'yt/camcorder-show-off)

Migrate to Ubuntu

My MacBookPro's hard drive stooped working last week and I managed to recover most of the data from a Time Machine back-up 6 months ago. But I couldn't get the mu4e and mu working. I feed up with googling, trying, and decide to immigrate to Ubuntu. It would save me from a lot of frustrations and time in making my Mac and office PC work the same way.

Ideally, I will built a Ubuntu on Mac which is exactly the same as the one on my office PC, by just copy over everything ¹. As a minimalist, I decided to build the system from scratch and install software one by one so that I can have an better understanding of what are the necessities for me.

In the last few days, I become extra mindful about the what and how I used the Ubuntu system in the office, and realise the things I need can be grouped into three categories:

1. Configuration,
   1. the .ssh folder for the ssh-agent,
   2. the .fonts folder for new fonts,
   3. the .mbsynrc file for sync emails,
   4. the .ledgerrc.
2. Software for
   1. Development: like git, gcc, Emacs, and R.
   2. Writing: org-mode, LaTeX,
   3. Email: mu, mu4e, and mbsync.
   4. Finance: ledger.
3. Personal git repositories
   1. public reposity on GitHub,
   2. private reposities on BitBucket

For 1), since they are small, I can zip up and copy over, or even better, create a git repository so that sync on two machines becomes better easier.

For 2), I need to find the software's package name in the Ubuntu's software repository, and then install all of them by a script. The dependencies should be resolved automatically.

For 3), I need to create a shared folder between the host system and the Ubuntu system, and then copy over the ~/git/ folder.

It really sounds like a plan! I am going to download the Ubuntu installation file now and hopefully the transition will be very smooth.

My Expeirence with Repetitive Strain Injury (RSI)

Someday I typed more than 80 thousand times just in Emacs. This is pretty awesome at first sight but it can cause serious health problem.

Last month, I felt burning pain of my forearms. It is an symptom of Repetitive strain injury (RSI). I realised that if continue typing like that, one day I will never able to do programming, like the Emacs celebrities in Xah Lee' article about RSI.

Since then I've deliberately tried to avoid aimless and unproductive typing, take more typing breaks, think though things before trying, write more on paper.

Conditions are getting better: I don't feel server pain any more, only sometimes uncomfortable.

But I need to find a better way to improve it. Because sometimes I got the idea, but can't touch the keyboard. This feeling really suck.

So I investigated the Hydra package and use it to group related commands together so that use only two keys are needed to perform frequent tasks.

For example, to search something in current project, instead of typing M-x helm proj grep, that's 16 keystrokes, I only need F5 G with Hydra. The implementation is listed in this post.

But calling functions/commands in Emacs counts only a small proportion of my typing; most of the time, I write code and report.

This is where Yasnippets kicks in, it enable me to type less without losing quality. For example, I use this snippet quite often when writing R code,

res <- sapply(seq_len(n), function(i) {
    ## 
})

That's more than 40 keystrokes. Yasnippets can short it to only 6s! After I type sapply and then hit TAB, it will expand to the region above.

I will investigate the Yasnippet package next week. If you know any good tutorials for Yasnippet or snippets for writing R code, please share your resources.

Start Enjoying Regular Expression In Emacs

The search-forward-regexp, replace-match, and match-string functions work together nicely, and makes my job much easier and enjoyable!

I am writing a release notes for the a software updates. Part of the process is to associate the SVN Revision number that relates to important changes, so that others can backtrack and review the code and see what exactly has been implemented.

In Phabricator, the revision number will be render automatically. Clicking them takes me to the exact revision, showing the difference with previous version. But the documentation will be eventually built by Sphinx and hosted on a remote server. So I have to manually add the URL to all the SVN revision number. For example, to replace rS1234 to

[[http://phabricator.domain.co.uk/rS1234][rS1234]]

There are 31 revision number in the whole document. I could do it manually but for the long term benefits, it would be more efficient write a function to process it automatically, maybe others can use it as well.

(defvar revision-pattern "rS[0-9]+"
  "The RegExp pattern of the SVN revision number")

(defvar repo-url "http://10.0.0.11/"
  "The IP address of the SVN repository")

(defun yt/add-link-to-SVN-revision-number ()
  "add links to svn commits identifier"
  (interactive)
  (while (search-forward-regexp revision-pattern)
    (let* ((commit (match-string 0))
           (link (concat repo-url commit)))
      (replace-match "")
      (org-insert-link nil link commit))))

(replace-match (concat "[[" link
                       "][" commit "]]"))                       

[[http://10.0.0.11/[[http://10.0.0.11/rS1234][rS1234]]][[[http://10.0.0.11/rS1234][rS1234]]]]