Speed up time-sensitive database process in Rails applications using a Connection pool, Threads, and Arel

Sometimes dealing with DB processes in a Rails application consume a lot of time. Often there are a bunch of small and simple DB queries that go endless. For example, bulk insertions or bulk updates in tables. Dividing it into various background jobs is an idle solution. In a few scenarios, we can't prefer background jobs e.g. the change you apply, makes the existing data invalid. In this article, we will see how to complete a large set of DB queries under minutes which would otherwise take hours if not optimized. It's a better practice to create a rake

Better performance with Index type `varchar_pattern_ops` operator_class in Rails

Rails has provision to provide order of indexing on a column for better performance of like queries. The varchar_pattern_ops improves the performance of like queries by 4 times i.e 4x. For example, lets have this like query on name column (that has sequential index.) Select * from users where name like 'John%' We might have added a regular Rails index in migration for this name column as add_index :users, :name - This will generate a sequential index. The same can be made 4 times faster using btree index by adding xxx_pattern_ops options add_index

Prepared statements in Rails

Rails 3.1 and above has an awesome feature of prepared_statements. The basic idea behind prepared statements is to compile SQL statements once and cached for it future use. In other words - The benefit to prepared statements is that the database does not have to compile a query plan for every piece of SQL sent to it, potentially saving a lot of time. Checkout an excellent blog(blog) to know more on prepared_statements. Recently, I had to get raw sql from AR object so that I can pass it to EXPLAIN statement to get total number of

Migrating (export and import) PostgreSQL database from Heroku to SoftLayer (or dedicated server)

Recently we had requirement in one of the project to migrate Heroku PostgreSQL database to SoftLayer (dedicated server) psql database. One major issue we faced during migration i.e. import Heroku pg database to SoftLayer database using pg_restore is the version conflict between the dump file that Heroku generates (for psql backups) and the old version 8.4. of PostGreSQL that SoftLayer instance has. We upgraded PostgreSQL to version 9.1 on SoftLayer. Remember to upgrade version above Heroku dump file or use same version but not below versions as pg_restore doesn't handle backward compatibility properly for dump