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
>