Re: [GENERAL][ADMIN][HACKERS]data fragmentation

[Joshua D. Drake]

Hello,

 An quicker option would be to use rsync (on a stopped database of 
course). You can rsync to a new directory (off the filesystem) and then 
reformat the data filesystem and move it back.

J

Somasekhar Bangalore wrote:

Hi,

I too had the same problem;  There was one query which used to take a very long time. What I did was, I took a backup of the whole database. Reinstalled postgres on a different mount point and restored the data back into the new database. Now my queries are running faster. Try it. All the very best.

Somasekhar

-Original Message-
From: Jaime Casanova [mailto:[EMAIL PROTECTED]
Sent: Friday, December 12, 2003 3:07 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [GENERAL][ADMIN][HACKERS]data fragmentation
Hi,

i have a theorical question. i was thought that data fragmentation can cause
a
loss of performance when retrieving data from a database. Some DBMS solved
this
with dbspaces, but postgresql doesn't support this concept.
so, pgsql databases tend to suffer from data fragmentation?
if yes, what is the solution you recommend?
also i was thought that even when DBMS support dbspaces DELETEing records
may
cause data fragmentation anyway.
so, can i think of DELETE statement as a double-edged sword?
it is indifferent in pgsql - it doesn't support dbspaces anyway?
thanks in advance,
Jaime Casanova (el_vigia)
_
The new MSN 8: smart spam protection and 2 months FREE* 
http://join.msn.com/?page=features/junkmail

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL][ADMIN][HACKERS]data fragmentation

[Alvaro Herrera]

On Tue, Dec 16, 2003 at 10:29:20PM +, Jaime Casanova wrote:
> >On Fri, Dec 12, 2003 at 09:59:23AM +0530, Somasekhar Bangalore wrote:

> >>I too had the same problem;  There was one query which used to take a
> >>very long time. What I did was, I took a backup of the whole database.
> >>Reinstalled postgres on a different mount point and restored the data
> >>back into the new database. Now my queries are running faster. Try it.
> >>All the very best.
> >
> >You could instead apply CLUSTER to the affected tables.  (I'm assuming you
> >already apply VACUUM periodically and REINDEX as appropiate)
> 
> What does CLUSTER does, and how can i use it?

Everything You Always Wanted To Know About CLUSTER

http://www.postgresql.org/docs/7.4/static/sql-cluster.html

-- 
Alvaro Herrera ()
"El que vive para el futuro es un iluso, y el que vive para el pasado,
un imbécil" (Luis Adler, "Los tripulantes de la noche")

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL][ADMIN][HACKERS]data fragmentation

[Alvaro Herrera]

On Fri, Dec 12, 2003 at 09:59:23AM +0530, Somasekhar Bangalore wrote:
> Hi,
> 
> I too had the same problem;  There was one query which used to take a
> very long time. What I did was, I took a backup of the whole database.
> Reinstalled postgres on a different mount point and restored the data
> back into the new database. Now my queries are running faster. Try it.
> All the very best.

You could instead apply CLUSTER to the affected tables.  (I'm assuming you
already apply VACUUM periodically and REINDEX as appropiate)

-- 
Alvaro Herrera ()
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match