Este artículo discutirá las características proporcionadas por la partición incorporada o declarativa en la versión 12 de PostgreSQL. La demostración se preparó para la
conferencia homónima en la
conferencia HighLoad ++ Siberia 2019 (upd: apareció un
video con la conferencia).
Todos los ejemplos se ejecutan en la versión beta que apareció recientemente:
=> SELECT version();
version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit (1 row)
Los ejemplos utilizan las tablas de reservas y tickets de la base de datos de demostración. La tabla de reservas contiene entradas para tres meses de junio a agosto de 2017 y tiene la siguiente estructura:
=> \d bookings
Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Una reserva puede incluir varios boletos. La estructura de la mesa con tickets:
=> \d tickets
Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Esta información debería ser suficiente para comprender ejemplos en los que intentaremos hacer particiones de tablas.
→ Obtenga más información sobre la base de demostración
aquí.Particionamiento de rango
Primero, intente dividir la tabla de reservas por rango de fechas. En este caso, la tabla se crearía así:
=> CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date);
Secciones separadas para cada mes:
=> CREATE TABLE bookings_range_201706 PARTITION OF bookings_range FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz); => CREATE TABLE bookings_range_201707 PARTITION OF bookings_range FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);
Para indicar los límites de una sección, puede usar no solo constantes, sino también expresiones, por ejemplo, una llamada a función. El valor de la expresión se calcula en el momento en que se crea la sección y se almacena en el directorio del sistema:
=> CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));
Descripción de la tabla:
=> \d+ bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')
Eso es suficiente No hay disparador para insertar registros; no se necesitan restricciones CHECK. El parámetro CONSTRAINT_EXCLUSION tampoco es necesario, incluso puede desactivarlo:
=> SET constraint_exclusion = OFF;
Relleno con diseño automático en secciones:
=> INSERT INTO bookings_range SELECT * FROM bookings;
INSERT 0 262788
La sintaxis declarativa aún oculta las tablas heredadas, por lo que puede ver la distribución de filas en secciones por consulta:
=> SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;
tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)
Pero no hay datos en la tabla principal:
=> SELECT * FROM ONLY bookings_range;
book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)
Verifique la exclusión de secciones en el plan de consulta:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)
Escaneando solo una sección, como se esperaba.
El siguiente ejemplo utiliza la función to_timestamp con la categoría de variabilidad ESTABLE en lugar de una constante:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)
El valor de la función se calcula cuando el plan de consulta se inicializa y parte de las secciones se excluyen de la visualización (Línea de subplanes eliminados).
Pero esto solo funciona para SELECT. Al cambiar los datos, la exclusión de sección basada en los valores de las funciones ESTABLES aún no se implementa:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)
Por lo tanto, debe usar constantes:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)
Clasificación de índice
Para realizar la siguiente consulta, se requiere ordenar los resultados obtenidos de diferentes secciones. Por lo tanto, en el plan de consulta, vemos el nodo SORT y el alto costo inicial del plan:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77..25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00..4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00..94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00..2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00..1139.28 rows=57528 width=52) (6 rows)
Crea un índice en book_date. En lugar de un único índice global, se crean índices en cada sección:
=> CREATE INDEX book_date_idx ON bookings_range(book_date);
=> \di bookings_range*
List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)
La consulta anterior con la clasificación ahora puede usar el índice en la clave de partición y devolver el resultado de diferentes secciones inmediatamente en forma ordenada. El nodo SORT no es necesario y se requiere el costo mínimo para producir la primera fila del resultado:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12..14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28..385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42..8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42..4566.76 rows=88423 width=52) (4 rows)
Los índices particionados creados de esta manera son compatibles centralmente. Al agregar una nueva sección, se creará automáticamente un índice en ella. Y no puede eliminar el índice de una sola sección:
=> DROP INDEX bookings_range_201706_book_date_idx;
ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.
Solo en su totalidad:
=> DROP INDEX book_date_idx;
DROP INDEX
CREAR ÍNDICE ... CONCURRENTEMENTE
Al crear un índice en una tabla particionada, no puede especificar CONCURRENTEMENTE.
Pero puedes hacer lo siguiente. Primero, creamos el índice solo en la tabla principal, recibirá el estado no válido:
=> CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ f (1 row)
Luego cree índices en todas las secciones con la opción CONCURRENTEMENTE:
=> CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);
Ahora conectamos los índices locales a los globales:
=> ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;
Esto es similar a la conexión de tablas de particiones, que veremos más adelante. Tan pronto como todas las secciones del índice estén conectadas, el índice principal cambiará su estado:
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ t (1 row)
Conectar y desconectar secciones
No se proporciona la creación automática de secciones. Por lo tanto, deben crearse con anticipación, antes de agregar registros con nuevos valores de la clave de partición a la tabla.
Crearemos una nueva sección mientras otras transacciones trabajan con la tabla, al mismo tiempo que veremos los bloqueos:
=> BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
count ------- 5 (1 row)
=> SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)
El bloqueo de AccessShareLock se impone en la tabla principal, todas las secciones e índices al comienzo de la declaración. El cálculo de la función to_timestamp y la exclusión de secciones se produce más tarde. Si se utilizara una constante en lugar de una función, solo se bloquearían la tabla principal y la sección bookings_range_201707. Por lo tanto, si es posible, especifique constantes en la solicitud; esto debe hacerse, de lo contrario, el número de líneas en pg_locks aumentará en proporción al número de secciones, lo que puede llevar a la necesidad de aumentar max_locks_per_transaction.
Sin completar la transacción anterior, cree la siguiente sección para septiembre en una nueva sesión:
|| => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)
Al crear una nueva sección, el bloqueo ShareUpdateExclusiveLock, compatible con AccessShareLock, se impone en la tabla principal. Por lo tanto, las operaciones de agregar particiones no entran en conflicto con las consultas en una tabla particionada.
=> COMMIT;
|| => COMMIT;
El particionamiento se realiza con el comando ALTER TABLE ... DETACH PARTITION. La sección en sí no se elimina, sino que se convierte en una tabla independiente. Los datos se pueden descargar de él, se pueden eliminar y, si es necesario, volver a conectar (ADJUNTAR PARTICIÓN).
Otra opción para deshabilitar es eliminar la sección con el comando DROP TABLE.
Desafortunadamente, ambas opciones, DROP TABLE y DETACH PARTITION, usan el bloqueo AccessExclusiveLock en la tabla principal.
Sección predeterminada
Si intenta agregar un registro para el que aún no se ha creado una sección, se producirá un error. Si no se desea este comportamiento, puede crear una sección predeterminada:
=> CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;
Suponga que al agregar registros, mezclan la fecha sin especificar un milenio:
=> INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1
Notamos que la frase RETURNING devuelve una nueva línea, que cae en la sección predeterminada.
Después de configurar la fecha actual (cambiando la clave de partición), el registro se mueve automáticamente a la sección deseada, no se necesitan disparadores:
=> UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1
Lista de valores de seccionamiento
En la base de datos de demostración, la columna book_ref debe ser la clave principal de la tabla de reservas. Sin embargo, el esquema de partición seleccionado no permite crear una clave de este tipo:
=> ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);
ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.
La clave de partición debe incluirse en la clave primaria.
Para desglosar por meses e incluir book_ref en la clave primaria, intentemos otro esquema para dividir la tabla de reservas, de acuerdo con la lista de valores. Para hacer esto, agregue la columna redundante book_month como la clave de partición:
=> CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);
Formaremos secciones dinámicamente en función de los datos de la tabla de reservas:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
Esto es lo que sucedió:
=> \d+ bookings_list
Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')
Relleno con diseño en secciones:
=> INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;
INSERT 0 262788
Como un retiro. Para completar automáticamente book_month es tentador usar la nueva funcionalidad de la versión 12: columnas SIEMPRE GENERADAS. Pero, desafortunadamente, no se pueden usar como una clave de partición. Por lo tanto, la tarea de llenar el mes debe resolverse de otras maneras.
Las restricciones de integridad como CHECK y NOT NULL se pueden crear en una tabla particionada. Al igual que con la herencia, especificar INHERIT / NOINHERIT indica si la restricción debe heredarse en todas las tablas de particiones. HERENCIA predeterminada:
=> ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;
=> \d bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)
=> \d bookings_range_201706
Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)
Una restricción EXCLUDE solo se puede crear localmente en particiones.
Se buscará en book_ref en todas las secciones, pero por índice, gracias a que book_ref aparece primero:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';
QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)
Una búsqueda en book_ref y un rango de secciones solo debe verse en el rango especificado:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)
El comando INSERTAR ... EN CONFLICTO encuentra correctamente la sección deseada y realiza la actualización:
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1
Claves foráneas
En la base de datos de demostración, la tabla de tickets se refiere a reservas.
Para hacer posible la clave externa, agregue la columna book_month y, al mismo tiempo, divídala en secciones por mes, como bookings_list.
=> CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);
La restricción de la CLAVE EXTRANJERA merece una mirada más cercana. Por un lado, esta es la clave externa
de la tabla particionada (tickets_list) y, por otro lado, esta es la clave
de la tabla particionada (bookings_list). Por lo tanto, las claves foráneas para tablas particionadas se admiten en ambas direcciones.
Crear secciones:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
=> \d+ tickets_list
Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')
Completamos:
=> INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);
INSERT 0 366733
=> VACUUM ANALYZE tickets_list;
Distribución de líneas en secciones:
=> SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;
tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)
Solicitudes de conexión y agregación
Unir dos tablas divididas de la misma manera:
=> EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ---------------------------------------------------------------------------- Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Append -> Seq Scan on tickets_list_201706 t -> Seq Scan on tickets_list_201707 t_1 -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Append -> Seq Scan on bookings_list_201706 b -> Seq Scan on bookings_list_201707 b_1 -> Seq Scan on bookings_list_201708 b_2 (11 rows)
Antes de comenzar una conexión, cada tabla combina primero las secciones que entran en la condición de consulta.
Pero primero puede combinar las secciones mensuales correspondientes de ambas tablas y luego combinar el resultado. Esto se puede lograr habilitando el parámetro enable_partitionwise_join:
=> SET enable_partitionwise_join = ON; => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ------------------------------------------------------------------------------------------ Append -> Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Seq Scan on tickets_list_201706 t -> Hash -> Seq Scan on bookings_list_201706 b -> Hash Join Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month)) -> Seq Scan on tickets_list_201707 t_1 -> Hash -> Seq Scan on bookings_list_201707 b_1 -> Hash Join Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month)) -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Seq Scan on bookings_list_201708 b_2 (16 rows)
Ahora, primero, se unen las secciones correspondientes de las dos tablas y luego se combinan los resultados de las uniones.
Una situación similar con la agregación:
=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Append -> Parallel Seq Scan on bookings_list_201707 -> Parallel Seq Scan on bookings_list_201708 -> Parallel Seq Scan on bookings_list_201706 (8 rows)
Tenga en cuenta que los escaneos de sección se pueden realizar en paralelo. Pero primero se unen las secciones, solo entonces comienza la agregación. Alternativamente, puede realizar la agregación en cada sección y luego combinar el resultado:
=> SET enable_partitionwise_aggregate = ON; => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Parallel Append -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201707 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201708 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201706 (10 rows)
Estas características son especialmente importantes si parte de las secciones son tablas externas. Por defecto, ambos están deshabilitados porque los parámetros apropiados afectan el tiempo del plan, pero no siempre se pueden usar.
Particionamiento de hash
Una tercera forma de particionar una tabla es la partición hash.
Crear una tabla:
=> CREATE TABLE bookings_hash ( book_ref character(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) ) PARTITION BY HASH(book_ref);
En esta versión de book_ref, como clave de partición, puede declararla inmediatamente como clave principal.
Divide en tres secciones:
=> CREATE TABLE bookings_hash_p0 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); => CREATE TABLE bookings_hash_p1 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1); => CREATE TABLE bookings_hash_p2 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);
Relleno con diseño automático en secciones:
=> INSERT INTO bookings_hash SELECT * FROM bookings;
INSERT 0 262788
La distribución de líneas en secciones se produce de manera uniforme:
=> SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;
partition | count ------------------+------- bookings_hash_p0 | 87649 bookings_hash_p1 | 87651 bookings_hash_p2 | 87488 (3 rows)
Nuevo comando para ver objetos particionados:
=> \dP+
List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description ----------+--------------------+---------+-------------------+----------------+------------+------------- bookings | bookings_hash | student | partitioned table | | 13 MB | bookings | bookings_list | student | partitioned table | | 15 MB | bookings | bookings_range | student | partitioned table | | 13 MB | bookings | tickets_list | student | partitioned table | | 50 MB | bookings | book_date_idx | student | partitioned index | bookings_range | 5872 kB | bookings | bookings_hash_pkey | student | partitioned index | bookings_hash | 5800 kB | bookings | bookings_list_pkey | student | partitioned index | bookings_list | 8120 kB | bookings | tickets_list_pkey | student | partitioned index | tickets_list | 19 MB | (8 rows)
=> VACUUM ANALYZE bookings_hash;
Subconsultas y uniones de bucle anidado
La exclusión de secciones en tiempo de ejecución es posible con conexiones de bucle anidadas.
Distribución de las primeras 10 reservas en secciones:
=> WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10 ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;
partition | count ------------------+------- bookings_hash_p0 | 3 bookings_hash_p1 | 3 bookings_hash_p2 | 4 (3 rows)
Veamos el plan de ejecución de la consulta con la combinación de la tabla bookings_hash y la subconsulta anterior: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.632 ms Execution Time: 0.278 ms (13 rows)
La conexión se realiza utilizando el método de bucle anidado. El bucle externo de acuerdo con la expresión de tabla general se ejecuta 10 veces. Pero preste atención a la cantidad de llamadas a las secciones de la tabla (bucles). Para cada valor book_ref del bucle externo, solo se explora la sección donde este valor se almacena en la tabla bookings_hash.Comparar con exclusión de sección deshabilitada: => SET enable_partition_pruning TO OFF; => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.886 ms Execution Time: 0.771 ms (13 rows)
=> RESET enable_partition_pruning;
Si reduce la selección a una reserva, entonces dos secciones no serán visibles en absoluto: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1 ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;
QUERY PLAN --------------------------------------------------------------------------------------------------- Nested Loop (actual rows=1 loops=1) -> Limit (actual rows=1 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=1) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.250 ms Execution Time: 0.090 ms (13 rows)
En lugar de una subconsulta, puede usar la función que devuelve un conjunto con la categoría de variabilidad ESTABLE: => CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' USING top; END;$$ LANGUAGE plpgsql STABLE;
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Function Scan on get_book_ref f (actual rows=10 loops=1) -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = f.book_ref) Planning Time: 0.175 ms Execution Time: 0.843 ms (11 rows)
Resumen
En resumen, podemos decir que la partición incorporada o declarativa en PostgreSQL 12 ha recibido un amplio conjunto de características y se puede recomendar de forma segura reemplazar la partición a través de la herencia.