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
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
Recent
- Database UUID primaire sleutels van je webapplicatie verbergen
- Don't Repeat Yourself (DRY) met Jinja2
- SQLAlchemy, PostgreSQL, maximum aantal rijen per user
- Toon de waarden in SQLAlchemy dynamische filters
- Veilige gegevensoverdracht met Public Key versleuteling en pyNaCl
- rqlite: een alternatief voor SQLite met hoge beschikbaarheid en distributed
Meest bekeken
- Met behulp van Python's pyOpenSSL om SSL-certificaten die van een host zijn gedownload te controleren
- Gebruik van UUIDs in plaats van Integer Autoincrement Primary Keys met SQLAlchemy en MariaDb
- Maak verbinding met een dienst op een Docker host vanaf een Docker container
- PyInstaller en Cython gebruiken om een Python executable te maken
- SQLAlchemy: Gebruik van Cascade Deletes om verwante objecten te verwijderen
- Flask RESTful API verzoekparametervalidatie met Marshmallow-schema's