angle-uparrow-clockwisearrow-counterclockwisearrow-down-uparrow-leftatcalendarcard-listchatcheckenvelopefolderhouseinfo-circlepencilpeoplepersonperson-fillperson-plusphoneplusquestion-circlesearchtagtrashx

SQLAlchemy PostgreSQL: Voeg een tweede BigInteger Primary Key toe

Het gebruik van UUIDs is prima, maar als je te maken hebt met miljoenen records, blijf dan bij BigIntegers

28 mei 2022
post main image
https://www.pexels.com/nl-nl/@fotios-photos

Stel dat we UUID Primary Keys in ons project gebruiken. Dit werkt prima, maar er zijn ook een paar tabellen met miljoenen records. We weten dat SELECT en INSERT operaties met UUID gebaseerde Primary Key tabellen
veel langzamer kunnen zijn vergeleken met Integer gebaseerde Primary Key tabellen. Wat we willen is dat deze enorme tabellen alleen BigInteger Primary Keys hebben en ze te koppelen aan andere UUID primary key gebaseerde tabellen, met BigInteger Foreign Keys.

Test en TestResultaat

In ons project hebben we de modellen Test en 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)

Stap1: In TestResult, vervang de UUID Primary Key door een BigInteger Primary Key

Dit is gemakkelijk. In TestResult vervangen we de regel

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

door:

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

Stap2: In Test, voeg een tweede auto-increment BigInteger 'Primairy Key' toe

Hiervoor moeten we graven in de PostgreSQL en SQLAlchemy documenten. Het blijkt dat we voor PostgreSQL 10 en hoger IDENTITY kolommen kunnen gebruiken en dit wordt ondersteund door SQLAlchemy, zie onderstaande links. Om een tweede auto-increment 'Primary Key' aan Test toe te voegen voegen we een extra kolom:

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

Stap3: Vervang in TestResult de UUID Foreign Key door een BigInteger Foreign Key

Ook dit is eenvoudig, we linken niet terug naar test.id maar naar test.id_bi. Vervangen:

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

door:

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

Onze modellen na deze wijzigingen

Na onze wijzigingen zien Test en TestResult er als volgt uit:

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)

Samenvatting

Wanneer je met miljoenen records te maken hebt, kun je het beste UUID's vermijden. Zij kunnen SELECT, en wat nog belangrijker is INSERT operaties aanzienlijk vertragen. Hier hebben we alle UUIDs verwijderd uit de tabel die miljoenen records bevat door een tweede BigInteger 'Primary Key' aan Test toe te voegen en de Foreign Key te veranderen van UUID in BigInteger. Sneller dan dat kunnen we niet.

Links / credits

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

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

Lees meer

SQLAlchemy

Laat een reactie achter

Reageer anoniem of log in om commentaar te geven.

Opmerkingen

Laat een antwoord achter

Antwoord anoniem of log in om te antwoorden.