Optimiza tu aplicación rails utilizando índices en Postgres
Tags: rails, postgres, sql, database
Published: 2022-04-30 00:12:56

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.

![consulta en base de datos usando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3km7p5jxllzwmr9qvq90.png)

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_.

![Estructura índice b-tree](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eoonr4z5mqz5cgek7829.png)

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.

![Estructura índice GIN](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4wj6cg80fmpxzcdjekcz.png)

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.
![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/68e8dikzgia94wgstlpc.png)

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. 
![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mx846lfio5wyyay2lrrq.png)

####Í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.
![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qzmzytt0ccegjx1kcxiw.png)

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. 
![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8con02cbu4u6wf6x88c1.png)

####Í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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0sqm1r75i859hjjp19nf.png)

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. 

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pcyqkuunt8s97rfanc0e.png)

####Í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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ivk9elc7w4rly0t9xps9.png)

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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4o5tjpxd45mh7u3xcqmk.png)

**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**.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ag66gzgtwo00p31xcr5s.png)

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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r036i6gktccz538m8ink.png)

###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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bagbirk6tgfoeeb37w8k.png)

Se utiliza _Index Scan_ cuando la consulta se hace sobre la columna con índice _GIN_ (_array_b_).

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ezi5gsqi4q3idgbi5y24.png)

####Í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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hfocsyqwbz31od3aw0ig.png)

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.


![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x8evyb9a4l4ixv4y2udc.png)

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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m5ppggi7lwu7p2izcap1.png)

**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]`

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/86r7wdog94p72uaohvgz.png)

####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.

![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vmxv8fjd72rj317vq246.png)

####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.


![consulta utilizando EXPLAIN ANALYZE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gwb3bvetopec5nw9pykz.png)

####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.