SQL Query Guidelines


Using LIKE Statements

The most common way to search for data is using the LIKE statement. For example, to get all issues with a title starting with "WIP:" you'd write the following query:

FROM issues
WHERE title LIKE 'WIP:%';

On PostgreSQL the LIKE statement is case-sensitive. On MySQL this depends on the case-sensitivity of the collation, which is usually case-insensitive. To perform a case-insensitive LIKE on PostgreSQL you have to use ILIKE instead. This statement in turn isn't supported on MySQL.

To work around this problem you should write LIKE queries using Arel instead of raw SQL fragments as Arel automatically uses ILIKE on PostgreSQL and LIKE on MySQL. This means that instead of this:

Issue.where('title LIKE ?', 'WIP:%')

You'd write this instead:




table = Issue.arel_table


For PostgreSQL this produces:

FROM issues
WHERE (title ILIKE 'WIP:%' OR foo ILIKE 'WIP:%')

In turn for MySQL this produces:

FROM issues
WHERE (title LIKE 'WIP:%' OR foo LIKE 'WIP:%')

LIKE & Indexes

Neither PostgreSQL nor MySQL use any indexes when using LIKE / ILIKE with a wildcard at the start. For example, this will not use any indexes:

FROM issues
WHERE title ILIKE '%WIP:%';


MySQL provides no known solution to this problem. Luckily PostgreSQL does provide a solution: trigram GIN indexes. These indexes can be created as follows:

ON table_name
USING GIN(column_name gin_trgm_ops);






class MigrationName < ActiveRecord::Migration

For example:

class AddUsersLowerUsernameEmailIndexes < ActiveRecord::Migration

  def up
    return unless Gitlab::Database.postgresql?


  def down
    return unless Gitlab::Database.postgresql?

    remove_index :users, :index_on_users_lower_username
    remove_index :users, :index_on_users_lower_email

Plucking IDs

This can't be stressed enough: never use ActiveRecord's pluck to pluck a set of values into memory only to use them as an argument for another query. For example, this will make the database very sad:

projects = Project.all.pluck(:id)

MergeRequest.where(source_project_id: projects)


MergeRequest.where(source_project_id: Project.all.select(:id))

The only time you should use pluck is when you actually need to operate on the values in Ruby itself (e.g. write them to a file). In almost all other cases you should ask yourself "Can I not just use a sub-query?".


UNIONs aren't very commonly used in most Rails applications but they're very powerful and useful. In most applications queries tend to use a lot of JOINs to get related data or data based on certain criteria, but JOIN performance can quickly deteriorate as the data involved grows.


FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE projects.name ILIKE '%gitlab%'
OR namespaces.name ILIKE '%gitlab%';


SELECT projects.*
FROM projects
WHERE projects.name ILIKE '%gitlab%'


SELECT projects.*
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE namespaces.name ILIKE '%gitlab%';





Project.from("(#{union.to_sql}) projects")

Ordering by Creation Date

When ordering records based on the time they were created you can simply order by the id column instead of ordering by created_at. Because IDs are always unique and incremented in the order that rows are created this will produce the exact same results. This also means there's no need to add an index on created_at to ensure consistent performance as id is already indexed by default.



In Rails you have to use this by creating SQL fragments:



FROM projects
    SELECT 1
    FROM users
    WHERE projects.creator_id = users.id
    AND users.foo = X