SQLAlchemy PostgreSQL: Añadir un segundo BigInteger Primary Key
Utilizar UUIDs está bien, pero cuando se trata de millones de registros, hay que utilizar BigIntegers
Supongamos que utilizamos UUID Primary Keys en nuestro proyecto. Esto funciona bien, pero también hay algunas tablas con millones de registros. Sabemos que las operaciones con SELECT y INSERT basadas en UUID
pueden ser mucho más lentas en comparación con las tablas Integer . Lo que queremos es que estas enormes tablas sólo tengan BigInteger Primary Keys y que se vinculen a otras tablas basadas en clave primaria UUID , con BigInteger Foreign Keys.
Test y TestResultado
En nuestro proyecto tenemos los modelos Test y TestResult:
import uuid
import sqlalchemy as sa
import sqlalchemy.dialects.postgresql as pg
class Test(Base):
__tablename__ = 'test'
id = sa.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
...
class TestResult(Base):
__tablename__ = 'test_result'
id = sa.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
# foreign key
test_id = sa.Column(pg.UUID(as_uuid=True), sa.ForeignKey('test.id'), nullable=False, index=True)
Paso 1: En TestResult, reemplazar el UUID Primary Key por un BigInteger Primary Key
Esto es fácil. En TestResult reemplazamos la línea
id = sa.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
por:
id = sa.Column(sa.BigInteger, primary_key=True)
Paso 2: En Test, añadir un segundo autoincremento BigInteger 'Primairy Key'
Para ello tenemos que indagar en los documentos PostgreSQL y SQLAlchemy . Parece que para PostgreSQL 10 y superiores podemos utilizar columnas IDENTITY y esto es soportado por SQLAlchemy, ver enlaces más abajo. Para añadir un segundo autoincremento 'Primary Key' a Test añadimos una columna extra:
# extra 'primary key' for use with test_result
id_bi = sa.Column(sa.BigInteger, sa.Identity(always=True, increment=1), unique=True, index=True)
Paso3: En TestResult, reemplazar el UUID Foreign Key por un BigInteger Foreign Key
Esto también es fácil, no enlazamos a test.id sino a test.id_bi. Reemplazar:
# foreign key
test_id = sa.Column(pg.UUID(as_uuid=True), sa.ForeignKey('test.id'), index=True)
por:
# foreign key
test_id_bi = sa.Column(sa.BigInteger, sa.ForeignKey('test.id_bi'), index=True)
Nuestros modelos después de estos cambios
Después de nuestros cambios, Test y TestResult se ven así:
class Test(Base):
__tablename__ = 'test'
id = sa.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
# extra 'primary key' for use with test_result
id_bi = sa.Column(sa.BigInteger, sa.Identity(always=True, increment=1), unique=True, index=True)
class TestResult(Base):
__tablename__ = 'test_result'
id = Column(BigInteger, primary_key=True)
# foreign key
test_id_bi = sa.Column(sa.BigInteger, sa.ForeignKey('test.id_bi'), index=True)
Resumen
Cuando se trata de millones de registros, es mejor evitar los UUID. Pueden ralentizar considerablemente las operaciones de SELECT y, sobre todo, de INSERT . Aquí hemos eliminado todos los UUID de la tabla que contiene millones de registros añadiendo un segundo BigInteger 'Primary Key' a Test y cambiando el Foreign Key de UUID a BigInteger. No podemos ser más rápidos que eso.
Enlaces / créditos
PostgreSQL Identity Column
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-identity-column
SQLAlchemy - PostgreSQL
https://docs.sqlalchemy.org/en/14/dialects/postgresql.html
Leer más
SQLAlchemy
Recientes
- Cómo ocultar las claves primarias de la base de datos UUID de su aplicación web
- Don't Repeat Yourself (DRY) con Jinja2
- SQLAlchemy, PostgreSQL, número máximo de filas por user
- Mostrar los valores en filtros dinámicos SQLAlchemy
- Transferencia de datos segura con cifrado de Public Key y pyNaCl
- rqlite: una alternativa de alta disponibilidad y dist distribuida SQLite
Más vistos
- Usando Python's pyOpenSSL para verificar los certificados SSL descargados de un host
- Usando UUIDs en lugar de Integer Autoincrement Primary Keys con SQLAlchemy y MariaDb
- Conectarse a un servicio en un host Docker desde un contenedor Docker
- Usando PyInstaller y Cython para crear un ejecutable de Python
- SQLAlchemy: Uso de Cascade Deletes para eliminar objetos relacionados
- Flask RESTful API validación de parámetros de solicitud con esquemas Marshmallow