Thread: Is there a shortage of postgresql skilled ops people

Hello -

Over the past couple of years I have made use of postgresql as my database
of choice when developing new software. During that time, my clients have
in multiple cases eventually come back to me and requested a re-targeting
to "Any database that we (my client) can find skilled ops staff to
support." In a most recent case, professional recruiters were employed to
try to find such people. The search was disappointing at best.

My question for this community is, what do enterprises that you deploy
postgresql within do for skilled operations staffing? I can understand
trying to convert a mysql or Oracle person to work on postgresql, but it
would be very helpful to have a potential talent pool to draw from that
was similar to those others. Finding people with HA, scaling and
performance tuning knowledge is something that seems impossible to find
except in people wanting to be developers.

The sad reality from what I have observed is that unless more people gain
those skills and want to work in ops, it's becoming very hard for me to
justify recommending postgresql for enterprise (or larger) scale projects.

What do others  do and/or experience?

Thanks in advance - Marc

On 3/27/07, Marc Evans <Marc@softwarehackery.com> wrote:
> Hello -
>
> Over the past couple of years I have made use of postgresql as my database
> of choice when developing new software. During that time, my clients have
> in multiple cases eventually come back to me and requested a re-targeting
> to "Any database that we (my client) can find skilled ops staff to
> support." In a most recent case, professional recruiters were employed to
> try to find such people. The search was disappointing at best.
>
> My question for this community is, what do enterprises that you deploy
> postgresql within do for skilled operations staffing? I can understand
> trying to convert a mysql or Oracle person to work on postgresql, but it
> would be very helpful to have a potential talent pool to draw from that
> was similar to those others. Finding people with HA, scaling and
> performance tuning knowledge is something that seems impossible to find
> except in people wanting to be developers.
>
> The sad reality from what I have observed is that unless more people gain
> those skills and want to work in ops, it's becoming very hard for me to
> justify recommending postgresql for enterprise (or larger) scale projects.
>
> What do others  do and/or experience?

PostgreSQL talent is in high demand.  From perspective of
maintainability, this is probably the only drawback (but a serious
one) to choose it as a platform to run a company on.  There is, IMO, a
good reason for this...pg people tend to be very good and tend to stay
employed...

If I was in your position, I would suggest contracting is the best way
to go for those companies, either through yourself (the obvious
choice), or hook them up with some of the bigger names in the
postgresql community, command prompt, agliodbs, etc.

merlin

Marc,
I forwarded your message to pgsql-advocacy list.

The same situation in Russia ! I think, that the major problem here is
lack of "official" training courses on PostgreSQL and certificates.
"Official" mean something that was accepted by the PostgreSQL community.
This is a real pity, since we (developers) are working on adding nice
features, making porting popular software solutions to PostgreSQL easier,
but there are no certified postgresql admins available on market.
For example, I and Teodor last year participated in porting of very
popular accounting enterprize solution from MS SQL to PostgreSQL and
there are about 800,000 installations already, so in principle, we have
big market, but people needed to be educated and certified, so company
could decide to switch from MS SQL to PostgreSQL.

Probably, it's time to sponsor our book-writers and other enthusiasts
to write "PostgreSQL Administration handbook", which we (community) will
accept, support and translate to different languages. I'm willing
to contribute "Full Text Search" chapter, for example. AFAIK, we have enough
people, already wrote PostgreSQL books. I like Corry's book, for example.
I don't know how much it might costs, but I'm sure community has money for this.

As for certificates, I see no real problem. We need to design nice
certificate, translate to different languages, publish on www.postgresql and
approve a list of people, who can sign certificate. We have many members of
our community in different regions/countries, so this is not a problem.

btw, probably, this project could be a nice introducing for PostgreSQL EU.


Oleg
On Tue, 27 Mar 2007, Marc Evans wrote:

> Hello -
>
> Over the past couple of years I have made use of postgresql as my database of
> choice when developing new software. During that time, my clients have in
> multiple cases eventually come back to me and requested a re-targeting to
> "Any database that we (my client) can find skilled ops staff to support." In
> a most recent case, professional recruiters were employed to try to find such
> people. The search was disappointing at best.
>
> My question for this community is, what do enterprises that you deploy
> postgresql within do for skilled operations staffing? I can understand trying
> to convert a mysql or Oracle person to work on postgresql, but it would be
> very helpful to have a potential talent pool to draw from that was similar to
> those others. Finding people with HA, scaling and performance tuning
> knowledge is something that seems impossible to find except in people wanting
> to be developers.
>
> The sad reality from what I have observed is that unless more people gain
> those skills and want to work in ops, it's becoming very hard for me to
> justify recommending postgresql for enterprise (or larger) scale projects.
>
> What do others  do and/or experience?
>
> Thanks in advance - Marc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On 3/27/07, Oleg Bartunov <oleg@sai.msu.su> wrote:
> Marc,
> I forwarded your message to pgsql-advocacy list.
>
> The same situation in Russia ! I think, that the major problem here is
> lack of "official" training courses on PostgreSQL and certificates.
> "Official" mean something that was accepted by the PostgreSQL community.
> This is a real pity, since we (developers) are working on adding nice
> features, making porting popular software solutions to PostgreSQL easier,
> but there are no certified postgresql admins available on market.
> For example, I and Teodor last year participated in porting of very
> popular accounting enterprize solution from MS SQL to PostgreSQL and
> there are about 800,000 installations already, so in principle, we have
> big market, but people needed to be educated and certified, so company
> could decide to switch from MS SQL to PostgreSQL.
>
> Probably, it's time to sponsor our book-writers and other enthusiasts
> to write "PostgreSQL Administration handbook", which we (community) will
> accept, support and translate to different languages. I'm willing
> to contribute "Full Text Search" chapter, for example. AFAIK, we have enough
> people, already wrote PostgreSQL books. I like Corry's book, for example.
> I don't know how much it might costs, but I'm sure community has money for this.
>
> As for certificates, I see no real problem. We need to design nice
> certificate, translate to different languages, publish on www.postgresql and
> approve a list of people, who can sign certificate. We have many members of
> our community in different regions/countries, so this is not a problem.
>
> btw, probably, this project could be a nice introducing for PostgreSQL EU.
>

Hi Oleg,
I agree with your question.

I've just sent a message that question about the official
certification from postgresql.org on the advocacy list.

I hope that the european group can discuss about this important
question and eventually create an european relationship structure
(maybe at pgday ;) .

Regards
Federico

Oleg Bartunov wrote:
> Probably, it's time to sponsor our book-writers and other enthusiasts
> to write "PostgreSQL Administration handbook", which we (community)
> will accept, support and translate to different languages.

http://www.postgresql.org/docs/current/static/admin.html

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Oleg Bartunov wrote:
>> Probably, it's time to sponsor our book-writers and other enthusiasts
>> to write "PostgreSQL Administration handbook", which we (community)
>> will accept, support and translate to different languages.
>
> http://www.postgresql.org/docs/current/static/admin.html
>

With all kudos to that link, that is not a handbook it is a reference.

Joshua D. Drake

--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> Peter Eisentraut wrote:
> > Oleg Bartunov wrote:
> >> Probably, it's time to sponsor our book-writers and other enthusiasts
> >> to write "PostgreSQL Administration handbook", which we (community)
> >> will accept, support and translate to different languages.
> >
> > http://www.postgresql.org/docs/current/static/admin.html
> >
>
> With all kudos to that link, that is not a handbook it is a reference.

Well, there is a lot of descriptive text in the admin section.

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Tue, 27 Mar 2007, Peter Eisentraut wrote:

> Oleg Bartunov wrote:
>> Probably, it's time to sponsor our book-writers and other enthusiasts
>> to write "PostgreSQL Administration handbook", which we (community)
>> will accept, support and translate to different languages.
>
> http://www.postgresql.org/docs/current/static/admin.html

Thanks, I know it. I meant sort of training program.

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Tue, 27 Mar 2007, Bruce Momjian wrote:

> Joshua D. Drake wrote:
>> Peter Eisentraut wrote:
>>> Oleg Bartunov wrote:
>>>> Probably, it's time to sponsor our book-writers and other enthusiasts
>>>> to write "PostgreSQL Administration handbook", which we (community)
>>>> will accept, support and translate to different languages.
>>>
>>> http://www.postgresql.org/docs/current/static/admin.html
>>>
>>
>> With all kudos to that link, that is not a handbook it is a reference.
>
> Well, there is a lot of descriptive text in the admin section.

Lecturers should know better, but I think training course should include
control questions, the order of lecturers, how much time should be
enough to learn a lesson well, practical tasks, etc. This is what people
expects. admin.html is a good foundation, of course. We need better
illustration, on the whole, everything which makes courses professional
(I'm not a specialist, sorry). My young colleagues (Nikolay and Ivan)
are trying to setup Pgsql master class and spent several days to create
training live cd, which is a good idea.

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Tue, 2007-03-27 at 08:54, Marc Evans wrote:
> Hello -
>
> Over the past couple of years I have made use of postgresql as my database
> of choice when developing new software. During that time, my clients have
> in multiple cases eventually come back to me and requested a re-targeting
> to "Any database that we (my client) can find skilled ops staff to
> support." In a most recent case, professional recruiters were employed to
> try to find such people. The search was disappointing at best.
>
> My question for this community is, what do enterprises that you deploy
> postgresql within do for skilled operations staffing? I can understand
> trying to convert a mysql or Oracle person to work on postgresql, but it
> would be very helpful to have a potential talent pool to draw from that
> was similar to those others. Finding people with HA, scaling and
> performance tuning knowledge is something that seems impossible to find
> except in people wanting to be developers.
>
> The sad reality from what I have observed is that unless more people gain
> those skills and want to work in ops, it's becoming very hard for me to
> justify recommending postgresql for enterprise (or larger) scale projects.
>
> What do others  do and/or experience?

I got my current job when a recruiter was scouring the pgsql mailing
lists and emailed me asking me if I was interested in working said
company.

I guess that's one way to look for pgsql people.  She just happened to
catch me right after my last company had decided to switch to Windows
and I'd decided to take a severance package and a short vacation.

I know at least three other people who would make damned good pgsql
admins, but who aren't necessarily looking for that job right now.  I'm
sure most other pgsql users are in the same boat.

Oleg Bartunov wrote:
> On Tue, 27 Mar 2007, Bruce Momjian wrote:
>
> > Joshua D. Drake wrote:
> >> Peter Eisentraut wrote:
> >>> Oleg Bartunov wrote:
> >>>> Probably, it's time to sponsor our book-writers and other enthusiasts
> >>>> to write "PostgreSQL Administration handbook", which we (community)
> >>>> will accept, support and translate to different languages.
> >>>
> >>> http://www.postgresql.org/docs/current/static/admin.html
> >>>
> >>
> >> With all kudos to that link, that is not a handbook it is a reference.
> >
> > Well, there is a lot of descriptive text in the admin section.
>
> Lecturers should know better, but I think training course should include
> control questions, the order of lecturers, how much time should be
> enough to learn a lesson well, practical tasks, etc. This is what people
> expects. admin.html is a good foundation, of course. We need better
> illustration, on the whole, everything which makes courses professional
> (I'm not a specialist, sorry). My young colleagues (Nikolay and Ivan)
> are trying to setup Pgsql master class and spent several days to create
> training live cd, which is a good idea.

My point is that the admin manual is more than a reference, not that the
admin manual is a _training_ manual.

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On 3/27/07, Erik Jones <erik@myemma.com> wrote:
> Not having looked myself, this is as much a question as a suggestion, but
> are there not postgres dba training seminars/courses you could recommend
> they send their dba's to?

There are some classes out there but in my opinion your best bet (from
point of view of looking for good talent) is to get people that found
their way to postgresql themselves.  In that sense you want to hook up
with people from the mailing lists or develop contacts from within the
community.  So, training classes are useful for beefing up on
knowledge and learning new tricks, but  postgresql dbas are born, not
made :)

merlin

>> The sad reality from what I have observed is that unless more people gain
>> those skills and want to work in ops, it's becoming very hard for me to
>> justify recommending postgresql for enterprise (or larger) scale projects.
>>
>> What do others  do and/or experience?
>>
>
>
I think there are people around, but maybe they don't want to move etc.

If a PostgreSQL job where ever to show up in Milwaukee, I would apply
for it in a heartbeat.


Another thing is this, how hard could it possibly be for a MS SQL DBA or
Oracle DBA to pick up using PostgreSQL?
I don't think it would take a decent admin of any database to come up to
speed in a very short time as long as they were interested in doing so.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

On Tue, 2007-03-27 at 15:09, Tony Caduto wrote:
> >> The sad reality from what I have observed is that unless more people gain
> >> those skills and want to work in ops, it's becoming very hard for me to
> >> justify recommending postgresql for enterprise (or larger) scale projects.
> >>
> >> What do others  do and/or experience?
> >>
> >
> >
> I think there are people around, but maybe they don't want to move etc.
>
> If a PostgreSQL job where ever to show up in Milwaukee, I would apply
> for it in a heartbeat.
>
>
> Another thing is this, how hard could it possibly be for a MS SQL DBA or
> Oracle DBA to pick up using PostgreSQL?
> I don't think it would take a decent admin of any database to come up to
> speed in a very short time as long as they were interested in doing so.

I've certainly converted a few MySQL and MSSQL dbas in the past.

"Merlin Moncure" <mmoncure@gmail.com> writes:

> On 3/27/07, Erik Jones <erik@myemma.com> wrote:
>> Not having looked myself, this is as much a question as a suggestion, but
>> are there not postgres dba training seminars/courses you could recommend
>> they send their dba's to?
>
> There are some classes out there but in my opinion your best bet (from
> point of view of looking for good talent) is to get people that found
> their way to postgresql themselves.  In that sense you want to hook up
> with people from the mailing lists or develop contacts from within the
> community.  So, training classes are useful for beefing up on
> knowledge and learning new tricks, but  postgresql dbas are born, not
> made :)

I have the same opinion.  Just look around and see how many "certified
something" are there and how many of them *really* know the product, its
details, how to work with it.

Certifications don't even certify the minimum knowledge.  They are like tests
that we do in school: they show how we are feeling and what we "know" (or
memorized during the night) at the instant of the test.  Some people even
cheat on tests (not that I'm saying it is done or is common with certification
tests...).

So, if I have a good memory to retain information for a week, I'll excel in
certification tests.  But then, what after that week?

I'm against certifications for any product.  It just doesn't show the
reality.


--
Jorge Godoy      <jgodoy@gmail.com>

On Tue, 2007-03-27 at 15:58, Jorge Godoy wrote:

> I have the same opinion.  Just look around and see how many "certified
> something" are there and how many of them *really* know the product, its
> details, how to work with it.
>
> Certifications don't even certify the minimum knowledge.  They are like tests
> that we do in school: they show how we are feeling and what we "know" (or
> memorized during the night) at the instant of the test.  Some people even
> cheat on tests (not that I'm saying it is done or is common with certification
> tests...).
>
> So, if I have a good memory to retain information for a week, I'll excel in
> certification tests.  But then, what after that week?
>
> I'm against certifications for any product.  It just doesn't show the
> reality.

I would say that really depends on the certification.  My flatmate is an
RHCE, and that is a pretty rigorous certification.  Lots of applied
knowledge to fixing purposely broken computer systems.

OTOH, I've read the MCSE study guides before and was very underwhelmed.
Seemed like a guide on which button to push to get a banana.

But neither one is a substitute for 20+ years of on the job experience
of a system.

tony_caduto@amsoftwaredesign.com (Tony Caduto) writes:
> Another thing is this, how hard could it possibly be for a MS SQL DBA
> or Oracle DBA to pick up using PostgreSQL?
> I don't think it would take a decent admin of any database to come up
> to speed in a very short time as long as they were interested in doing
> so.

It's not that big a stretch as long as there is interest.

Those two are probably among the more painful from the specific
perspective that both depend on, in effect, hiding the OS from the
user to a great extent.

PostgreSQL *doesn't* have layers to hide that there is an OS.

In that particular sense, DB2 and Informix are probably moderately
easier "jumps."

There is also the factor that Oracle and Microsoft have the habit of
pretending that their products define what the applications are, as
opposed to merely being instances of the sort of application.  If
people have "drunk the koolaid" and think that "it *must* be like
Oracle to be a proper DBMS," well, there's some painful unlearning
ahead.  Users of not-quite-so-smugly-market-leading systems are
somewhat less likely to fall into that particular hole.
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/advocacy.html
This Bloody Century
"Early this century there was a worldwide socialist revolution. The
great battles were then between International Socialism, National
Socialism, and Democratic Socialism. Democratic Socialism won because
the inertia of democracy prevented the socialism from doing as much
damage here. Capitalism first reemerged from the ashes of National
Socialism, in Germany and Japan. It is now reemerging from the ashes
of International Socialism.  Next?

After all, inertia works both ways..."
-- Mark Miller

tony_caduto@amsoftwaredesign.com (Tony Caduto) writes:
> Another thing is this, how hard could it possibly be for a MS SQL DBA
> or Oracle DBA to pick up using PostgreSQL?
> I don't think it would take a decent admin of any database to come up
> to speed in a very short time as long as they were interested in doing
> so.

It's not that big a stretch as long as there is interest.

Those two are probably among the more painful from the specific
perspective that both depend on, in effect, hiding the OS from the
user to a great extent.

PostgreSQL *doesn't* have layers to hide that there is an OS.

In that particular sense, DB2 and Informix are probably moderately
easier "jumps."

There is also the factor that Oracle and Microsoft have the habit of
pretending that their products define what the applications are, as
opposed to merely being instances of the sort of application.  If
people have "drunk the koolaid" and think that "it *must* be like
Oracle to be a proper DBMS," well, there's some painful unlearning
ahead.  Users of not-quite-so-smugly-market-leading systems are
somewhat less likely to fall into that particular hole.
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/advocacy.html
This Bloody Century
"Early this century there was a worldwide socialist revolution. The
great battles were then between International Socialism, National
Socialism, and Democratic Socialism. Democratic Socialism won because
the inertia of democracy prevented the socialism from doing as much
damage here. Capitalism first reemerged from the ashes of National
Socialism, in Germany and Japan. It is now reemerging from the ashes
of International Socialism.  Next?

After all, inertia works both ways..."
-- Mark Miller

On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:

> Another thing is this, how hard could it possibly be for a MS SQL
> DBA or Oracle DBA to pick up using PostgreSQL?
> I don't think it would take a decent admin of any database to come
> up to speed in a very short time as long as they were interested in
> doing so.

We've been working with a consultant to re-design/optimize some
existing DB systems we have running, and his background is mostly
Oracle and DB/2.  Some of the optimizations -- actually operationally
related choices on how to do things -- are remarkably off-base for
Postgres.  There is a *lot* to learn about a system before one can
truly "know" it.

Vivek Khera wrote:
>
> On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:
>
>> Another thing is this, how hard could it possibly be for a MS SQL DBA
>> or Oracle DBA to pick up using PostgreSQL?
>> I don't think it would take a decent admin of any database to come up
>> to speed in a very short time as long as they were interested in
>> doing so.
>
> We've been working with a consultant to re-design/optimize some
> existing DB systems we have running, and his background is mostly
> Oracle and DB/2.  Some of the optimizations -- actually operationally
> related choices on how to do things -- are remarkably off-base for
> Postgres.  There is a *lot* to learn about a system before one can
> truly "know" it.

It would be a really great service to this community if you would
capture those issues and publish documentation (but feel free to change
or omit the names to protect the incompetent^w innocent!).

On Monday April 09, 2007 at 10:19:53 (AM) Erik Jones wrote:


> On Apr 9, 2007, at 8:15 AM, Vivek Khera wrote:
>
> >
> > On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:
> >
> >> Another thing is this, how hard could it possibly be for a MS SQL
> >> DBA or Oracle DBA to pick up using PostgreSQL?
> >> I don't think it would take a decent admin of any database to come
> >> up to speed in a very short time as long as they were interested
> >> in doing so.
> >
> > We've been working with a consultant to re-design/optimize some
> > existing DB systems we have running, and his background is mostly
> > Oracle and DB/2.  Some of the optimizations -- actually
> > operationally related choices on how to do things -- are remarkably
> > off-base for Postgres.
>
> Can you give some examples of this?  It's not that I don't believe
> you, I'd just like some concrete examples from someone in your
> situation.

Erik, would it be possible for you to post in 'plain text' and not HTML?
This is a mail forum, not a web page.

Thanks!

--
Gerard

On Apr 9, 2007, at 10:09 AM, btober@ct.metrocast.net wrote:

> It would be a really great service to this community if you would
> capture those issues and publish documentation (but feel free to
> change or omit the names to protect the incompetent^w innocent!).

There's no incompetence involved... the guy is clearly not a Postgres
person, but overall an excellent DB designer.  He works on extremely
large databases at a large public university.

The main one that comes to mind is that he suggested adding multi-
part primary indexes to keep the data ordered.  Apparently Oracle and/
or DB2 keep the data sorted by primary key index.  Since the only
reason was to keep the data sorted, the index would be useless under Pg.

Also, he recommended the use of 'index-only' tables -- eg, when the
table is just two or three integers, and the PK is a multi-part key
of all fields, it makes sense not to store the data twice.  However,
in Pg you can't do that since visibility is only stored in the data,
not the index.

One thing that was really counter-intuitive to me from a guy who runs
really large databases, was to get rid of some of the FK's and manage
them in the application layer.  This one scares me since I've had my
behind saved at least a couple of times by having the extra layer in
the DB to protect me... the data integrity would be managed by some
external program that sweeps the DB every so often and purges out
data that should no longer be there (ie stuff that would have been
CASCADE DELETEd).

On Apr 9, 2007, at 9:40 AM, Gerard wrote:

> On Monday April 09, 2007 at 10:19:53 (AM) Erik Jones wrote:
>
>
>> On Apr 9, 2007, at 8:15 AM, Vivek Khera wrote:
>>
>>>
>>> On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:
>>>
>>>> Another thing is this, how hard could it possibly be for a MS SQL
>>>> DBA or Oracle DBA to pick up using PostgreSQL?
>>>> I don't think it would take a decent admin of any database to come
>>>> up to speed in a very short time as long as they were interested
>>>> in doing so.
>>>
>>> We've been working with a consultant to re-design/optimize some
>>> existing DB systems we have running, and his background is mostly
>>> Oracle and DB/2.  Some of the optimizations -- actually
>>> operationally related choices on how to do things -- are remarkably
>>> off-base for Postgres.
>>
>> Can you give some examples of this?  It's not that I don't believe
>> you, I'd just like some concrete examples from someone in your
>> situation.
>
> Erik, would it be possible for you to post in 'plain text' and not
> HTML?
> This is a mail forum, not a web page.


Hmmm...  I didn't have anything HTML set anywhere.  I did however
have message formatting set to Rich-Text (although I was unaware) and
have switched that to plaintext.  This look better?

erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)

On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:

>
> On Apr 9, 2007, at 10:09 AM, btober@ct.metrocast.net wrote:
>
>> It would be a really great service to this community if you would
>> capture those issues and publish documentation (but feel free to
>> change or omit the names to protect the incompetent^w innocent!).
>
> There's no incompetence involved... the guy is clearly not a
> Postgres person, but overall an excellent DB designer.  He works on
> extremely large databases at a large public university.
>
> The main one that comes to mind is that he suggested adding multi-
> part primary indexes to keep the data ordered.  Apparently Oracle
> and/or DB2 keep the data sorted by primary key index.  Since the
> only reason was to keep the data sorted, the index would be useless
> under Pg.

You do have CLUSTER available for ordering a table on a single
index.  However, after you do a CLUSTER new rows and updates don't
respect that and you have to CLUSTER again periodically, but isn't
difficult to add to a regular maintenance schedule/script.

>
> Also, he recommended the use of 'index-only' tables -- eg, when the
> table is just two or three integers, and the PK is a multi-part key
> of all fields, it makes sense not to store the data twice.
> However, in Pg you can't do that since visibility is only stored in
> the data, not the index.

That would be cool.

>
> One thing that was really counter-intuitive to me from a guy who
> runs really large databases, was to get rid of some of the FK's and
> manage them in the application layer.  This one scares me since
> I've had my behind saved at least a couple of times by having the
> extra layer in the DB to protect me... the data integrity would be
> managed by some external program that sweeps the DB every so often
> and purges out data that should no longer be there (ie stuff that
> would have been CASCADE DELETEd).

This is often debated and it does seem strange to here that stance
from a dba.  It's normally the application developers who want to do
that.

erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)

On Mon, 9 Apr 2007 10:34:22 -0500
Erik Jones <erik@myemma.com> wrote:

> Hmmm...  I didn't have anything HTML set anywhere.  I did however
> have message formatting set to Rich-Text (although I was unaware)
> and have switched that to plaintext.  This look better?

Yes, much better. I believe that 'rich text' is essentially HTML,
although I might be mistaken. I know that 'GMail' users have that
problem all the time. Of course 'GMail' users have lots of other
problems also.

--
Gerard

Having a wonderful wine, wish you were beer.

On Apr 9, 2007, at 18:10 , Gerard Seibert wrote:

> On Mon, 9 Apr 2007 10:34:22 -0500
> Erik Jones <erik@myemma.com> wrote:
>
>> Hmmm...  I didn't have anything HTML set anywhere.  I did however
>> have message formatting set to Rich-Text (although I was unaware)
>> and have switched that to plaintext.  This look better?
>
> Yes, much better. I believe that 'rich text' is essentially HTML,
> although I might be mistaken. I know that 'GMail' users have that
> problem all the time. Of course 'GMail' users have lots of other
> problems also.

Erik has actually been posting messages as "multipart/alternative",
where the message is included in both 7-bit plaintext *and* HTML, the
idea being that the mail viewer itself can pick the format it knows
best. You are simply using a mail reader which prioritizes HTML;
perhaps it has a setting to let you prefer plaintext?

Alexander.

In response to Alexander Staubo <alex@purefiction.net>:

> On Apr 9, 2007, at 18:10 , Gerard Seibert wrote:
>
> > On Mon, 9 Apr 2007 10:34:22 -0500
> > Erik Jones <erik@myemma.com> wrote:
> >
> >> Hmmm...  I didn't have anything HTML set anywhere.  I did however
> >> have message formatting set to Rich-Text (although I was unaware)
> >> and have switched that to plaintext.  This look better?
> >
> > Yes, much better. I believe that 'rich text' is essentially HTML,
> > although I might be mistaken. I know that 'GMail' users have that
> > problem all the time. Of course 'GMail' users have lots of other
> > problems also.
>
> Erik has actually been posting messages as "multipart/alternative",
> where the message is included in both 7-bit plaintext *and* HTML, the
> idea being that the mail viewer itself can pick the format it knows
> best. You are simply using a mail reader which prioritizes HTML;
> perhaps it has a setting to let you prefer plaintext?

<email religion>
Sylpheed has this option, which is one of the reasons I use it.
</email religion>

--
Bill Moran
http://www.potentialtech.com

On Monday 09 April 2007 11:50, Erik Jones wrote:
> On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:
> > On Apr 9, 2007, at 10:09 AM, btober@ct.metrocast.net wrote:
> >> It would be a really great service to this community if you would
> >> capture those issues and publish documentation (but feel free to
> >> change or omit the names to protect the incompetent^w innocent!).
> >
> > There's no incompetence involved... the guy is clearly not a
> > Postgres person, but overall an excellent DB designer.  He works on
> > extremely large databases at a large public university.
> >
> > The main one that comes to mind is that he suggested adding multi-
> > part primary indexes to keep the data ordered.  Apparently Oracle
> > and/or DB2 keep the data sorted by primary key index.  Since the
> > only reason was to keep the data sorted, the index would be useless
> > under Pg.
>
> You do have CLUSTER available for ordering a table on a single
> index.  However, after you do a CLUSTER new rows and updates don't
> respect that and you have to CLUSTER again periodically, but isn't
> difficult to add to a regular maintenance schedule/script.
>

There are a lot of scenarios where you really cant afford the penelty running
a cluster entails (24/7 operation, 100gb tables, etc...)

> > Also, he recommended the use of 'index-only' tables -- eg, when the
> > table is just two or three integers, and the PK is a multi-part key
> > of all fields, it makes sense not to store the data twice.
> > However, in Pg you can't do that since visibility is only stored in
> > the data, not the index.
>

You can achieve the same effect with another version of clustered tables
available in other databases, where the order is preserved when data is
added/updated.  Of course we don't have that either.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/09/07 14:05, Bill Moran wrote:
> In response to Alexander Staubo <alex@purefiction.net>:
>
>> On Apr 9, 2007, at 18:10 , Gerard Seibert wrote:
>>
>>> On Mon, 9 Apr 2007 10:34:22 -0500
>>> Erik Jones <erik@myemma.com> wrote:
>>>
>>>> Hmmm...  I didn't have anything HTML set anywhere.  I did however
>>>> have message formatting set to Rich-Text (although I was unaware)
>>>> and have switched that to plaintext.  This look better?
>>> Yes, much better. I believe that 'rich text' is essentially HTML,
>>> although I might be mistaken. I know that 'GMail' users have that
>>> problem all the time. Of course 'GMail' users have lots of other
>>> problems also.
>> Erik has actually been posting messages as "multipart/alternative",
>> where the message is included in both 7-bit plaintext *and* HTML, the
>> idea being that the mail viewer itself can pick the format it knows
>> best. You are simply using a mail reader which prioritizes HTML;
>> perhaps it has a setting to let you prefer plaintext?
>
> <email religion>
> Sylpheed has this option, which is one of the reasons I use it.
> </email religion>

As does Tbird.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGH4wcS9HxQb37XmcRAvC/AJsENy7qxU2ydLBoIFdbEuEksQ22lACg6cKm
yOgrtz3cBjTUNeuTsKRYCY8=
=Gf+v
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/09/07 10:50, Erik Jones wrote:
>
> On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:
[snip]
>> One thing that was really counter-intuitive to me from a guy who runs
>> really large databases, was to get rid of some of the FK's and manage
>> them in the application layer.  This one scares me since I've had my
>> behind saved at least a couple of times by having the extra layer in
>> the DB to protect me... the data integrity would be managed by some
>> external program that sweeps the DB every so often and purges out data
>> that should no longer be there (ie stuff that would have been CASCADE
>> DELETEd).
>
> This is often debated and it does seem strange to here that stance from
> a dba.  It's normally the application developers who want to do that.

It depends on how efficient your engine and site are at deleting
cascades.  If it causes an unacceptable amount of extra locking in a
multi-user situation, away goes the FK and in comes the off-hour
sweeper.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGH41yS9HxQb37XmcRArGnAJ4n12NxeKleCf7n1OFUtOQYnJy1wQCg6OVz
fMjwTsezDnukoV8yyXTouJw=
=XgVW
-----END PGP SIGNATURE-----

Oracle's suggested solution for 'mutating table error' is to create a global
temporary table for the parent
To avoid inconsistent behaviour with the parent table a AFTER ROW trigger
checks new rows and commits rows only to the temporary table then
the  changes from the temp table are committed to permanent parent table
when AFTER STATEMENT trigger is executed
http://www.akadia.com/services/ora_mutating_table_problems.html
M--
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed.  If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy.  Thank you.

----- Original Message -----
From: "Ron Johnson" <ron.l.johnson@cox.net>
To: <pgsql-general@postgresql.org>
Sent: Friday, April 13, 2007 10:02 AM
Subject: Re: [GENERAL] Is there a shortage of postgresql skilled ops people


> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 04/09/07 10:50, Erik Jones wrote:
>>
>> On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:
> [snip]
>>> One thing that was really counter-intuitive to me from a guy who runs
>>> really large databases, was to get rid of some of the FK's and manage
>>> them in the application layer.  This one scares me since I've had my
>>> behind saved at least a couple of times by having the extra layer in
>>> the DB to protect me... the data integrity would be managed by some
>>> external program that sweeps the DB every so often and purges out data
>>> that should no longer be there (ie stuff that would have been CASCADE
>>> DELETEd).
>>
>> This is often debated and it does seem strange to here that stance from
>> a dba.  It's normally the application developers who want to do that.
>
> It depends on how efficient your engine and site are at deleting
> cascades.  If it causes an unacceptable amount of extra locking in a
> multi-user situation, away goes the FK and in comes the off-hour
> sweeper.
>
> - --
> Ron Johnson, Jr.
> Jefferson LA  USA
>
> Give a man a fish, and he eats for a day.
> Hit him with a fish, and he goes away for good!
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFGH41yS9HxQb37XmcRArGnAJ4n12NxeKleCf7n1OFUtOQYnJy1wQCg6OVz
> fMjwTsezDnukoV8yyXTouJw=
> =XgVW
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>