Particionamiento de Tablas en PostgreSQL

Introducción

En este laboratorio, aprenderá a implementar la partición de tablas en PostgreSQL. El objetivo es dividir una tabla grande en partes más pequeñas y manejables, lo que puede mejorar significativamente el rendimiento de las consultas y simplificar tareas de gestión de datos como copias de seguridad o archivado.

Comenzará creando una tabla "padre" principal diseñada para la partición. Luego, definirá varias tablas "hijas", o particiones, cada una conteniendo datos para un rango de fechas específico. Finalmente, insertará datos en la tabla padre y observará cómo PostgreSQL los dirige automáticamente a la partición correcta. También aprenderá a consultar la tabla particionada y verá cómo PostgreSQL optimiza estas consultas accediendo solo a las particiones relevantes.

Crear la Tabla Particionada Principal

En este paso, creará la tabla principal sales, que servirá como tabla padre para nuestras particiones. Esta tabla define la estructura para todas sus particiones, pero no almacenará ningún dato por sí misma.

Primero, necesita conectarse a la base de datos PostgreSQL. Abra una terminal y use el siguiente comando para iniciar el shell interactivo psql como el usuario postgres:

sudo -u postgres psql

Ahora debería ver el prompt de PostgreSQL, que se ve como postgres=#. Todos los comandos SQL subsiguientes en este laboratorio se ejecutarán desde este prompt.

A continuación, cree la tabla sales. Esta tabla se particionará por rango en la columna sale_date.

CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

Analicemos este comando:

• CREATE TABLE sales (...): Define las columnas para nuestros datos de ventas.
• PRIMARY KEY (sale_id, sale_date): En una tabla particionada, la clave primaria debe incluir la columna de partición (sale_date).
• PARTITION BY RANGE (sale_date): Esta es la parte clave. Declara que esta tabla se particiona utilizando el método RANGE en la columna sale_date.

Después de ejecutar el comando, debería ver un mensaje de confirmación CREATE TABLE.

Para verificar que la tabla se creó, puede usar el comando \d en psql para describir la estructura de la tabla.

\d sales

La salida mostrará las columnas de la tabla y, en la parte inferior, confirmará que es una "Tabla particionada" y listará la "Clave de partición".

                                       Table "public.sales"
   Column    |     Type      | Collation | Nullable |                    Default
-------------+---------------+-----------+----------+------------------------------------------------
 sale_id     | integer       |           | not null | nextval('sales_sale_id_seq'::regclass)
 sale_date   | date          |           | not null |
 product_id  | integer       |           |          |
 sale_amount | numeric(10,2) |           |          |
Partition key: RANGE (sale_date)
Indexes:
    "sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0

Observe que el "Número de particiones" es 0. Creará las particiones reales en el siguiente paso.

Definir Particiones para Rangos de Fechas

Ahora que tiene la tabla padre sales, necesita crear las particiones reales donde se almacenarán los datos. Cada partición contendrá datos para un rango de fechas específico. En este paso, creará particiones trimestrales para los años 2023 y 2024.

Debería seguir en la terminal interactiva psql.

Primero, cree las cuatro particiones para 2023. Cada comando define una nueva tabla como una partición de sales y especifica el rango de fechas que cubrirá.

CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

La cláusula FOR VALUES FROM ... TO ... define el rango para cada partición. El límite inferior es inclusivo y el límite superior es exclusivo. Por ejemplo, sales_2023_q1 almacenará registros donde sale_date sea desde 2023-01-01 hasta, pero sin incluir, 2023-04-01.

A continuación, cree las particiones para el año 2024 utilizando el mismo esquema trimestral:

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE sales_2024_q3 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE sales_2024_q4 PARTITION OF sales
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

Después de ejecutar cada comando CREATE TABLE, verá un mensaje de confirmación.

Para verificar que todas las particiones se han creado, puede listar las tablas en la base de datos nuevamente.

\dt

Ahora debería ver la tabla padre sales y las ocho particiones que acaba de crear (sales_2023_q1, sales_2023_q2, etc.) en la salida.

Insertar y Enrutar Datos

En este paso, insertará datos de ejemplo. Una característica clave del particionamiento es que inserta datos directamente en la tabla padre (sales), y PostgreSQL enruta automáticamente cada fila a la partición correcta basándose en el valor de la clave de partición (sale_date).

Debería seguir en la terminal interactiva psql.

Ejecute la siguiente sentencia INSERT para agregar 16 registros de ventas de ejemplo que abarcan 2023 y 2024:

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);

Después de que el comando se complete, verá la salida INSERT 0 16, que indica que se insertaron 16 filas correctamente.

Para verificar que los datos se enrutaron correctamente, puede consultar las particiones individuales. Por ejemplo, verifiquemos el recuento de registros en el primer trimestre de 2023:

SELECT COUNT(*) FROM sales_2023_q1;

La salida debería ser:

 count
-------
     2
(1 row)

Ahora, verifique el recuento para el cuarto trimestre de 2024:

SELECT COUNT(*) FROM sales_2024_q4;

La salida también debería ser 2. Esto confirma que PostgreSQL ha colocado los datos en las tablas de partición subyacentes correctas.

Consultar Datos y Analizar Rendimiento

En este paso final, consultará la tabla sales particionada. El principal beneficio del particionamiento, conocido como "partition pruning" (poda de particiones), es que el planificador de consultas de PostgreSQL es lo suficientemente inteligente como para escanear solo las particiones necesarias, evitando un escaneo completo de todo el conjunto de datos.

Debería seguir en la terminal interactiva psql.

Primero, ejecute una consulta para recuperar todas las ventas del primer trimestre de 2023.

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Verá los dos registros que caen dentro de este rango de fechas. Para ver cómo PostgreSQL optimiza esto, puede usar el comando EXPLAIN, que muestra el plan de ejecución de la consulta.

EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

La salida se verá algo así:

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on sales_2023_q1 sales  (cost=0.00..31.75 rows=7 width=28)
   Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)

Observe la línea Seq Scan on sales_2023_q1. Esto demuestra que PostgreSQL solo escaneó la partición sales_2023_q1 e ignoró las otras siete, lo que hace que la consulta sea mucho más rápida en un conjunto de datos grande.

Ahora, ejecutemos una consulta más compleja para encontrar el monto total de ventas por producto en 2024.

SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;

Esta consulta escaneará eficientemente solo las cuatro particiones de 2024 para calcular el resultado. La salida mostrará las ventas totales de cada producto desde el 109 hasta el 116.

Finalmente, puede salir de la terminal interactiva de PostgreSQL escribiendo:

\q

Volverá a su prompt de shell habitual.

Resumen

En este laboratorio, ha aprendido los fundamentos del particionamiento de tablas en PostgreSQL. Creó con éxito una tabla padre particionada por rango de fechas, definió particiones específicas para diferentes períodos de tiempo e insertó datos que se enrutaron automáticamente a la partición correcta. Lo más importante es que utilizó el comando EXPLAIN para ver el "partition pruning" (poda de particiones) en acción, demostrando cómo el particionamiento puede mejorar significativamente el rendimiento de las consultas al permitir que la base de datos escanee solo un subconjunto de los datos. Esta es una técnica poderosa para administrar conjuntos de datos a gran escala de manera eficiente.