CODEWITHSUNDEEP
postgresqltriggers
user2800089
user2800089

Reputation: 2391

Should I use database trigger in insert heavy operation table?

My use-case is that I need to copy a few columns from TABLE A to another TABLE B and also derive values of a few other columns of TABLE B by some calculation.

As per current estimation around 50,000 rows will be inserted on daily basis in TABLE A.

TABLE B should be updated with all data before End of day.

Hence, either I can use trigger which will be invoked on INSERT operation on TABLE A or schedule some Job at EOD which read all data in bulk from TABLE A , do some calculation and insert in TABLE B.

As I am new to trigger, I am not sure which option should i pick for this use-case. Any suggestion which would be a better approach ?

So far what I have read about triggers, they can slow down DBs performance if they are invoked frequently.

As around 50,000 insert operation will happen daily , so can I assume that 50,000 falls under heavy operations where triggers would not be beneficial ?

EDIT 1 : 50,000 insert operation will reach 100,000 insert operations daily

Postgres DB is used.

Upvotes: 0

Views: 1536

Answers (1)

jjanes
jjanes

Reputation: 44295

If you are doing bulk COPY into an unindexed table, adding a simple trigger will slow you down by a lot (like 5 fold). But if you are using single-row INSERTs or the table is indexed, the marginal slow down of adding a simple trigger will be pretty small.

50,000 inserts per day is not very many. You should have no trouble using a trigger on that, unless the trigger has to scan a large table on each call or something.

Upvotes: 3

Related Questions