SQLAlchemy PostgreSQL: Добавить второй BigInteger Primary Key
Использование UUIDs - это хорошо, но когда вы имеете дело с миллионами записей, используйте BigIntegers.
Предположим, мы используем 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
Недавний
- Скрытие первичных ключей базы данных UUID вашего веб-приложения
- Don't Repeat Yourself (DRY) с Jinja2
- SQLAlchemy, PostgreSQL, максимальное количество строк для user
- Показать значения в динамических фильтрах SQLAlchemy
- Безопасная передача данных с помощью шифрования Public Key и pyNaCl
- rqlite: альтернатива dist с высокой степенью готовности и SQLite
Большинство просмотренных
- Используя Python pyOpenSSL для проверки SSL-сертификатов, загруженных с хоста
- Использование UUID вместо Integer Autoincrement Primary Keys с SQLAlchemy и MariaDb
- Подключение к службе на хосте Docker из контейнера Docker
- Использование PyInstaller и Cython для создания исполняемого файла Python
- SQLAlchemy: Использование Cascade Deletes для удаления связанных объектов
- Flask Удовлетворительный запрос API проверка параметров запроса с помощью схем Маршмэллоу