Одна большая таблица или две разных

Добрый день. Мне нужна консультация по архитектуре.

Короткая формулировка вопроса: При SELECT по первичному ключу имеет ли значение размер таблицы?

Подробное описание проблемы:

Есть таблица на 10 Гб.

CREATE TABLE actions ( user_uni INT NOT NULL, project_id INT NOT NULL, action_time INT, status SMALLINT )

Первичный ключ составной: user_uni, project_id

Нужно постоянно проверять выполнил ли пользователь user_uni действие по проекту project_id.

Есть вариант разбить таблицу на две actions (1-2 ГБ) и actions_history (8-9 ГБ), где actions будет хранить только действия по актуальным проектам, а actions_history - действия по проектам, которые на данный момент закрыты.

Я не сомневался бы в правильности этого решения, но есть один нюанс: закрытый проект может быть открыт заново. В случае если проект будет открыт заново (а это происходит регулярно) нужно перенести данные из actions_history в actions, а это может быть от 1000 до 100000 строк. INSERT 100000 строк в одну таблицу и DELETE 100000 из другой таблицы существенная нагрузка.

Возникает вопрос: а имеет ли смысл разделение на две таблицы?

Спасибо за ваше внимание. Буду благодарен за любые советы.

commanddotcom
INSERT 100000 строк в одну таблицу и DELETE 100000 из другой таблицы существенная нагрузка.

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

commanddotcom,

решение с переносом кучек туда--сюда конечно негодное.

если удастся разбить партиции так, чтобы перенос в архив и обратно происходил без delete/insert (а только ALTER TABLE partXXX NO INHERIT xxx ; ALTER TABLE partXXX INHERIT yyy;) -- то возможно такое партицирование будет уместно. (хотя там есть подводный камень неприятного размера прямо в архитектуре пж -- куча запросов может в момент этого самого NO INHERIT взбрыкнуть с ошибкой. и это -- не лечится [а гурья не чешутся, и даже не собираются]).

commanddotcom
Есть таблица на 10 Гб. CREATE TABLE actions ( user_uni INT NOT NULL, project_id INT NOT NULL, action_time INT, status SMALLINT )

за счет чего 10 гигов,или это не вся структура

ПЕНСИОНЕРКА
commanddotcom Есть таблица на 10 Гб. пропущено... за счет чего 10 гигов,или это не вся структура

30 млн записей

commanddotcom,

Можно выиграть в размере индекса, если сделать условный индекс
Предположим, что активный проект имеет статус = 0, тогда:

create index i_actions_active on actions (project_id,user_uni) where status = 0;

Если активных проектов мало, то выборка будет довольно эффективной

tadmin
commanddotcom, Можно выиграть в размере индекса, если сделать условный индекс Предположим, что активный проект имеет статус = 0, тогда: create index i_actions_active on actions (project_id,user_uni) where status = 0; Если активных проектов мало, то выборка будет довольно эффективной

Так project_id,user_uni все равно первичный ключ. Вы его убрать предлагаете?

Развивая Вашу мысль я могу добавить поле project_status SMALLINT и в момент когда проект закрыт делать UPDATE для этого поля при изменении типа кампании на "открыт"\"закрыт". Ну, и соответственно индекс:

create index i_actions_active on actions (project_id,user_uni) where project_status = 0;

Запрос SELECT типа

SELECT * FROM actions WHERE user_uni=20 AND project_id=88 AND project_status=0

Но будет ли движек PostgreSQL использовать этот индекс, если все равно есть первичный ключ?

commanddotcom

Добрый день. Мне нужна консультация по архитектуре. Короткая формулировка вопроса: При SELECT по первичному ключу имеет ли значение размер таблицы? Подробное описание проблемы: Есть таблица на 10 Гб. CREATE TABLE actions ( user_uni INT NOT NULL, project_id INT NOT NULL, action_time INT, status SMALLINT ) Первичный ключ составной: user_uni, project_id Нужно постоянно проверять выполнил ли пользователь user_uni действие по проекту project_id. Есть вариант разбить таблицу на две actions (1-2 ГБ) и actions_history (8-9 ГБ), где actions будет хранить только действия по актуальным проектам, а actions_history - действия по проектам, которые на данный момент закрыты. Я не сомневался бы в правильности этого решения, но есть один нюанс: закрытый проект может быть открыт заново. В случае если проект будет открыт заново (а это происходит регулярно) нужно перенести данные из actions_history в actions, а это может быть от 1000 до 100000 строк. INSERT 100000 строк в одну таблицу и DELETE 100000 из другой таблицы существенная нагрузка. Возникает вопрос: а имеет ли смысл разделение на две таблицы? Спасибо за ваше внимание. Буду благодарен за любые советы.

Мой совет - да 100% имеет смысл. Просто потому что actions на 1-2Gb будет эффективно кешироваться в памяти базы а вот общая таблица с историей - далеко не факт (и скорее всего в таблицу истории особо никто ходить не будет).
А перекинуть 100.000 строк в транзакции не такая уж большая проблема если это делается редко.


--
Maxim Boguk
www.postgresql-consulting.ru

tadmin,

Я поддерживаю решение с частичным индексом, я бы предпочел такое решение партиционированию. Частичный индекс при частом использовании будет всегда горячим, как и та часть таблицы, которую он охватывает. Если вы не будете в запросе выбирать не-индексируемые колонки, то возможно и IOS будет случаться.

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

У вас есть частый запрос, исходите от него. Если предикаты запроса высокой селективности — стройте дополнительный индекс по всем полям. Если какое-то поле малой селективности (тот же статус), то постройте частичный индекс.

PostgreSQL принимает решение о том, какой индекс выгоднее, в первую очередь исходя из размеров индекса. Гадать тут смысла нет — сделайте дамп ваших таблиц, загрузите в тестовую базу, постройте индексы, сделайте партиционирование. И сравните скорость исполнения запросов для разных решений. На основании тестов принимайте решение.

condomotsosadom,

количество операций при доступе по btree-индексу зависит от глубины/высоты индекса, которая зависит логарифмически от количества записей.
то есть, разница 100k и 100m отсутствует или ничтожно мала. с другой стороны, не стоит игнорироавать такой фактор, как попадание страницы индекса в кеш. при интенсивной работе по всем значениям потери на невлезающем в кеш индексе будут существенны.

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