SQLAlchemy PostgreSQL : Ajouter un deuxième BigInteger Primary Key

Supposons que nous utilisions UUID Primary Keys dans notre projet. Cela fonctionne bien, mais il y a aussi quelques tables avec des millions d'enregistrements. Nous savons que les opérations SELECT et INSERT avec des tables UUID basées sur Primary Key
peuvent être beaucoup plus lentes par rapport aux tables Integer basées sur Primary Key . Ce que nous voulons, c'est que ces énormes tables n'aient que BigInteger Primary Keys et qu'elles soient liées à d'autres tables à clé primaire UUID , avec BigInteger Foreign Keys.

Test et TestResult

Dans notre projet, nous avons les modèles Test et 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)

Etape 1 : Dans TestResult, remplacer le UUID Primary Key par un BigInteger Primary Key.

C'est facile. Dans TestResult, nous remplaçons la ligne :

    id = sa.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)

par :

    id = sa.Column(sa.BigInteger, primary_key=True)

Etape 2 : Dans Test, ajouter un deuxième BigInteger auto-incrémenté 'Primairy Key'.

Pour cela, nous devons creuser dans les documents PostgreSQL et SQLAlchemy . Il apparaît que pour PostgreSQL 10 et plus, nous pouvons utiliser des colonnes IDENTITY et que ceci est supporté par SQLAlchemy, voir les liens ci-dessous. Pour ajouter un deuxième auto-incrément 'Primary Key' à Test, nous ajoutons une colonne supplémentaire :

    # extra 'primary key' for use with test_result
    id_bi = sa.Column(sa.BigInteger, sa.Identity(always=True, increment=1), unique=True, index=True)

Etape 3 : Dans TestResult, remplacer le UUID Foreign Key par un BigInteger Foreign Key.

Ceci est également facile, nous ne faisons pas de lien vers test.id mais vers test.id_bi. Remplacer :

    # foreign key
    test_id = sa.Column(pg.UUID(as_uuid=True), sa.ForeignKey('test.id'), index=True)

par :

    # foreign key
    test_id_bi = sa.Column(sa.BigInteger, sa.ForeignKey('test.id_bi'), index=True)

Nos modèles après ces changements

Après nos changements, Test et TestResult ressemblent à ceci :

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)

Résumé

Lorsque vous traitez des millions d'enregistrements, il est préférable d'éviter les UUID. Elles peuvent ralentir considérablement les opérations de SELECT, et surtout de INSERT . Ici, nous avons supprimé tous les UUID de la table contenant des millions d'enregistrements en ajoutant un deuxième BigInteger 'Primary Key' à Test et en modifiant le Foreign Key de UUID à BigInteger. On ne peut pas aller plus vite que ça.

Liens / crédits

PostgreSQL Identity Column
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-identity-column

SQLAlchemy - PostgreSQL
https://docs.sqlalchemy.org/en/14/dialects/postgresql.html