Как улучшить базу данных 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’}]}

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

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 — отличный инструмент. Он целиком и полностью справился с поставленной задачей. При этом мы даже не использовали огромное количество настроек, позволяющих очень тонко варьировать его работу.