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. Sorry in 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 Postgres would get the most bang for the buck by supporting 2PC and eager (postgres-r) asap. Add lazy replication when you can later. (Besides 2PC, and postgres-r seem to be closer to completion and integration with the main postgres development tree)
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 perform some operations on a pair of queues as a single transaction. In order for this to be possible we need both replication and 2PC, and they have to work together! In this situation 2PC and replication have to be developed with knowledge of 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 database supports the feature. However, from what I last saw on the JDBC list archives it doesn't appear as if the JDBC driver developers know you guys are changing the FE/BE protocol and may soon be adding 2PC. I think the JDBC developers should be made aware of this asap... If all of a sudden the JDBC driver doesn't work with the newest version of Postgres there is going to be some upset people! You should also ask the JDBC driver developers to take part in the discussions of the new FE/BE protocol (if they aren't already). 2PC / XA resources in the JDBC driver will provide a very large 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 windows developer is concerned. That cost is the time and effort required to learn some flavor of UN*X in order to deploy the database. That is a very significant investment in time ($) to learn and money ($) for books. (Yes you can learn everything 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 Postgres and that will have a very large effect on the "mind share" postgres has. I would bet one of the reasons MySQL is so 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" environment as 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 money on 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 Windows in 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 option for an open source DB on Windows was MySQL. (Now you have Firebird, SAPDB, and probably others). So yes in some aspect you are competing with MySQL here... But why compete, just make the best product you can and let the user decide which one 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 know does 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 this will 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 than these 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, so they 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-master soon. You can already do multi-master replication in MySQL. (Yes we all know that what they have for replication is not as robust, especially if you use it in multi-master (circular replication a->b->c->a) but at least they have something. Lets not waste our time discussing what they have.. Lets instead spend that time building an even better 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? See earlier 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 building block 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 And that is true only for people already familiar with UN*X. Dont get me wrong here folks I love Postgres.. Its just time for us to take a step back and look at the facts, and the facts are Postgres needs even more to be truly competitive where it should be competing. (Not competing with MySQL.) I think many of the features Postgres needs to be a better 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 is obvious because of the numerous open source projects in and for java. Jakarta.apache.org and all the frameworks on it like log4j, Struts, etc. Free open source J2EE app servers like Jboss, Jonas, etc.. However, the JDBC driver for Postgres is HARD TO FIND! Its hard to find because someone new to postgres cant find anything about it on the Postgresql.org home page. There is no link to it, and no mention of it. Granted the JDBC driver can be found by searching google, 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 many different 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 just something 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 takes you to another site with a completely different look and feel. The side effect of this is that the Postgres project looks as if it is un-organized. Even more needs to be done in this area. The postgres site should be more cohesive with a single look and feel for as much of the site and its links as possible. Look at apache.org. They have MANY projects going on, but have a singular (or at least very similar) look and feel across most of their projects. 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. Whats 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! 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 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, going back to the Java thing, the link for PostgreSQL JDBC 2.0 API compliance on the JDBC homepage goes to a dead link and has 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 to catch 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.
Import Notes
Resolved by subject fallback
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 link from 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 running the new version. Version 2.0.0 is just the latest enhancements for GreatBridge.org and includes many changes based on 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...)
Import Notes
Resolved by subject fallback
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 link from 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.
Import Notes
Resolved by subject fallback
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
Hi Ben
-----Original Message-----
From: Ben Clewett [mailto:B.Clewett@roadrunner.uk.com]
Sent: 17 April 2003 10:45
Cc: pgsql-hackers@postgresql.org
Subject: [HACKERS] For the ametures. (related to "Are we
losing momentum?")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.)
Probably more powerful ;-)
- A true Windows version which people can learn their craft on.
Coming with 7.4...
- Tools which look like Access, to do row level data
editing with no SQL.
It looks more like SQL Server's Enterprise Manager but does most if not
all of what I expect you need.
- Centrally located complete documentation in many
consistent easy to
read formats, of the system and *ALL* API's, including
in-line tutorials
and examples.
The tarball includes the complete documentation in HTML format, and in
pgAdmin there's a searchable copy in the main chm help file.
- Data types like 'ENUM' which appeal to ametures.
Isn't that just syntactic sugar for a column with a check for specific
values on it?
- 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.)
PostgreSQL is by no means alone in this requirement. SQL Server for
example has 'optimizations' that are performed usually as part of a
scheduled maintenance plan and are analagous to vacuum in some ways.
- 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.
One reason for not doing this is that a table in PostgreSQL might span
mutiple files if it exceeds a couple of gigs in size.
- They have extensive backup support. Including now,
concurrent backup
without user interuption or risk of inconsistency.
So does PostgreSQL (pg_dump/pg_dumpall).
Regards, Dave
PS, it's nice you decided not to go to the Dark Side :-)
Import Notes
Resolved by subject fallback
Hi Dave,
A brief defence of my posting. After which I'll retire to my side of
the fence :)
- A true Windows version which people can learn their craft on.
Coming with 7.4...
I look forward to this greatly. Maybe here I'll have the chance to fix
some problems for the greater community.
- Tools which look like Access, to do row level data
editing with no SQL.It looks more like SQL Server's Enterprise Manager but does most if not
all of what I expect you need.
Sorry, my fault, an excellent program.
The tarball includes the complete documentation in HTML format, and in
pgAdmin there's a searchable copy in the main chm help file.
But not the API's. Not in one central location. Some of it, the stuff
I use, is on GBorg, and in inconsistent format. I have personally found
some documentation very fragmented. So a subtle point about an ability
is lost as I have assumed all comments to be in a few pages, and missed
something vital or relevent in another sourse. Eg, see my comment at
the end. But it's better than msdn :)
- Data types like 'ENUM' which appeal to ametures.
Isn't that just syntactic sugar for a column with a check for specific
values on it?
Yes :) By point is not that PostgreSQL is lacking, only that the
ameture finds others more friendly and inviting.
Although this may be a point which is irrelevent?
My personal 'gripe' was when reading through the postings, some people
considered people who have not the time, patience or ability, to learn
PostgreSQL completelly, somehow not worthy.
I wanted to support us dumb users! :)
- 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.)PostgreSQL is by no means alone in this requirement. SQL Server for
example has 'optimizations' that are performed usually as part of a
scheduled maintenance plan and are analagous to vacuum in some ways.
Is this a weekness in DBMS's that don't require this? (MySQL, Liant
etc.) Is there a way of building a guarbage collector into the system?
My Windows PC has no 'cron'.
- 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.One reason for not doing this is that a table in PostgreSQL might span
mutiple files if it exceeds a couple of gigs in size.
They used multile files for tables, with a common pefix of the table
name. But they have dropped this them selves now.
I miss the way with MySQL I could delete a table, or move it, or back it
up, manually using 'rm', 'mv' or 'cp'.
Working with IDE drives on PC's, you can double the performace of a DB
just by putting half the tables on a disk on another IDE chain. Adding
a DB using 'tar' is very a powerful ability.
But hay, if I missed it that much, I would not have moved! :)
- They have extensive backup support. Including now,
concurrent backup
without user interuption or risk of inconsistency.So does PostgreSQL (pg_dump/pg_dumpall).
I have used this, and it's a great command.
I could not work out from the documentation whether it takes a snapshot
at the start time, or archives data at the time it find's it. The
documentation (app-pg-dump.html). As the documentation does not clarify
this very important point, I desided it's not safe to use when the
system is in use.
Can this command can be used, with users in the system making heavy
changes, and when takes many hours to complete, does produce a valid and
consistent backup?
If so, you have all MySQL has here and in a more useful format.
PS, it's nice you decided not to go to the Dark Side :-)
Thanks, Ben
On Thursday 17 April 2003 13:44, Ben Clewett wrote:
Hi Dave,
A brief defence of my posting. After which I'll retire to my side of
the fence :)
(snip)
- Data types like 'ENUM' which appeal to ametures.
Isn't that just syntactic sugar for a column with a check for specific
values on it?Yes :) By point is not that PostgreSQL is lacking, only that the
ameture finds others more friendly and inviting.Although this may be a point which is irrelevent?
Probably ;-) because MySQL too lacks a few "user friendly" features
(like boolean datatypes).
Ian Barwick
barwick@gmx.net
(please note that the word is "amateur" - it comes from French/Latin,
meaning people who do things for the love of it).
There are lots of cron clones for Windows - try a Google search.
Also, there is a native Windows port of Pg 7.2.1 available - we have been
using it for a couple of months now on a small project without a single
hitch. Search this mailing list for details.
Like you, we eagerly await the official Windows port in 7.4. (then we'll
have shema, for example).
Part of the problem that amateurs often face in dealing with things like a
DBMS is that their lack of formal training leads them to expect things to
work in some intuitive fashion, and they don't (for very good technical
reasons). As someone who in a past life had to teach relational theory and
practice, I can tell you that just getting across the idea of a Cartesian
product can be quite hard. And as a former DBA I can tell you that even
seasoned professional developers often don't/can't take the trouble to
analyse what their queries are doing and why they demand so much in
resources. Running a DBMS (*any* DBMS) which has significant requirements is
unfortunately something that requires both understanding and experience. It
never "just works".
Finally, one of the important things for my particular situation, is that Pg
comes with a BSDish license, which means we have no issues with bundling it.
AFAIK it's pretty much alone in that.
andrew
----- Original Message -----
From: "Ben Clewett" <B.Clewett@roadrunner.uk.com>
To: "Dave Page" <dpage@vale-housing.co.uk>; <pgsql-hackers@postgresql.org>
Sent: Thursday, April 17, 2003 7:44 AM
Subject: Re: [HACKERS] For the ametures. (related to "Are we losing
momentum?")
Show quoted text
Hi Dave,
A brief defence of my posting. After which I'll retire to my side of
the fence :)- A true Windows version which people can learn their craft on.
Coming with 7.4...
I look forward to this greatly. Maybe here I'll have the chance to fix
some problems for the greater community.- Tools which look like Access, to do row level data
editing with no SQL.It looks more like SQL Server's Enterprise Manager but does most if not
all of what I expect you need.Sorry, my fault, an excellent program.
The tarball includes the complete documentation in HTML format, and in
pgAdmin there's a searchable copy in the main chm help file.But not the API's. Not in one central location. Some of it, the stuff
I use, is on GBorg, and in inconsistent format. I have personally found
some documentation very fragmented. So a subtle point about an ability
is lost as I have assumed all comments to be in a few pages, and missed
something vital or relevent in another sourse. Eg, see my comment at
the end. But it's better than msdn :)- Data types like 'ENUM' which appeal to ametures.
Isn't that just syntactic sugar for a column with a check for specific
values on it?Yes :) By point is not that PostgreSQL is lacking, only that the
ameture finds others more friendly and inviting.Although this may be a point which is irrelevent?
My personal 'gripe' was when reading through the postings, some people
considered people who have not the time, patience or ability, to learn
PostgreSQL completelly, somehow not worthy.I wanted to support us dumb users! :)
- 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.)PostgreSQL is by no means alone in this requirement. SQL Server for
example has 'optimizations' that are performed usually as part of a
scheduled maintenance plan and are analagous to vacuum in some ways.Is this a weekness in DBMS's that don't require this? (MySQL, Liant
etc.) Is there a way of building a guarbage collector into the system?
My Windows PC has no 'cron'.- 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.One reason for not doing this is that a table in PostgreSQL might span
mutiple files if it exceeds a couple of gigs in size.They used multile files for tables, with a common pefix of the table
name. But they have dropped this them selves now.I miss the way with MySQL I could delete a table, or move it, or back it
up, manually using 'rm', 'mv' or 'cp'.Working with IDE drives on PC's, you can double the performace of a DB
just by putting half the tables on a disk on another IDE chain. Adding
a DB using 'tar' is very a powerful ability.But hay, if I missed it that much, I would not have moved! :)
- They have extensive backup support. Including now,
concurrent backup
without user interuption or risk of inconsistency.So does PostgreSQL (pg_dump/pg_dumpall).
I have used this, and it's a great command.
I could not work out from the documentation whether it takes a snapshot
at the start time, or archives data at the time it find's it. The
documentation (app-pg-dump.html). As the documentation does not clarify
this very important point, I desided it's not safe to use when the
system is in use.Can this command can be used, with users in the system making heavy
changes, and when takes many hours to complete, does produce a valid and
consistent backup?If so, you have all MySQL has here and in a more useful format.
PS, it's nice you decided not to go to the Dark Side :-)
Thanks, Ben
---------------------------(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
-----Original Message-----
From: Ben Clewett [mailto:B.Clewett@roadrunner.uk.com]
Sent: 17 April 2003 12:44
To: Dave Page; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] For the ametures. (related to "Are we
losing momentum?")But not the API's. Not in one central location. Some of it,
the stuff
I use, is on GBorg,
The API's that ship with the source (libpq et al.) are all documented in
the HTML docs that ship with the code as far as I'm aware.
I suspect what you look at on Gborg will be one or more of psqlODBC,
Npgsql or libpqxx? These are seperate projects, and hence have their own
documentation. I don't know about libpqxx, but the psqlODBC docs are
very old I admit. If anyone cares to work on them, please let me know.
The Npgsql stuff is all very new and very alpha and I guess of all the
Npgsql hackers, I'm probably the only one who hangs around here - that's
how seperate the project is from PostgreSQL itself.
What we could probably use is a page on the main website highlighting
all the programming interfaces - similar to
http://www.postgresql.org/users-lounge/interfaces.html but a bit more
prominent and focused. I'll put my third hat on now and repeat - if
anyone cares to work on this, please let me know :-)
My personal 'gripe' was when reading through the postings,
some people
considered people who have not the time, patience or ability,
to learn
PostgreSQL completelly, somehow not worthy.I wanted to support us dumb users! :)
That's certainly not the case for many of the people here, though you
must remember, the vast majority of us work voluntarily and prefer to
help users who have made an effort to help themselves first rather than
those who expect us to do everything for them for free. Thankfully those
people are few and far between, but they do crop up from time to time.
PostgreSQL is by no means alone in this requirement. SQL Server for
example has 'optimizations' that are performed usually as part of a
scheduled maintenance plan and are analagous to vacuum in some ways.Is this a weekness in DBMS's that don't require this? (MySQL, Liant
etc.) Is there a way of building a guarbage collector into
the system?
Potentially I guess, if they are cleaning up and trying to reuse space
on the fly then they could suffer a performance hit.
My Windows PC has no 'cron'.
No, but it probably has a Scheduled Tasks folder unless it's a really
old version.
Can this command can be used, with users in the system making heavy
changes, and when takes many hours to complete, does produce
a valid and
consistent backup?
Yes, pg_dump will give you a consistent backup - this is from section
9.1 of the Administrators Guide in the Backup and Restore section:
Dumps created by pg_dump are internally consistent, that is, updates to
the database while pg_dump is running will not be in the dump. pg_dump
does not block other operations on the database while it is working.
(Exceptions are those operations that need to operate with an exclusive
lock, such as VACUUM FULL.)
Regards, Dave.
Import Notes
Resolved by subject fallback
On Thu, Apr 17, 2003 at 11:44:07AM +0000, Ben Clewett wrote:
I miss the way with MySQL I could delete a table, or move it, or back it
up, manually using 'rm', 'mv' or 'cp'.
Under most circumstances, you can't do that _anyway_, because doing
so will break stuff unless the postmaster is stopped. I agree that
bing able to put tables and files on their own platters would be a
Good Thing, but in order to make it really safe, it needs to be
managed by the postmaster. Making this difficult is sort of a
defence mechanism, therefore: if you make it too easy, people will be
shooting themselves in the foot all the time.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110Ben 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
Dave Page wrote:
- They have extensive backup support. Including now,
concurrent backup
without user interuption or risk of inconsistency.So does PostgreSQL (pg_dump/pg_dumpall).
I have applied the following doc patch to the pg_dump documentation to
more clearly state that it can do consistent backups during concurrent
access --- too many people weren't seeing that capability.
--
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 19073Attachments:
/bjm/difftext/plainDownload
Index: pg_dump.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.60
diff -c -c -r1.60 pg_dump.sgml
*** pg_dump.sgml 25 Mar 2003 16:15:42 -0000 1.60
--- pg_dump.sgml 17 Apr 2003 15:34:16 -0000
***************
*** 33,74 ****
</title>
<para>
! <application>pg_dump</application> is a utility for saving a
! <productname>PostgreSQL</productname> database into a script or an
! archive file. The script files are in plain-text format and
! contain the SQL commands required to reconstruct the database to
! the state it was in at the time it was saved. To restore these
! scripts, use <xref linkend="app-psql">. They can be used to
! reconstruct the database even on other machines and other
! architectures, with some modifications even on other SQL database
! products.
</para>
<para>
! Furthermore, there are alternative archive file formats
! that are meant to be used with <xref linkend="app-pgrestore"> to
! rebuild the database, and they also allow
! <application>pg_restore</application> to be selective about what is
! restored, or even to reorder the items prior to being restored. The
! archive files are also designed to be portable across
architectures.
</para>
<para>
When used with one of the archive file formats and combined with
! <application>pg_restore</application>, <application>pg_dump</application> provides a
! flexible archival and
transfer mechanism. <application>pg_dump</application> can be used to
! backup an entire database, then <application>pg_restore</application> can
! be used to examine the archive and/or select which parts of the
! database are to be restored.
! The most flexible output file format is the <quote>custom</quote>
! format (<option>-Fc</option>). It allows for selection and
! reordering of all archived items, and is compressed by default. The
! <application>tar</application> format (<option>-Ft</option>) is not
! compressed and it is not possible to reorder data when loading, but
! it is otherwise quite flexible; moreover, it can be manipulated with
! other tools such as <command>tar</command>.
</para>
<para>
--- 33,79 ----
</title>
<para>
! <application>pg_dump</application> is a utility for backing up a
! <productname>PostgreSQL</productname> database. It makes consistent
! backups even if the database is being used concurrently.
! <application>pg_dump</application> does not block other users
! accessing the database (readers or writers).
</para>
<para>
! Dumps can be output in script or archive file formats. The script
! files are in plain-text format and contain the SQL commands required
! to reconstruct the database to the state it was in at the time it was
! saved. To restore these scripts, use <xref linkend="app-psql">. They
! can be used to reconstruct the database even on other machines and
! other architectures, with some modifications even on other SQL
! database products.
! </para>
!
! <para>
! The alternative archive file formats that are meant to be used with
! <xref linkend="app-pgrestore"> to rebuild the database, and they also
! allow <application>pg_restore</application> to be selective about
! what is restored, or even to reorder the items prior to being
! restored. The archive files are also designed to be portable across
architectures.
</para>
<para>
When used with one of the archive file formats and combined with
! <application>pg_restore</application>,
! <application>pg_dump</application> provides a flexible archival and
transfer mechanism. <application>pg_dump</application> can be used to
! backup an entire database, then <application>pg_restore</application>
! can be used to examine the archive and/or select which parts of the
! database are to be restored. The most flexible output file format is
! the <quote>custom</quote> format (<option>-Fc</option>). It allows
! for selection and reordering of all archived items, and is compressed
! by default. The <application>tar</application> format
! (<option>-Ft</option>) is not compressed and it is not possible to
! reorder data when loading, but it is otherwise quite flexible;
! moreover, it can be manipulated with other tools such as
! <command>tar</command>.
</para>
<para>
***************
*** 77,88 ****
light of the limitations listed below.
</para>
- <para>
- <application>pg_dump</application> makes consistent backups even if the
- database is being used concurrently. <application>pg_dump</application>
- does not block other users accessing the database (readers or
- writers).
- </para>
</refsect1>
<refsect1 id="pg-dump-options">
--- 82,87 ----
Ben Clewett <B.Clewett@roadrunner.uk.com> writes:
So does PostgreSQL (pg_dump/pg_dumpall).
I have used this, and it's a great command.
I could not work out from the documentation whether it takes a
snapshot at the start time, or archives data at the time it find's it.
The documentation (app-pg-dump.html). As the documentation does not
clarify this very important point, I desided it's not safe to use when
the system is in use.
Ummm, quoting from the pg_dump manpage:
pg_dump makes consistent backups even if the database is
being used concurrently. pg_dump does not block other
users accessing the database (readers or writers).
What part of this isn't clear?
It's safe. pg_dump does all its work inside a transaction, so MVCC
rules automatically guarantee that it sees a consistent snapshot.
Can this command can be used, with users in the system making heavy
changes, and when takes many hours to complete, does produce a valid
and consistent backup?
Absolutely.
If so, you have all MySQL has here and in a more useful format.
I think MySQL's consistent hot backup has to lock tables, while PG's
doesn't...
-Doug
Import Notes
Reply to msg id not found: BenClewettsmessageofThu17Apr2003114407+0000
Doug McNaught wrote:
Ben Clewett <B.Clewett@roadrunner.uk.com> writes:
So does PostgreSQL (pg_dump/pg_dumpall).
I have used this, and it's a great command.
I could not work out from the documentation whether it takes a
snapshot at the start time, or archives data at the time it find's it.
The documentation (app-pg-dump.html). As the documentation does not
clarify this very important point, I desided it's not safe to use when
the system is in use.Ummm, quoting from the pg_dump manpage:
pg_dump makes consistent backups even if the database is
being used concurrently. pg_dump does not block other
users accessing the database (readers or writers).What part of this isn't clear?
It's safe. pg_dump does all its work inside a transaction, so MVCC
rules automatically guarantee that it sees a consistent snapshot.
Too many people have missed that point --- it was too far down in the
manual page, after a long discussion about output formats. Now it is
right in the first paragraph, which should eliminate that question ---
it was almost becoming an FAQ.
--
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
On Thu, 17 Apr 2003, Dave Page wrote:
- Data types like 'ENUM' which appeal to ametures.
Isn't that just syntactic sugar for a column with a check for specific
values on it?
I believe it's actually different. In PostgreSQL you'd use a VARCHAR
column with CHECK constraints, which means there are actual possibly
lengthy strings in the database. In MySQL's ENUM, the table structure maps
a particular string to a bit pattern, so if you have two possible values,
'superdogfood' and 'onetwothreefourfivesixseven', your column will only
take 1 bit + overhead. Obviously no big deal until you get a few dozen
possibilities. This is also what allows the SET type to work -- it's a set
of binary flags for a named list of elements. The docs are here:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#ENUM
I don't like the fact that numbers don't really work (being used as
indices rather than names), that case isn't tolerated, that invalid
entries go in as empty strings, etc., so I certainly wouldn't want to see
them emulated exactly in PostgreSQL, but I imagine that ENUM could save a
lot of disk space in certain circumstances, and SET seems useful.
Jon
Ben Clewett writes:
I could not work out from the documentation whether it takes a snapshot
at the start time, or archives data at the time it find's it. The
documentation (app-pg-dump.html). As the documentation does not clarify
this very important point, I desided it's not safe to use when the
system is in use.Can this command can be used, with users in the system making heavy
changes, and when takes many hours to complete, does produce a valid and
consistent backup?
From the pg_dump reference page:
<para>
<application>pg_dump</application> makes consistent backups even if the
database is being used concurrently. <application>pg_dump</application>
does not block other users accessing the database (readers or
writers).
</para>
From the chapter Backup and Restore:
<para>
Dumps created by <application>pg_dump</> are internally consistent,
that is, updates to the database while <application>pg_dump</> is
running will not be in the dump. <application>pg_dump</> does not
block other operations on the database while it is working.
(Exceptions are those operations that need to operate with an
exclusive lock, such as <command>VACUUM FULL</command>.)
</para>
--
Peter Eisentraut peter_e@gmx.net
----- Original Message -----
From: "Ben Clewett" <B.Clewett@roadrunner.uk.com>
- 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.)PostgreSQL is by no means alone in this requirement. SQL Server for
example has 'optimizations' that are performed usually as part of a
scheduled maintenance plan and are analagous to vacuum in some ways.Is this a weekness in DBMS's that don't require this? (MySQL, Liant
etc.) Is there a way of building a guarbage collector into the system?
My Windows PC has no 'cron'.
Work is being done to build vacuum into the backend so that cron is not
required. Hopefully will be in 7.4
- 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.One reason for not doing this is that a table in PostgreSQL might span
mutiple files if it exceeds a couple of gigs in size.Working with IDE drives on PC's, you can double the performace of a DB
just by putting half the tables on a disk on another IDE chain. Adding
a DB using 'tar' is very a powerful ability.
You can do this using symlinks, but you do have to shut down the postmaster
before you play with the files directly.
- They have extensive backup support. Including now,
concurrent backup
without user interuption or risk of inconsistency.So does PostgreSQL (pg_dump/pg_dumpall).
I have used this, and it's a great command.
I could not work out from the documentation whether it takes a snapshot
at the start time, or archives data at the time it find's it. The
documentation (app-pg-dump.html). As the documentation does not clarify
this very important point, I desided it's not safe to use when the
system is in use.Can this command can be used, with users in the system making heavy
changes, and when takes many hours to complete, does produce a valid and
consistent backup?
Yes it takes a snapshot from when it starts dumping the database, so it's
consistent no matter how much activity is going on after you start pg_dump.
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, Pennsylvania 19073Bruce 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
Show quoted text
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 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, Pennsylvania 19073Christopher 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
On Thu, 17 Apr 2003, Dave Page wrote:
Hi Ben
- Data types like 'ENUM' which appeal to ametures.
Isn't that just syntactic sugar for a column with a check for specific
values on it?
Yes. But isn't serial just syntactic sugar for create sequence seqname;
then including that sequence in a default clause?
Personally, i could easily see a use for enum() covering the check in
constraint. MySQL users can use enum() at their leisure, postgresql users
can pick if up if they want to, and underneath it all is a check
constraint the user can see with /dt just like with serial.
- 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.)PostgreSQL is by no means alone in this requirement. SQL Server for
example has 'optimizations' that are performed usually as part of a
scheduled maintenance plan and are analagous to vacuum in some ways.
But at the same time, it's one of those gotchas that would be nice to get
rid of for people who just want a simple little database on their
workstation. It might be nice to have some kind of lazy auto vacuum
daemon installed by default and configured to run every hour or so
according to postgresql.conf.
- 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.One reason for not doing this is that a table in PostgreSQL might span
mutiple files if it exceeds a couple of gigs in size.
And let's face it, if we get tablespaces implemented, this kind of thing
goes awawy.
Matthew T. O'Connor wrote:
----- Original Message -----
From: "Ben Clewett" <B.Clewett@roadrunner.uk.com>
Working with IDE drives on PC's, you can double the performace of a DB
just by putting half the tables on a disk on another IDE chain.You can do this using symlinks, but you do have to shut down the postmaster
before you play with the files directly.
I was hoping this was the case. :)
From my data/base directory, I have a tree structure of numbered files
of no obvious structure. As well as some smaller directories, 'global',
'pg_xlog' and 'pg_clog'.
If I wanted to divide the postmaster read() calls evenly to files
located over several physical disks, how would you suggest distributing
the data-space? Would it be as simple as putting each child directory
in 'data/base' on a different physical disk in a round-robbin fasion
using symbolic links: Or is it more involved...
data/base/1 -> /dev/hda
data/base/2 -> /dev/hdb
data/base/3 -> /dev/hdc
data/base/4 -> /dev/hda
data/base/5 -> /dev/hdb
data/base/6 -> /dev/hdc (etc)
(I have made the assumption that the postmaster serves different
connections in parallel, otherwise this would have little effect :)
Thanks,
Ben
On Tuesday 22 April 2003 13:55, Ben Clewett wrote:
If I wanted to divide the postmaster read() calls evenly to files
located over several physical disks, how would you suggest distributing
the data-space? Would it be as simple as putting each child directory
in 'data/base' on a different physical disk in a round-robbin fasion
using symbolic links: Or is it more involved...data/base/1 -> /dev/hda
data/base/2 -> /dev/hdb
data/base/3 -> /dev/hdc
data/base/4 -> /dev/hda
data/base/5 -> /dev/hdb
data/base/6 -> /dev/hdc (etc)
Don't bother splitting across disks unless you put them on different IDE
channels as IDE channel bandwidth is shared.
If you have that many disk, put them on IDE RAID. That is a much simpler
solution.
Shridhar
On Tue, 2003-04-22 at 04:25, Ben Clewett wrote:
Matthew T. O'Connor wrote:
From my data/base directory, I have a tree structure of numbered files
of no obvious structure. As well as some smaller directories, 'global',
'pg_xlog' and 'pg_clog'.If I wanted to divide the postmaster read() calls evenly to files
located over several physical disks, how would you suggest distributing
the data-space? Would it be as simple as putting each child directory
in 'data/base' on a different physical disk in a round-robbin fasion
using symbolic links: Or is it more involved...data/base/1 -> /dev/hda
data/base/2 -> /dev/hdb
data/base/3 -> /dev/hdc
data/base/4 -> /dev/hda
data/base/5 -> /dev/hdb
data/base/6 -> /dev/hdc (etc)(I have made the assumption that the postmaster serves different
connections in parallel, otherwise this would have little effect :)
Yes connections are served in parallel. The best way to split the files
is something you have to figure out, probably based on usage. The round
robin directory method you mentioned above falls down in that it only
splits whole databases into different locations regardless of how much
I/O is related to those databases. You may wind up with inactive
databases on their own disk which would yield no performance gain. It's
also probably better to get down to the file / index level rather than
whole databases as you may have a few tables that get 90% of the work.
Hopefully some of that was helpful.
On Tuesday 22 April 2003 13:55, Ben Clewett wrote:
If I wanted to divide the postmaster read() calls evenly to files
located over several physical disks, how would you suggest
distributing the data-space? Would it be as simple as putting each
child directory in 'data/base' on a different physical disk in a
round-robbin fasion using symbolic links: Or is it more involved...data/base/1 -> /dev/hda
data/base/2 -> /dev/hdb
data/base/3 -> /dev/hdc
data/base/4 -> /dev/hda
data/base/5 -> /dev/hdb
data/base/6 -> /dev/hdc (etc)Don't bother splitting across disks unless you put them on different
IDE channels as IDE channel bandwidth is shared.
While that is electricaly "true" it is not completely true. Modern IDE hard
disks are very advanced with large read-ahead caches. That combined with
IDE-DMA access, low seek times, faster spin rates, means you can get
performance across two IDE drives on the same channel.
For instance, two databases, one on HDA and the other database on HDB.
Successive reads inteleaved HDA/HDB/HDA/HDB etc. will share electical
bandwidth (as would SCSI). AFAIK, there is no standard asynchronous command
structure for IDE, however, the internal read-ahead cache on each drive will
usually have a pretty good guess at the "next" block based on some
predictive caching algorithm.
So, the "next" read from the drive has a good chance at coming from cache.
Plus the OS may "scatter gather" larger requests into smaller successive
requests (so a pure "read-ahead" will work great). Then consider
write-caching (if you dare).
It is very true you want to have one IDE drive per IDE channel, but these
days two drives on a channel are not as bad as it once was. This is not due
to shared electrical bandwidth of the system (all bus systems suffer this)
but because of the electrical protocol to address the drives. ATA and EIDE
have made strides in this area.
If you have that many disk, put them on IDE RAID. That is a much
simpler solution.
A hardware RAID system is obviously an "easier" solution, and
www.infortrend.com makes a very cool system, but spreading multiple
databases across multiple IDE drives and controllers will probably provide
higher overall performance if you have additional IDE channels instead of
forcing all the I/O through one controller (IDE or SCSI) channel.
Pretty good PCI/EIDE-DMA controllers are cheap, $50~$100, and you can fit a
bunch of them into a server system. Provided your OS has a reentrent driver
model, it should be possible for PostgreSQL to be performing as many I/O
operations concurrently as you have drive controllers, where as with an
IDE->SCSI raid controller, you may still be limited to how good your
specific driver handles concurrency within one driver instance.
The "best" solution is one hardware raid per I/O channel per database, but
that is expensive. One IDE driver per IDE channel per database is the next
best thing. Two IDE drives per channel, one drive per database, is very
workable if you make sure that the more active databases are on separate
controllers.
Ben Clewett <B.Clewett@roadrunner.uk.com> writes:
If I wanted to divide the postmaster read() calls evenly to files
located over several physical disks, how would you suggest distributing
the data-space?
AFAIK, the single biggest win you can get in this dimension is to put
the WAL log ($PGDATA/pg_xlog/) on a separate spindle from everything
else. At least for write-intensive databases, that can buy you
something like 2x improvement for the price of one easy symlink.
After that, the conventional wisdom is to put indexes on a third spindle
(separate from base tables and from xlog). But the bookkeeping and
maintenance effort needed for that is really too high to make it worth
worrying about, IMHO :-(. Eventually we will have some kind of
tablespace feature to make it easy.
My recommendation at the moment would be: WAL on dedicated spindle,
everything else on the best RAID array you can set up. And buy as much
RAM as you can afford.
See past discussions in pgsql-performance for more info.
regards, tom lane
On Tuesday 22 April 2003 11:18, Tom Lane wrote:
My recommendation at the moment would be: WAL on dedicated spindle,
everything else on the best RAID array you can set up. And buy as much
RAM as you can afford.
Hmm. Is this safe? Let's assume for argument's sake that the RAID is 100%
reliable and that the local spindle will eventually fail. Can I lose data by
leaving WAL on the local spindle? Or are you suggesting two full RAID
systems?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner."D'Arcy J.M. Cain" <darcy@druid.net> writes:
Hmm. Is this safe? Let's assume for argument's sake that the RAID is 100%
reliable and that the local spindle will eventually fail. Can I lose data by
leaving WAL on the local spindle? Or are you suggesting two full RAID
systems?
A two-disk mirror for the WAL would be good, yes; it needs redundancy
just as much as the main storage. But the discussion was about
performance not reliability; the OP hadn't mentioned that he wanted any
redundant storage.
regards, tom lane
On Tuesday 22 April 2003 12:51, Tom Lane wrote:
A two-disk mirror for the WAL would be good, yes; it needs redundancy
just as much as the main storage. But the discussion was about
performance not reliability; the OP hadn't mentioned that he wanted any
redundant storage.
Ah. When you mentioned RAID I thought you were going after reliability. So I
am probably best in my environment to just leave everything on the RAID
device and let it do it's job as best that it can. It does seem to work
faster than using local storage, even SCSI.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.