Optimiza tu aplicación rails utilizando índices en Postgres

Algunos de los factores más comunes y que más impactan el “performance” de una aplicación están relacionados con las consultas en base de datos muy grandes, por lo tanto, entender cómo detectar y solucionar estos problemas es de gran relevancia en cualquier equipo de desarrollo backend.

Recientemente en Ayenda se detectó que una consulta a una tabla de nuestra base de datos estaba tardando demasiado tiempo en ser procesada, la consulta se hacía a una tabla con millones de registros y tardaba alrededor de 7.8 segundos en ser procesada en un entorno de desarrollo local.

Existen diferentes estrategias para abordar la solución a este problema, una de ellas y la implementada en este caso, es el uso de índices en la base de datos. Por tal motivo, desde el equipo de Tech en Ayenda queremos compartirles este blog post donde hablaremos de la definición, creación y aplicación de algunos tipos de índices en PostgreSQL bajo una aplicación construida con Ruby on Rails.

Temas tratados en el blog:

• Indexación en bases de datos.
• Definición de un índice en una base de datos.
• Índice B-Tree
• Índice GIN (Generalized Inverted Indexes).
• Creación de índices en Ruby on Rails.
• Uso del índice B-Tree.
• Creación y uso de índice GIN en Ruby on Rails
• Índices multicolumna
• Combinando multiples índices

Indexación en bases de datos.

Supongamos que queremos encontrar un usuario con un número de identificación específico en nuestra base de datos. Utilizando ActiveRecord podríamos ejecutar un query como el siguiente.

User.where(id_number: 123456789)

Cuando ejecutamos el query anterior, por lo general, PostgreSQL hará un escaneo secuencial para leer todas las filas de la tabla User buscando algún registro donde la columna id_number sea igual al número 123456789. Esto puede convertirse en un problema cuando la consulta se hace en una tabla que contiene millones de datos, debido a que el sistema tiene que buscar en toda la tabla para encontrar un registro dado, lo que puede impactar fuertemente en el perfomance de la base de datos. Sin embargo, al agregar un índice a la columna de id_number, PostgreSQL podrá realizar el query utilizando el índice creado para hacer un escaneo de la tabla mucho más eficiente.

A diferencia de un escaneo secuencial, un escaneo por índice es mucho más rápido, ya que en lugar de hacer una consulta donde el tiempo de ejecución está relacionado linealmente con la cantidad de datos ‘O(n)’, se hará un consulta con un performance logarítmico ‘O(log n)’.

Definición de un índice en una base de datos (DB).

Un índice, en palabras simples, es una estructura de datos que se define sobre una o varias columnas y permite ubicar de forma eficiente un registro determinado en una tabla. Una vez creado un índice, el sistema lo actualizará cuando se modifique la tabla y lo utilizará en consultas determinadas, por lo tanto, los índices aumentan la velocidad en las consultas solo para las instrucciones SELECT, UPDATE y DELETE, no en comandos de inserción CREATE. Adicionalmente un índice por definición debe ocupar un espacio en el disco duro, donde almacenará la información de su estructura de datos. Existen diferentes tipos de índices y cada uno utiliza un algoritmo particular que se adapta mejor al tipo de dato y al tipo de consulta. Actualmente PostgreSQL soporta varios tipos de índices, algunos de ellos son: B-tree, Hash, GiST y GIN.

Índice B-Tree

El índice B-Tree es el más utilizado debido a su versatilidad y además es el índice creado por defecto en PostgreSQL cuando no se especifica el tipo. B-Tree es un tipo especial de estructura de datos de tipo árbol autobalanceado en el que cada nodo puede contener más de una clave y más de dos nodos hijos. Este tipo de índice es utilizado comúnmente en datos de tipo numérico, fecha/hora y de tipo carácter (con algunas limitaciones). La siguiente imagen muestra la estructura de datos para un índice B-Tree.

Por lo general PostgreSQL considerará utilizar el índice B-Tree siempre que una columna indexada esté involucrada en una consulta que utilice alguno de los siguientes operadores: (<, <=, =, >=, >), BETWEEN, IN y condiciones como IS NULL o IS NOT NULL. PostgreSQL también considera utilizar este tipo de índice para consultas que involucran operadores de patrones de coincidencia como LIKE y ~ siempre y cuando el patrón sea una constante y esté en el comienzo de la cadena, por ejemplo, LIKE 'foo%' o ~ '^ foo', pero nunca para LIKE '%foo'. Por último también es posible utilizarlo con los operadores ILIKE y ~*, pero solo si el patrón comienza con caracteres no alfabéticos, es decir, caracteres que no son afectados por conversión de mayúsculas o minúsculas.

Índice GIN (Generalized Inverted Indexes).

El índice GIN es utilizado en tipos de datos que almacenan información estructurada como arrays, jsonb o hstore, adicionalmente se utiliza con frecuencia en implementaciones para optimizar búsquedas de texto. Este índice almacena un conjunto de pares (clave, lista), donde la lista es un conjunto de IDs de las filas donde aparece la clave. Un ID de una lista puede aparecer en otras listas, ya que un elemento puede contener más de una clave. Cada clave se almacena sólo una vez, por lo que el índice GIN es muy compacto para los casos en que la misma clave aparece muchas veces en los registros. Un ejemplo de la estructura de datos para un índice GIN se muestra a continuación.

Este tipo de índices admite diferentes estrategias de indexación para cada tipo de dato, por ende, los operadores con los que se puede usar este índice varían según la estrategia de indexación, por ejemplo, la implementación de GIN por defecto en PostgreSQL para un array admite consultas con los siguientes operadores (<@, @>, =, &&). Ejemplos:

• [‘a’, ‘b’, ‘c’] @> [‘a’, ‘b’, ‘b’, ‘c’] evalúa si [‘a’, ‘b’, ‘c’] contiene a [‘a’, ‘b’, ‘b’, ‘c’] (verdadero)
• [1, 1, 4] <@ [4, 3, 2, 1] evalúa si [1, 1, 4] es contenido por [4, 3, 2, 1] (verdadero)
• [1, 2] && [2, 3] evalúa si los dos array se superponen (verdadero)

Creación de índices en Ruby on Rails.

Ahora bien, Ruby on Rails nos brinda la capacidad gestionar la creación de índices mediante migraciones con ActiveRecord, por ejemplo, con la siguiente instrucción podemos crear la tabla IntegerTable con dos columnas de tipo entero (integer_a, integer_b) y con un índice para la columna integer_b.

rails g model IntegerTable integer_a:integer integer_b:integer:index

La instrucción anterior genera la migración que se muestra a continuación, donde podemos observar el método add_index encargado de generar el índice a la tabla y columna correspondiente. Si no se especifica el tipo de índice se crea por default un índice tipo B-Tree.

class CreateIntegerTables < ActiveRecord::Migration[7.0]
  def change
    create_table :integer_tables do |t|
      t.integer :integer_a
      t.integer :integer_b

      t.timestamps
    end
    add_index :integer_tables, :integer_b
  end
end

Uso del índice B-Tree.

Para analizar el uso de los índices B-Tree en diferentes tipos de datos utilizaremos algunas tablas y columnas con información creada previamente. Cada tabla tiene dos columnas del mismo tipo y con la misma información, sin embargo una de las columnas estará indexada con B-tree y la otra no. En los ejemplos utilizados a continuación contamos con alrededor de un millón de registros aleatorios para cada tabla. Para observar los resultados utilizaremos el IDE, DBeaver Community Edition (https://dbeaver.io/) y el comando EXPLAIN ANALYZE en cada query para conocer los detalles de las consultas ejecutadas. En el siguiente repositorio se encuentra el proyecto utilizado para todas la consultas mostradas en este blog: (https://github.com/julianpz21/database_indexes)

Índice B-Tree en columnas tipo números.

Analicemos el comportamiento de dos consultas realizadas a la tabla Integer_tables, la primera consulta se realiza sobre la columna sin indexar (integer_a) y la segunda consulta sobre la columna indexada mediante B-tree (integer_b). En las siguientes figuras podemos observar las diferencias entre el tiempo de ejecución y el tipo de query de cada una de las consultas.

La consulta sobre la columna integer_a utiliza un Parallel Seq Scan y se demora en ejecutarse alrededor de 39.399 ms.

La consulta sobre la columna integer_b utiliza un Index Scan y se demora en ejecutarse alrededor de 0.054 ms, algo así como 730 veces más rápido.

Índice B-Tree en columnas tipo fecha.

Igual que el ejemplo anterior, tenemos una tabla llamada date_tables con dos columnas (date_a, date_b), donde la última es indexada mediante B-tree. De nuevo, en las siguientes figuras podemos observar las diferencias entre el tiempo de ejecución y el tipo de query de cada una de las consultas.

La consulta sobre la columna date_a utiliza un Parallel Seq Scan y tarda en ejecutarse alrededor de 43.293 ms.

La consulta sobre la columna date_b utiliza un Index Scan y tarda en ejecutarse alrededor de 0.054 ms, algo así como 800 veces más rápido.

Índice B-Tree en columnas tipo strings.

Para analizar este escenario contamos con una tabla llamada string_tables que tiene dos columnas (string_a, string_b), donde la última es indexada mediante B-tree. De nuevo, en las siguientes figuras podemos observar las diferencias entre el tiempo de ejecución y el tipo de query de cada una de las consultas.

La consulta sobre la columna string_a utiliza un Parallel Seq Scan y tarda en ejecutarse alrededor de 52.202 ms.

La consulta sobre la columna string_b utiliza un Index Scan y tarda en ejecutarse alrededor de 0.062 ms, algo así como 841 veces más rápido.

Índice B-tree y el operador LIKE e ILIKE

Ahora bien, las consultas en columnas que tienen caracteres son algo especiales debido a que podemos utilizar en ellas operadores de patrones de coincidencia como LIKE o ILIKE, sin embargo se debe tener especial cuidado al utilizarlos. Para entender esto, haremos algunos ejemplos que nos mostrarán cuando la base de datos utiliza o no un Index Scan aun cuando la consulta se realiza sobre la columna indexada string_b.

Caso LIKE: PostgreSQL utilizará el índice B-tree (Index Scan) cuando una wildcard como ‘%’ NO está definida en el comienzo de la cadena, veamos un ejemplo:

Se realiza un Index Scan cuando la wildcard ‘%’ No está al comienzo de la cadena de caracteres.

En el caso contrario, PostgreSQL No utilizará el índice cuando una wildcard como ‘%’ está definida al comienzo de la cadena.

Observemos en el siguiente ejemplo como se realiza un Parallel Seq Scan cuando la wildcard ‘%’ está al comienzo de la cadena de caracteres. PostgreSQL no utiliza el índice en este tipo de query.

Caso ILIKE: En este tipo de consultas PostgreSQL utilizará el índice solo si el patrón comienza con caracteres no alfabéticos, es decir, caracteres que no se ven afectados por la conversión de mayúsculas o minúsculas.

Se realiza un Index Scan con el operador ILIKE cuando el patrón de búsqueda comienza con un carácter no alfabético, esto se muestra en la siguiente imagen donde el primer carácter del string en el query comienza con el número 1.

En caso contrario, PostgreSQL no utilizará el índice B-tree para consultas con el operador ILIKE. En la siguiente imagen vemos que se realiza un Parallel Seq Scan con el operador ILIKE cuando el patrón de búsqueda comienza con un carácter alfabético.

Creación y uso de índice GIN en Ruby on Rails

El índice GIN puede ser utilizado en columnas de tipo arrays, jsonb, hstore y texto. Un ejemplo que muestra creación de este tipo de índice en una columna de tipo array de enteros se muestra a continuación, en donde se especifica en la migración utilizar un índice GIN solo para la columna array_b.

class CreateArrayTables < ActiveRecord::Migration[7.0]
  def change
    create_table :array_tables do |t|
      t.integer :array_a, array: true
      t.integer :array_b, array: true

      t.timestamps
    end
    add_index :array_tables, :array_b, using: 'gin'
  end
end

En las siguiente figuras podemos observar y comparar la diferencia en el tiempo de ejecución entre una consulta que se realiza sobre una columna sin indexar y otra que se realiza sobre una columna indexada con GIN. La consulta sobre la columna indexada array_b es más eficiente.

Se utiliza Index Scan cuando la consulta se hace sobre la columna con índice GIN (array_b).

Índices multicolumna

Un índice puede ser definido en más de una columna. PostgreSQL permite indexar hasta un máximo de 32 columnas, pero únicamente para índices tipo B-tree, Gist, Gin y Brin. Este tipo de índices es útil cuando tenemos consultas que involucran el operador AND entre varias columnas. Un índice multicolumna se puede crear en Ruby on Rails de la siguiente manera

class CreateMulticolumnIndexTables < ActiveRecord::Migration[7.0]
  def change
    create_table :multicolumn_index_tables do |t|
      t.integer :integer_a
      t.integer :integer_b

      t.timestamps
    end
    add_index :multicolumn_index_tables, %i[integer_a integer_b]
  end
end

PostgreSQL utiliza el índice multicolumna creado cuando en una consulta intervienen las dos columnas indexadas con el operador AND. Observemos como el resultados del Explain Analyze nos muestra el uso de Index Scan.

El índice multicolumna también es utilizado cuando se hace una consulta solo para la columna integer_a, la cual corresponde a la columna definida más a la izquierda en la creación del índice.

Sin embargo, PostgreSQL NO utiliza el índice multicolumna para las consultadas que involucran el operador OR o solamente una columna que corresponda a la definida más a la derecha en la creación del índice, veamos los siguientes ejemplo para observar el comportamiento.

NO se realiza un Index Scan en un índice multicolumna para las consultas con el operador OR.

NO se utiliza el índice multicolumna cuando en la consulta solo interviene la columna definida más a la derecha en el índice. Recordemos que el orden de las columnas en la creación del indice fue [integer_a integer_b]

Combinando multiples índices

PostgreSQL tiene la capacidad de solucionar el inconveniente anterior combinando múltiples índices definidos individualmente (incluidos múltiples usos del mismo índice). De esta forma podemos ejecutar consultas con múltiples condiciones AND y OR a través del uso de los índices individuales de cada columna.

Para ejemplificar este comportamiento, crearemos índices individualmente para cada columna en una migración de la siguiente manera.

class CreateCombiningIndexTables < ActiveRecord::Migration[7.0]
  def change
    create_table :combining_index_tables do |t|
      t.integer :integer_a
      t.integer :integer_b

      t.timestamps
    end
    add_index :combining_index_tables, :integer_a
    add_index :combining_index_tables, :integer_b
  end
end

Luego de correr la migración anterior, podemos realizar una consulta con varias columnas indexadas individualmente, adicionalmente podemos utilizar el operador OR y PostgreSQL utilizará los índices creados en la consulta.

Como se muestra a continuación, en la consulta se utilizan los índices individuales de cada columna, a esto se le llama combinar múltiples índices.

One more thing! Cómo agregar índices a una tabla de una base de datos en producción?

Como se mencionó anteriormente, PostgreSQL y muchos otros RDBMS bloquean el acceso de escritura en la tabla mientras un índice es creado. Por supuesto, esto es inaceptable cuando nuestro proyecto está en producción y no podemos permitir un tiempo de inactividad considerable mientras se crea el índice. Hay que tener en cuenta que mientras más grande sea la tabla a indexar, más tiempo se tardara nuestro índice en ser creado.
Para sortear este problema, PostgreSQL nos ofrece una herramienta que construirá el índice sin tomar ningún bloqueo que impida las inserciones, actualizaciones o eliminaciones simultáneas en la tabla. Para esto se debe pasar en la creación del índice el siguiente parámetro algorithm: :concurrently.

El parámetro algorithm: :concurrently permite crear el índice sin bloquear la tabla mientras este es creado. Note que se debe agregar disable_ddl_transaction! para permitir que la migración no se ejecute por defecto dentro de una transacción.

class AddIndexToSimpleTable < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!

  def change
    add_index :simple_tables, :integer_a, algorithm: :concurrently
  end
end

Solución al problema planteado al principio del blog:

Se decide crear un índice multicolumna B-Tree en la tabla, esta migración tarda alrededor de 26 segundos en ser completada en el entorno de desarrollo local. La misma consulta realizada luego de la creación de los índices tardó alrededor de 0.15 ms en ser ejecutada, mucho más veloz que los 7.8 segundos que se tardaba antes de crear el índice.

Algunos consejos finales:

Es complejo formular un procedimiento general para determinar qué índices crear, pero los siguientes son algunos consejos que le permitirán tomar una mejor decisión.

• Siempre analiza las consultas con el comando EXPLAIN ANALYZE, de esta manera puedes comprender que hacer correctamente sin adivinar a ciegas qué sucederá si se agrega o elimina un índice.
• El índice predeterminado de PostgreSQL es el índice B-Tree.
• Utilice datos reales para la experimentación cuando va a crear y probar un índice. Es decir, que la tabla en el entorno local donde realizará las pruebas del comportamiento del índice tenga un numero de registros similar al entorno de producción.
• Es recomendable crear índices solo sobre las consultas ejecutadas con más frecuencia y que son más costosas a nivel de performance. Evite crear un índice para satisfacer una consulta específica que no impacte el rendimiento de su aplicación.
• Tómese el tiempo necesario para abordar la solución, itere si es necesario. Es mejor analizar cuál índice es el apropiado para la consulta y descartar los índices innecesarios.
• No hay un límite específico en el número de índices en una tabla; sin embargo, se recomienda crear la menor cantidad de índices que satisfagan su carga de trabajo.