Re: [GENERAL] What is the average salary for Postgresql DBA
On Wed, May 18, 2011 at 10:52 PM, AI Rumman wrote: > Hey guys, > > If I get H1B visa as Postgresql DBA, then what may I expect to get as my > salary? > Does anyone have idea on this? > > Thanks. > This topic never gets many replies. I will reply because I am not really part of the community. It depends massively on the company, where you live and your skill level. For a normal area (not San Francisco, etc) I think the following is sane: Jr DBA: 50-75k (probably 3+ years of relevant experience, but not DBA experience) DBA: 60-90k (several years DBA experience) Sr DBA:80-120k (several years DBA experience plus extensive knowledge) Anyone feel like correcting me? -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?
On Fri, Jul 30, 2010 at 5:41 AM, Brad Nicholson wrote: > On 10-07-29 08:54 PM, Greg Smith wrote: >> >> Brad Nicholson wrote: >>> >>> Postgres also had a reputation of being slow compared to MySQL. >>> This was due to a lot of really poor MySQL vs Postgres benchmarks >>> floating around in the early 2000's. >> >> I think more of those were fair than you're giving them credit for. > > I'm sure some where, but I recall a lot that were not. > > The main problems I recall is that they took the stock postgresql.conf > (which was far to restrictive) and measured it against a much better MySQL > config. They then measured some unrealistic test for most applications and > declared MySQL the clear winner for everything and Postgres slow as a dog. > I would like to point out that in general the opposite is probably generally in effect at this point. For software dev that downloads MySQL 5.1 and PG 8.4 and selects sane options PG will probably have a significant advantage. MyISAM is dead.* Innodb does not make much use of fs caching, while PG depends on it. With a "detuned" instance PG will likely have a significant advantage over Innodb for that reason. *Pretend to be a developer and install MySQL on windows. You will probably not get a MyISAM default. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 8:56 AM, Scott Marlowe wrote: > On Fri, Jun 25, 2010 at 11:48 AM, Rob Wultsch wrote: >> - Innodb : The primary transactional storage engine for MySQL. It does >> not have all the features of PG (like check contraints), but it has >> some features (like Compression!!!) which are *exceptionally* useful. > > You do know that pg has compression for text types built in already, > right? I'm sure there are subtle differences in the way compression > is done in each engine, just pointing that out. > I do, but TOAST is a very different animal than compression at the page level. The innodb buffer pool is also effectively compressed which allows for far greater use of memory. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 4:58 AM, Scott Marlowe wrote: > Next up: PostgreSQL stores its system catalogs in transaction safe > table types, like everything else it stores. MySQL stores its table > defs in myisam, even if the whole of the db you create is innodb and > innodb is the default. System crash in the middle of DDL? Might lose > a table or two. This is not true. MySQL stores users, acl, etc in MyISAM tables. In almost all setups users and acl do not change often so the crash sensitivity is not a big issue. I have dealt with (and still do deal with) horribly abused MySQL instances and I very very rarely run into issues with corruption on the system schema. MyISAM in not involved in the storage of data about Innodb. MySQL table definitions are stored in .frm files. Alterations to table definitions in MySQL (in general) are done by building a temporary table with the new definitions and the existing data and then shell gaming the files in. It is possible to have issues from a crash, but it is very rare. > > Next up: MySQL has optimizations made without proper testing. For > example, see this bug: > > http://bugs.mysql.com/bug.php?id=28591 > > This "optimization" made MySQL ignore the DESC keywork in innodb > tables. It was pushed into live, production ready MySQL code > midstream in version 5.0.28 on 2007-08-02. MySQL GA (i.e. production > ready) release had been made two years previously in March of 2005. > > Fix was pushed out on 2007-09-24, nearly 60 days later, to version > 5.0.48. Problem solved right? Well, not really, according to > http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed. The > actual fix gets pushed out on 2008-09-12. > > This shows several things about the MySQL release philosophy, at least > at the time. 1: Introducing performance enhancments without thorough > testing in a production release is A-OK. 2: The fix may or may not > actually work when it does get applied. and 3: It can take about a > year to get that fix in place. > > Things may be a LOT better by now. I'd certainly hope so. But I have > no real confidence or evidence of such an internal change. Sun/Oracle has improved things a lot. Long standing bugs are being closed and it feels like more care is being put into releases. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 1:44 AM, Dave Page wrote: > On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch wrote: >> MySQL has several full text search solutions. The built in MyISAM >> solution is the best known, but there is also an engine for using >> sphinx. >> >> ... >> >> And there are features that MySQL has that PG does not. Index only >> queries is a massive feature. Pluggable backend storage engines are >> another. > > Some might argue that is not a feature. Sure, it means you can have > different types of storage, but it means the feature set gets > fragmented - for example, if you want text search, you use MyISAM, but > if you want relational integrity you have to use InnoDB or some other > backend. You want both? Oh. Hmmm. > > It could also be argued that having a storage engine API means that > the query planner/optimiser cannot have nearly as much knowledge about > how the data is stored and what access characteristics it may have > thus preventing it from being as well optimised as Postgres. > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise Postgres Company > In many cases this criticism would be correct with the current interface. Drizzle has already changed the interface and is looking to allow more knowledge to be passed back to planner. In a few years MySQL like systems may have as much knowledge as PG does. The freedom of the storage engine interface allows for much more varied backend characteristics. Some examples: - NDB: A GPL'ed distributed highly redundant transactional storage engine for MySQL that can non-impactfully survive the lose of servers. I know of no PG equivilant. - TokuDB and Infobright: Data warehousing backends. The alternatives data warehousing forks based on PG that I know of are true forks which end up diverging significantly from the mainline. With MySQL the esoteric backends can stay current with mainline easily. IIRC both have incorporated new planner'ish features. - Sphinx : Fulltext indexing in MySQL done right. - CSV : A SQL interface to CRUD CSV. I know of no comparable in PG. - Blitzdb :An interesting new non-transactional engine that has recently been merged into Drizzle. - Innodb : The primary transactional storage engine for MySQL. It does not have all the features of PG (like check contraints), but it has some features (like Compression!!!) which are *exceptionally* useful. The backend being seperate from the core has in the last few years allowed significant features additions/changes (thank you Oracle). PostGIS is somewhat similar in how it is decoupled from core. - Blackhole : A storage engine that does not actually store data. I have used this for a variety of purposes including making ORM happy and allowing the dropping of parts of an application backend without breaking the application completely. To some in the MySQL community much of the most interesting development has happened outside of core. I guess the crux of my point is that storage engine interface allows for many features that are not found in PG. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Thu, Jun 24, 2010 at 10:03 PM, Jim Montgomery wrote: > Remove me from your email chain. > Remove yourself. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 1:22 AM, John Gage wrote: > There are features, are there not, that Postgres has that MySQL does not > have? > > I refer in particular to things like tsvector. > > Am I mistaken in this? > > John > > > On Jun 25, 2010, at 3:46 AM, Rob Wultsch wrote: > >> unless there was a specific reason to migrate > > MySQL has several full text search solutions. The built in MyISAM solution is the best known, but there is also an engine for using sphinx. ... And there are features that MySQL has that PG does not. Index only queries is a massive feature. Pluggable backend storage engines are another. MySQL is also somewhat simpler to tune. Both systems can work well. Both have advantages. Both can suck. As has been just demonstrated, both have communities that suck at mingling with the other major open source rdms. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Thu, Jun 24, 2010 at 6:13 PM, Scott Marlowe wrote:
> For instant, by default, this will work in mysql:
>
> create table test (i int);
> insert into test (i) values ('');
>
> with a warning, but will produce an error in most modern versions of pgsql.
>
However it is easy to get mostly sane behavior from MySQL:
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)
mysql> create table test (i int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test (i) values ('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'i' at row 1
If it were me I would generally work with whichever system I knew
better unless there was a specific reason to migrate. Both systems
will be a bit of a pain as they are both complicated. C'est la vie.
All else being equal I would start a new project with PG.
Full disclosure: I am a MySQL DBA.
Best,
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Completely wrong row estimates
2010/4/4 Björn Lindqvist : > Subject: Completely wrong row estimates > > Hello everybody, > > Here is the EXPLAIN ANALYZE output for a simple query in my database > running on postgres 8.3.9: > > EXPLAIN ANALYZE > SELECT * FROM word w JOIN video_words vw ON w.id = vw.word_id > WHERE w.word = 'tagtext'; > > QUERY PLAN > --- > Nested Loop (cost=18.89..2711.16 rows=95 width=24) (actual > time=19.266..131.255 rows=43374 loops=1) > -> Index Scan using word_word_key on word w (cost=0.00..8.28 > rows=1 width=12) (actual time=0.029..0.034 rows=1 loops=1) > Index Cond: ((word)::text = 'tagtext'::text) > -> Bitmap Heap Scan on video_words vw (cost=18.89..2693.31 > rows=766 width=12) (actual time=19.227..77.809 rows=43374 loops=1) > Recheck Cond: (vw.word_id = w.id) > -> Bitmap Index Scan on video_words_word_id_key > (cost=0.00..18.70 rows=766 width=0) (actual time=12.662..12.662 > rows=43374 loops=1) > Index Cond: (vw.word_id = w.id) > Total runtime: 154.215 ms > > Note how the planner estimates that there are 766 rows in the table > that matches the word 'tagtext'. In reality 43374 does. I've tried to > get postgres to refresh the statistics by running with > enable_statistics_target=100, running VACUUM, VACUUM FULL, VACUUM FULL > ANALYZE etc but nothing works. Postgres seem stuck with its bad > statistics and unwilling to change them. There are many other strings > that also matches tens of thousands of rows in the table which > postgres only thinks matches 766. > > Is this a bug in postgres? > > > -- > mvh Björn > You probably want to run "analyze" or "vacuum analyze" to update statistics. Do you have auto vacuum setup? -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is Cost 100 in stored procedure
On Tue, Jan 12, 2010 at 8:06 PM, Yan Cheng Cheok wrote: > May I know what is the meaning of Cost 100, at the end of stored procedure? > > > END;$BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > ALTER FUNCTION create_tables() OWNER TO postgres; > > Thanks and Regards > Yan Cheng CHEOK > "execution_cost A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. If the cost is not specified, 1 unit is assumed for C-language and internal functions, and 100 units for functions in all other languages. Larger values cause the planner to try to avoid evaluating the function more often than necessary. " http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] haversine formula with postgreSQL
On Thu, Sep 17, 2009 at 2:47 PM, Tom Lane wrote:
> Jonathan writes:
> > Here is my PHP with SQL:
> > $query = sprintf("SELECT 'ID', 'FACILITY', 'ADDRESS', latitude,
> > longitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians
> > ( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
> > ( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM
> > aaafacilities HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20",
>
> Sigh, you've been misled by MySQL's nonstandard behavior. You cannot
> refer to output columns of a query in its HAVING clause; it's disallowed
> per spec and not logically sensible either. The simplest way to deal
> with it is just to repeat the expression in HAVING. If you really
> really don't want to write it twice, you can use a subquery.
>
>regards, tom lane
>
This practice is also a bad habit for MySQL users. I regularly see queries
from users that have conditions that logically belong in the WHERE clause
but the user shoves it into the HAVING. This is often done without a
specific GROUP BY. The MySQL optimizer does not deal with this well.
When would it make logical sense to have a HAVING clause that deals with a
column that is not inside a aggregating function?
--
Rob Wultsch
wult...@gmail.com
Re: [GENERAL] Annoying Reply-To
On Thu, Oct 23, 2008 at 10:42 AM, ries van Twisk <[EMAIL PROTECTED]> wrote: > > On Oct 23, 2008, at 12:25 PM, Collin Kidder wrote: > > Bruce Momjian wrote: >> >>> >>>> Mikkel is right, every other well-organized mailing list I've ever been >>>> on handles things the sensible way he suggests, but everybody on his side >>>> who's been on lists here for a while already knows this issue is a dead >>>> horse. Since I use the most advanced e-mail client on the market I just >>>> work around that the settings here are weird, it does annoy me a bit >>>> anytime >>>> I stop to think about it though. >>>> >>>> >>> I think this is the crux of the problem --- if I subscribed to multiple >>> email lists, and some have "rely" going to the list and some have >>> "reply" going to the author, I would have to think about the right reply >>> option every time I send email. >>> >>> Fortunately, every email list I subscribe to and manage behaves like the >>> Postgres lists. >>> >>> >>> >> I find it difficult to believe that every list you subscribe to behaves as >> the Postgres list does. Not that I'm doubting you, just that it's difficult >> given that the PG list is the ONLY list I've ever been on to use Reply as >> just replying to the author. Every other list I've ever seen has reply as >> the list address and requires Reply All to reply to the original poster. >> Thus, I would fall into the category of people who have to think hard in >> order to do the correct thing when posting to this list. >> > > I have the same experience, only PG list seems to behave different. > > In my humble opinion I feel that I am subscribed to the list (It also says > on the bottom Sent via pgsql-general mailing list ( > pgsql-general@postgresql.org)), so a reply (not reply all --- remove > original author) should go back to the list where I am subscribed at, in in > my opinion the source is the list aswell (that's why I am getting it in the > first place). > I know of at least one other list that is similar: MySQL. And I brought it up a year ago with no eventual change: http://lists.mysql.com/mysql/209593 After a while you just get used to hitting reply all when you mean to reply all. I now prefer (though not strongly) this setting. -- Rob Wultsch
Re: [GENERAL] Optimzing Postgresql
On Sat, May 24, 2008 at 11:57 AM, Ram Ravichandran <[EMAIL PROTECTED]> wrote: > ..."High Performance MySQL" ... BTW: The current version of this book is (somewhat) out of date, and the next version will be released in next few months. -- Rob Wultsch [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, May 1, 2008 at 10:27 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > >> Maybe I'm some crazy, radical DBA, but I've never had a version of > >> pgsql get EOLed out from underneath me. > > Just for fun, I did a bit of digging in the release notes > http://developer.postgresql.org/pgdocs/postgres/release.html > and came up with this table about PG major releases and their > follow-on bug fix/minor releases: > > Version Release date# updates Days till final update Days till > next major > > 6.0 1997-01-29 0 0 130 > 6.1 1997-06-08 1 44 116 > 6.2 1997-10-02 1 15 150 > 6.3 1998-03-01 2 37 243 > 6.4 1998-10-30 2 51 222 > 6.5 1999-06-09 3 126 334 > 7.0 2000-05-08 3 187 340 > 7.1 2001-04-13 3 124 297 > 7.2 2002-02-04 8 1190296 > 7.3 2002-11-27 21 1867355 > 7.4 2003-11-17 19+ ? 429 > 8.0 2005-01-19 15+ ? 293 > 8.1 2005-11-08 11+ ? 392 > 8.2 2006-12-05 7+ ? 426 > 8.3 2008-02-04 1+ ? ? > > It's pretty clear that there was a sea-change around 7.2/7.3 --- > before that, nobody thought that PG releases were anything that > might be long-lived. And there's nothing in this table that > suggests we've really settled on a new lifespan ... other than that > we're still putting out new majors at a constant rate, and the community > hasn't got the resources or interest to maintain an ever-increasing > number of back branches. > > regards, tom lane Not really Postgres's problem, but for whatever its worth if I do the following on Debian stable: $apt-get install postgresql I get 7.4 . When I install Debian I generally expect the software to be supported for a long time. Perhaps it might make sense to declare it dead except for security issues? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 2:51 PM, Robert Treat <[EMAIL PROTECTED]> wrote: > On Wednesday 23 April 2008 14:10, Karsten Hilbert wrote: > > On Wed, Apr 23, 2008 at 12:38:48PM +0200, Andreas 'ads' Scherbaum wrote: > > > Yes. You should/can use ENUM for something like 'gender': > > > male, female, unknown. You don't need to add other values ever (yeah, i > > > skipped some special cases). > > > > I was gonna say ! :-) > > > > Add > > > > hermaphrodite > > transgender with female phenotype > > transgender with male phenotype > > > > and you should be set from current medical science's point > > of view ;-) > > > > The standard is unknown, male, female, and n/a. Both unknown and n/a sounds like NULL to me. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgre vs MySQL
On Tue, Mar 11, 2008 at 11:55 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > People here are bound to prefer PostgreSQL to MySQL, otherwise you'd > find us on a MySQL list. What sort of database were you looking at? On > what operating system? With what hardware? I semi regularly post on the MySQL General Discussion list, and not here (until now). The knowledge base is much deeper and there is much more traffic here. MySQL has a very low barrier to entry, and allows people to form very bad habits. Postgres has a superset of the MySQL features. I wish I worked in a Postgres environment. -- Rob Wultsch -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
