Is updating a table by delete+insert better than merging with a temp table?

Question

I have two tables in Postgres. The first table has 120 columns (stock metrics like returns, sales etc.), and the second table has 5 columns with a one-to-many relationship and on cascade delete constraint with the first table (this table contains 10-year stock prediction data of all public companies). All these table values should get updated every day as the stock price moves every day.

In my project, there are two ways to update the database:

1. One is on the API level where only one row in the first table is only updated. This depends on the number of times the user wants to update but there won't be a lot of updates from the user side.
2. Another one is executed on a weekly basis where all the rows in both tables are updated.

For both ways, I first delete the records in the first table and insert data in both the tables using python (for-loop).

Is deleting and inserting a better approach than creating a temp table and merging (update for existing records and insert for non-existent rows) it with the old table?

Answer 1

Postgresql uses MVCC so every update, is under the hood, an insert and a delete.

Is deleting and inserting a better approach than creating a temp table and merging (update for existing records and insert for non-existent rows) it with the old table?

Deleting and inserting causes there to be a time when the data is missing or requires a long-lived lock on the tables (especially for the daily bulk update) if this is not a problem for you it will be as fast as any other approach (such as merge)

Creating a temporary table will take a similar amount of time to inserting the data, so this alternative workflow will not save time. it will however make the duration of the locking of the tables much shorter.

Remember that an insert command can insert several rows in a single command, exploiting this from python can be somewhat tricky but will lead to a significant performace gain over single row inserts.

Answer 2

The first table has 120 columns, and each primary key has one row

The "Primary Key", by definition, uniquely identifies each row.
I suspect that's not what you mean here.

the second table has 5 columns, and each primary key has 1000 rows

Then it cannot be the "Primary Key" of the second table.
I assume you mean that the second table has 1000 rows for each corresponding [Primary] Key in the first table.

... two ways to update the database ...
One is on the API level where only one primary key is updated.
Another one ... where all primary keys are updated.

The Primary Key of any record should be generated / stored when the record is first created and should remain unchanged for the entire lifetime of that record, until the record is finally destroyed.

Nothing should update Primary Key values.
It's a hugely expensive operation, will lock up the database while it's happening and, to all intents and purposes, it's a complete waste of time. If you're using a surrogate, numeric id for each record to make tying things together in the database "easier", then that value should never, ever change (and no-one, outside the database, should ever, ever see it).

Every change to the database is logged, so effectively deleting and recreating the table, by "rewriting" every record is going to hammer your disk system, with an image of every deleted record and then every inserted record. That's going to be a lot of disk activity and CPU load and record/table locking and Application performance impact - for something that you probably shouldn't be doing at all.

An analogy:
Consider the effect if your bank decided to renumber everybody's accounts whenever someone else closed their account ... That's the sort of thing we're talking about here.

Answer 3

You may have a terminology or other understanding problem with the term “primary key” as identified in Phil w's response. Skipping over that to the operations being taken on rows:

Is updating a table by delete+insert better than <anything>

No.

As well as performance issues caused by taking multiple actions against the same row, and other resource issues slightly further down the line if you are using log based backups, replication, etc., delete-then-insert can lead to unexpected data loss or corruption.

If any foreign keys have been defined with ON CASCADE DELETE child data will be silently deleted too, and not brought back when you re-insert the row. This is true even if both are done within a single explicit transaction: the DB will not wait to see if the PK value reappears before taking the cascaded delete action. This could lead to further deletes in even more tables if the cascaded delete causes further cascades due to more FKs defined this way.

Similarly, triggers can cause data loss, and might also cause other logical corruption (as they can do other than simply delete) or unintended actions (due to the changes they make in other data being reacted to by other parts of your applications).

Of course delete+insert is safe if no such actions are defined, but you don't know that they will never be added later. Or you might later want to add them for some something, but not be able to because existing code that you don't have time to change relies on them not being present. It might be fine forever for this particular DB+code, but I would get into the habit of avoiding such methods ASAP so you don't have to unlearn the habit of using them at a later time.

As far as is possible/practical, keep your code doing literally what it is doing functionally: an update to a row should be an update¹, not a delete followed by an insert. This has the added advantage of making your code clearer to anyone² else looking at it.

_{[1] well, to be more complete, merge and upsert operations are updates for those rows too, if your DB supports such constructs, as they are really syntactic sugar around the insert/update/delete primitive operations.}

_{[2] where “anyone” could be future you who barely remembers writing the ocde e has been asked to make alterations}