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.

> 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