In a daily routine, I need to drop and do a bulk load of a database.

The problem is that I have a WebApp relying on that database. So I can't drop the database just like this.

What would be a good way to tackle this problem ?

  • 2
    No offense, but I wouldn't suggest doing a bulk load of the db every day. Find a way to merge the changes from your source data, and then, voila, no webapp downtime. Jul 7, 2011 at 2:51

6 Answers 6


Well, this is straightforward in most databases. While your app is running, from your loading job:

  1. Start a transaction (this may be implicit or may require a command, you don't specify which DB you are using)
  2. DELETE all the data from the tables (not drop or truncate)
  3. INSERT the new data
  4. COMMIT the transaction

While steps 1-3 are running, the app will see the "old" version of the data and can continue as normal. The instant step 4 completes, the app will see the new data as soon as it runs the next query.

  • the app will see the new data as soon as it runs the next query => when it starts the next transaction.
    – user953
    Jul 8, 2011 at 12:01

Do you need to drop and reload the database? In a case like yours, I would look at a way to synchronize the database with the reference set (the data you are loading).

An alternative approach would be to have two databases. Load a different one each day. Configure the webapp so that you can swap databases on the fly.


Truncate the tables, then bulk load the data. If needed lock the web app from the table before truncating the table.


this depends on how the dependency trees are. If you have lots of dependend objects on the table[s] where you load the data in, best is not to change the object, the table because lots of invalidations will take place.

truncate table gets rid of the data very fast but won't work when referential constraints are in place. It also won't work when the app is running because of locking issues.

Best for the application will be a normal delete/insert sequence. Not the fastest option but it is best for availabillity and has the least problems with locking.

Other options can be partition exchange loading. Constraints can be problematic and take a lot of time to validate. Never ever drop a table just to get rid of the data. A table is to be considered as application infrastructure and should allways be available, because the application will throw lots of errors to your users.

  1. Stop the webapp.
  2. Drop/restore the DB.
  3. Start the webapp again.
  • 1
    My intention is to find a way to never stop the WebApp :)
    – Spredzy
    Jul 5, 2011 at 12:40
  • You might not be able to drop the DB unless you stop the WebApp. And what will your users see? Drop/restore is not atomic operation.
    – alex
    Jul 5, 2011 at 14:04
  • 1
    What bothering me is that if I stop the WebApp it won't be accesible, somthing that I'd like to avoid.
    – Spredzy
    Jul 5, 2011 at 14:14
  • Well if you could put the app in read-only mode and point it to a clone of your DB...
    – alex
    Jul 5, 2011 at 17:07

i load the new data in a temporary table. this way, i have to lock only the rows, that i need to update. Afterwards i delete missing rows and insert new rows.

create table permanent ( key int primary key, field1 int, field2 text );
create temporary table temporary as select * from permanent where false;
\copy temporary from 'actual_data.dump'
alter table temporary add constraint temporary_pkey primary key ( key );

update permanent                              
   set field1 = temporary.field1,
       field2 = temporary.field2
  from temporary
 where permanent.key = temporary.key
   and (     permanent.field1 is distinct from temporary.field1
         or  permanent.field2 is distinct from temporary.field2

delete from permanent where not exists ( select 1
                                           from temporary
                                          where key = permanent.key );
insert into permanent ( select *
                          from temporary
                         where not exists ( select 1
                                              from permanent
                                             where key = temporary.key ) );

this works at least for postgresql.

for oracle i use the following:

update ( select p.field1 p_field1,
                p.field2 p_field2,
                t.field1 t_field1,
                t.field2 t_field2
           from permanent p, temporary t
          where p.key = t.key )
   set p_field1 = t_field1,
       p_field2 = t_field2
 where p_field1 <> t_field1
    or p_field1 is null and t_field1 is not null
    or p_field1 is not null and t_field1 is null
    or p_field2 <> t_field2
    or p_field2 is null and t_field2 is not null
    or p_field2 is not null and t_field2 is null
when i have impossible values, i may use NVL instead:

 where nvl(p_field1, -1) <> nvl(t_field1, -1)
    or nvl(p_field2, '{null}') <> nvl(t_field2, '{null}')

Your Answer

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

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