Thread: forcce a table in memory

forcce a table in memory

From
"Priya G"
Date:
<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. 

copying databases w/ indexes

From
Yuji Shinozaki
Date:
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


Re: copying databases w/ indexes

From
Sam Barnett-Cormack
Date:
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

Re: copying databases w/ indexes

From
Yuji Shinozaki
Date:
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



Re: copying databases w/ indexes

From
Tom Lane
Date:
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

Re: copying databases w/ indexes

From
Yuji Shinozaki
Date:
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



Re: forcce a table in memory

From
Bruce Momjian
Date:
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