AK
но это уже - другая история
Size: a a a
2019 December 19
n
Hi, I have a postgres database containing a number of tables. When any of the tables are updated/inserted, I need to construct a JSON from a join spanning all the tables, and put into a mongo collection. Since join can be very expensive (several Ks of rows over a dozen of tables), doing this in a trigger for every update/insert is too heavy. Any idea about how to do it the best?
2019 December 20
КБ
Hi, I have a postgres database containing a number of tables. When any of the tables are updated/inserted, I need to construct a JSON from a join spanning all the tables, and put into a mongo collection. Since join can be very expensive (several Ks of rows over a dozen of tables), doing this in a trigger for every update/insert is too heavy. Any idea about how to do it the best?
I’m not sure why you would need to do this in the first place, but anyway, if there’s a possibility to spin up a replica of the main database, you could do the processing on a replica, thus reducing strain on master.
Also if you don’t need to update mongo in realtime, it might be preferred to accumulate ids of updated records and then sync them in a cron task. Selecting and joining multiple records is much more efficient than doing this in a for-loop one-by-one.
If by trigger you mean postgres triggers, I wouldn’t do any network communication inside those, I have a feeling this is going to delay transactions committing and slow everything down massively…
Also if you don’t need to update mongo in realtime, it might be preferred to accumulate ids of updated records and then sync them in a cron task. Selecting and joining multiple records is much more efficient than doing this in a for-loop one-by-one.
If by trigger you mean postgres triggers, I wouldn’t do any network communication inside those, I have a feeling this is going to delay transactions committing and slow everything down massively…
n
Thanks.
n
The reason I'm doing this is because of keeping a legacy system working until they migrate. It also doesn't have to be very fast, so as you described, I accumulate the ids in a table, and then a periodic job processes them.
n
I decided to use postgres triggers to accumulate the ids in the separate table.
n
One thing I will check is if TypeORM supports Sequelize's "fetch separate" option for relations. Selecting each table separately and then merging the resultset in memory is more efficient than database joins for large datasets (in terms of number of rows fetched from the DB).
n
I’m not sure why you would need to do this in the first place, but anyway, if there’s a possibility to spin up a replica of the main database, you could do the processing on a replica, thus reducing strain on master.
Also if you don’t need to update mongo in realtime, it might be preferred to accumulate ids of updated records and then sync them in a cron task. Selecting and joining multiple records is much more efficient than doing this in a for-loop one-by-one.
If by trigger you mean postgres triggers, I wouldn’t do any network communication inside those, I have a feeling this is going to delay transactions committing and slow everything down massively…
Also if you don’t need to update mongo in realtime, it might be preferred to accumulate ids of updated records and then sync them in a cron task. Selecting and joining multiple records is much more efficient than doing this in a for-loop one-by-one.
If by trigger you mean postgres triggers, I wouldn’t do any network communication inside those, I have a feeling this is going to delay transactions committing and slow everything down massively…
As for the replica, I will have to use CloudSQL, not sure if it's possible to achieve that with it.
n
Essentially migrating a mongo app to postgres.
n
-- Trigger function that inserts the table name and the row id into sync_update_log table
CREATE OR REPLACE FUNCTION sync_update_log_<%= entity %>()
RETURNS trigger AS $$
BEGIN
INSERT INTO sync_update_log (entity, entity_id, updated_at) VALUES (entity, NEW.id, now());
RETURN NEW;
END; $$
LANGUAGE PLPGSQL;
-- Trigger that calls the trigger function after update
CREATE TRIGGER trigger_sync_update_log_<%= entity %>
AFTER UPDATE
ON <%= entity %>
FOR EACH ROW
EXECUTE PROCEDURE sync_update_log_<%= entity %>();
-- Trigger that calls the trigger function after insert
CREATE TRIGGER trigger_sync_update_log_<%= entity %>
AFTER INSERT
ON <%= entity %>
FOR EACH ROW
EXECUTE PROCEDURE sync_update_log_<%= entity %>();
AK
Нужна помощь - пользуюсь queryBuilder и мне нужно возвращать объект типа SelectQueryBuilder<Entity>
Но дело в другом - есть две таблицы: Допустим Заказы (М) —> Компания(1)
мне нужно сделать GroupBy company.id, но если это сделать из ЗаказEntity то в билдере обязательно нужно употребить Id родительской сущности(заказа), но тогда я не смогу воспользоваться groupBy так как id заказа нарушает агрегацию
Но дело в другом - есть две таблицы: Допустим Заказы (М) —> Компания(1)
мне нужно сделать GroupBy company.id, но если это сделать из ЗаказEntity то в билдере обязательно нужно употребить Id родительской сущности(заказа), но тогда я не смогу воспользоваться groupBy так как id заказа нарушает агрегацию
КБ
One thing I will check is if TypeORM supports Sequelize's "fetch separate" option for relations. Selecting each table separately and then merging the resultset in memory is more efficient than database joins for large datasets (in terms of number of rows fetched from the DB).
I think TypeORM is clever enough to do this automatically. Its «joins» aren’t always real database joins. If you put TYPEORM_LOGGING=1 in environment, you can see all queries it makes in the terminal.
КБ
It also seems you could benefit from deduplicating ids in
sync_update_log. There’s no need to migrate same entity mutiple times. I’m not sure if postgres has ON DUPLICATE KEY UPDATE, I mostly work with MySQL. Maybe INSERT IGNORE?…2019 December 21
n
It also seems you could benefit from deduplicating ids in
sync_update_log. There’s no need to migrate same entity mutiple times. I’m not sure if postgres has ON DUPLICATE KEY UPDATE, I mostly work with MySQL. Maybe INSERT IGNORE?…Append-only log mechanism was intentional. The reason being the application uses "Serializable" isolation level, and updating the same row by multiple concurrent transactions would cause conflict.
n
So instead they are only appending new rows each time instead of updating the same row.
n
I'm not sure if there's a simpler way to sync a postgres database into mongodb as an aggregation of multiple tables.
2019 December 23
n
I'll look into postgres oplog streaming instead.
2019 December 27
АЕ
Добрый день может кто подсказать как правильнее сделать функционал лайков и дизлайков к посту?
2019 December 28
IK
А причём тут орм и база данных
2019 December 29
АЕ
В том как павильнее сделать entity для этой цели