How to do a bulk load of a database when application are relying on it?

Question

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 ?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

1. Stop the webapp.
2. Drop/restore the DB.
3. Start the webapp again.

Answer 6

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}')