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

SQLAlchemy PostgreSQL: Hinzufügen eines zweiten BigInteger Primary Key

Die Verwendung von UUIDs ist in Ordnung, aber wenn Sie es mit Millionen von Datensätzen zu tun haben, sollten Sie bei BigIntegers bleiben.

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

Angenommen, wir verwenden UUID Primary Keys in unserem Projekt. Das funktioniert gut, aber es gibt auch einige Tabellen mit Millionen von Datensätzen. Wir wissen, dass SELECT und INSERT -Operationen mit UUID-basierten Primary Key -Tabellen
im Vergleich zu Integer -basierten Primary Key -Tabellen viel langsamer sein können. Wir möchten, dass diese riesigen Tabellen nur BigInteger Primary Keys haben und mit anderen UUID -Tabellen, die auf Primärschlüsseln basieren, mit BigInteger Foreign Keys verbunden werden.

Test und TestResult

In unserem Projekt haben wir die Modelle Test und 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)

Schritt 1: Ersetzen Sie in TestResult die UUID Primary Key durch eine BigInteger Primary Key

Das ist ganz einfach. In TestResult ersetzen wir die Zeile:

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

durch:

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

Schritt 2: In Test ein zweites Autoinkrement BigInteger 'Primairy Key' hinzufügen

Hierfür müssen wir in den Dokumenten PostgreSQL und SQLAlchemy suchen. Es scheint, dass wir für PostgreSQL 10 und höher IDENTITY-Spalten verwenden können und dies wird von SQLAlchemy unterstützt, siehe Links unten. Um ein zweites Autoinkrement 'Primary Key' zum Test hinzuzufügen, fügen wir eine zusätzliche Spalte hinzu:

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

Schritt 3: Ersetzen Sie in TestResult die UUID Foreign Key durch eine BigInteger Foreign Key

Auch dies ist einfach, da wir nicht auf test.id, sondern auf test.id_bi zurückverweisen. Ersetzen:

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

durch:

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

Unsere Modelle nach diesen Änderungen

Nach unseren Änderungen sehen Test und TestResult wie folgt aus:

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)

Zusammenfassung

Beim Umgang mit Millionen von Datensätzen ist es am besten, UUIDs zu vermeiden. Sie können die SELECT- und vor allem die INSERT -Vorgänge erheblich verlangsamen. Hier haben wir alle UUIDs aus der Tabelle mit Millionen von Datensätzen entfernt, indem wir eine zweite BigInteger 'Primary Key' zu Test hinzugefügt und die Foreign Key von UUID zu BigInteger geändert haben. Schneller geht's nicht mehr.

Links / Impressum

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

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

Mehr erfahren

SQLAlchemy

Einen Kommentar hinterlassen

Kommentieren Sie anonym oder melden Sie sich zum Kommentieren an.

Kommentare

Eine Antwort hinterlassen

Antworten Sie anonym oder melden Sie sich an, um zu antworten.