Thread: Many comments (related to "Are we losing momentum?")
Hello all In earlier threads on the Hackers mailing list there were discussions of a few things that I would like to comment on. Sorryin advance for the length of this post. First, PostgreSQL needs 2PC AND eager replication AND lazy replication all at the same time. Of those however, I think Postgreswould get the most bang for the buck by supporting 2PC and eager (postgres-r) asap. Add lazy replication when youcan later. (Besides 2PC, and postgres-r seem to be closer to completion and integration with the main postgres developmenttree) Also, 2PC and eager replication should work together simultaneously. Why?.... I may want performance and reliability provided by eager replication, but I also need to update the database and performsome operations on a pair of queues as a single transaction. In order for this to be possible we need both replicationand 2PC, and they have to work together! In this situation 2PC and replication have to be developed with knowledgeof each other. Lazy replication could be added to this situation later without to much effect. Another thing I noted is that people are asking for 2PC with the JDBC driver. Obviously that can only happen when the databasesupports the feature. However, from what I last saw on the JDBC list archives it doesn't appear as if the JDBC driverdevelopers know you guys are changing the FE/BE protocol and may soon be adding 2PC. I think the JDBC developers shouldbe made aware of this asap... If all of a sudden the JDBC driver doesn't work with the newest version of Postgresthere is going to be some upset people! You should also ask the JDBC driver developers to take part in the discussionsof the new FE/BE protocol (if they aren't already). 2PC / XA resources in the JDBC driver will provide a verylarge boon to Postgres in the Java / J2EE arena. Second, PostgreSQL needs to run as a production grade system on Windows if you want to increase "mind share". Why?.... Postgres is great, and free ($) but if you can only run a "production" system on UN*X there is a cost as far as a windowsdeveloper is concerned. That cost is the time and effort required to learn some flavor of UN*X in order to deploythe database. That is a very significant investment in time ($) to learn and money ($) for books. (Yes you can learneverything you need online for free, but who is going to do that... People buy books.) When you get a production quality Postgres on Windows you will be immediately increasing the potential user base for Postgresand that will have a very large effect on the "mind share" postgres has. I would bet one of the reasons MySQL isso popular is because a Windows developer can just install a binary and use it on their systems. Also a very important point everyone seemed to miss in regards to MySQL is that you CAN use it in a "commercial" environmentas long as you are not distributing your product. I.E. if you create an app only to be used by your company internally,then the company doesn't need to buy a MySQL license. Also, if the company downloads some open source software,and downloads MySQL, and deploys them both for internal use it is not required to purchase a license for MySQL. Why do I KNOW all this? Because I am a Windows user / Java developer, who has been spending a lot of time ($) and moneyon books ($) to learn Linux (Because it is awesome!)... I also spent a lot of time going over MySQL, Postgres, Firebird,SAPDB and even the old Sybase version that was released for Linux. Ive also worked (at work) with Oracle, DB2,MS-SQL. Postgres should not and is not competing with MySQL, and that should not be your focus. Postgres is competing with Oracle,MS-SQL, DB2, Sybase, Firebird, etc. Why?... For a few reasons: 1) Some MySQL users have a near religious affiliation with MySQL. You will never convert these people. Don't even try. 2) Some MySQL users are Windows users who would use any open source database they can, as long as they can run it on Windowsin a "production" setting. For these users Postgres is not an option for the moment. When Postgres Win32 is available(and production grade) I think many of these users will begin investigating it. Until recently the only optionfor an open source DB on Windows was MySQL. (Now you have Firebird, SAPDB, and probably others). So yes in some aspectyou are competing with MySQL here... But why compete, just make the best product you can and let the user decide whichone they want to use. 3) Some MySQL users only need what MySQL provides. There is no incentive for these users to switch; the system they knowdoes what they need. Don't try to compete with MySQL it's a waste of time. DO compete with Oracle, etc.. 1) Postgres is a contender in many significant areas to the commercial DB's. When postgres has replication and 2PC thiswill definitely increase the threat that Postgres is to the commercial DB market. 2) Users who need triggers, stored procedures, etc. cannot consider MySQL.. it doesn't support those features (at the moment). They can look at Postgres, SAPDB, Firebird, and the commercial DB's. To compete Postgres has to be better thanthese other options. What makes Postgres the better solution in this arena? a) price - although remember the related costs I mentioned above. Also firebird and SAPDB are free and run on Windows, sothey may have a "lower price" for Windows developers for the moment. Postgres Win32 will help us in this area. b) reliability - no, all the DB's are fairly reliable. (lets not waste time discussing this one ok folks) c) XA resources in Java, 2PC.. No, we don't have it yet.. We need it. I think the firebird JDBC driver has this already. This sounds like its coming in 7.4. d) Replication - no, we don't have it yet.. We need it. Sounds like master slave is coming in 7.4. We need to get multi-mastersoon. You can already do multi-master replication in MySQL. (Yes we all know that what they have for replicationis not as robust, especially if you use it in multi-master (circular replication a->b->c->a) but at least theyhave something. Lets not waste our time discussing what they have.. Lets instead spend that time building an evenbetter solution for Postgres.) e) cross-db queries - no, we don't have it yet.. We need it. (extend postgres views capability, dblink and use 2pc? Seeearlier post). f) stored procedures that return result sets (tuples). Postgres finally got that in 7.3. g) insert your needed feature here If Postgres had 2pc, and Replication that would definitely move it closer to killing the commercial db's. 2pc is a buildingblock to getting cross-db queries (across servers). So, from the above RIGHT NOW Postgres is NOT a better solution than the commercial DBs except in the area of price Andthat is true only for people already familiar with UN*X. Dont get me wrong here folks I love Postgres.. Its just timefor us to take a step back and look at the facts, and the facts are Postgres needs even more to be truly competitivewhere it should be competing. (Not competing with MySQL.) I think many of the features Postgres needs to be abetter competitor are coming in the 7.4 release. Other things that can / need to be done to increase Postgres mind share. a) Embrace the Java community. The Java community has very good and close ties to the open source community. This isobvious because of the numerous open source projects in and for java. Jakarta.apache.org and all the frameworks on itlike log4j, Struts, etc. Free open source J2EE app servers like Jboss, Jonas, etc.. However, the JDBC driver for Postgresis HARD TO FIND! Its hard to find because someone new to postgres cant find anything about it on the Postgresql.orghome page. There is no link to it, and no mention of it. Granted the JDBC driver can be found by searchinggoogle, and by looking in the interfaces folder of the download. But a new Java visitor may not search google,and probably wont bother to download Postgres if they dont think it supports JDBC. Also, Postgres supports manydifferent languages for use in functions and stored procedures, but it doesnt support Java. Many commercial DBs do. Maybe Postgres should consider adding support for Java in this area? (I dont think this is overly important, its justsomething else that could be done.) b) The Postgres group has done a lot to improve the homepage of Postgresql.org. But when you click on most links it takesyou to another site with a completely different look and feel. The side effect of this is that the Postgres projectlooks as if it is un-organized. Even more needs to be done in this area. The postgres site should be more cohesivewith a single look and feel for as much of the site and its links as possible. Look at apache.org. They have MANYprojects going on, but have a singular (or at least very similar) look and feel across most of their projects. You wanta real shocker Visit the gborg homepage (click the gborg link on the bottom left corner of the postgres site). Thenread the news on the right side of the gborg homepage. See that url for http://www.greatbridge.org/ in the GreatBridge.OrgVersion 2.0.0 Release story from way back in 2001! Go ahead, visit that URL. Whats the first thing yousee on that page? Purchase IBM DB2 At IBM.com. Come on! Not even a mention of Postgres, and this is right on thegborg homepage! The firebird, sapdb and mysql sites are killing postgres here. The postgres homepage and related linksis the first thing someone new to postgres sees! There shouldnt be news on any of the main pages from back in 2001. It looks like nothing is going on with Postgres. There has got to be more recent news that can be put up. Also, goingback to the Java thing, the link for PostgreSQL JDBC 2.0 API compliance on the JDBC homepage goes to a dead link andhas been that way for weeks. What does this tell a new person looking to use Postgres from Java? I could keep going here, but this message is long enough already Basically my points are: 1) Postgres IS GREAT, but for the moment the real competition (Commercial DBs) are providing more features. We need tocatch up in some areas, and provide things they cant in others (easy / free / safe / multi-master eager replication) 2) We shouldnt be competing with MySQL. 3) Postgres may be great, but the perception given by collection of sites for postgres is that the project is un-organized. All the other open source DBs have a better look / feel / organization in this area. Later Rob
> -----Original Message----- > From: Rob Butler [mailto:robert.butler5@verizon.net] > Sent: 16 April 2003 16:33 > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] Many comments (related to "Are we losing > momentum?") > > > You > want a real shocker... Visit the gborg homepage (click the > gborg link on the bottom left corner of the postgres site). > Then read the news on the right side of the gborg homepage. > See that url for http://www.greatbridge.org/ in the > GreatBridge.Org Version 2.0.0 Release story from way back in > 2001! Go ahead, visit that URL. What's the first thing you > see on that page? "Purchase IBM DB2 At IBM.com". Come on! > Not even a mention of Postgres, and this is right on the > gborg homepage! We don't own that site, and we never did. We did manage to inherit the projects database through it's key programmer and his goodwill. > The firebird, sapdb and mysql sites are > killing postgres here. The postgres homepage and related > links is the first thing someone new to postgres sees! There > shouldn't be news on any of the main pages from back in 2001. There isn't. The oldest news is from January 2003. Wrt your comments on the style of some of the pages linked off the main site (the archives spring to mind), if there are any volunteers to help fix that, please raise your hands because my time is limited and I could do with some committed help on that sort of thing. Regards, Dave.
On Wed, 2003-04-16 at 11:51, Dave Page wrote: > > -----Original Message----- > > From: Rob Butler [mailto:robert.butler5@verizon.net] > > Sent: 16 April 2003 16:33 > > To: pgsql-hackers@postgresql.org > > Subject: [HACKERS] Many comments (related to "Are we losing > > momentum?") > > > > > > You > > want a real shocker... Visit the gborg homepage (click the > > gborg link on the bottom left corner of the postgres site). > > Then read the news on the right side of the gborg homepage. > > See that url for http://www.greatbridge.org/ in the > > GreatBridge.Org Version 2.0.0 Release story from way back in > > 2001! Go ahead, visit that URL. What's the first thing you > > see on that page? "Purchase IBM DB2 At IBM.com". Come on! > > Not even a mention of Postgres, and this is right on the > > gborg homepage! > > We don't own that site, and we never did. We did manage to inherit the > projects database through it's key programmer and his goodwill. > Oddly I don't see the link he's referring to, but I've always thought it unfortunate that the postgresql community didn't hold onto those domain names. I was just speaking with a tech writer last night who mentioned how the company that was initially behind postgresql went kaput ("I think it was called great river or something?") Took me a minute to set him straight on that. Anyways, I've offered to look into setting up gforge services in the past as it's a mature project with a good development community and the founder (Tim Perdue) has always been friendly with the PostgreSQL community. Course I don't see this as an immediate need, but long term I think it would be a good idea. > > The firebird, sapdb and mysql sites are > > killing postgres here. The postgres homepage and related > > links is the first thing someone new to postgres sees! There > > shouldn't be news on any of the main pages from back in 2001. > > There isn't. The oldest news is from January 2003. > > Wrt your comments on the style of some of the pages linked off the main > site (the archives spring to mind), if there are any volunteers to help > fix that, please raise your hands because my time is limited and I could > do with some committed help on that sort of thing. > I'm in the process of adding the newsletters to the main page (expect questions your way later today ;-) but once I am done with that "cohesiveness" will definitely be on my radar screen. Robert Treat
> > Oddly I don't see the link he's referring to, but I've always thought it > unfortunate that the postgresql community didn't hold onto those domain > names. Hmm, very odd. I just went out to: http://gborg.postgresql.org/project/gborg/projdisplay.php today by following the linkfrom the postgres homepage. On the right side the oldest news story is all the way back to 2000. Copied the last news stories on the page to show it's really there. Perhaps it could be the proxy server I'm behind? The newest story is "Additional Project Configuration Options Available". ryan : 03/22/2001 GreatBridge.Org Version 2.0.0 Release The latest version of GreatBridge.Org's GBSite, version 2.0.0, has been released and http://www.greatbridge.org/ is now runningthe new version. Version 2.0.0 is just the latest enhancements for GreatBridge.org and includes many changes basedon user feedback, as well as extra functionality we know everyone will like. Current users of our site will notice the enhanced bug and task tools, including advanced searches, search memory for last search and default search. Tools for admins and users of the site to receive bug/task change notifications have also been added. In addition, we have also added the ability for admins to have news and bugs sent to their mailing lists when items are added or modified. New functional areas include project-specific FAQ pages and Feature Request tools. We encourage and hope our users and fellow developers will take advantage of all of these new features. We encourage your feedback and suggestions to help GreatBridge.Org to be the best open source development tool on the internet. (full story...) ryan : 02/23/2001 GBorg GBSite version 1.1.1 (Happy New Year) Just in time for the New Year Great Bridge has released the latest version of our GBorg GBSite application. We added several small additions including, but not limited to: Additional Bug/Task selection "Not Closed" Errata page and manager Show Bug counts on Project Home page Show Bug Comment counts on Bug List page Show counts of projects in Project Categories fixed several minor bugs (full story...) ryan : 12/28/2000 GBorg GBSite Version 1.1.0 Released The new version of the site is now up and running and available for download. One of the best new features is the ability to add comments to bugs. Check it out as I will be filling comments in on some of the more recent bugs so you can see what we are doing with the problems you have found! (full story...) ryan : 12/11/2000 GreatBridge.org Preps For Ver 1.1 The GBORG project team is currently working on delivering version 1.1 by December 8. This version will feature a few new areas. These include: Errata Manager FTP Upload Access Bug Enhancements Online Mailing List Response Code Snippets To learn more about these features, visit the GBORG project and check the task list. Thanks to everyone for their support. If you have any ideas or suggestions for further improvement, drop us a line. (full story...)
Robert Treat <xzilla@users.sourceforge.net> writes:
> Oddly I don't see the link he's referring to, but I've always thought it
> unfortunate that the postgresql community didn't hold onto those domain
> names.
We were not offered the chance; Landmark wanted to hold onto the Great
Bridge trademark, apparently. We did come away with the postgres.com,
postgres.org, and postgresql.com domains, which GB had managed to wrestle
away from some korean domain squatter.
The fact that some of those aren't currently resolving nicely (eg,
www.postgres.org gets you a Horde login page) is our own darn fault.
As Dave Page was mentioning, some additional hands in the webpage
maintenance effort would be great.
regards, tom lane
Rob Butler <robert.butler5@verizon.net> writes: > Hmm, very odd. I just went out to: http://gborg.postgresql.org/project/gborg/projdisplay.php today by following the linkfrom the postgres homepage. On the right side the oldest news story is all the way back to 2000. It looks like the links embedded in those news stories still point at www.greatbridge.org, which domain apparently has lapsed and been snatched up by a Hong Kong domain squatter :-(. I dunno whether it's still appropriate to keep three-year-old news on the project page, but if it is, updating the links to point at gborg.postgresql.org would be good ... regards, tom lane
On Wed, 2003-04-16 at 13:29, Tom Lane wrote: > Robert Treat <xzilla@users.sourceforge.net> writes: > > Oddly I don't see the link he's referring to, but I've always thought it > > unfortunate that the postgresql community didn't hold onto those domain > > names. > > We were not offered the chance; Landmark wanted to hold onto the Great > Bridge trademark, apparently. We did come away with the postgres.com, > postgres.org, and postgresql.com domains, which GB had managed to wrestle > away from some korean domain squatter. > Sorry. I knew that but I guess my statement implied different. > The fact that some of those aren't currently resolving nicely (eg, > www.postgres.org gets you a Horde login page) is our own darn fault. > As Dave Page was mentioning, some additional hands in the webpage > maintenance effort would be great. > Well, that's more of a DNS issue than a webpage, and afaik Marc is the only one who can make that change. If he's willing to change it now by all means let's do so. If he's too busy but willing to give someone else access to do it I'm sure we can dig someone up (like me) Robert Treat
Glad to see all the comments about the web page stuff, and see that people recognize the need for a cohesive look and feel for Postgres.org. Do people have any comments about the rest of the stuff in my post? Like, are the JDBC developers aware that you will be changing the FE/BE protocol? Do they know you will (possibly) be adding 2PC? Any communication going on between core and postgres-r developers to make sure that replication and 2PC will work together simultaneously as I described in my earlier e-mail? Any comments about the section on competition? Do people agree / disagree that postgres should not be competing with MySQL (because it's no real competition) or does everyone think MySQL is a real threat? Do people agree / disagree that the real competition is commercial DB's and Firebird / SAP db? Later Rob
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Any comments about the section on competition? Do people agree / disagree > that postgres should not be competing with MySQL (because it's no real > competition) or does everyone think MySQL is a real threat? Do people > agree / disagree that the real competition is commercial DB's and Firebird > / SAP db? There has been plenty of discussion on the advocacy list about this: it is a much better place for this sort of talk than hackers anyway. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200304161743 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+nc6+vJuQZxSWSsgRAmt/AJwKpkYWubiF+fz0mgZoXSIaMBpASACcDgjl 9Ks9DmXP+SjkzHA7DIMF0q8= =qmPX -----END PGP SIGNATURE-----
"Rob Butler" <robert.butler5@verizon.net> writes:
> Like, are the JDBC developers aware that you will be changing the FE/BE
> protocol?
The ones who have been participating in the discussion are ;-)
> Do they know you will (possibly) be adding 2PC?
The odds of that appearing in 7.4 are nil, IMHO.
regards, tom lane
> -----Original Message----- > From: Rob Butler [mailto:robert.butler5@verizon.net] > Sent: 16 April 2003 17:48 > To: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Many comments (related to "Are we > losing momentum?") > > > > > > Oddly I don't see the link he's referring to, but I've > always thought > > it unfortunate that the postgresql community didn't hold onto those > > domain names. > > Hmm, very odd. I just went out to: > http://gborg.postgresql.org/project/gborg/projdisplay.php > today by following the link from the postgres homepage. On > the right side the oldest news story is all the way back to 2000. That news is relevant to the Gborg project itself - i.e. the codebase that drives gborg.postgresql.org. Each project on Gborg can maintain it's own news, and it's down to the project members to do so, not us. Regards, Dave.
I am not a hacker of PgSQL, and new to Databases. I was using MySQL under .NET, but was annoyed by their agressive licence agreements and immaturity. (Their sales personel are also very rude. One girl once told me that if I didn't like their licence terms I should just use flat-files instead.) One of the .NET developers for MySQL advised me to look at PostgreSQL, and I have never looked back. This was not the fist time I looked at PostgreSQL, I initially looked at 30+ Databases, but rejected PostgreSQL off hand. There was no Windows version. Speaking stricktly as an ameture, I would like to make a few comments I could not see mensioned in this thread. This is not a dig, more a wish list! Because: With the expanding industry and popularity of IT as part of unrelated collage courses (Engineering, Accountancy etc), there are lots of ametures and newbe's. We will never be first class hackers, we will probably never get to the end of the manuals or understand what P2C / FE/BE is or where it's used. But we are the silent magority. (I am personally extreamly dyslexic. I learn from example, talking and brief painful trips into the documentation archives.) Therefore we learn as much as we need to know. In time I am sure we all want to be guru's in everything. I have lots of ameture friends at this level, running ISP's, producing commercial applications, in unrelated research, needing office systems... All needing a DB. Most using MS-SQL or MySQL. To draw on a popular examples, MySQL helps the ameture: (I'm putting my foot in it that some of this probably exists. I just haven't found it yet.) - A true Windows version which people can learn their craft on. - Tools which look like Access, to do row level data editing with no SQL. - Easy to use and remember command extensions, like 'CREATE IF NOT EXISTS', 'DROP IF EXISTS' which are universal. - Centrally located complete documentation in many consistent easy to read formats, of the system and *ALL* API's, including in-line tutorials and examples. - Data types like 'ENUM' which appeal to ametures. - There are no administrative mandatorys. Eg, VACUUM. (A stand-alone commercial app, like an Email client, will be contrainted by having to be an app and a DBA in one.) - The tables (not innodb) are in different files of the same name. Allowing the OS adminitrator great ability. EG, putting tables on separate partitions and therefore greatly speeding performance. - They have extensive backup support. Including now, concurrent backup without user interuption or risk of inconsistency. Now I have begun to climb the ladder a bit, I know this it of little importance compared to working referential constraints, triggers, procedures and transactions... You also have the excelent mailing list 'novice', with excelent support for Ametures, with the most friendly welcome note: 'No problem too minor'! Thanks to you all for providing the system I am now beginning to enjoy using. PS: I like the '\dt'. Especially the way it can be used half way through a true statement, inspired bit of genious there. Ben
Ben Clewett <B.Clewett@roadrunner.uk.com> writes:
> - The tables (not innodb) are in different files of the same name.
> Allowing the OS adminitrator great ability. EG, putting tables on
> separate partitions and therefore greatly speeding performance.
FWIW, we used to do it that way too, many releases ago. We gave it up
because it was impossible to support rollback of table deletion/rename
with that storage rule underneath. Consider
BEGIN; DROP TABLE a; CREATE TABLE a (with-some-other-schema); -- oops, think better of it ROLLBACK;
With table files named after the table, we could not support the above,
because we'd need two "a"'s in existence at the same time. Postgres'
catalog mechanisms can handle rollback of the catalog changes, but the
Unix filesystem never heard of rollback :-(
There are other reasons, which some folks have pointed out elsewhere in
this thread, but that was the killer one.
I notice that MySQL seems to be migrating in this direction as well...
regards, tom lane
Tom Lane wrote: > Ben Clewett <B.Clewett@roadrunner.uk.com> writes: > > - The tables (not innodb) are in different files of the same name. > > Allowing the OS adminitrator great ability. EG, putting tables on > > separate partitions and therefore greatly speeding performance. > > FWIW, we used to do it that way too, many releases ago. We gave it up > because it was impossible to support rollback of table deletion/rename > with that storage rule underneath. It occurs to me that we could make it possible to get some of the performance gains MySQL gets through its naming conventions by including the type of object in the path of the object. For instance, a table with relfilenode 52715 in database with relfilenode 46722 would have a path of $PGDATA/table/46722/52715, an index in the same database with OID 98632 would have a path of $PGDATA/index/46722/98632, etc. Then you could use symlinks to have tables, indexes, etc. point to various places on disk where they really need to live. Is that even remotely feasible? I looked into making the changes required but didn't see an obvious way to get a type string from an object's RelFileNode internally (once you have that, it's a matter of changing relpath() appropriately). -- Kevin Brown kevin@sysexperts.com
Kevin Brown <kevin@sysexperts.com> writes:
> It occurs to me that we could make it possible to get some of the
> performance gains MySQL gets through its naming conventions by
> including the type of object in the path of the object.
"Performance gains"? Name one.
We have been there and done that. I see no reason to go back.
regards, tom lane
Tom Lane wrote: > Kevin Brown <kevin@sysexperts.com> writes: > > It occurs to me that we could make it possible to get some of the > > performance gains MySQL gets through its naming conventions by > > including the type of object in the path of the object. > > "Performance gains"? Name one. Instead of tables and their indexes being on the same platter, you'd be able to put them on separate platters. Sounds like it would likely yield a performance gain to me... > We have been there and done that. I see no reason to go back. I'm not proposing that we return to calling the individual files (or the database they reside in) by name, only that we include a "type" identifier in the path so that objects of different types can be located on different spindles if the DBA so desires. As it is right now, tables and indexes are all stored in the same directory, and moving the indexes to a different spindle is an uncertain operation at best (you get to shut down the database in order to move any newly-created indexes, and dropping a moved index will not free the space occupied by the index as it'll only remove the symlink). All the current transactional operations (including things like table rename ops) will still be transactional, with the only difference being that instead of one directory (base/) to deal with you'd have several (one for each type of object, thus a base/<type>/ directory for each object type). Creating a database would mean creating a directory for the database in each of the type directories instead of just one in base/, and dropping it would mean removing said directories. It's not like we'd be losing anything by it: the only operations that you wouldn't necessarily be able to run in a transaction are the ones that you can't currently run in a transaction anyway, like CREATE DATABASE. But the benefit is that you can now safely put indexes on a different spindle than the data. That sounds like a net win to me. -- Kevin Brown kevin@sysexperts.com
Kevin Brown wrote:
> Tom Lane wrote:
> > Kevin Brown <kevin@sysexperts.com> writes:
> > > It occurs to me that we could make it possible to get some of the
> > > performance gains MySQL gets through its naming conventions by
> > > including the type of object in the path of the object.
> >
> > "Performance gains"? Name one.
>
> Instead of tables and their indexes being on the same platter, you'd
> be able to put them on separate platters. Sounds like it would likely
> yield a performance gain to me...
>
> > We have been there and done that. I see no reason to go back.
>
> I'm not proposing that we return to calling the individual files (or
> the database they reside in) by name, only that we include a "type"
> identifier in the path so that objects of different types can be
> located on different spindles if the DBA so desires. As it is right
> now, tables and indexes are all stored in the same directory, and
> moving the indexes to a different spindle is an uncertain operation at
> best (you get to shut down the database in order to move any
> newly-created indexes, and dropping a moved index will not free the
> space occupied by the index as it'll only remove the symlink).
The thing is, this isn't necessarily particularly useful in managing the
partitioning of data across disks.
If I have, defined, /disk1, /disk2, /disk3, /disk4, and /disk5, it is highly
unlikely that my partitioning will be based on the notion of "put indices on
disk1, tables on disk2, and, well, skip the others."
I'm liable to want WAL separate from all the others, for a start, but then
look for what to put on different disks based on selecting particular tables
and indices as candidates.
Your observation about the dropping of a moved index is well taken; that would
point to the idea that the top level "thing" containing each table/index
perhaps should be a directory, with two interesting properties:
- By being a directory, and putting files in it, this allows extensions to be
more clearly tied to the table/index when a file grows towards the
not-uncommon 2GB barrier;
- In order for the linking to physical devices to be kept under control,
particularly if an index gets dropped and recreated, the postmaster needs to
be able to establish the links, suggesting an extension to syntax. At first
blush:
CREATE INDEX FROBOZZ_IDX LOCATION '/disk1/pgindices' on FROBOZZ(ID);
Supposing the OID number was 234231, the postmaster would then create the
symbolic link from $PGDATA/base/234231 to the freshly-created directory
/disk1/pgindices/234231, where the index would reside. (And if the directory
exists, there should be some complaint :-).)
I have made that up out of whole cloth; it _doesn't_ take into consideration
how you would specify the location of implicitly-created indices.
But it seems a useful approach that can be robust, and where it's even
plausible that the postmaster could cope with a request to shift a table or
index to another location. (Which would, quite naturally, put a lock on
access to the object for the duration of the operation.)
--
output = reverse("gro.gultn@" "enworbbc")
http://www.ntlug.org/~cbbrowne/
"The dinosaurs died because they didn't have a space program."
-- Arthur C Clarke
Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> "Performance gains"? Name one.
> Instead of tables and their indexes being on the same platter, you'd
> be able to put them on separate platters. Sounds like it would likely
> yield a performance gain to me...
That has *nothing* to do with whether we name files after tables or not.
As Andrew pointed out, you don't really want people munging file
locations by hand anyway; until we have a proper tablespace
implementation, it's going to be tedious and error-prone no matter what.
> I'm not proposing that we return to calling the individual files (or
> the database they reside in) by name, only that we include a "type"
> identifier in the path so that objects of different types can be
> located on different spindles if the DBA so desires.
This has been proposed and rejected repeatedly in the tablespace
discussions. It's too limiting; and what's worse, it's not actually
any easier to implement than a proper tablespace facility. The
low-level I/O routines still need access to a piece of info they do
not have now. You may as well make it a tablespace identifier instead
of a file-type identifier.
The real point here is that "put the indexes on a different platter"
is policy. One should never confuse policy with mechanism, nor build a
mechanism that can only implement one policy.
regards, tom lane
Tom Lane wrote: > > I'm not proposing that we return to calling the individual files (or > > the database they reside in) by name, only that we include a "type" > > identifier in the path so that objects of different types can be > > located on different spindles if the DBA so desires. > > This has been proposed and rejected repeatedly in the tablespace > discussions. It's too limiting; and what's worse, it's not actually > any easier to implement than a proper tablespace facility. It's not? This is a little surprising, since the type information is already stored, is it not? A proper tablespace implementation requires the addition of commands to manage it and table infrastructure to store it. That seems like a bit more work than writing a function to translate an object ID into a type name (and changing CREATE/DROP DATABASE to deal with multiple directories). But since you're much more familiar with the internals, I'll take your word for it. I figured getting the type name of the object would be a relatively easy thing to do, obvious to anyone with any real familiarity with the source. Guess not... -- Kevin Brown kevin@sysexperts.com
Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> This has been proposed and rejected repeatedly in the tablespace
>> discussions. It's too limiting; and what's worse, it's not actually
>> any easier to implement than a proper tablespace facility.
> It's not? This is a little surprising, since the type information is
> already stored, is it not? A proper tablespace implementation
> requires the addition of commands to manage it and table
> infrastructure to store it.
Well, yeah, you do have to provide some user interface stuff ;-)
But the hard, dirty, dangerous stuff is all in the low-level internals
(bufmgr, smgr, etc). I don't want to put a kluge in there when the same
amount of work will support a non-kluge solution.
Also, you'd still have to provide some user interface stuff for the
kluge, so it's not like you can avoid doing any work at that level.
regards, tom lane
> > Instead of tables and their indexes being on the same platter, you'd > > be able to put them on separate platters. Sounds like it would likely > > yield a performance gain to me... > > That has *nothing* to do with whether we name files after tables or not. > As Andrew pointed out, you don't really want people munging file > locations by hand anyway; until we have a proper tablespace > implementation, it's going to be tedious and error-prone no matter what. Just so people are aware, I'm getting Jim Buttfuoco's tablespaces patch from him again, and getting it up to CVS. I'll then see what needs to be done to it such that it would be accepted... Chris
Great. I think it can be made acceptable with little work. --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > > > Instead of tables and their indexes being on the same platter, you'd > > > be able to put them on separate platters. Sounds like it would likely > > > yield a performance gain to me... > > > > That has *nothing* to do with whether we name files after tables or not. > > As Andrew pointed out, you don't really want people munging file > > locations by hand anyway; until we have a proper tablespace > > implementation, it's going to be tedious and error-prone no matter what. > > Just so people are aware, I'm getting Jim Buttfuoco's tablespaces patch > from him again, and getting it up to CVS. I'll then see what needs to be > done to it such that it would be accepted... > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Great. I think it can be made acceptable with little work.
IIRC, the reason Jim's patch got bounced was exactly that it offered an
implementation of only one policy, with no possibility of extension.
regards, tom lane
On Sat, 19 Apr 2003, Bruce Momjian wrote: > > Great. I think it can be made acceptable with little work. Just keep in mind my track record of finding things too hard ;) Jim has offered to help, he was just put off by some negativity... Chris > > Christopher Kings-Lynne wrote: > > > > Instead of tables and their indexes being on the same platter, you'd > > > > be able to put them on separate platters. Sounds like it would likely > > > > yield a performance gain to me... > > > > > > That has *nothing* to do with whether we name files after tables or not. > > > As Andrew pointed out, you don't really want people munging file > > > locations by hand anyway; until we have a proper tablespace > > > implementation, it's going to be tedious and error-prone no matter what. > > > > Just so people are aware, I'm getting Jim Buttfuoco's tablespaces patch > > from him again, and getting it up to CVS. I'll then see what needs to be > > done to it such that it would be accepted... > > > > Chris > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 >
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Great. I think it can be made acceptable with little work. > > IIRC, the reason Jim's patch got bounced was exactly that it offered an > implementation of only one policy, with no possibility of extension. I read all the comments regarding Jim's patch, but would you mind stating exactly what your concern is, Tom? What do you mean by 'one policy'? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I read all the comments regarding Jim's patch, but would you mind stating
> exactly what your concern is, Tom? What do you mean by 'one policy'?
I don't want something that will only support a policy of "put the
indexes over there". It should be possible to assign individual tables
or indexes to particular tablespaces if the DBA wants to do that.
I have nothing against making it easy to "put the indexes over there"
--- for example, we might say that a database has a default tablespace
for each kind of object. But if the mechanism can only support a
per-object-kind determination of tablespace then it's insufficiently
flexible.
I no longer recall any details about Jim's patch, but I believe we felt
that it failed the flexibility criterion.
regards, tom lane
Christopher Kings-Lynne wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Great. I think it can be made acceptable with little work. > > > > IIRC, the reason Jim's patch got bounced was exactly that it offered an > > implementation of only one policy, with no possibility of extension. > > I read all the comments regarding Jim's patch, but would you mind stating > exactly what your concern is, Tom? What do you mean by 'one policy'? As I remember, the patch only put indexes in one place, and tables in another place. We need a general tablespace solution where we can create tablespaces and put tables/indexes in any of those. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073