Re: [GENERAL][HACKERS]data fragmentation

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."

On Tue, Dec 16, 2003 at 10:29:20PM +0000, 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 (<alvherre[a]dcc.uchile.cl>)
"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")

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: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