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

SQLAlchemy PostgreSQL: Добавить второй BigInteger Primary Key

Использование UUIDs - это хорошо, но когда вы имеете дело с миллионами записей, используйте BigIntegers.

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

Предположим, мы используем UUID Primary Keys в нашем проекте. Это прекрасно работает, но есть несколько таблиц с миллионами записей. Мы знаем, что операции SELECT и INSERT с таблицами UUID на базе Primary Key
могут быть намного медленнее по сравнению с таблицами Integer на базе Primary Key . Мы хотим, чтобы эти огромные таблицы имели только BigInteger Primary Keys и связывали их с другими таблицами UUID , основанными на первичном ключе, с BigInteger Foreign Keys.

Test и TestResult

В нашем проекте у нас есть модели Test и 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)

Шаг 1: В TestResult замените UUID Primary Key на BigInteger Primary Key.

Это просто. В TestResult мы заменяем строку:

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

by:

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

Шаг2: В Test добавляем второй автоинкремент BigInteger 'Primairy Key'.

Для этого нам нужно покопаться в документах PostgreSQL и SQLAlchemy . Оказывается, что для PostgreSQL 10 и выше мы можем использовать столбцы IDENTITY, и это поддерживается SQLAlchemy, см. ссылки ниже. Чтобы добавить второй автоинкремент 'Primary Key' в Test, мы добавим дополнительный столбец:

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

Шаг 3: В TestResult замените UUID Foreign Key на BigInteger Foreign Key.

Это также просто, мы не ссылаемся на test.id, а ссылаемся на test.id_bi. Замените:

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

на:

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

Наши модели после этих изменений

После наших изменений Test и TestResult выглядят следующим образом:

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)

Резюме

При работе с миллионами записей лучше избегать UUID. Они могут значительно замедлить работу SELECT и, что более важно, INSERT . Здесь мы удалили все UUID из таблицы, содержащей миллионы записей, добавив второй BigInteger 'Primary Key' в Test и изменив Foreign Key с UUID на BigInteger. Мы не можем стать быстрее, чем это.

Ссылки / кредиты

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

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

Подробнее

SQLAlchemy

Оставить комментарий

Комментируйте анонимно или войдите в систему, чтобы прокомментировать.

Комментарии

Оставьте ответ

Ответьте анонимно или войдите в систему, чтобы ответить.