Is there a shortage of postgresql skilled ops people

[Marc Evans]

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

[Merlin Moncure]

Post by Marc Evans
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Erik Jones]

Post by Merlin Moncure

Post by Marc Evans
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.

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?

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

[Merlin Moncure]

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Jorge Godoy]

Post by Merlin Moncure

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.

[Scott Marlowe]

Post by Jorge Godoy
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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Erik Jones]

Post by Jorge Godoy

Post by Merlin Moncure

Post by Erik Jones
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.

While I agree with everything you guys have said on this, my point
was that client's like seeing that kind of stuff. I'm sure a lot of
companies would give that second thought to converting their systems
over if they had what they perceived as decent training available for
their existing staffs.

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

[Oleg Bartunov]

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

Post by Marc Evans
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: ***@sai.msu.su, http://www.sai.msu.su/~megera/
phone: [phone]

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Federico]

Post by Oleg Bartunov
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Peter Eisentraut]

Post by Oleg Bartunov
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

[Joshua D. Drake]

Post by Peter Eisentraut

Post by Oleg Bartunov
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/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Bruce Momjian]

Post by Joshua D. Drake

Post by Peter Eisentraut

Post by Oleg Bartunov
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 <***@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[Oleg Bartunov]

Post by Bruce Momjian

Post by Joshua D. Drake

Post by Peter Eisentraut

Post by Oleg Bartunov
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: ***@sai.msu.su, http://www.sai.msu.su/~megera/
phone: [phone]

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Bruce Momjian]

Post by Oleg Bartunov

Post by Bruce Momjian

Post by Joshua D. Drake

Post by Peter Eisentraut

Post by Oleg Bartunov
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.

[Oleg Bartunov]

Post by Peter Eisentraut

Post by Oleg Bartunov
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: ***@sai.msu.su, http://www.sai.msu.su/~megera/
phone: [phone]

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Scott Marlowe]

Post by Marc Evans
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Tony Caduto]

Post by Marc Evans
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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Scott Marlowe]

Post by Tony Caduto

Post by Marc Evans
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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Chris Browne]

Post by Tony Caduto
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Vivek Khera]

Post by Tony Caduto
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.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[b***@ct.metrocast.net]

Post by Vivek Khera

Post by Tony Caduto
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!).



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Vivek Khera]

Post by b***@ct.metrocast.net
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).

[Erik Jones]

Post by Vivek Khera

Post by b***@ct.metrocast.net
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.

Post by Vivek Khera
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.

Post by Vivek Khera
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 <***@myemma.com>
software developer
615-296-0838
emma(r)




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Robert Treat]

Post by Erik Jones

Post by Vivek Khera

Post by b***@ct.metrocast.net
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...)

Post by Erik Jones

Post by Vivek Khera
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.

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[snip]

Post by Vivek Khera
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

[Martin Gainty]

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" <***@cox.net>
To: <pgsql-***@postgresql.org>
Sent: Friday, April 13, 2007 10:02 AM
Subject: Re: [GENERAL] Is there a shortage of postgresql skilled ops people

Post by Ron Johnson
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[snip]

Post by Vivek Khera
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Gerard]

Post by Vivek Khera

Post by Tony Caduto
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!

[Erik Jones]

Post by Gerard

Post by Vivek Khera

Post by Tony Caduto
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 <***@myemma.com>
software developer
615-296-0838
emma(r)




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Gerard Seibert]

On Mon, 9 Apr 2007 10:34:22 -0500

Post by Erik Jones
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.

[Alexander Staubo]

Post by Gerard Seibert
On Mon, 9 Apr 2007 10:34:22 -0500

Post by Erik Jones
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.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Bill Moran]

Post by Alexander Staubo

Post by Gerard Seibert
On Mon, 9 Apr 2007 10:34:22 -0500

Post by Erik Jones
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>

[Ron Johnson]

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

Post by Bill Moran

Post by Alexander Staubo

Post by Gerard Seibert
On Mon, 9 Apr 2007 10:34:22 -0500

Post by Erik Jones
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-----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Erik Jones]

Post by Vivek Khera

Post by Tony Caduto
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 jones <***@myemma.com>
software developer
615-296-0838
emma(r)