CODEWITHSUNDEEP
databasedev-to-production
Girish Rao
Girish Rao

Reputation: 2669

What are good strategies for updating a live database table?

I have a db table that gets entirely re-populated with fresh data periodically. This data needs to be then pushed into a corresponding live db table, overwriting the previous live data.

As the table size increases, the time required to push the data into the live table also increases, and the app would look like its missing data.

One solution is to push the new data into a live_temp table and then run an SQL RENAME command on this table to rename it as the live table. The rename usually runs in sub-second time. Is this the "right" way to solve this problem?

Are there other strategies or tools to tackle this problem? Thanks.

Upvotes: 1

Views: 3799

Answers (3)

Induster
Induster

Reputation: 733

Create a duplicate table - exact copy.

Create a new table that does nothing more than keep track of the "up to date" table. MostCurrent (table) id (column) - holds name of table holding the "up to date" data.

When repopulating, populate the older table and update MostCurrent.id to reflect this table.

Now, in your app where you bind the data to the page, bind the newest table.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29619

I don't like messing with schema objects in this way - it can confuse query optimizers and I have no idea what will happen to any transactions that are going on while you execute the rename.

I much prefer to add a version column to the table, and have a separate table to hold the current version.

That way, the client code becomes

select * 
from myTable t, 
     myTable_currentVersion tcv
where t.versionID = tcv.CurrentVersion

This also keeps history around - which may or not be useful; if it's not delete old records after setting the CurrentVersion column.

Upvotes: 1

BillThor
BillThor

Reputation: 7576

Would it be appropriate to only push changes to the live db table? For most applications I have worked with changes have been minimal. You should be able to apply all the changes in a single transaction. Committing the transaction will make them visible with no outage on the table.

If the data does change entirely, then you could configure the database so that you can replace all the data in a single transaction.

Upvotes: 0

Related Questions