ChatGPT y PostgreSQL

ChatGPT y PostgreSQL

Le pregunté a ChatGPT «¿Para que puede usar ChatGPT un DBA?» y sin demora me contestó:

Como modelo de lenguaje, ChatGPT no tiene la capacidad de utilizar directamente un DBA (Administrador de Base de Datos) para administrar una base de datos. Sin embargo, es posible que ChatGPT pueda proporcionar respuestas o soluciones a preguntas o problemas relacionados con la gestión de bases de datos a través de una conversación en lenguaje natural.

Algunas preguntas o problemas relacionados con la gestión de bases de datos que un DBA podría plantear a ChatGPT son:

¿Cómo puedo optimizar el rendimiento de mi base de datos? ¿Cuáles son las mejores prácticas para la recuperación de desastres? ¿Cómo puedo solucionar problemas de bloqueo y concurrencia en mi base de datos? ¿Cómo puedo diseñar una base de datos eficiente para una aplicación específica? ¿Cuáles son las herramientas más efectivas para monitorizar el rendimiento de mi base de datos?

En respuesta, ChatGPT podría proporcionar información sobre las mejores prácticas de administración de bases de datos, soluciones comunes a problemas de rendimiento, recomendaciones de herramientas de monitorización y otras soluciones. Sin embargo, es importante tener en cuenta que ChatGPT no reemplaza la experiencia y el conocimiento de un DBA calificado en la gestión de bases de datos.

Sin embargo, ayer hice uso de este modelo de lenguaje de IA de una manera que no está incluída en sus recomendaciones.

El problema

En este momento estoy preparando algunos materiales para una consultoría moderadamente extensa sobre varios temas relacionados con la administración de bases de datos, en particular con PostgreSQL.

Uno de esos temas es el de la creación y uso de tablas particionadas. Ese tipo de tablas, según ChatGPT,

…son una forma de dividir grandes tablas en fragmentos más pequeños llamados particiones. Cada partición tiene su propio conjunto de datos y se define mediante una regla de partición. El particionamiento de tablas mejora el rendimiento y facilita la administración de grandes volúmenes de datos

Algunas de las tareas que tengo que realizar para la capacitación del personal de TI de la empresa que me contrata, en este tema en particular, son: Diseñar las tablas que voy a particionar, es decir, definir sus nombre y el tipo de campos que va a tener, así como el tipo de dato de cada uno de ellos. Poblar esas tablas, lo cual implica contar con un conjunto de datos (dataset) con varios miles de registros y con los tipos de datos acordes a la tabla en la que van a vivir. Crear las tablas particionadas por medio de las sentencias escritas en el lenguaje SQL.

Diseño de las tablas

Para esta primera iteración decidí replicar el ejemplo de particionado declarativo que viene en el manual oficial de PostgreSQL:

La tabla particionada en sí es una tabla "virtual" que no tiene almacenamiento propio. En cambio, el almacenamiento pertenece a las particiones, que son tablas ordinarias asociadas con la tabla particionada. Cada partición almacena un subconjunto de los datos definidos por sus límites de partición. Todas las filas insertadas en una tabla particionada se enrutarán a la partición adecuada en función de los valores de las columnas de clave de partición.

En el ejemplo del manual la tabla virtual se genera con la siguiente sentencia:

CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate);

y las tablas que almacenan realmente los datos se generan como una tabla particionada especificando la cláusula PARTITION BY, que incluye el método de partición (RANGE en este caso) y la lista de columnas para usar como clave de partición.

El ejemplo que ponen en el manual es el siguiente:

Crear particiones. La definición de cada partición debe especificar los límites que corresponden al método de partición y la clave de partición del padre. Tenga en cuenta que especificar límites de modo que los valores de la nueva partición se superpongan con los de una o más particiones existentes provocará un error. Las particiones así creadas son en todos los sentidos tablas PostgreSQL normales (o, posiblemente, tablas foráneas). Es posible especificar un tablespace y parámetros de almacenamiento para cada partición por separado. Para nuestro ejemplo, cada partición debe contener los datos de un mes, para cumplir con el requisito de eliminar los datos de un mes a la vez. Entonces los comandos podrían verse así:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
... 
CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

El diseño de la prueba de concepto

Para efecto de probar el uso de tablas particionadas necesitaba escribir ciertas sentencias en el lenguaje SQL para lograr los siguiente:

La creación de la tabla virtual. La creación de las tablas particionadas, acordes con fechas cercanas a la del momento de hacer la prueba. Generar suficientes datos para probar el funcionamiento del particionamiento. Poblar las tablas Realizar consultas a la tabla virtual para revisar su comportamiento.

La tabla virtual

La sentencia SQL para generar la tabla virtual se muestra arriba, usé la misma tabla del ejemplo del manual.

Creación de las tablas particionadas

Las tablas particionadas se definieron bajo el supuesto de que van a almacenar los datos del año anterior y al menos los del año actual, es decir de 2022 y 2023.

La tarea de escribir las 24 sentencias SQL considerando que cada una de ella debe tener un nombre que informe sobre el rango de las fechas de los datos que almacenará y, por otra parte, el rango de las fechas en las parte de …FOR VALUES FROM… es, por decir lo menos, tediosa y propensa a errores.

Así que decidí dejarle la tarea de generar esas sentencias ChatGPT

ChatGPT en acción

A menos que el lector esté desconectado de las noticias internacionales, ya debe saber que

ChatGPT es un modelo de lenguaje creado por OpenAI que utiliza técnicas de aprendizaje profundo y procesamiento de lenguaje natural para entender y generar texto de manera autónoma. ChatGPT es capaz de responder preguntas, completar oraciones y generar texto coherente y relevante, y se ha utilizado en diversas aplicaciones, incluyendo chatbots, asistentes virtuales y generación de texto automático. El modelo se ha entrenado con grandes cantidades de datos textuales para desarrollar una comprensión profunda del lenguaje natural y ha demostrado ser altamente efectivo en diversas tareas de procesamiento de texto.

Una de las características de este bot es que puede generar respuestas textuales a preguntas (prompts) que le plantee el usuario. En mi caso, le pedí que replicara la estructura de una de las sentencias para crear las tablas particionadas para todo el rango de fechas que iba a utilizar.

Es común que deba diseñarse la petición por ensayo y error para que ChatGPT nos entregue lo esperado. Lo recomendable es generar la petición con la mayor precisión posible, es decir, estipulando claramente lo que deseamos que genere.

El prompt que finalmente me funcionó fue el siguiente:

Prompt

Tomando como base la siguiente sentencia SQL:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

Crea sentencias similares para cada uno de los meses de los años completos 2022 a 2023. Debes terminar hasta el mes 12 de 2023, no antes.

Escribe la respuesta en una sola celda, para que la pueda copiar completa.

La respuesta final se muestra a continuación, abreviada:

CREATE TABLE measurement_y2022m01 PARTITION OF measurement FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'); 
CREATE TABLE measurement_y2022m02 PARTITION OF measurement FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'); 
CREATE TABLE measurement_y2022m03 PARTITION OF measurement FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'); 
... 
CREATE TABLE measurement_y2023m11 PARTITION OF measurement FOR VALUES FROM ('2023-11-01') TO ('2023-12-01'); 
CREATE TABLE measurement_y2023m12 PARTITION OF measurement FOR VALUES FROM ('2023-12-01') TO ('2024-01-01');

Generación e inserción de datos

La generación de datos de prueba es una de las tareas que con más frecuencia consumen el tiempo de un DBA. Los datos de prueba sirven para varias tareas: Prueba de conceptos Pruebas de un proyecto de software en desarrollo. Pruebas de funcionamiento. En mi caso, estoy probando la manera en que se configura una tabla particionada en PostgreSQL y la manera de usarla para la carga y recuperación de los datos que viven en ella.

Le pedí a ChatGPT que diseñara un script en el lenguaje Python que me permitiera generar decenas de miles de registros para poblar una tabla que reside en una base de datos en un servidor de PostgreSQL. Especifiqué el nombre de la tabla en cuestión y su estructura, es decir, los campos que contiene y el tipo de dato de cada uno de ellos. Además, especifique que debería usar la biblioteca psycopg2 de Python. Los datos generados al azar los debería insertar en la tabla mencionada por medio de una sentencia INSERT.

El resultado que me entregó es el siguiente:

import psycopg2 from random 
import randint from datetime 
import datetime, timedelta
#Configuración de la conexión a la base de datos
conn = psycopg2.connect( host="x.x.x.x", database="partition_test", user="postgres", password="*****", )

#Cursor para ejecutar las consultas SQL

cur = conn.cursor()

#Función para generar registros aleatorios
def generar_registros(cantidad): for i in range(cantidad): 
    city_id = randint(1, 5) 
    # Cambia los valores mínimo y máximo según tus necesidades 
    peaktemp = randint(15, 25) unitsales = randint(1, 20) 
    logdate = datetime.now() - timedelta(days=randint(1, 365)) 
    logdate_str = logdate.strftime('%Y-%m-%d')
    yield (city_id,     peaktemp, unitsales, logdate_str)

#Cantidad de registros a generar
cantidad_registros = 5000 # Cambia este valor según tus necesidades

#Generar registros aleatorios
registros = generar_registros(cantidad_registros)

#Insertar registros en la base de datos
query = "INSERT INTO measurement (city_id, peaktemp, unitsales, logdate) VALUES (%s, %s, %s, %s)" cur.executemany(query, registros)

# Confirmar cambios y cerrar la conexión
conn.commit() 
cur.close()
conn.close()

En el código mostrado obviamente estoy ocultando la IP de mi servidor y la contraseña del usuario postgres.

Los estructura de tablas generadas

Las tablas que se generaron con el script de lenguaje SQL se muestran en la aplicación psql de PostgreSQL:

partition_test=# \dt List of relations 
Schema | Name | Type | Owner
--------+-------------------------+-------------------+---------- 
public | measurement | partitioned table | postgres 
public | measurement_traditional | table | postgres 
public | measurement_y2022m01 | table | postgres 
public | measurement_y2022m02 | table | postgres 
public | measurement_y2022m03 | table | postgres 
...
public | measurement_y2023m08 | table | postgres 
public | measurement_y2023m09 | table | postgres 
public | measurement_y2023m10 | table | postgres 
public | measurement_y2023m11 | table | postgres 
public | measurement_y2023m12 | table | postgres

La estructura de la primera tabla es la siguiente

partition_test=# \d measurement_y2022m01 
Table "public.measurement_y2022m01" Column | Type | Collation | Nullable | Default 
-----------+---------+-----------+----------+--------- 
city_id | integer | | not null | 
logdate | date | | not null | 
peaktemp | integer | | | 
unitsales | integer | | | 
Partition of: measurement FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')

Una vez ejecutado el script de Python para realizar la inserción de los datos de prueba, el resultado de una consulta a la tabla measurement_y2023m03 es la siguiente:

partition_test=# select * from measurement_y2023m03 limit 20;
 city_id | logdate | peaktemp | unitsales 
---------+------------+----------+-----------
 4 | 2023-03-07 | 22 | 6
 5 | 2023-03-06 | 15 | 1
 2 | 2023-03-06 | 17 | 12
 2 | 2023-03-04 | 17 | 5
 3 | 2023-03-01 | 18 | 2
 5 | 2023-03-01 | 23 | 4
 1 | 2023-03-07 | 23 | 16
 1 | 2023-03-01 | 19 | 5
 4 | 2023-03-04 | 25 | 1
 5 | 2023-03-05 | 25 | 15
 3 | 2023-03-07 | 21 | 2
 5 | 2023-03-06 | 23 | 19
 4 | 2023-03-02 | 19 | 18
 3 | 2023-03-01 | 24 | 11
 4 | 2023-03-03 | 20 | 8
 1 | 2023-03-04 | 25 | 17
 3 | 2023-03-02 | 24 | 10
 2 | 2023-03-01 | 25 | 6
 2 | 2023-03-01 | 25 | 6
 3 | 2023-03-03 | 16 | 2
 (20 rows)

Lo cual es lo esperado.

Finalmente hice la prueba de insertar y borrar registros con fechas específicas para convencerme que el particionamiento funcionaba correctamente, lo cual sucedió.

Puesto que para esta última parte no acudí a ChatGPT dejaré hasta aquí este documento que ya es bastante largo.

Roberto Andrade F.

randradedev@gmail.com

Marzo de 2023.