Ответы пользователя по тегу Проектирование баз данных
  • Как лучше хранить методы выплат?

    Melkij
    @Melkij
    PostgreSQL DBA
    Тот самый случай где как раз уместнее просто jsonb.
    Для каждой системы будут нужны свои данные, это всё равно у вас будет фабрика в коде для обработки и валидации требований конкретной системы. А поиск по этому json напротив является исключением, а не частой ситуацией.

    То есть payment_data: id, user_id, payment_system (enum), data jsonb
    В users default_payment FK на payment_data

    PS: по моему опыту, лучше сразу предположите что в будущем возможно деление платежа в настраиваемых пользователем пропорциях на несколько кошельков. С задачей такой встречался несколько раз, во всех случаях через несколько лет приходили к задаче "нужно дать возможность пользователю настроить выплаты на несколько кошельков".
    Ответ написан
    2 комментария
  • Как будет выглядеть схема для таблицы с отчетами за месяц?

    Melkij
    @Melkij
    PostgreSQL DBA
    create table aggregate_montly (
        user_id bigint not null references users(id),
        month date not null check (date_trunc('month', date_period) = date_period),
        orders_count bigint not null default 0,
        -- прочие предаггрегированные данные
        primary key (user_id, month)
    );


    Дату писать всегда как первое число месяца, check constraint гарантирует, что вы не запишете по ошибке данные за 8 февраля вместо месяца.
    id выкинут за бессмысленностью. Тем более uuid.
    Ответ написан
    Комментировать
  • Создать несколько таблиц или попробовать унифицировать?

    Melkij
    @Melkij
    PostgreSQL DBA
    json поле.
    Данные авторизации нужны только конкретному коду, работающему с этой авторизацией. Искать по токену авторизации, какому же сервису API принадлежит этот токен вы ведь не будете всё равно. (хотя и так можно будет при желании и достаточно современной СУБД)

    но в таком случае, скорее всего, понадобиться таблица с именами провайдеров

    А она у вас всё равно есть или будет.
    Хоть вы сами работаете с API и к каждому сервису максимум один комплект данных авторизации (таблица, где будут ключи храниться и будет такой таблицей с именами провайдеров). Хоть вы даёте своим пользователям возможность добавить API себе - тогда у вас будет табличка user_id, api_provider_id, credentials
    Ответ написан
    1 комментарий
  • В чем польза шардирования БД при наличии индексов?

    Melkij
    @Melkij
    PostgreSQL DBA
    Пока у вас всего лишь миллионы записей - то вам ни шардирование ни даже партицирование не нужно.

    В общем, в чем конкретно выигрыш от шардирования?

    Масштабирование операций записи когда более производительные диски и уже не поставить.
    Ответ написан
    Комментировать
  • В Postgresql схема: зачем нужна и как я её могу использовать в своих проектах?

    Melkij
    @Melkij
    PostgreSQL DBA
    В оракле схем разве нет?..

    schema - дополнительный уровень структуризации объектов. Как namespace в программировании. И, к слову, входит в стандарт SQL.

    Вы можете сделать таблицы:
    user_subscriptions
    user_orders
    user_favorites
    Вы можете сделать
    user.subscriptions
    user.orders
    user.favorites
    И в этом нет никакой разницы для СУБД. Но может быть удобно разработчику оперировать не с сотней таблиц одним списком, десятки из которых с одинаковыми префиксами (т.к. относятся к своим сущностям), а отдельные схемы по сущностях.

    Пилить же одну таблицу на несколько смысла при этом не так много, зато добавляется хлопот.
    Если вы хотите давать прямой доступ пользователю к базе - то зачем? Не надо так делать в разделяемой среде. Любую СУБД можно положить каким-нибудь интересным запросом. А в то что люди временами будут писать интересные и сильно творческие запросы - по опыту DBA вам гарантирую. Иногда такого наворотят... 0,5тб временный файлов одним запросом, например. Или сожрать 30гб RAM и увести базу в аварийный рестарт от OOM.
    Ответ написан
    1 комментарий
  • Какие есть годные практики для работы с большим количеством записей в таблице (1M+)?

    Melkij
    @Melkij
    PostgreSQL DBA
    Объединение нескольких колонок в одну, для однотипных данных. Они будут храниться в формате JSON.

    JSON объёмнее плоской таблицы хотя бы из-за необходимости в хранении ключей.
    И, насколько знаю, mysql пока умеет только btree. Что подходит для произвольного поиска от слова никак. Только для заранее известного критерия.

    3. Партиционирование.

    планирую создать новую, "горячую" таблицу с идентичной структурой.

    Слово неплохое упомянуто. Осталось понять зачем всё остальное. Партицировать таблицы умеет штатно даже такая удивительная штука как mysql и очень давно.

    Профилируйте где время теряете и смотрите что с данными делаете.
    Озвученные цифры не впечатляют от слова совсем. Если человек хочет поиграться с разными подходами индексирования и производительностью сложных запросов - я посоветую с миллиона записей только начинать.

    Типичная ошибка - пытаетесь строить аггрегаты и прочие count(*) поверх миллионов строк налету.
    Ответ написан
    Комментировать
  • Как запретить ввод данных в колонку в sql таблице?

    Melkij
    @Melkij
    PostgreSQL DBA
    Ваша субд не умеет generated columns? Например, mysql умеет
    В более общем случае - добавляете триггер, в котором высчитываете новое значение и таким образом игнорируете все попытки его исправить вручную.
    Ответ написан
    Комментировать
  • Как ускорить запрос с DISTINCT в PostgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Планировщик Postgresql на данный момент не умеет loose index scan. Поэтому планировщик вполне обоснованно со своей точки зрения выбирает не использовать какие-либо индексы.

    Поэтому если необходим именно distinct по не очень селективному полю - то эффективнее объяснить планировщику что от него хотят руками: https://wiki.postgresql.org/wiki/Loose_indexscan

    Вам же скорее необходимо менять схему хранения данных.

    Возможно в самом запросе нужно явно указать, чтобы использовался индекс. Как это сделать в PostgreSQL?Возможно в самом запросе нужно явно указать, чтобы использовался индекс. Как это сделать в PostgreSQL?

    Никак. Сообщество сознательно PostgreSQL отказывается добавлять какие-либо хинты планировщика, предпочитая узнавать об ошибках планировщика как о багах и по возможности их исправлять.
    Ответ написан
    5 комментариев
  • Какие есть примеры архитектуры бд в которых у каждого пользователя отдельная база данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    Нужно чтобы у каждого пользователя была как бы отдельная база данных.

    Дело в том что у каждого пользователя будет свой словарь и еще некоторые данные.

    Между предложениями нет никакой связи, уж тем более причинно-следственной.

    Для начала ответьте, по каким соображениям вы решили отбросить обычную нормальную форму (это технический термин) с добавлением к сущностям поля со ссылкой на владельца.
    Ответ написан
    Комментировать
  • Какие типы данных выбирать для новой базы данных для экономии места?

    Melkij
    @Melkij
    PostgreSQL DBA
    Вероятно вы путаете понятия база и таблица. Иначе непонятно, что вы имеете в виду говор о FK между базами.

    Если цель оптимизировать занимаемое место для явно timeseries данных - то и используйте timeseries базы.
    Полновесный версионник с 23 байтами оверхеда на один только заголовок каждой строки (ещё не начиная считать данные, плюс выравнивание структур) сильно сомнительно что окажется компактнее ориентированных на timeseries данные базы.
    Ответ написан
    1 комментарий
  • Как в БД подставлять старые значения справочников?

    Melkij
    @Melkij
    PostgreSQL DBA
    Для начала между работником и учебным заведением связь многие-ко-многим.
    Во-вторых, с чего это для новых работников выбираются только новые значения? Можно быть одновременно новым работником и закончить лет *дцать назад именно бывшее тогда учебное заведение. Выбирать можно из всего списка, либо, что лучше, хранить для учебного заведения год открытия и год закрытия (default null) и выбирать подходящие по годам обучения.
    Если сохраняете ещё и даты обучения - то важно учесть, что учебное заведение может быть закрыто в любой момент между поступлением и выпуском студента. Поступил в одно заведение, выпустился из другого - запросто.

    Если нет особых требований к поиску учебных заведений, то достаточно хранить год закрытия (default null) и ссылку на новую форму после реорганизации, если есть (поэтому тоже default null).
    Для вашего примера будет КГПА например с edu_id = 105, reorg_to_id null и КГПУ со своим edu_id, датой закрытия и reorg_to_id = 105
    Ответ написан
    Комментировать
  • Автоматический set в mysql?

    Melkij
    @Melkij
    PostgreSQL DBA
    Зачем set? Таблица связей типично состоит из пары внешних ключей на связываемые таблицы. Т.е. обычно два инта и составной первичный ключ.

    Возможность есть понаписать триггер. Только сначала хотелось бы понять смысл.
    Ответ написан
    Комментировать
  • Как хранить и работать с деньгами в коде и базе данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    > Если пишите что-то вроде «деньги нельзя хранить в float» — пишите почему именно.
    Как почему? Потеря точности и постоянные округления, конечно. И весьма забавные вычисления около нуля.
    Но при чём тут float, когда рассматриваете int и decimal?

    Ещё одно «за» int — тупое целочисленное действие. В отличии от decimal, не являющегося простым типом данных.
    Ответ написан
    3 комментария