CODEWITHSUNDEEP
javadatabasespringhibernatepostgresql
northernMonkey
northernMonkey

Reputation: 1393

WIll Hibernate handle table "bait and switch"?

I have a huge dataset that changes daily, that is bound to a Java object. My application uses Hibernate in the DAO layer.

When I change the data, I need to rebuild indexes, but if I did this with the live table whilst being used the Database will grind to a halt.

If I had a staging table to upload all my new data into and created the new indexes here, could I then drop the live table and rename my staging table without hibernate noticing?

Upvotes: 0

Views: 265

Answers (2)

northernMonkey
northernMonkey

Reputation: 1393

I solved this by changing my data structure slightly.

ALTER TABLE my_table RENAME to my_table_old;

I then created a view with the same name as the original table

CREATE OR REPLACE VIEW my_table AS
select * from my_table_old;

When I get my new data set I create a new table (with sequence appended at the end of the name)

EXECUTE 'CREATE TABLE my_table_'||nextval('seq_table_id')|| ' AS SELECT * FROM my_table_old WHERE 1=2;'

Create all the indexes and analyze the it

EXECUTE 'CREATE INDEX idx_column1_'||currval('seq_table_id')|| ' ON my_table_'||
        currval('seq_table_id')||' USING btree(column1);'||
EXECUTE 'CREATE INDEX idx_column2_'||currval('seq_table_id')|| 
        ' ON my_table_'||currval('seq_table_id')||' USING btree(column2);'

Whilst all of this is being done my webapp continues to map to the old table, completely unaffected.

Finally I analyze the table

EXECUTE 'ANALYZE my_table_'||currval('seq_table_id');

And once I'm happy with everything, I 'do the old switcheroo', and point my view to the new table

EXECUTE 'CREATE OR REPLACE VIEW my_table AS SELECT * FROM my_table_'||(SELECT last_value FROM seq_table_id)||';';

Everything switches instantly, already indexed, and Hibernate is none the wiser!

Upvotes: 2

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

No, you're going to have to have some 'planned downtime', where you shut the app down, drop and rename the tables, and then start the app back up.

Since it might take some time to drop the 'live' table, you might want to minimize the down time by just renaming both tables, and then restarting the app. You can then drop the old table at your leisure.

Upvotes: 0

Related Questions