"Mohawksoft":

> I forgot to specify that tablepaces should be on separate volumes. (sorry)
> If all they have is one volume, no worries, but instructing the use of
> alternate volumes for system and data will improve performance by
> separating WAL and data operations.

... and the place for this is the documentation, maybe with a nice script to 
help automate it.   Twisting users' arms will just get us a lot of angry 
e-mail.  Plus force separation of tablespaces is not appropriate for many 
kinds of installations:
-- the 1MB 2-table database of someone's DVD collection;
-- the 700GB database running off a $75,000 NAS (which appears to the OS as a 
single volume)

Also, you're getting confused here ... Tablespaces has nothing to do with the 
location of pg_xlog.

> Tablespaces are a familiar construct to experienced DBAs who may not be
> familiar with PostgreSQL. PostgreSQL being similar to other databases will
> have it better "make sense" to new users.

I'll have to disagree with you there.  I used to be a certified MSSQL admin, 
and I can tell you that not one in 25 members of MSDN Database forum had any 
idea how to use the analogous feature on MSSQL -- despite it being operative 
since 1998.   So forcing new users to deal with tablespaces, even if they 
don't need them, is a great way to get new users to adopt MySQL.

> So, the "preferred" installation procedure, i.e.
> the one with the easy to follow directions, should showcase features the
> user should know, and leave the user in a good place. 

No, the "preferred" newbie installation is the one that gets them up and 
running and playing with PostgreSQL in the minimum amount of time.  Setup is 
boring, confusing, and often frustrating, and each step we add to the 
required minimum setup loses us another dozen newbies who weren't sure if 
they are ready to upgrade from MS Access or MySQL.  Heck, for the CDs we're 
making to hand out at conventions, we're running PostgreSQL on Knoppix so 
that users don't have to install *anything*.

Now, if you want to add a "power user setup" to the Tutorial in our official 
docs, please do!   We could use more guides.   But don't force the guy with 
the personal DVD database to set things up like he's running Ameritrade.

Also, consider that about half our users install from packages: RPMs and Deb 
packages (and soon MSI as well).   Those users aren't going to be going 
through a manual installation procedure at all, so your efforts to "educate" 
them through proper database setup won't get very far.

> IMHO, the user's
> database on one volume and pg_xlog on another is a better starting place.

For some setups, yes.   For others, no.  It depends on your hardware and 
application.   And, as I said above, Tablespaces will not determine the 
location of pg_xlog AFAIK.

> BTW: Is there a public spec on what will be tablespace compatible and how?
> For instance: will is be possible to create a table on a separate
> tablespace than the DB? Will it be possible to create an index on a
> separate tablespace than the table?

This is in the archives of this list.     The whole point of tablespaces is to 
allow placing individual tables and indexes on seperate volumes.   AFAIR, 
we're not that concerned about whole databases, which have always been easily 
re-locatable via symlinks and/or mounts.

P.S. if you signed your e-mails, I'd stop calling you "mohawksoft".

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Thu, 27 May 2004 10:02:39 -0700
Josh Berkus <josh@agliodbs.com>
Re: tablespaces and DB administration 
> "Mohawksoft":
>
>> I forgot to specify that tablepaces should be on separate volumes.
>> (sorry)
>> If all they have is one volume, no worries, but instructing the use of
>> alternate volumes for system and data will improve performance by
>> separating WAL and data operations.
>
> ... and the place for this is the documentation, maybe with a nice script
> to
> help automate it.   Twisting users' arms will just get us a lot of angry
> e-mail.  Plus force separation of tablespaces is not appropriate for many
> kinds of installations:
> -- the 1MB 2-table database of someone's DVD collection;
> -- the 700GB database running off a $75,000 NAS (which appears to the OS
> as a
> single volume)
>
> Also, you're getting confused here ... Tablespaces has nothing to do with
> the
> location of pg_xlog.

I'm not "confused" but, it is an inverse logic thing. By persuading users
to create databases on a separate tablespace than the system (which
contains pg_xlog), you are by definition separating database and pg_xlog.


>
>> Tablespaces are a familiar construct to experienced DBAs who may not be
>> familiar with PostgreSQL. PostgreSQL being similar to other databases
>> will
>> have it better "make sense" to new users.
>
> I'll have to disagree with you there.  I used to be a certified MSSQL
> admin,
> and I can tell you that not one in 25 members of MSDN Database forum had
> any
> idea how to use the analogous feature on MSSQL -- despite it being
> operative
> since 1998.   So forcing new users to deal with tablespaces, even if they
> don't need them, is a great way to get new users to adopt MySQL.

That's food for thought. That's different than my experience. I've set up
a few MSSQL systems and recall it saying that you should create a
different database file from the system, but if you say that it will
confuse new users, that is something that should be considered.


>
>> So, the "preferred" installation procedure, i.e.
>> the one with the easy to follow directions, should showcase features the
>> user should know, and leave the user in a good place.
>
> No, the "preferred" newbie installation is the one that gets them up and
> running and playing with PostgreSQL in the minimum amount of time.  Setup
> is
> boring, confusing, and often frustrating, and each step we add to the
> required minimum setup loses us another dozen newbies who weren't sure if
> they are ready to upgrade from MS Access or MySQL.  Heck, for the CDs
> we're
> making to hand out at conventions, we're running PostgreSQL on Knoppix so
> that users don't have to install *anything*.

That's cool, PostgreSQL on knoppix? How do you do that? RAM disk?

>
> Now, if you want to add a "power user setup" to the Tutorial in our
> official
> docs, please do!   We could use more guides.   But don't force the guy
> with
> the personal DVD database to set things up like he's running Ameritrade.
>
> Also, consider that about half our users install from packages: RPMs and
> Deb
> packages (and soon MSI as well).   Those users aren't going to be going
> through a manual installation procedure at all, so your efforts to
> "educate"
> them through proper database setup won't get very far.

I agree that there is a section missing in the manual for this sort of
information.

>
>> IMHO, the user's
>> database on one volume and pg_xlog on another is a better starting
>> place.
>
> For some setups, yes.   For others, no.  It depends on your hardware and
> application.   And, as I said above, Tablespaces will not determine the
> location of pg_xlog AFAIK.

See above/.

>
>> BTW: Is there a public spec on what will be tablespace compatible and
>> how?
>> For instance: will is be possible to create a table on a separate
>> tablespace than the DB? Will it be possible to create an index on a
>> separate tablespace than the table?
>
> This is in the archives of this list.     The whole point of tablespaces
> is to
> allow placing individual tables and indexes on seperate volumes.   AFAIR,
> we're not that concerned about whole databases, which have always been
> easily
> re-locatable via symlinks and/or mounts.
>
> P.S. if you signed your e-mails, I'd stop calling you "mohawksoft".
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>


Thu, 27 May 2004 14:01:02 -0400 (EDT)
pgsql@mohawksoft.com
Re: tablespaces and DB administration