Dice Chess Wiki

🎓 Что есть в PostgreSQL, чего НЕТ в SQLite

2 min read

Это исключительно важный вопрос, который влияет на то, какие “фишки” базы данных мы можем использовать.

Я вспомнил, что в правилах твоего проекта (dicechess-lab/AGENTS.md) прямо указано: “SQLite: WAL mode for low-RAM devices”. Raspberry Pi 3 имеет всего 1 ГБ оперативной памяти и работает с SD-картой. Разворачивать полноценный PostgreSQL в Docker на RPi3 можно, но это “съест” много ресурсов. Для RPi3 и быстрых локальных тестов SQLite — идеальный выбор.

Однако у нас возникает проблема совместимости диалектов SQL, если мы хотим, чтобы один и тот же код (через SQLAlchemy или сырые SQL-миграции) работал и на SQLite (staging/local), и на PostgreSQL (production server ASUS).

Что есть в PostgreSQL, чего НЕТ в SQLite:

  1. Нативный тип ENUM (о котором мы только что говорили). SQLite его не поддерживает.
  2. Тип массивов VARCHAR[] (который я предложил для поля played_moves). В SQLite нет нативных массивов.
  3. Нативный тип UUID. В SQLite UUID хранится просто как строка VARCHAR(36).

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

Нам нужно использовать “наименьший общий знаменатель”, который отлично работает в обеих базах:

  1. Вместо ENUM используем VARCHAR(20) + CHECK Тот самый вариант, который я изначально написал в плане. В коде (Python/SQLAlchemy) мы создадим класс Enum, и SQLAlchemy будет валидировать его на уровне приложения, а в саму базу (и в SQLite, и в Postgres) писать обычную текстовую строку 'timeout'. Это гарантирует 100% совместимость.

  2. Вместо массивов VARCHAR[] используем JSON Поле played_moves в таблице turns мы сделаем типа JSON (в Postgres это будет быстрый JSONB, а в SQLite — текстовое поле с проверкой на валидный JSON). Список микро-ходов будет храниться как ["b1c3", "e2e4"]. Оба движка умеют отлично работать с JSON.

  3. Вместо типа UUID используем VARCHAR(36) Сами значения будут настоящими UUID, но в структуре таблиц мы определим их как строки.

Моя рекомендация по инфраструктуре:

  • Local Dev (Твой Mac): Можно использовать SQLite для скорости разработки, либо поднять локальный PostgreSQL через Docker (чтобы среда была ближе к проду).
  • Staging (Raspberry Pi 3): Однозначно SQLite (в режиме WAL). Легко, быстро, не убивает SD-карту лишними фоновыми процессами.
  • Production Analytics (ASUS Server): Однозначно PostgreSQL. Когда ты загрузишь туда 10 миллионов партий, SQLite начнет тяжело дышать на сложных аналитических JOIN-запросах со статистикой, а Postgres “проглотит” это благодаря параллельным запросам и большому объему оперативной памяти ASUS-сервера.

Итог: Если ты хочешь сохранить поддержку SQLite для RPi3, нам нужно вернуться к варианту VARCHAR + CHECK для поля termination и поменять VARCHAR[] на JSON для played_moves.

Согласен ли ты на такой компромисс ради универсальности кода?

Graph View