Thread: Postgresql vs. MySQL page techdocs conversion
Following some last-minute improvements from Chris Browne and much editing over the weekend, I just finished converting the page at http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL to the techdocs site. As that percolates through the approval process I guess it should get added to the "Converting from other Databases to PostgreSQL" section, then comes the fun of figuring out how to promote it. Except for trivial formatting differences, the content is identical on both right now, so if you want to check out the final result you can just look at what's on the wiki. One thing I tweaked at the end was clearly stating the bias of the document as a whole and that of some of the people involved, prempting the press or bloggers from putting their own spin in that area. As part of that, I made some comments characterizing how I see the priorities of the core PostgreSQL team. I'll certainly adjust that if anyone feels I misrepresented your position in any way. Here are some suggestions for anyone else who intends to follow this writing model, creating the content on the Wiki and then converting to techdocs later (which I highly recommend because in addition to the ease of collaboration, it's way easier than fighting with the limitations of techdocs the whole time): -If you use the Wikipedia "Printable version" link and then view the source to that, that gives you something you can cut and paste to start with. It won't actually generate a useful document initially, trying to create a starter page using it will just get a complaint from techdocs about it not being valid XHTML. Paste that into some other editor and expect to work on it for a bit in there to do the corrections necessary before techdocs will accept it. -Make sure you have Javascript enabled when using the techdocs editor, it still works without but it's painful. Since I always browse with Firefox+NoScript I didn't notice this at first. Once you've done that, note there is a little "HTML" button on the Javascript WYSIWYG editor that lets you drill down to fix low-level HTML problems. -All the table of contents and similar header material leading up to the content will need to be removed, and baggage at the end of the document after the content has to go too. -Each place there's a header level that's included in the table of contents, there will be some '<div class="editsection"' stuff around it that all has to be removed. Basicially everything from that up til the <Hx> tag has to go. -All of the external URLs will have 'class="external text"' and 'rel="nofollow"' tags on them that techdocs won't accept; use an editor with search and replace to remove all of those. -techdocs doesn't allow H1 level headings; it seems to only support H2 and H3 ones. It will save you much frustration if you build the initial wiki pages accordingly (== translates to H2, === to H3). The items I intially had as H1 level material I replaced with H2+underline. -Expect to make a careful pass over the document to adjust some subtle differences in how whitespace is handled, particularly around the header lines. I had to remove multiple extra lines throughout. Note the Undo button in the Javascript editor if you accidentally remove formatting on a header line during such editing. It took me about two hours to do all that, but since that included figuring out all of the above I'd expect a pre-informed conversion would take less than an hour to complete. Certainly I saved way more time than that by doing all the earlier work in Wikipedia. Because there is a fair amount of work involved, I consider this a one-way conversion, and I only intend to update the techdocs version of the document moving forward. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Mon, 13 Aug 2007, Greg Smith wrote: > Following some last-minute improvements from Chris Browne and much editing > over the weekend, I just finished converting the page at > http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL to > the techdocs site. As that percolates through the approval process I guess why not have more friendly URL like http://developer.postgresql.org/docs/Why_PostgreSQL_instead_of_MySQL In my understanding it could be possible adding ScriptAlias lines to httpd.conf, something like ScriptAlias /docs/ /path/to/index.php/ ScriptAlias /docs /path/to/index.php Also, printable version of page should have some margins for more comfortable printing. 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
Greg Smith wrote: > Following some last-minute improvements from Chris Browne and much > editing over the weekend, I just finished converting the page at > http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL > to the techdocs site. As that percolates through the approval process I > guess it should get added to the "Converting from other Databases to > PostgreSQL" section, then comes the fun of figuring out how to promote > it. Except for trivial formatting differences, the content is identical > on both right now, so if you want to check out the final result you can > just look at what's on the wiki. > Looks good. One comment - one paragraph reads: ==== One add-on tool some find useful for exploring the PostgreSQL planner is Visual Explain, originally a RedHat component that has been kept current and improved by Enterprise DB. It comes bundled with the EnterpriseDB Advanced Server package and can be built to run against other PostgreSQL installations using the source code to their Developer Studio package. ==== For the record, pgAdmin also includes a visual explain tool with similar capabilities. Regards, Dave
In response to Dave Page <dpage@postgresql.org>: > Greg Smith wrote: > > Following some last-minute improvements from Chris Browne and much > > editing over the weekend, I just finished converting the page at > > http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL > > to the techdocs site. As that percolates through the approval process I > > guess it should get added to the "Converting from other Databases to > > PostgreSQL" section, then comes the fun of figuring out how to promote > > it. Except for trivial formatting differences, the content is identical > > on both right now, so if you want to check out the final result you can > > just look at what's on the wiki. > > Another minor nitpick ... I noticed references to both 8.2 and 8.1, and it felt slightly inconsistent to me. -- Bill Moran http://www.potentialtech.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Bill Moran wrote: > In response to Dave Page <dpage@postgresql.org>: > >> Greg Smith wrote: >>> Following some last-minute improvements from Chris Browne and much >>> editing over the weekend, I just finished converting the page at >>> http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL >>> to the techdocs site. As that percolates through the approval process I >>> guess it should get added to the "Converting from other Databases to >>> PostgreSQL" section, then comes the fun of figuring out how to promote >>> it. Except for trivial formatting differences, the content is identical >>> on both right now, so if you want to check out the final result you can >>> just look at what's on the wiki. >>> > > Another minor nitpick ... I noticed references to both 8.2 and 8.1, and it > felt slightly inconsistent to me. > We should probably only talk about current. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGwGjwATb/zqfZUUQRAjtfAKCtN48Cfpnln9qNLYx0mf/zRqABMQCdHhBK 7+4kD8uWTTt48EXsR0YBC2U= =PFlo -----END PGP SIGNATURE-----
On Mon, 13 Aug 2007, Oleg Bartunov wrote: > why not have more friendly URL like > http://developer.postgresql.org/docs/Why_PostgreSQL_instead_of_MySQL The URL on the developer page is temporary; once a permanent one is established on techdocs the Wiki version will fade away and the rest of what you brought up won't matter. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Hi, Sorry for bringing these two points up so late: 1) One of the disadvantages of the multi storage engine concept is that you rally need to tune the settings for each of them independently and that they tend to use separate buffers etc. So in effect one must realize that you are running independent storage components with a single SQL parser! So in the current version of your document you only talk about key buffers, but these will not affect all storage engines! I am not a DB tuning expert, so this should be double checked. 2) In the spirit of MySQL silently ignoring what it does not support, MySQL will actually implicitly commit after several kinds of statements including all DDL. http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html Overall I am very impressed with what you have done here. It truly sounds well balanced and I am more versed in MySQL than PostgreSQL. Before we publish it, we might also want to alert some of the high profile MySQL tuning experts and get their comments. I can take care of this if you would like me to. regards, Lukas regards, Lukas
On Mon, 13 Aug 2007, Bill Moran wrote:
> Another minor nitpick ... I noticed references to both 8.2 and 8.1, and it
> felt slightly inconsistent to me.
I was trying to establish that performance comparisions made against
PostgreSQL versions before 8.1 shouldn't be considered relevant today
because of the scale of improvements in that particular release, but that
either 8.1 or 8.2 are speed-competitive when fairly compared again MySQL 5
using InnoDB+strict mode. Next time I'm editing in there, I'll try to
state that more explicitly.
I don't think it's a good idea to only talk about 8.2 when so many vendors
are still shipping 8.1 as their mainstream supported release. I'd hate
for someone who was considering using PostgreSQL on, say, their shiny new
RHEL 5 box go through this thought process: "oh, that comes with 8.1.
That paper we read only talked about 8.2, so that must be the old slow
version. We shouldn't use that, and since we'll lose support if we don't
run RedHat's package we can't upgrade to 8.2. Scrap that idea, we'll have
to use MySQL if we want it to be fast".
And let me cut off any factual arguments that it doesn't work that way by
saying I have this particular discussion ("we'll lose support if we touch
any of the vendor packages") all the time with people, and it's a very
common perception of how things work among general businesspeople whether
or not it's true for a particular software product.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Mon, 13 Aug 2007, Lukas Kahwe Smith wrote: > in the current version of your document you only talk about key buffers, > but these will not affect all storage engines! I am not a DB tuning > expert, so this should be double checked. The way I read their documentation, that tunable works the same way for all the storage engines. If that's not the case, that's a mistake in the MySQL documentation I link to. I'll put a note about it if you can find evidence otherwise. That "implicit commit" stuff is good to know, will add a pointer to that next time I'm touching the document. It goes along with the general theme of sloppy implementation I've been weaving in that document. > Before we publish it, we might also want to alert some of the high > profile MySQL tuning experts and get their comments. I can take care of > this if you would like me to. I say bring 'em on. If somone seriously into MySQL wants to send me corrections, I'll be glad to incorporate them. And if they want to write a full-on rebuttal, why I'll even link to that right in the document if it's well done. But if they're not as fair as I've tried to be or make a mistake doing that, I might just switch into debunking mode instead; I'm tricky that way. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith wrote: > On Mon, 13 Aug 2007, Lukas Kahwe Smith wrote: > >> in the current version of your document you only talk about key >> buffers, but these will not affect all storage engines! I am not a DB >> tuning expert, so this should be double checked. > > The way I read their documentation, that tunable works the same way for > all the storage engines. If that's not the case, that's a mistake in > the MySQL documentation I link to. I'll put a note about it if you can > find evidence otherwise. http://www.databasejournal.com/features/mysql/article.php/3367871 "While the key_buffer_size is the variable to target for MyISAM tables, for InnoDB tables, it is innodb_buffer_pool_size." Obviously key_buffer_size does affect all storage engines, since MyISAM is the core internal storage engine used for MySQL internals. > That "implicit commit" stuff is good to know, will add a pointer to that > next time I'm touching the document. It goes along with the general > theme of sloppy implementation I've been weaving in that document. Actually very few RDBMS support transactional DDL, though right this second I do not remember what they do in case you issue DDL inside a transaction. I would assume/hope that they give you an error and not do an implicit commit. regards, Lukas
Lukas, > Actually very few RDBMS support transactional DDL, though > right this second I do not remember what they do in case you > issue DDL inside a transaction. I would assume/hope that they > give you an error and not do an implicit commit. See here for Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact. htm "Committing means that a user has explicitly or implicitly requested that the changes in the transaction be made permanent. An explicit request occurs when the user issues a COMMIT statement. An implicit request occurs after normal termination of an application or completion of a data definition language (DDL) operation." - Luke
All, > Actually very few RDBMS support transactional DDL, though right this > second I do not remember what they do in case you issue DDL inside a > transaction. I would assume/hope that they give you an error and not do > an implicit commit. Speaking of which, we should make a big point of our transactional DDL as a feature. It's something that even some of the big proprietary DBs don't have. -- Josh Berkus PostgreSQL @ Sun San Francisco
On Tue, 14 Aug 2007, Josh Berkus wrote: > Speaking of which, we should make a big point of our transactional DDL as a > feature. It's something that even some of the big proprietary DBs don't > have. Already working on it. Got a great example comparing PG against MySQL sent to me off-list, once I get permission I'll put that up and we can see if someone wants to add Oracle/MS-SQL/etc. examples using the same format. Going to add a section on this to the PG/MySQL document, which by the way now has a permanent home at http://www.postgresql.org/docs/techdocs.83 All of the feedback I've gotten since the techdocs conversion will be applied to that soon, the version of the document on the Wiki is turning into a redirect. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
There is now is a discussion of the benefits of PostgreSQL's transactional DDL abilities on the otherwise deprecated page at http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL If someone else could provide me with an Oracle sample showing the same behavior, I'd like to include that (assuming they won't sue me for it). Examples from MS-SQL, DB2, etc. would be nice too. I intend to put this into a separate techdocs page from the main PG vs. MySQL one, because it's a bit big to push into there, and then just summarize the issue and link to the full discussion. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On 8/14/07, Josh Berkus <josh@agliodbs.com> wrote: > All, > > > Actually very few RDBMS support transactional DDL, though right this > > second I do not remember what they do in case you issue DDL inside a > > transaction. I would assume/hope that they give you an error and not do > > an implicit commit. > > Speaking of which, we should make a big point of our transactional DDL as a > feature. It's something that even some of the big proprietary DBs don't > have. I second that. Non-transactional DDL has been seriously bugging me in dealings with Oracle lately. - Yet Another Josh
Greg Smith wrote: > There is now is a discussion of the benefits of PostgreSQL's > transactional DDL abilities on the otherwise deprecated page at Somewhat related ALTER TABLE in MySQL is actually a copy+drop, which obviously is quite costly and time consuming. This is why they can offer the convenience of allowing you to decide where to place a column in an existing table. This in turn is something that ActiveRecord implementations love, because they default to using the column order in their view presentation. Which means a lot of people perceive this as a missing feature in PostgreSQL. regards, Lukas
All, What I would really love to see is an article on "PostgreSQL and Agile Development" where someone who knows AD talks about how our transactional DDL helps them to do AD. I think that would turn some heads in the "Web 2.0" camp. Alas, I don't have the right background ... -- Josh Berkus PostgreSQL @ Sun San Francisco
On Wed, 15 Aug 2007, Josh Berkus wrote: > What I would really love to see is an article on "PostgreSQL and Agile > Development" where someone who knows AD talks about how our transactional DDL > helps them to do AD. Agile practices have such a strong focus on version control that I'm not so sure how this would be compelling. If you're working in a proper AD situation, you should be able to rebuild your schema and related code in a working copy without caring about rollback if it doesn't work out. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD