Particionamiento de Tablas en PostgreSQL: Mejora el Rendimiento

Introducción

En este laboratorio, aprenderá cómo implementar el particionamiento de tablas en PostgreSQL. El objetivo es dividir una tabla grande en piezas más pequeñas y manejables para mejorar el rendimiento de las consultas y simplificar la gestión de datos.

Comenzará configurando una tabla particionada por rango llamada sales, particionada por la columna sale_date. Esto implica crear la tabla principal sales y luego definir particiones para diferentes rangos de fechas. Luego procederá a insertar datos en la tabla sales, que se enrutarán automáticamente a las particiones correctas, y realizará consultas en las tablas particionadas.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") subgraph Lab Skills postgresql/db_setup -.-> lab-550963{{"Particionamiento de Tablas en PostgreSQL"}} postgresql/table_init -.-> lab-550963{{"Particionamiento de Tablas en PostgreSQL"}} postgresql/row_add -.-> lab-550963{{"Particionamiento de Tablas en PostgreSQL"}} postgresql/data_all -.-> lab-550963{{"Particionamiento de Tablas en PostgreSQL"}} postgresql/data_where -.-> lab-550963{{"Particionamiento de Tablas en PostgreSQL"}} end

Crear la tabla `sales` y las particiones iniciales

En este paso, creará la tabla principal sales y las particiones iniciales para el año 2023. El particionamiento divide una tabla grande en piezas más pequeñas y manejables, mejorando el rendimiento de las consultas.

Primero, conéctese a la base de datos PostgreSQL como el usuario postgres. Abra una terminal y use el siguiente comando:

sudo -u postgres psql

Ahora debería estar en la terminal interactiva de PostgreSQL.

A continuación, cree la tabla sales. Esta tabla almacenará datos de ventas, incluida la fecha de venta, el ID del producto y el importe de la venta.

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

Este comando crea la tabla sales y especifica que se particionará por rango (partitioned by range) en la columna sale_date.

Ahora, cree las particiones para el año 2023, dividiendo el año en trimestres:

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');

Estos comandos crean cuatro particiones para la tabla sales, cada una cubriendo un trimestre del año 2023.

Para verificar que la tabla y las particiones se han creado correctamente, liste todas las tablas:

\dt

Debería ver la tabla sales y sus particiones (sales_2023_q1, sales_2023_q2, sales_2023_q3, sales_2023_q4) en la salida.

Definir particiones para 2024

Ahora que ha creado la tabla sales y las particiones para 2023, agreguemos particiones para el año 2024. Esto asegura que pueda almacenar datos de ventas para todo el año.

Aún debería estar en la terminal interactiva de PostgreSQL del paso anterior. Si no, conéctese de nuevo usando:

sudo -u postgres psql

Cree particiones para la tabla sales para el año 2024, siguiendo el mismo esquema de particionamiento 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');

Estos comandos crean cuatro nuevas particiones para la tabla sales, cubriendo cada trimestre del año 2024.

Para verificar que las nuevas particiones se han creado correctamente, liste todas las tablas de nuevo:

\dt

Ahora debería ver la tabla sales, las particiones de 2023 y las nuevas particiones de 2024 en la salida.

Insertar datos en la tabla `sales`

En este paso, insertará datos de ejemplo en la tabla sales. PostgreSQL enrutará automáticamente los datos a la partición correcta basándose en la sale_date (fecha de venta).

Aún debería estar en la terminal interactiva de PostgreSQL. Si no, conéctese de nuevo usando:

sudo -u postgres psql

Inserte los siguientes datos de ejemplo en la tabla sales:

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);

Estas sentencias INSERT añaden registros de ventas para varias fechas en 2023 y 2024. PostgreSQL enrutará automáticamente cada registro a la partición apropiada basándose en la sale_date.

Para verificar que los datos se han insertado correctamente, consulte la tabla sales para contar el número total de registros:

SELECT COUNT(*) FROM sales;

La salida debería mostrar un total de 16 registros.

Consultar la tabla `sales` particionada

En este paso, consultará la tabla sales particionada para recuperar datos. Puede consultar la tabla completa como si fuera una sola tabla, y PostgreSQL optimizará la consulta para acceder solo a las particiones relevantes.

Aún debería estar en la terminal interactiva de PostgreSQL. Si no, conéctese de nuevo usando:

sudo -u postgres psql

Para recuperar todos los registros de ventas de la tabla sales completa, use la siguiente sentencia SELECT:

SELECT * FROM sales;

Esta consulta devolverá todos los datos de todas las particiones de la tabla sales.

También puede usar cláusulas WHERE para filtrar los datos. Por ejemplo, para recuperar todos los registros de ventas del año 2023:

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

PostgreSQL utilizará la clave de partición (sale_date) para determinar a qué particiones acceder, mejorando el rendimiento de la consulta.

Probemos otra consulta para encontrar el importe total de ventas para cada producto en el año 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;

Esta consulta calcula la suma de sale_amount para cada product_id dentro del año 2024.

Finalmente, salga de la terminal interactiva de PostgreSQL:

\q

Resumen

En este laboratorio, ha aprendido cómo implementar el particionamiento de tablas (table partitioning) en PostgreSQL. Creó una tabla sales, la particionó por rango de fechas, definió particiones para los años 2023 y 2024, insertó datos en la tabla y consultó la tabla particionada. El particionamiento puede mejorar significativamente el rendimiento de las consultas y simplificar la gestión de datos para tablas grandes.

Particionamiento de Tablas en PostgreSQL