SQLAlchemy PostgreSQL: Añadir un segundo BigInteger Primary Key

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