Thread

1. Re: Index location patch for review

   Vadim Mikheev <vmikheev@sectorbase.com> — 2001-09-12T16:58:20Z

   > > Attached is a patch that adds support for specifying a
   > > location  for indexes via the "create database" command.
   > > 
   > > I believe this patch is complete, but it is my first .
   > 
   > This patch allows index locations to be specified as
   > different from data locations. Is this a feature direction
   > we want to go in?  Comments?
   
   The more general and "standard" way to go are TABLESPACEs.
   But probably proposed feature will be compatible with
   tablespaces, when we'll got them: we could use new "create
   database" syntax to specify default tablespace for indices.
   Unfortunately I removed message with patch, can you send it
   to me, Bruce?
   
   Vadim
   
   

2. Re: Index location patch for review

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-12T17:54:02Z

   "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
   > The more general and "standard" way to go are TABLESPACEs.
   > But probably proposed feature will be compatible with
   > tablespaces, when we'll got them:
   
   Will it be?  I'm afraid of creating a backwards-compatibility
   problem for ourselves when it comes time to implement tablespaces.
   
   At the very least I'd like to see some information demonstrating
   how much benefit there is to this proposed patch, before we
   consider whether to adopt it.  If there's a significant performance
   benefit to splitting a PG database along the table-vs-index divide,
   then it's interesting as a short-term improvement ... but Jim didn't
   even make that assertion, let alone provide evidence to back it up.
   
   			regards, tom lane
   
   

3. Re: Index location patch for review

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-09-12T18:22:05Z

   > "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
   > > The more general and "standard" way to go are TABLESPACEs.
   > > But probably proposed feature will be compatible with
   > > tablespaces, when we'll got them:
   > 
   > Will it be?  I'm afraid of creating a backwards-compatibility
   > problem for ourselves when it comes time to implement tablespaces.
   > 
   > At the very least I'd like to see some information demonstrating
   > how much benefit there is to this proposed patch, before we
   > consider whether to adopt it.  If there's a significant performance
   > benefit to splitting a PG database along the table-vs-index divide,
   > then it's interesting as a short-term improvement ... but Jim didn't
   > even make that assertion, let alone provide evidence to back it up.
   
   If that is your only concern, I can tell you for sure that if the
   locations are on different drives, there will be a performance benefit. 
   It is standard database practice to put indexes on different drives than
   data.  In fact, sometimes you want to put two tables that are frequently
   joined on separate drives.
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

4. Re: Index location patch for review

   Thomas Lockhart <lockhart@fourpalms.org> — 2001-09-12T18:24:34Z

   ...
   > At the very least I'd like to see some information demonstrating
   > how much benefit there is to this proposed patch, before we
   > consider whether to adopt it.  If there's a significant performance
   > benefit to splitting a PG database along the table-vs-index divide,
   > then it's interesting as a short-term improvement ... but Jim didn't
   > even make that assertion, let alone provide evidence to back it up.
   
   Clearly there can be a *storage management* benefit to having control
   over what gets put where, so this does not need to be justified strictly
   on a performance basis.
   
   For features like this, we will feel free to evolve them or
   revolutionize them with further development, so I'm not worried about
   the backward compatibility issue for cases like this.
   
   Comments?
   
                       - Thomas
   
   

5. Re: Index location patch for review

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-09-12T18:26:26Z

   > ...
   > > At the very least I'd like to see some information demonstrating
   > > how much benefit there is to this proposed patch, before we
   > > consider whether to adopt it.  If there's a significant performance
   > > benefit to splitting a PG database along the table-vs-index divide,
   > > then it's interesting as a short-term improvement ... but Jim didn't
   > > even make that assertion, let alone provide evidence to back it up.
   > 
   > Clearly there can be a *storage management* benefit to having control
   > over what gets put where, so this does not need to be justified strictly
   > on a performance basis.
   > 
   > For features like this, we will feel free to evolve them or
   > revolutionize them with further development, so I'm not worried about
   > the backward compatibility issue for cases like this.
   > 
   > Comments?
   
   Agreed.
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026