Re: [GENERAL] replication in Postgres
Someone is working on extending the current system to allow read-only queries on a standby server [1], thus making it a hot standby, but this feature apparently won't be included until 8.4 [2]. My 2 cents... I would rather see someone working on true synchronous replication, rather than a readable hot-standby. Yeah, I know, different problems with different solutions. But,. for my money, a readable hot-standby doesn't give me added functionality, it's just a performance option. Whereas synchronous replication has much greater value from a the perspective of running a 24x7 business. I'm on the verge of dumping our commercial DB in favor of PostgreSQL, but this is one issue that is holding me back. Jeff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
Jeff Larsen escribió: Someone is working on extending the current system to allow read-only queries on a standby server [1], thus making it a hot standby, but this feature apparently won't be included until 8.4 [2]. My 2 cents... I would rather see someone working on true synchronous replication, rather than a readable hot-standby. Yeah, I know, different problems with different solutions. But,. for my money, a readable hot-standby doesn't give me added functionality, it's just a performance option. Whereas synchronous replication has much greater value from a the perspective of running a 24x7 business. I'm on the verge of dumping our commercial DB in favor of PostgreSQL, but this is one issue that is holding me back. Maybe you can persuade Markus Schiltknecht to let you have a peek at his Postgres-R project, http://www.postgres-r.org http://www.postgres-r.org/about/sponsoring -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC ¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo (Mafalda) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] replication in Postgres
Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. Postgres-r sounds very nice but moving our organisations data onto a system that it work in progress is very scary. You are already offloading your data to PostgreSQL which is a work in progress too ... -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ En el principio del tiempo era el desencanto. Y era la desolación. Y era grande el escándalo, y el destello de monitores y el crujir de teclas. (Sean los Pájaros Pulentios, Daniel Correa) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] replication in Postgres
On Mon, 2007-11-26 at 07:25 -0600, Jeff Larsen wrote: Someone is working on extending the current system to allow read-only queries on a standby server [1], thus making it a hot standby, but this feature apparently won't be included until 8.4 [2]. My 2 cents... I would rather see someone working on true synchronous replication, rather than a readable hot-standby. Yeah, I know, different problems with different solutions. But,. for my money Well, I'm looking for sponsors to allow me to work on synchronous replication, amongst other issues. It looks like its going to have to be user companies, rather than vendors that sponsor these things. I've got the plans, I just need the time to execute them. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
Alvaro Herrera, 26.11.2007 15:07: EnterpriseDB has no replication solution that I know of. Quote from http://www.enterprisedb.com/products/enterprisedb_replication.do EnterpriseDB Replication Server replicates data across the enterprise in near real time to meet a wide array of business challenges. Data can be replicated to or from heterogeneous EnterpriseDB, Oracle and PostgreSQL databases across distant geographies Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
Alvaro Herrera wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. Yeah, there is: http://www.enterprisedb.com/products/enterprisedb_replication.do Regards. Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] replication in Postgres
Alvaro Herrera wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. Yeah, there is: http://www.enterprisedb.com/products/enterprisedb_replication.do Yes, but I'd like something better than near real time as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Migrating from 32 to 64 bit
On Nov 24, 2007, at 6:18 PM, Laurent CARON wrote: Question: I'd like to know if it is possible (and wise) to just keep the /var/lib/postgres.. directories from the old 32Bit server to use on the 64Bit version. This is just as a personal interest since I can also just dump and restore the database in about 2.5 hrs. No, you must dump/reload. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
On Nov 26, 2007, at 10:14 AM, Jeff Larsen wrote: Yes, but I'd like something better than near real time as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. So you want synchronous replication. Search on that term in the archives for possible solutions (or lack thereof) in postgres. If you don't specify your requirements clearly, don't expect useful advice ;-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
Yes, but I'd like something better than near real time as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. So you want synchronous replication. Search on that term in the archives for possible solutions (or lack thereof) in postgres. If you don't specify your requirements clearly, don't expect useful advice ;-) I'm not looking for advice. My original contribution to this thread suggested a preferred course of future development. I know what my options are with the present version, but I see room for improvement. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
Jeff Larsen wrote: Alvaro Herrera wrote: Glyn Astill wrote: Yes, but I'd like something better than near real time as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. Well do what EnterpriseDB does :) use Slony. Which is free of course. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. Well in a proper asynchronous environment this is possible, e.g; if it gets successfully replicated it will commit on the slave. However synchronous is obviously the fool proof way to go about this as you won't get a commit until everyone commits. Now, if you really want to make your life cheap :) Use PostgreSQL + Slony on two nodes, then run a third node explicitly for use with drdbd which is synchronous block level replication. No license fees :) Sincerely, Joshua D. Drake Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Primary Key
Martijn van Oosterhout wrote: On Fri, Nov 23, 2007 at 09:33:13AM +, Peter Childs wrote: I tend to agree that primary keys should be single fields if they need to be referenced but should also be natural if at all possible. ie use car number plates rather than some serial int. Car number plates are unique over time? I didn't think so... It's worse than that. If we presume that the plate is a key to a vehicle, then we immediately run into problems as a vehicle can, over time, have several plates (lost, stolen, changed to vanity...) and a plate can belong, sequentially, to several vehicles (especially when vanity plates are transferred to new cars). And when you have your char(6) plate-number column, they run out of numbers and switch to 7-characters requiring changes to all tables that used the plate as a key. Or you realize that ABC123 could be ABC123-California, ABC123-Nevada or ABC123-New York (I'm assuming that AAA999 is a valid format in those states). Although I haven't seen it much, recently, semi-trucks used to regularly have with numerous plates - one for each state in which they operated. And some states such as Texas allow you to have the same amateur-radio plate number on multiple vehicles. I won't argue that there are no reasonable natural keys. But I have sure seen plenty of cases where what appeared to be a natural key was discovered, generally at a very inopportune time in the development process, to be not-so-natural after all. Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] replication in Postgres
[EMAIL PROTECTED] (Jeff Larsen) writes: Alvaro Herrera wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. Yeah, there is: http://www.enterprisedb.com/products/enterprisedb_replication.do Yes, but I'd like something better than near real time as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. I believe that what they are using is a version of Slony-I, which certainly falls into the near real time replication category. Historically, when people think they require something better than near-real-time, they frequently find that the something better turns out to be too expensive to live with. Near real time usually refers to the notion of asynchronous replication, where it is a little bit nondeterministic how far behind a replica may be. (Which is definitely the case for Slony-I.) Unfortunately, the only way to make things deterministic (or to get from near real time to *GUARANTEED* real time) is to jump to synchronous replication, which is not much different from 2PC (Two Phase Commit), and which is certain to be prohibitively expensive across a WAN. At this point, I tend to get visions of Tom Cruise telling Jack Nicholson, I want real time replication!, and getting the response: You can't HANDLE real time replication! -- (format nil [EMAIL PROTECTED] cbbrowne linuxfinances.info) http://cbbrowne.com/info/slony.html Any sufficiently complicated C or Fortran program contains an ad hoc informally-specified bug-ridden slow implementation of half of Common Lisp. -- Philip Greenspun ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Primary Key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 10:11:37 -0800 Steve Crawford [EMAIL PROTECTED] wrote: Although I haven't seen it much, recently, semi-trucks used to regularly have with numerous plates - one for each state in which they operated. And some states such as Texas allow you to have the same amateur-radio plate number on multiple vehicles. I won't argue that there are no reasonable natural keys. But I have sure seen plenty of cases where what appeared to be a natural key was discovered, generally at a very inopportune time in the development process, to be not-so-natural after all. In theory the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). Joshua D. Drake Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSw4xATb/zqfZUUQRAgNgAJ9y3duugBxPeXdnyyIGycZwRGLrowCePzqo v54FYcWDsMTnDcBzix+MvOM= =1leN -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Primary Key
--- On Mon, 11/26/07, Joshua D. Drake [EMAIL PROTECTED] wrote: In theory the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). So this is why the service stations always record my cars VIN number when I show up for oil changes. ;) Ofcourse, there is a whole industry built around auto theft where they restamp the stolen car with a differnt vin number. I wonder if these stolen cars end up with duplicated VIN numbers or if the VIN's they are given do not pass the the VIN check-sum (if such a think exists). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Primary Key
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Broersma Jr Sent: Monday, November 26, 2007 10:28 AM To: Joshua D. Drake Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Primary Key --- On Mon, 11/26/07, Joshua D. Drake [EMAIL PROTECTED] wrote: In theory the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). So this is why the service stations always record my cars VIN number when I show up for oil changes. ;) Ofcourse, there is a whole industry built around auto theft where they restamp the stolen car with a differnt vin number. I wonder if these stolen cars end up with duplicated VIN numbers or if the VIN's they are given do not pass the the VIN check-sum (if such a think exists). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly VIN encoding is covered here http://en.wikipedia.org/wiki/Vehicle_Identification_Number Looks like a poor choice for a primary key: too many confliciting, meaningful, evolving-over-time digits that can be mis-interepreted by your customers. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Primary Key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 10:28:03 -0800 (PST) Richard Broersma Jr [EMAIL PROTECTED] wrote: --- On Mon, 11/26/07, Joshua D. Drake [EMAIL PROTECTED] wrote: In theory the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). So this is why the service stations always record my cars VIN number when I show up for oil changes. ;) Ofcourse, there is a whole industry built around auto theft where they restamp the stolen car with a differnt vin number. I wonder if these stolen cars end up with duplicated VIN numbers or if the VIN's they are given do not pass the the VIN check-sum (if such a think exists). They may end up with duplicate VINs but that isn't really relevant as at that point we know that one of the two (or six or whatever) cars are invalid, thus the natural key is still valid and designed to alert us if there is any potential problem :) Sincerely, Joshua D. Drake Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSxTtATb/zqfZUUQRAoDbAJ9h+NcHeyj7b1dmFAXl5uSWUbHzJwCfVOSW vBFs5lpCXrgeCwOu0wud9S8= =9AfY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
--- Alvaro Herrera [EMAIL PROTECTED] wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. This is bullsh*t, it does as I've been talking to them this week. Postgres-r sounds very nice but moving our organisations data onto a system that it work in progress is very scary. You are already offloading your data to PostgreSQL which is a work in progress too ... Except Postgress has stable releases and is proven and used in tons of businesses. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ En el principio del tiempo era el desencanto. Y era la desolación. Y era grande el escándalo, y el destello de monitores y el crujir de teclas. (Sean los Pájaros Pulentios, Daniel Correa) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication in Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 18:57:19 + (GMT) Glyn Astill [EMAIL PROTECTED] wrote: --- Alvaro Herrera [EMAIL PROTECTED] wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. This is bullsh*t, it does as I've been talking to them this week. Glyn, relax.. he did say, that I know of. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSxfvATb/zqfZUUQRAiCdAJ993n1hJgnxbmH1ewNmzBA9c+/4fACfZnu7 QR4D3O7EZd1N8GSqHf8SgDA= =K/76 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] replication in Postgres
It it possible to get a system that does syncronous replication and also allows slaves to catch up if they're down for a period of time like you can with asyncronous? I'm just interested. Of course a grid or a clustwer is better to makesure all servers are in sync, but there's performance issues with the 2 phase commit isn't there? Just for the record I'm a programmer, not a database person really, so I only know the basics. --- Jeff Larsen [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. Yeah, there is: http://www.enterprisedb.com/products/enterprisedb_replication.do Yes, but I'd like something better than near real time as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Glyn Astill __ Yahoo! Mail now has unlimited storage, which means you can have spam control and more space for those important e-mails. http://uk.mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication in Postgres
Glyn Astill escribió: It it possible to get a system that does syncronous replication and also allows slaves to catch up if they're down for a period of time like you can with asyncronous? Guess what, Postgres-R is designed to do that. Just for the record I'm a programmer, not a database person really, so I only know the basics. Good to know, that means I can treat anything you say as bullsh*t :-) -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 No renuncies a nada. No te aferres a nada. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
--- Original Message --- From: Chris Browne [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: 26/11/07, 17:39:42 Subject: Re: [GENERAL] replication in Postgres I believe that what they are using is a version of Slony-I, which certainly falls into the near real time replication category. I don't know how modified it might be, but I should also note that there's another tool for synchronising data with Oracle which is not based on Slony. Iirc, it's intended more for tasks like regular updating of data onto a reporting/analysis server. Regards, Dave (who, for those that don't realise, does work for EnterpriseDB, just not much on Advanced Server and it's add-ons). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] replication in Postgres
Okay I'm relaxed ;-) honest. It does irritate me sometimes (my fault) when people post back comments as if they have knowledge on a subject when they don't though, if you don't know then keep quiet. All it does is confuse prople like me, who really don't know, and are reaching out for a little help from those that do. --- Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 18:57:19 + (GMT) Glyn Astill [EMAIL PROTECTED] wrote: --- Alvaro Herrera [EMAIL PROTECTED] wrote: Glyn Astill wrote: Thanks everyone for your replies. EnterpriseDB looks like the way to go if we want good replication. Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. This is bullsh*t, it does as I've been talking to them this week. Glyn, relax.. he did say, that I know of. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSxfvATb/zqfZUUQRAiCdAJ993n1hJgnxbmH1ewNmzBA9c+/4fACfZnu7 QR4D3O7EZd1N8GSqHf8SgDA= =K/76 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Glyn Astill ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Primary Key
It's worse than that. It's even worse than that. Decades ago, Florida used to issue multiple plates with the same number, differentiated by color. There are other cases of states having multiple types of license plates, with overlapping numbers. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
On Nov 26, 2007 1:02 PM, Glyn Astill [EMAIL PROTECTED] wrote: It it possible to get a system that does syncronous replication and also allows slaves to catch up if they're down for a period of time like you can with asyncronous? Ummm, if one server falls behind, and the other keeps going, that, by definition, is not synchronous. In a synchronous system, you either wait for the other system to catch up, or declare it dead to the world and keep going without it. I do like the recommendation of setting up a pair of synch masters and having one feed a slony slave for big nasty queries. Of course a grid or a clustwer is better to makesure all servers are in sync, but there's performance issues with the 2 phase commit isn't there? ayup. The most important word you can learn to use when talking about replication and clustering is TANSTAAFL. There ain't no such thing as a free lunch. Just for the record I'm a programmer, not a database person really, so I only know the basics. Stick around, you'll learn plenty here. Admittedly a little bluntly at times. :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Primary Key
Joshua D. Drake wrote: In theory the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). And some kind of allowance for Title 49, Sec. 565.4, subsection (d): The VINs of any two vehicles manufactured within a 30-year period shall not be identical. After 30 years, all bets are off. And the manufacturers have been tinkering with the VIN due to the inadequacies of the VIN number. Since VINs were first standardized in 1980, does this mean we are approaching a Y2010 problem? I'm sure someone has defined a vehicle, but I don't know what number applies when you've pieced together a rebuilt engine, salvaged transmission, junkyard hood and so-on to get a working car. I think custom builders end up applying for a new VIN but I don't think that applies to repairs. Of course defining when a vehicle becomes a different vehicle is a problem that remains regardless of choice of key. VINs are apparently not required on directly imported vehicles (ie. those imported by other than the manufacturer). The above applies to the US. I'm sure the laws of other countries vary. Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Primary Key
On Nov 26, 2007 1:30 PM, Steve Crawford [EMAIL PROTECTED] wrote: I'm sure someone has defined a vehicle, but I don't know what number applies when you've pieced together a rebuilt engine, salvaged transmission, junkyard hood and so-on to get a working car. I think custom builders end up applying for a new VIN but I don't think that applies to repairs. Of course defining when a vehicle becomes a different vehicle is a problem that remains regardless of choice of key. There's a small car building company in the west that buys the old VINs / chassis to Shelby 350 / 500GTs and builds whole new cars with those VINs. Course, the older ones are in the junkyard by then. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
On Mon, Nov 26, 2007 at 07:02:35PM +, Glyn Astill wrote: It it possible to get a system that does syncronous replication and also allows slaves to catch up if they're down for a period of time like you can with asyncronous? This is what Postgres-R is intended to do. In order to get that value, you have to have all -- ALL -- transactions on all nodes in SERIALIZABLE mode. Is that ok? Then talk to Markus. He's a smart guy. He needs someone to help him make his work public. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication in Postgres
On Mon, Nov 26, 2007 at 07:25:04AM -0600, Jeff Larsen wrote: My 2 cents... I would rather see someone working on true synchronous replication, It will cost more than US$0.02. But if you're willing to put up real money, there are people willing to put in the work. Or, if you're willing to put up real money, you can have IBM DB2-style 24x7 systems running tomorrow. But it requires real money: (realtively) expensive hardware, and serious admins who know what they're doing. What you can't have is this without cost today. Or, I venture to say, any day within the next 5 years. (Beyond that, I'm unwilling to speculate.) Tricky problems require expensive tricks. See the manual for tricks you can perform today. Hint: PostgreSQL depends on the OS, so using OS tricks is an option. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
On Mon, Nov 26, 2007 at 03:31:46PM +0100, Thomas Kellerer wrote: EnterpriseDB Replication Server replicates data across the enterprise in near real time to meet a wide array of business challenges. Data can Slony does this, except that it can't talk to Oracle. What's wrong with Slony? My employer developed and released it because our business depended on this functionality. That you can get to postgresql.org today proves that it's working. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] ALTER syntax question and usernames with hyphens
Hi all, What is the proper syntax/escape character when using 'ALTER ... OWNER TO user-name'? I've tried single quotes, backslashes, backticks and various others without luck. Is it at all possible? Thanks! Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ALTER syntax question and usernames with hyphens
Madison Kelly wrote: Hi all, What is the proper syntax/escape character when using 'ALTER ... OWNER TO user-name'? I've tried single quotes, backslashes, backticks and various others without luck. Is it at all possible? Double quotes (same as for any identifier) -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Find a bug in a program, and fix it, and the program will work today. Show the program how to find and fix a bug, and the program will work forever (Oliver Silfridge) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Primary Key
Joshua D. Drake wrote: On Mon, 26 Nov 2007 10:28:03 -0800 (PST) Richard Broersma Jr [EMAIL PROTECTED] wrote: --- On Mon, 11/26/07, Joshua D. Drake [EMAIL PROTECTED] wrote: In theory the item that would be a natural key in this instance is the VIN. And you then need to deal with cars that have mismatched Body VIN numbers and Engine VIN numbers? It's not uncommon on older cars or crash damaged cars. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] replication in Postgres
Since no one's mentioned it, and while I don't have any personal experience with it, I thought I'd mention the recently released Bucardo (http://bucardo.org/) as another Postgres replication option. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication in Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 12:39:42 -0500 Chris Browne [EMAIL PROTECTED] wrote: Unfortunately, the only way to make things deterministic (or to get from near real time to *GUARANTEED* real time) is to jump to synchronous replication, which is not much different from 2PC (Two Phase Commit), and which is certain to be prohibitively expensive across a WAN. At this point, I tend to get visions of Tom Cruise telling Jack Nicholson, I want real time replication!, and getting the response: You can't HANDLE real time replication! You're damn right I ordered real time replication! - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSzrvATb/zqfZUUQRAqfwAKCA+p8tzxIQJGrnEm8F7D5H0HbkCQCfTk7J D0mtHxYmVhXVAfbvUYMO6tw= =0m/Q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Primary Key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/26/07 12:11, Steve Crawford wrote: [snip] If we presume that the plate is a key to a vehicle, then we immediately run into problems as a vehicle can, over time, have several plates (lost, stolen, changed to vanity...) and a plate can belong, sequentially, to several vehicles (especially when vanity plates are transferred to new cars). And when you have your char(6) plate-number column, they run out of numbers and switch to 7-characters requiring changes to all tables that used the plate as a key. Or you realize that ABC123 could be ABC123-California, ABC123-Nevada or ABC123-New York (I'm assuming that AAA999 is a valid format in those states). We use this as a *non*-unique index: PLATE_NUMBERCHAR(10) PLATE_STATE CHAR(2) PLATE_COUNTRY CHAR(4) The country field could be dropped off and Canada/USA differentiated by the state/province code, but with NAFTA it's possible that Mexican plates will turn up soon, and there's always the off chance that a European car will show up. (We used to have PLATE_STATE first, but then discovered how many northeasterners don't know what state their vehicle is registered in.) - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSzxlS9HxQb37XmcRAh0QAKCLp5aNkoPPs8P5oXQCJ0HI28MNuACeKtFH eECn8XRwrjOqonUuDr8DDH8= =cYiG -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication in Postgres
So what is the state-of-the-art in the Postgresql world if I _do_ want synchronous replication? 2-phase commit from the client application? Any success/horror stories about doing it in Java? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] replication in Postgres
On Nov 26, 2007 3:41 PM, Garber, Mikhail [EMAIL PROTECTED] wrote: So what is the state-of-the-art in the Postgresql world if I _do_ want synchronous replication? 2-phase commit from the client application? Any success/horror stories about doing it in Java? Depending on the restrictions you're willing to live with, pgpool may be a good choice, and it is dirt simple to implement. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Primary Key
On Nov 26, 2007 1:11 PM, Steve Crawford [EMAIL PROTECTED] wrote: It's worse than that. If we presume that the plate is a key to a vehicle, then we immediately run into problems as a vehicle can, over time, have several plates (lost, stolen, changed to vanity...) and a plate can belong, sequentially, to several vehicles (especially when vanity plates are transferred to new cars). And when you have your char(6) plate-number column, they run out of numbers and switch to 7-characters requiring changes to all tables that used the plate as a key. Or you realize that ABC123 could be ABC123-California, ABC123-Nevada or ABC123-New York (I'm assuming that AAA999 is a valid format in those states). Although I haven't seen it much, recently, semi-trucks used to regularly have with numerous plates - one for each state in which they operated. And some states such as Texas allow you to have the same amateur-radio plate number on multiple vehicles. I won't argue that there are no reasonable natural keys. But I have sure seen plenty of cases where what appeared to be a natural key was discovered, generally at a very inopportune time in the development process, to be not-so-natural after all. if you miss the key and blow it, you fix it. yes, there are tons of examples of this particular number not exactly lining up with something, like a person, vehicle, etc. of course this all means that the number in question is simply not enough information by itself, and so is either a partial definition or defines something else. i will concede that changing a key across 10 tables is easier than redefining a constraint on one table. this is why the compromise mentioned way upthread by josh drake (namely, to define the natural but use surrogate for joining) is good in certain cases like this, especially when you have a complex key that is used in many tables. the problem is that, because surrogates allow skipping the problem without defining a proper key at all, the vague data relationships you mention never get properly defined in the database and end up being caught in code or by the user because the id is trusted to express the relationship when in fact it doesn't. this causes much worse problems than redefining keys by the way, and helps create the messy databases that those of us who know how to do things both ways complain about. in other words, if you create tables by defining the id p-key, throwing a bunch of fields on it that approximately describe the item, plus maybe some indexes for performance, you have already loaded the gun to shoot yourself in the foot. many of the safeguards the database can provide in keeping your data organized have been removed... merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication in Postgres
[EMAIL PROTECTED] (Erik Jones) writes: Since no one's mentioned it, and while I don't have any personal experience with it, I thought I'd mention the recently released Bucardo (http://bucardo.org/) as another Postgres replication option. It's Yet Another Asynchronous Replication System, ergo as unsatisfactory for forcibly real time requirements as any of the other async systems... -- let name=cbbrowne and tld=linuxfinances.info in name ^ @ ^ tld;; http://cbbrowne.com/info/advocacy.html Self Reference is its Own Reward If tautologies do not convey information, mathematicians would not be surprised by them. -- Mark Miller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
[EMAIL PROTECTED] (Glyn Astill) writes: It it possible to get a system that does syncronous replication and also allows slaves to catch up if they're down for a period of time like you can with asyncronous? Well, a modal approach is possible - that's what Postgres-R tries to do. Of course, once you drop into a mode that allows slaves to catch up, then you have given up on synchronicity, and have fallen back to asynchronous replication. If you systematically have a way to do that, then you no longer have a replication system that can honestly be called synchronous. If it's *truly* synchronous, then when nodes fall over, the system MUST stop accepting transactions. -- output = reverse(ofni.secnanifxunil @ enworbbc) http://www3.sympatico.ca/cbbrowne/linux.html Signs of a Klingon Programmer - 2. Specifications are for the weak and timid! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
On Nov 26, 2007, at 3:21 PM, Chris Browne wrote: [EMAIL PROTECTED] (Erik Jones) writes: Since no one's mentioned it, and while I don't have any personal experience with it, I thought I'd mention the recently released Bucardo (http://bucardo.org/) as another Postgres replication option. It's Yet Another Asynchronous Replication System, ergo as unsatisfactory for forcibly real time requirements as any of the other async systems... True. But, as has been pointed out by others, people often ask for synchronous replication when they don't really realize what that is or what it implies and, since they're often willing to revise their requirements once they do, I thought it'd be nice (to the Bucardo guys at least) to point out Bucardo as a viable replication option. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] speed up insert query
Hey everybody. I'm trying to speed up a query (not general optimization, one query in particular), and I'm not sure if there's any way to get it to go faster. The query looks like this INSERT INTO transaction ( tr_acct_num, tr_acct_typ, tr_atm_rec, tr_audit_seq, tr_branch_cd, tr_cash_amt, ... tr_tran_time, tr_trn_rev_point, tr_typ, tr_typ_cd, atm_trn_reg_e, dataset ) SELECT iq_numeric(tr_acct_num), tr_acct_typ, iq_numeric(tr_atm_rec), iq_numeric(tr_audit_seq), iq_numeric(tr_branch_cd), iq_numeric(tr_cash_amt), ... cast(tr_tran_time as time), iq_numeric(tr_trn_rev_point), iq_numeric(tr_typ), iq_numeric(tr_typ_cd), atm_trn_reg_e, 0 FROM transaction_import WHERE is_ok = 'TRUE' ; There's not a lot I seem to be able to do about the select portion of this query (index on is_ok, the planner didn't even want to use it), but is there anything I can do to speed up the import? This is the EXPLAIN ANALYZE on the query QUERY PLAN - Seq Scan on transaction_import (cost=0.00..30953.68 rows=69239 width=434) (actual time=0.146..2974.609 rows=68913 loops=1) Filter: is_ok Total runtime: 179091.119 ms (3 rows) The query is inserting ~70,000 rows into a table with ~1.8 million rows already in it. Anybody have any idea how I can keep this query from taking so long? -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] speed up insert query
2 things tr_tran_time needs to be already in 'time format' is_ok needs to be indexed (preferably bitmapped index) HTH/ Martin - Original Message - From: Tom Hart [EMAIL PROTECTED] To: Postgres General List pgsql-general@postgresql.org Sent: Monday, November 26, 2007 5:30 PM Subject: [GENERAL] speed up insert query Hey everybody. I'm trying to speed up a query (not general optimization, one query in particular), and I'm not sure if there's any way to get it to go faster. The query looks like this INSERT INTO transaction ( tr_acct_num, tr_acct_typ, tr_atm_rec, tr_audit_seq, tr_branch_cd, tr_cash_amt, ... tr_tran_time, tr_trn_rev_point, tr_typ, tr_typ_cd, atm_trn_reg_e, dataset ) SELECT iq_numeric(tr_acct_num), tr_acct_typ, iq_numeric(tr_atm_rec), iq_numeric(tr_audit_seq), iq_numeric(tr_branch_cd), iq_numeric(tr_cash_amt), ... cast(tr_tran_time as time), iq_numeric(tr_trn_rev_point), iq_numeric(tr_typ), iq_numeric(tr_typ_cd), atm_trn_reg_e, 0 FROM transaction_import WHERE is_ok = 'TRUE' ; There's not a lot I seem to be able to do about the select portion of this query (index on is_ok, the planner didn't even want to use it), but is there anything I can do to speed up the import? This is the EXPLAIN ANALYZE on the query QUERY PLAN -- --- Seq Scan on transaction_import (cost=0.00..30953.68 rows=69239 width=434) (actual time=0.146..2974.609 rows=68913 loops=1) Filter: is_ok Total runtime: 179091.119 ms (3 rows) The query is inserting ~70,000 rows into a table with ~1.8 million rows already in it. Anybody have any idea how I can keep this query from taking so long? -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] speed up insert query
Martin Gainty wrote: 2 things tr_tran_time needs to be already in 'time format' is_ok needs to be indexed (preferably bitmapped index) HTH/ Martin The data is COPY'ed from csv's that our internal software creates, and we don't have control over output format. Is coaxing tr_tran_time into proper time format on the _import table going to be less costly than doing it on the fly in the query? Also, there are a couple more casts in the query (as date). Are casts extremely costly? The iq_numeric function uses regex to determine whether to return a number or null. How costly are regex based functions used like this? I can't see it being more efficient to edit this data while it's in a table with all text fields, no key (have to permit duplicates at this stage), and as of yet no indexes. (As I said I tried an index on is_ok, both a btree and a hash, and the planner seems completely uninterested). Also, I'm sure you've heard this, but the date on your email client is drastically wrong. I appreciate your assistance but I can only imagine that there are quite a few people missing your good advice because they're not looking through the new posts from 2000. Hey everybody. I'm trying to speed up a query (not general optimization, one query in particular), and I'm not sure if there's any way to get it to go faster. The query looks like this INSERT INTO transaction ( tr_acct_num, tr_acct_typ, tr_atm_rec, tr_audit_seq, tr_branch_cd, tr_cash_amt, ... tr_tran_time, tr_trn_rev_point, tr_typ, tr_typ_cd, atm_trn_reg_e, dataset ) SELECT iq_numeric(tr_acct_num), tr_acct_typ, iq_numeric(tr_atm_rec), iq_numeric(tr_audit_seq), iq_numeric(tr_branch_cd), iq_numeric(tr_cash_amt), ... cast(tr_tran_time as time), iq_numeric(tr_trn_rev_point), iq_numeric(tr_typ), iq_numeric(tr_typ_cd), atm_trn_reg_e, 0 FROM transaction_import WHERE is_ok = 'TRUE' ; -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] speed up insert query
Tom Hart wrote: Martin Gainty wrote: 2 things tr_tran_time needs to be already in 'time format' is_ok needs to be indexed (preferably bitmapped index) HTH/ Martin The data is COPY'ed from csv's that our internal software creates, and we don't have control over output format. Is coaxing tr_tran_time into proper time format on the _import table going to be less costly than doing it on the fly in the query? Also, there are a couple more casts in the query (as date). Are casts extremely costly? The iq_numeric function uses regex to determine whether to return a number or null. How costly are regex based functions used like this? I can't see it being more efficient to edit this data while it's in a table with all text fields, no key (have to permit duplicates at this stage), and as of yet no indexes. (As I said I tried an index on is_ok, both a btree and a hash, and the planner seems completely uninterested). Also, I'm sure you've heard this, but the date on your email client is drastically wrong. I appreciate your assistance but I can only imagine that there are quite a few people missing your good advice because they're not looking through the new posts from 2000. I found at least one major optimization (or rather de-optimization already in place, if the english language doesn't mind being flexed a bit). My supervisor was playing with indexes on the rather large transaction table. It turns out he had two multi-column indexes that were composed of fields that were already indexed. These two indexes didn't seem to be helping queries against the table much, but removing just those two (there are still 4 or 5 single column indexes) cut my execution time by 70%. That brings it to a much more manageable amount of time. Thanks for your reply. Hope this helps somebody else :-) Hey everybody. I'm trying to speed up a query (not general optimization, one query in particular), and I'm not sure if there's any way to get it to go faster. The query looks like this INSERT INTO transaction ( tr_acct_num, tr_acct_typ, tr_atm_rec, tr_audit_seq, tr_branch_cd, tr_cash_amt, ... tr_tran_time, tr_trn_rev_point, tr_typ, tr_typ_cd, atm_trn_reg_e, dataset ) SELECT iq_numeric(tr_acct_num), tr_acct_typ, iq_numeric(tr_atm_rec), iq_numeric(tr_audit_seq), iq_numeric(tr_branch_cd), iq_numeric(tr_cash_amt), ... cast(tr_tran_time as time), iq_numeric(tr_trn_rev_point), iq_numeric(tr_typ), iq_numeric(tr_typ_cd), atm_trn_reg_e, 0 FROM transaction_import WHERE is_ok = 'TRUE' ; -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On 11/13/07 10:02 AM, Scott Ribe [EMAIL PROTECTED] wrote: What you're referring to must be that the kernel was essentially single-threaded, with a single kernel-funnel lock. (Because the OS certainly supported threads, and it was certainly possible to write highly-threaded applications, and I don't know of any performance problems with threaded applications.) This has been getting progressively better, with each release adding more in-kernel concurrency. Which means that 10.5 probably obsoletes all prior postgres benchmarks on OS X. While I've never seen this documented anywhere, it empirically looks like 10.5 also (finally) adds CPU affinity to better utilize instruction caching. On a dual CPU system under 10.4, one CPU bound process would use two CPU's at 50%. Under 10.5 it uses one CPU at 100%. I never saw any resolution to this thread - were the original tests on the Opteron and OS X identical, or were they two different workloads? Wes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Rules slower than Dynamic SQL ?
Hi all, I've got a data warehouse with pretty high rate of insert into partitioned tables. What I've noticed, is that rule-based partitioning seems to be somewhat slower than insertions made directly into partitions through execution of dynamic SQL. Is it really true ? Thanks! Best regards, Alex Vinogradovs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Rules slower than Dynamic SQL ?
On Mon, 2007-11-26 at 16:01 -0800, Alex Vinogradovs wrote: I've got a data warehouse with pretty high rate of insert into partitioned tables. What I've noticed, is that rule-based partitioning seems to be somewhat slower than insertions made directly into partitions through execution of dynamic SQL. Is it really true ? Depends how complex your rules are, but I think yes is the short answer. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Mon, 2007-11-26 at 17:37 -0600, Wes wrote: On 11/13/07 10:02 AM, Scott Ribe [EMAIL PROTECTED] wrote: What you're referring to must be that the kernel was essentially single-threaded, with a single kernel-funnel lock. (Because the OS certainly supported threads, and it was certainly possible to write highly-threaded applications, and I don't know of any performance problems with threaded applications.) This has been getting progressively better, with each release adding more in-kernel concurrency. Which means that 10.5 probably obsoletes all prior postgres benchmarks on OS X. While I've never seen this documented anywhere, it empirically looks like 10.5 also (finally) adds CPU affinity to better utilize instruction caching. On a dual CPU system under 10.4, one CPU bound process would use two CPU's at 50%. Under 10.5 it uses one CPU at 100%. I never saw any resolution to this thread - were the original tests on the Opteron and OS X identical, or were they two different workloads? resolution? http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php conclusion? Mac was still pretty slow in comparison Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Desparately seeking new India Regional Contact for postgresql.org
PostgreSQL users, Given the importance of India to the world of software, it's critical that we have an Indian Regional Contact who can be the community press relations person for India. In the past, we had Vishal and Shridhar who did a terrific job but are no longer available. If you are an Indian PostgreSQL user and: a) want to contribute to the project b) have experience or interest in marketing and/or PR c) know some tech reporters or bloggers in India (or have time to find them) Then I could really, really use your help as Regional Contact for the PostgreSQL 8.3 press release. E-mail me ASAP. Thanks! -- --Josh Josh Berkus PostgreSQL Core Team San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
So what is the state-of-the-art in the Postgresql world if I _do_ want synchronous replication? 2-phase commit from the client application? Any success/horror stories about doing it in Java? For Java, you could check out Sequoia (http://sequoia.continuent.org/) or their commercial version uni/cluster. I believe it can be configured for true synchronous replication. -- m@ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
