Thread: forcce a table in memory
<div style="background-color:"><div>Is there any way in postgres that you can force a table to be in memory.</div></div><brclear="all" /><hr />Tired of spam? Get <a href="http://g.msn.com/8HMQENUS/2734??PS=">advanced junkmail protection</a> with MSN 8.
I have two questions about restoring and moving databases (in pg 7.3), to which I have not been able to find a suitable answers in the documentation (and searching the list archives has been painfully slow and fruitless). 1) What is the fastest way to restore a database *with indexes*? We have situation where I need to make a duplicate of a production database for development work, so that we can develop code without interfering with the production database. I use pg_dump for this, but I have not been able to get pg_restore to work (usually because of constraint problems). So I do the restores from a text dump piped into psql (with all the constraints applied last). Then the problem is that reindexing takes a very long time. One particular index is taking on the order of 12 hours to finish. Is there a way to dump and restore the index itself so that the tables do not need to be reindexed? The dumps and restores are between identical installations of postgres, sometimes even the same running instance of postgres. And a tangentially related question: 2) Is there a way to rename a database? That is the other thing that I occasionally need to do, and the only way I have found is dump/restore, and again we pay the penalty of the 12-hour reindexing. Any pointers? Thanks! yuji ---- Yuji Shinozaki Computer Systems Senior Engineer ys2n@virginia.edu Advanced Technologies Group (434)924-7171 Information Technology & Communication http://www.people.virginia.edu/~ys2n University of Virginia
On Thu, 14 Aug 2003, Yuji Shinozaki wrote: > > I have two questions about restoring and moving databases (in pg 7.3), to > which I have not been able to find a suitable answers in the documentation > (and searching the list archives has been painfully slow and fruitless). > > 1) What is the fastest way to restore a database *with indexes*? > > We have situation where I need to make a duplicate of a production > database for development work, so that we can develop code without > interfering with the production database. I use pg_dump for this, but I > have not been able to get pg_restore to work (usually because of > constraint problems). So I do the restores from a text dump piped into > psql (with all the constraints applied last). Then the problem is that > reindexing takes a very long time. One particular index is taking on the > order of 12 hours to finish. I've been dumping and restoring a very very big database, with indexes, with no such problems. The table details that get dumped setup the indexes, and AIUI the indexes are kept up-to-date as items are added. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
Aha! Thanks for the reply, because it got me thinking, and I reexamined the schema and discovered that the problem was that we had a foreign key constraint involving two columns of different type (varchar vs. integer!). [Yeesh.] I think the type conversion was what was making reindexing (not to mention inserts) slow! Now the restore only takes about 20 minutes (yay!) Thanks, yuji ---- On Thu, 14 Aug 2003, Sam Barnett-Cormack wrote: > On Thu, 14 Aug 2003, Yuji Shinozaki wrote: > > > > > I have two questions about restoring and moving databases (in pg 7.3), to > > which I have not been able to find a suitable answers in the documentation > > (and searching the list archives has been painfully slow and fruitless). > > > > 1) What is the fastest way to restore a database *with indexes*? > > > > We have situation where I need to make a duplicate of a production > > database for development work, so that we can develop code without > > interfering with the production database. I use pg_dump for this, but I > > have not been able to get pg_restore to work (usually because of > > constraint problems). So I do the restores from a text dump piped into > > psql (with all the constraints applied last). Then the problem is that > > reindexing takes a very long time. One particular index is taking on the > > order of 12 hours to finish. > > I've been dumping and restoring a very very big database, with indexes, > with no such problems. The table details that get dumped setup the > indexes, and AIUI the indexes are kept up-to-date as items are added. > > -- > > Sam Barnett-Cormack > Software Developer | Student of Physics & Maths > UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Yuji Shinozaki Computer Systems Senior Engineer ys2n@virginia.edu Advanced Technologies Group (434)924-7171 Information Technology & Communication http://www.people.virginia.edu/~ys2n University of Virginia
Yuji Shinozaki <ys2n@virginia.edu> writes:
> Then the problem is that
> reindexing takes a very long time. One particular index is taking on the
> order of 12 hours to finish.
Seems like a lot. What is the index definition exactly, and how many
rows in the table? If it's a btree, have you tried increasing sort_mem
in the restore session?
regards, tom lane
See my other reply, but the problem was that I had a foreign key constraint between an integer and varchar. (The column was a varchar and the foreign key was an integer field). Seems to me postgres shouldn't have allowed me to do this at all, but I guess it did a slow automatic type conversion. So, reindexing was deathly slow. It also explains the fact that inserts into this table were getting increasingly slow as the table grew. I manually changed the type in the dump and restoring takes about 20 minutes instead of 12 hours! Phew! yuji ---- On Thu, 14 Aug 2003, Tom Lane wrote: > Yuji Shinozaki <ys2n@virginia.edu> writes: > > Then the problem is that > > reindexing takes a very long time. One particular index is taking on the > > order of 12 hours to finish. > > Seems like a lot. What is the index definition exactly, and how many > rows in the table? If it's a btree, have you tried increasing sort_mem > in the restore session? > > regards, tom lane > Yuji Shinozaki Computer Systems Senior Engineer ys2n@virginia.edu Advanced Technologies Group (434)924-7171 Information Technology & Communication http://www.people.virginia.edu/~ys2n University of Virginia
No, no way to force a table into memory. The system will cache them based on recent access. --------------------------------------------------------------------------- Priya G wrote: [ text/html is unsupported, treating like TEXT/PLAIN ] > <html><div style='background-color:'><DIV>Is there any way in postgres that you can force a table to be in memory.</DIV></div><brclear=all><hr>Tired of spam? Get <a href="http://g.msn.com/8HMQENUS/2734??PS=">advanced junk mail protection</a>with MSN 8.</html> > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073