This article introduces to GIN index in the Postgres database and how to use it in the Rails application.

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

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

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

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