0

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?

1
  • "Better" is rather unspecific. What do you want to optimize for? Jan 19, 2022 at 16:03

3 Answers 3

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.

0

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.

5
  • What do you mean it will "lock up the database"? In PG Primary Key doesn't seem to have any significance compared to Secondary Key with an index (in other systems like MySQL there is a difference), and you can use on cascade update to update FKs automatically. So not sure why you're so negative at updating PKs. Having said that, I've never built or seen any system that would update its PKs - but probably it's because we typically work with surrogate keys. Jan 19, 2022 at 17:29
  • Consider if you had an Accounts table with 1000 rows in it. Every Account has, say, 1000 Transactions recorded against it, each of which has the Primary Key of the Account in it. Now, to change those 1000 Account PKs, you /also/ have to update 1000*1000 records in the Transactions table (plus any number of other records in other, also-related tables). Every single change has to be logged (and, in PG, new versions of rows created, to be vacuumed out again later), which means a HUGE amount of work for your database to do. All for no Good Reason.
    – Phill W.
    Jan 21, 2022 at 13:59
  • Why would you need to store 1000*1000 records and what's Transactions table? When you update a value in a row (regardless of whether it's a PK) PG will create a new version of that row. So if you update 1000 rows (doesn't matter in 1 transaction or not) PG will effectively create 1000 new rows. Jan 21, 2022 at 14:29
  • That is true. Now, here we are talking about updating - and cascading changes from - the PK of those rows. Say you have an Account record with PK=1. That same value also appears in /every/ Transaction record related to Account #1. If there are 1000 such Transactions, then you are updating 1 (Account) + 1000 (Transaction) rows to make that "one" change. Multiply that up by the /real/ number of Accounts and you're looking at a /huge/ workload. For something that should never change at all ...
    – Phill W.
    Jan 21, 2022 at 15:52
  • So by Transaction you don't mean a PG transaction, you mean a table called "Transaction"? This was confusing. If so, then yes you will have to update rows in related table. But only if there is an FK. Changing PK which isn't referenced by FK will not be any different than updating other columns with a unique index (or I simply don't know situations where this can matter). Jan 21, 2022 at 16:26
0

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

2
  • I have edited the question for more detail. There are more than 100k rows in the first table. Executing an UPDATE statement 100k times every day seems a bit more costly than doing merge and upsert. Especially in the second table (1-to-many) where there are 2.5 million rows and doing updates every day again seems costly. In merge and upsert, I will push the new data to a temp table, execute 2 queries - Update and Insert with the first table. This approach seems simple because I don't have to execute a lot of queries. Is my understanding wrong? Jan 19, 2022 at 14:45
  • @KaushikGanesan - merge or upsert operations are fine, and yes will likely be much more efficient than many individual updates. It is delete-then-insert to emulate an upsert/merge (or basic update) operation that is dangerous. Jan 19, 2022 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.