Thread: [GENERAL][HACKERS]data fragmentation

[GENERAL][HACKERS]data fragmentation

From
"Jaime Casanova"
Date:
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


Re: [GENERAL][HACKERS]data fragmentation

From
"Somasekhar Bangalore"
Date:
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
ofthe whole database. Reinstalled postgres on a different mount point and restored the data back into the new database.
Nowmy queries are running faster. Try it. All the very best. 

Somasekhar


-----Original Message-----
From: Jaime Casanova [mailto:el_vigia_ec@hotmail.com]
Sent: Friday, December 12, 2003 3:07 AM
To: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org; pgsql-admin@postgresql.org
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

Re: [GENERAL][HACKERS]data fragmentation

From
Alvaro Herrera
Date:
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 (<alvherre[a]dcc.uchile.cl>)
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."

Re: [GENERAL][HACKERS]data fragmentation

From
"Joshua D. Drake"
Date:
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
ofthe whole database. Reinstalled postgres on a different mount point and restored the data back into the new database.
Nowmy queries are running faster. Try it. All the very best. 
>
>Somasekhar
>
>
>-----Original Message-----
>From: Jaime Casanova [mailto:el_vigia_ec@hotmail.com]
>Sent: Friday, December 12, 2003 3:07 AM
>To: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org; pgsql-admin@postgresql.org
>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 majordomo@postgresql.org)
>
>

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