Как улучшить базу данных PostgreSQL при разработке мобильного приложения

Дата публикации:

Большинство современных людей страстно желает находиться в курсе всех самых интересных и актуальных событий. Но справиться с потоком информации, бурлящей на просторах интернета, очень сложно. Скажем больше — практически невозможно. К чему мы это? К тому, что недавно наша команда работала над проектом Sparksfly. Если в двух словах, то он заключался в отфильтровке и агрегации информации с целого ряда социальных сетей. Пользователь ставит определенные фильтры и читает новости, которые ему интересны. Нерелевантная информация отсеивается. Просто и удобно.

А теперь к тому, с чем нам пришлось столкнуться. Среднестатистический пользователь внезапно имеет аккаунты сразу в нескольких социальных сетях. А это около 500-1000 постов в день. Разбирать такое количество информации очень трудно. И мы решили разработать приложение, которое бы помогало пользователю справляться с потоком разношерстных новостей.

Наши задачи

Какие функции должно выполнять приложение?

  • Собирать все данные из соцсетей (facebook, twitter, linkedin, instagram).
  • Позволять пользователю создавать собственные фильтры по следующим параметрам: социальная сеть, контакты из соцсети, ключевые слова.
  • Анализировать посты, выделять из них наиболее важные и показывать их пользователю.

В этой статье речь пойдет о структуре базы данных и проблемах, с которыми мы столкнулись:

  • Медленная запись в базу;
  • Медленная выборка;
  • Коллизии при записи в базу.

Проблема #1 — медленная запись в базу данных

Поскольку нам предстояло агрегировать данные, то для каждого их типа мы разработали таблицу с уникальным набором полей. А далее приводили всю входящую информацию к разработанным параметрам фильтрации. Абстрактно — мы получали примерно следующие данные:

{id: ‘1111’, autor: {Id: ‘1’, name: ‘Some User’, username: ‘someuser’ }, body: ‘postbody’, likes: ‘42’, photos: [{url: ‘url1’}, {url: ‘url2’}]}

Первоначальная структура базы выглядела следующим образом:

PostgreSQL

Users — зарегистрированные пользователи.
SocialProfiles — таблица с записями о профилях этих пользователей в соцсетях.
uid — идентификатор пользователя в соцсети;
service — соц сеть (строковое значение facebook | twitter | linkedin | instagram);
access_token, secret key — токены для обращения к API соцсети от имени пользователя.

Profiles — данные о пользователе — имя, юзернейм и т.д.
Posts.
uid — идентификатор поста в социальных сетях;
social_profile_id — ссылка на аккаунт автора;
body — тело поста.

Activities — так как один пост может быть в ленте сразу у нескольких пользователей, то нам понадобится еще одна таблица связей. Это — главная таблица. Именно по ней будет строиться выборка.

Как все это работает? Получая пост, мы его обрабатываем по всей строгости закона:

  1. Приложение берет данные “author” и ищет существующий SocialProfile;
  2. При его отсутствии создает записи в двух таблицах SocialProfiles и Profiles (SocialProfile создается без user_id — то есть пост приходит от пользователя, которого нет в системе. Если же он зарегистрируется, то мы получим social_uid, а также свяжем social_profile и user);
  3. Создает пост и фото (если таковые имеются) — тут все относительно просто;
  4. Создает запись в таблице activities и связывает пост с пользователем.

В качестве базы данных мы решили использовать PostgreSQL. Также было принято решение сохранять входные данные для возможности, в случае чего, все пересчитать. Для этого на помощь пришла MongoDB.

Мы разработали и эмулировали инструмент для нагрузочного тестирования сервера и приложения. Ожидаемый поток данных — 1 миллион постов в сутки.

Первой упала MongoDB. Она не справилась с банальной задачей записывать данные в дамп. И при большом потоке запросов просто лочила диск. Тогда мы решили хранить данные в postgres, в сериализованном виде.

В результате возник новый алгоритм работы:

  1. В таблицу пост добавляем поля: author_uid, author_username, author_full_name, author_picture.
  2. При создании поста ищем его существующий профиль, и если таковой имеется, проставляем связь в sociall_profile_id.
  3. Если профиля нет, просто ничего не делаем. Создаем пост без привязки к профилю.
  4. Раз в день парсим друзей пользователя. Но не проставляем связи при создании нового профиля.
  5. В результате все новые посты выходят уже со связью. А при регистрации пользователя во всех его постах имеется author_uid, и мы всегда можем восстановить связь.

Таким образом, при обработке больших объемов данных огромную пользу приносит их избыточность. Ведь мы можем сразу отобразить всю необходимую информацию и продемонстрировать ее пользователю. А потом уже сохранить данные в нужном для нас виде

Проблема #2 — медленная выборка данных

Итак, у нас ежедневно добавляется 400 — 500 тысяч постов. Это около миллиона записей в базе данных.

Еще до релиза мы провели нагрузочное тестирование, и наложили индексы, оптимизировав самые медленные запросы. Правда здесь мы тоже не учли одну важную вещь. Тестирование проводилось на пустой базе, и в результате было сгенерировано примерно 2 000 000 записей. А все это следовало делать с не менее, чем 10 000 000 записей.

В результате мы пришли к следующим выводам:

  • Индексы необходимы для всех полей. Поначалу запрос всегда достаточно быстрый, но это обязательно изменится, как только база данных увеличится.
  • Порядок в составных индексах исключительно важен. Первым идет поле, чье дерево индексов менее вырождено.
  • В связывающие таблицы, кроме составного индекса, необходимо добавить индекс на каждое поле.

В итоге приложение стало работать быстрее. Но когда база снова выросла в размерах, мы опять уперлись в проблему производительности. После того как количество постов приблизилось к миллиарду, даже поиск по индексу стал занимать довольно много времени. Между тем, проверка выполняется каждый раз перед созданием нового поста. Аналогичная ситуация с поиском по social_profiles.

Но выход из ситуации мы все-таки нашли. Им стал Partitioning постгреса. Это механизм, позволяющий разбить огромные таблицы на физически разные части.

Так мы и сделали. Разбили таблицы по социальным сетям и получили следующую структуру:

Activities
— facebook_activities
— twitter_activities
— instagram_activities
— linkedin_activities

SocialProfiles
— facebook_social_profiles
— twitter_social_profiles
— instagram_social_profiles
— linkedin_social_profiles

Posts
— facebook_posts
— instagram_posts
— twitter_posts
— linkedin_posts

Как это работает? На мастер таблицу навешивается триггер, раскидывающий данные по дочерним таблицам. В запросах мы продолжаем делать выборку из мастер таблицы. Но если они упираются в поле service, то постгрес при выборке проходит не все записи, а только те, что находятся в указанных разделах. Затем мы везде, где только можно переписали запросы, чтобы они использовали имя социальной сети в условии. И тут наше приложение вновь заработало с удовлетворительной скоростью.

При этом подходе важно понимать, что индексы, добавляемые на мастер таблицу, не перенесутся на дочерние таблицы автоматически. Их нужно создавать вручную и не забывать вносить коррективы при изменении индексов мастер таблиц.

Еще один важный момент — запрос на вставку в мастер больше не вернет ID созданной записи. В Rails необходимо добавить insert_returning: false параметр в config/database.yml.

Когда мы столкнулись с проблемой производительности в следующий раз, она была решена предельно простым способом: хранить в базе только данные за последние несколько месяцев (примерно 2 миллиарда), а остальные перемещать в архивную базу данных.

Проблема #3 — коллизии при записи в базу данных

В процессе разработки мы заметили, что у нас в базе появляются совершенно идентичные посты. Но ведь у нас была Rails валидация на уникальность. А значит, такого быть не должно.

При изучении проблемы оказалось, что происходят коллизии при записи в базу данных. Соответственно, когда проходил запрос на уникальность — он мог уже быть записан в базу. Но коммит, который его туда записал, не подтвержден, и валидация проходила.

Мы решили эту проблему следующим образом. Сделали индексирование по полю uid в таблице posts уникальным. И разработали свой метод для ActiveRecord:

Он ищет запись в базе и в случае неудачи делает попытку записи. Если же база возвращает ошибку валидации — попытка повторяется. Мы специально не переопределяли метод save, так как хотели продемонстрировать, что за сохранением стоит кастомная логика.

На первый взгляд столь надежная схема не нуждается в retry counter. Но все не так просто. Случиться может всякое, поэтому всегда стоит перестраховаться. Но об этом мы поговорим подробнее в следующих статьях.

Выводы

  1. Работа с большой базой данных требует ответственности и постоянного мониторинга. Запрос, который только что был очень быстрым, в какой-то момент может стать блокирующим. Также высока вероятность пересмотра используемых индексов. В случае же с партишингом не стоит забывать добавлять индексы на дочерние таблицы.
  2. Мы пришли к выводу, что PostgreSQL — отличный инструмент. Он целиком и полностью справился с поставленной задачей. При этом мы даже не использовали огромное количество настроек, позволяющих очень тонко варьировать его работу.

Мы Крым Диджитал

С 2015 года мы предоставляем полный цикл услуг мобильной и веб-разработки клиентам из различных отраслей и разных стран.

Подпишись
на наши новости

Контакты пресс-службы

+ 7 (926) 118-80-32

WhatsApp, Viber, Telegram

Давайте обсудим Ваш проект

или свяжитесь с нами по почте projects@crimeadigital.ru

Нажимая кнопку «Отправить», вы даете согласие на обработку персональных данных

Заполните форму или свяжитесь
удобным для Вас способом

Контакты

г. Севастополь, ул. Руднева, д.41, 4 этаж технопарк ИТ-Крым +7 978 679-76-353 agro@crimeadigital.ru

Социальные сети

Нажимая на кнопку, вы даете согласие на обработку персональных данных и соглашаетесь c политикой конфиденциальности

Крым Диджитал приняла участие в стратегической сессии

Руководители Крым Диджитал приняли участие в стратегической сессии, которая прошла на базе СевГУ 10 июня. Вместе с Правительством Севастополя, Институтом информационных технологий и управления в технических системах СевГУ и приглашенными ИТ-компаниями города обсудили перспективу развития системы высшего образования в Севастополе.Представители бизнеса, власти и образовательной системы выступали со своим видением будущих потребностей региона в кадрах, поднимали насущные вопросы обучения студентов, прохождения практики и дальнейшего трудоустройства. Крым Диджитал является амбассадором идеи образования и взращивания молодых кадров, развивает образовательные проекты и на протяжении 5 последних лет ведет активную работу в направлении поддержки и развития молодых специалистов ИТ-отрасли Крыма.

Руководители Крым Диджитал приняли участие в стратегической сессии, которая прошла на базе СевГУ 10 июня.

Вместе с Правительством Севастополя, Институтом информационных технологий и управления в технических системах СевГУ и приглашенными ИТ-компаниями города обсудили перспективу развития системы высшего образования в Севастополе.
Представители бизнеса, власти и образовательной системы выступали со своим видением будущих потребностей региона в кадрах, поднимали насущные вопросы обучения студентов, прохождения практики и дальнейшего трудоустройства.

Крым Диджитал является амбассадором идеи образования и взращивания молодых кадров, развивает образовательные проекты и на протяжении 5 последних лет ведет активную работу в направлении поддержки и развития молодых специалистов ИТ-отрасли Крыма.

Выпуск курса Software Testing

Мы поздравляем выпускников нашего первого в этом году курса Крым Диджитал Академии по Software Testing! Всего курс успешно завершили 13 человек. В течение 2 месяцев несмотря на теплую погоду и манящее море ребята ответственно посещали занятия 2 раза в неделю, делали домашние задания и проверочные работы. Трое начинающих специалистов теперь стажеры нашей компании. Следующий курс намечен на август. Не пропусти анонс записи!

Мы поздравляем выпускников нашего первого в этом году курса Крым Диджитал Академии по Software Testing!

Всего курс успешно завершили 13 человек. В течение 2 месяцев несмотря на теплую погоду и манящее море ребята ответственно посещали занятия 2 раза в неделю, делали домашние задания и проверочные работы. Трое начинающих специалистов теперь стажеры нашей компании.

Следующий курс намечен на август. Не пропусти анонс записи!

Лицензия на образовательную деятельность

В 2022 году мы получили лицензию на образовательную деятельность по программам дополнительного профессионального образования! Теперь мы можем обучать специалистов по направлениям Ruby on Rails, ReactJS и Software Testing и выдавать удостоверения о повышении квалификации государственного образца.
В 2022 году мы получили лицензию на образовательную деятельность по программам дополнительного профессионального образования! Теперь мы можем обучать специалистов по направлениям Ruby on Rails, ReactJS и Software Testing и выдавать удостоверения о повышении квалификации государственного образца.

Мы вошли в Реестр эффективно и социально значимых предприятий.

По результатам ежегодной финансово-экономической аналитики Межотраслевой рейтинговой компании Крым Диджитал включена в Реестр эффективных и социально значимых предприятий. По итогу аналитики, в рамках отрасли (ОКВЭД 62.01) и региона Крым, CDG вошло в 4% лучших компаний страны, с результатом – 92 балла!
По результатам ежегодной финансово-экономической аналитики Межотраслевой рейтинговой компании Крым Диджитал включена в Реестр эффективных и социально значимых предприятий. По итогу аналитики, в рамках отрасли (ОКВЭД 62.01) и региона Крым, CDG вошло в 4% лучших компаний страны, с результатом – 92 балла!