pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: ischamay.andbergsay@activestateway.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: Sun, 29 Aug 2004 11:59:01 -0700
Message-ID: <200408291159.01205.josh@agliodbs.com> (raw)
In-Reply-To: <3csXc.56326$X12.25148@edtnps84>
References: <200408261818.i7QIIrr09640@candle.pha.pa.us>
	<200408261132.30732.josh@agliodbs.com>
	<3csXc.56326$X12.25148@edtnps84>

Mishca,

> Ummm ... not quite. In MSSQL/Sybase/Oracle, a clustered index maintains
> its space saturation as part of each update operation. High activity
> does indeed result in less-full pages (typically 60-80% full for tables
> with heavy deletions or rowsize changes). To bring the percentage back
> up, you run DBCC INDEXDEFRAG, which also does what you'd expect of a
> normal file defragmenter -- put related disk pages together on the platter.

Sure, it does now, which is a nice thing.  It didn't in the first version 
(6.5) where this cluster maint needed to be done manually and asynchronously, 
as I recall.   

> As for SQL Server being a 'single-user database' ... ummm ... no, I
> don't think so. 

Hmmm ... perhaps it would be better if I said "serial-only database".   MSSQL 
(like earlier versions of Sybase) handles transactions by spooling everything 
out to a serial log, effectively making all transcations SERIAL isolation.   
This has some significant benefits in performance for OLAP and data 
warehousing, but really kills you on high-concurrency transaction processing.

> I'm REALLY happy to be shut of the Microsoft world, but 
> MSSQL 7/2000/2005 is a serious big DB engine. It also has some serious
> bright heads behind it. They hired Goetz Graefe and Paul (aka Per-Ake)
> Larsen away from academia, and it shows, in the join and aggregate
> processing. I'll be a happy camper if I manage to contribute something
> to PG that honks the way their stuff does. Happy to discuss, too.

Yeah, they also have very speedy cursor handling.  I can do things with 
cursors in MSSQL that I wouldn't consider with other DBs.   Not that that 
makes up for the lack of other functionality, but it is nice when you need 
it.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



view thread (41+ messages)  latest in thread

Message-ID: <200408291159.01205.josh@agliodbs.com>
Permalink:  ../200408291159.01205.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200408291159.01205.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, ischamay.andbergsay@activestateway.com
  Subject: Re: Equivalent praxis to CLUSTERED INDEX?
  In-Reply-To: <200408291159.01205.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox