PostgreSQL 10 se lanzó a principios de octubre de 2017, hace casi un año.
Una de las nuevas "características" más interesantes es la partición incondicionalmente declarativa. Pero, ¿qué pasa si no tienes prisa por actualizar a 10k? Amazon, por ejemplo, no tiene prisa, y
presentó el soporte PostgreSQL 10 solo en los últimos días de febrero de 2018.
Entonces, una buena partición a través de la herencia viene al rescate. Soy el arquitecto de software del departamento de finanzas de una compañía de taxis, por lo que todos los ejemplos estarán relacionados con los viajes de una forma u otra (dejaremos los problemas de dinero para otro momento).
Desde que comenzamos a reescribir nuestro sistema financiero en 2015, cuando me uní a la compañía, no se habló de ninguna partición declarativa. Hasta el día de hoy, la técnica descrita a continuación se ha utilizado con éxito.
La razón original para escribir este artículo fue que la mayoría de los ejemplos de particiones en PostgreSQL que encontré eran muy básicos. Aquí hay una tabla, aquí hay una columna que estamos viendo, y tal vez incluso sepamos de antemano qué valores contiene. Parece que todo es simple. Pero la vida real hace sus propios ajustes.
En nuestro caso, dividimos las tablas en dos columnas, una de las cuales contiene fechas de viaje. Es este caso el que consideraremos.
Comencemos con el aspecto de nuestra mesa:
create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb
Para cada inquilino, la tabla contiene millones de filas por mes. Afortunadamente, los datos entre los inquilinos nunca se cruzan, y las solicitudes más difíciles se hacen al cabo de uno o dos meses.
Para aquellos que no han profundizado en cómo funcionan las particiones PostgreSQL (¡qué suerte de Oracle, hola!), Describiré brevemente el proceso.
PostgreSQL se basa en dos de sus "características" para esto: la capacidad de heredar tablas, herencia de tablas y condiciones verificadas.
Comencemos con la herencia. Usando la palabra clave INHERITS, indicamos que la tabla que creamos hereda todos los campos de la tabla heredada. Esto también crea una relación entre las dos tablas: al hacer una consulta desde el padre, también obtenemos todos los datos de los hijos.
Las condiciones marcadas complementan la imagen al garantizar que los datos no se crucen. Por lo tanto, el optimizador PostgreSQL puede cortar parte de las tablas secundarias al confiar en los datos de la consulta.
El primer escollo de este enfoque parecería bastante obvio: cualquier solicitud debe contener tenant_id. Y, sin embargo, si no se recuerda constantemente esto, tarde o temprano usted mismo escribirá un SQL personalizado en el que olvidará especificar este tenant_id. Como resultado, una exploración de todas las particiones y una base de datos que no funciona.
Pero volvamos a lo que queremos lograr. A nivel de aplicación, me gustaría transparencia: siempre escribimos en la misma tabla, y la base de datos ya elige exactamente dónde colocar estos datos.
Para hacer esto, utilizamos el siguiente procedimiento almacenado:
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN
Lo primero a lo que debe prestar atención es al uso de TG_TABLE_NAME. Dado que esto es un desencadenante, PostgreSQL llena bastantes variables para nosotros a las que podemos acceder. La lista completa se puede
encontrar aquí .
En nuestro caso, queremos obtener el nombre del padre de la tabla en la que funcionó el desencadenador. En nuestro caso, serán paseos. Utilizamos un enfoque similar en varios microservicios, y esta parte se puede transferir prácticamente sin cambios.
PERFORM es útil si queremos llamar a una función que no devuelve nada. Por lo general, en los ejemplos, intentan poner toda la lógica en una función, pero tratamos de tener cuidado.
USING NEW indica que en esta consulta usamos los valores de la cadena que intentamos agregar.
$1.* expandirá todos los valores de nueva línea. De hecho, esto se puede traducir a
NEW.* . Lo que se traduce en
NEW.ID, NEW.TENANT_ID, …El siguiente procedimiento, que llamamos con
PERFORM , creará una nueva partición, si aún no existe. Esto sucederá una vez por período para cada inquilino.
CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS $BODY$ DECLARE sql text; BEGIN
Como se describió anteriormente, usamos
INHERITS para crear una tabla similar a la principal, y
CHECK para determinar qué datos deben ir allí.
RAISE NOTICE simplemente imprime una cadena en la consola. Si ahora ejecutamos
INSERT desde psql, podemos ver si se creó la partición.
Tenemos un nuevo problema
INHERITS no hereda índices. Para hacer esto, tenemos dos soluciones:
Crear índices usando herencia:
Use
CREATE TABLE LIKE y luego
ALTER TABLE INHERITSO cree índices procesales:
CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN
Es muy importante no olvidarse de indexar tablas secundarias, porque incluso después de la partición, cada una de ellas tendrá millones de filas. Los índices en padre no son necesarios en nuestro caso, ya que el padre siempre permanecerá vacío.
Finalmente, creamos un disparador que se llamará cuando se cree una nueva línea:
CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row();
Hay otra sutileza en la que rara vez prestan atención. Particionar es mejor en columnas donde los datos nunca cambian. En nuestro caso, esto funciona: el viaje nunca cambia tenant_id y created_at. El problema que surge si esto no es así es que PostreSQL no nos devolverá parte de los datos. Luego le prometimos VERIFICAR que todos los datos son válidos.
Hay varias soluciones (excepto lo obvio: no mute los datos para los que estamos particionando):
En lugar de
UPDATE siempre hacemos
DELETE+INSERT a nivel de aplicación
Agregamos un disparador más en
UPDATE que transferirá datos a la partición correcta
Otra advertencia que vale la pena considerar es cómo indexar correctamente las columnas que contienen fechas. Si usamos AT TIME ZONE en las consultas, no debemos olvidar que esta es realmente una llamada de función. Y eso significa que nuestro índice debe estar basado en funciones. Lo olvidé Como resultado, la base está muerta nuevamente por la carga.
El último aspecto que vale la pena considerar es cómo las particiones interactúan con varios marcos ORM, ya sea ActiveRecord en Ruby o GORM en Go.
Las particiones en PostgreSQL se basan en el hecho de que la tabla primaria siempre estará vacía. Si no usa ORM, puede regresar con seguridad al primer procedimiento almacenado y cambiar RETURN NEW; en RETURN NULL; Luego, la fila en la tabla principal simplemente no se agregará, que es exactamente lo que queremos.
Pero el hecho es que la mayoría de los ORM utilizan la cláusula RETURNING con INSERT. Si devolvemos NULL desde nuestro disparador, el ORM entrará en pánico, creyendo que la fila no se ha agregado. Se agrega, pero no donde ORM está buscando.
Hay varias formas de evitar esto:
- No use ORM para INSERTOS
- Parche ORM (que a veces se recomienda en el caso de ActiveRecord)
- Agregue otro disparador, que eliminará la línea del padre.
La última opción no es deseable, porque para cada operación realizaremos tres. Sin embargo, a veces es inevitable, porque lo consideraremos por separado:
CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN delete from only rides where id = NEW.ID; RETURN null; END; $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_row_trigger AFTER INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE delete_parent_row();
Lo último que debemos hacer es probar nuestra solución. Para hacer esto, generamos un cierto número de líneas:
DO $script$ DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d']; BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP; END $script$;
Y veamos cómo se comporta la base de datos:
explain select * from rides where tenant_id = 'tenant_a' and created_at AT TIME ZONE 'UTC' > '20171102' and created_at AT TIME ZONE 'UTC' <= '20171103';
Si todo salió bien, deberíamos ver el siguiente resultado:
Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone)) (5 rows)
A pesar de que cada inquilino tiene cientos de miles de filas, solo seleccionamos del segmento de datos deseado. Éxito!
Espero que este artículo sea interesante para aquellos que aún no están familiarizados con lo que es el particionamiento y cómo se implementa en PostgreSQL. Pero aquellos para quienes este tema ya no es nuevo, sin embargo, aprendieron un par de trucos interesantes.
UPD:
Como
bigtrot observó correctamente, toda esta magia callejera no funcionará si la configuración
CONSTRAINT_EXCLUSION está desactivada.
Puedes verificar esto usando el comando
show CONSTRAINT_EXCLUSION
La configuración tiene tres valores: encendido, apagado y partición
La configuración de la partición es más óptima si de repente le gusta usar CHECK CONSTRAINTS no solo para las particiones, sino también para la normalización de datos.