Reflecting on the great community discussion
Posted Tuesday Jan 27th, 2009 03:47pm
by Joshua Drake | Permalink
As I diligently prepare for PostgreSQL Conference East 09 by trying to ensure that we have enough talks (hint: talk submission closes Feb 27th). We have a suggested hotel and that everyone knows it is going on (including the Groovy, Python, MySQL, PHP and Ruby communities), I take heart in knowing that our community PostgreSQL can have a well thought out, pointed and productive discussion like no other. I was thinking what other great and historical discussions have we had?
blog comments powered by Disqus
Posted Tuesday Jan 27th, 2009 03:47pm
by Joshua Drake | Permalink
|
As I diligently prepare for PostgreSQL Conference East 09 by trying to ensure that we have enough talks (hint: talk submission closes Feb 27th). We have a suggested hotel and that everyone knows it is going on (including the Groovy, Python, MySQL, PHP and Ruby communities), I take heart in knowing that our community PostgreSQL can have a well thought out, pointed and productive discussion like no other. I was thinking what other great and historical discussions have we had?
Any that I missed?Vi vs. Emacs? (answer: joe) cvs vs. git vs. svn (answer: svn) python versus perl (answer: Python) C vs C++ (answer: C, but only because I have had patches accepted) fbsd vs linux (answer: my nick is linupoet, you guess) Red versus Blue (answer: They both suck)
Categories: PostgreSQL, OpenSource
blog comments powered by Disqus
East 09: Call for papers!
Posted Monday Jan 26th, 2009 10:02am
by Joshua Drake | Permalink
PostgreSQL Conference, U.S., East 09 will be held in Philadelphia at historic Drexel University from April 3rd through 5th. The call for papers is now out.
blog comments powered by Disqus
Posted Monday Jan 26th, 2009 10:02am
by Joshua Drake | Permalink
|
|
PostgreSQL Conference, U.S., East 09 will be held in Philadelphia at historic Drexel University from April 3rd through 5th. The call for papers is now out.
As always we let our submissions define our actual tracks. If you have something you want to talk about it. Submit it. As long as it is about PostgreSQL (or doing something with PostgreSQL) we will consider it.
We are seeking creative ideas about things we can do at the conference. At West we had a code sprint. The sprint was very successful as it was about all things postgresql and open source. It wasn't just hacking back end code but people worked on all kinds of things.. Is someone up for running a code sprint?
There has also been specific interest in having us add (in addition to our advanced topics) a newbie track. Please do not be afraid to submit a talks on items such as:
Backing up PostgreSQL Understanding and Configuring Autovacuum Normalization Trigger Happy (how to use triggers ;) PITR -- happiness is a shipped transaction log
Other topics we are interested in beyond the standard PostgreSQL architectural fanfare are:
So don't delay, PostgreSQL Conference, U.S. is the premeire PostgreSQL conference series for the United States PostgreSQL community! Submit your talk today.Groovy/Grails Django PHP Pylons SQL Alchemy
blog comments powered by Disqus
FK, CHECK, ENUM or DOMAIN. That is the question.
Posted Wednesday Jan 21st, 2009 04:05pm
by Joshua Drake | Permalink
We have a customer that recently asked me to comment on which I would use for a particular problem. This is a simple validating lookup. For example, CHECK(VALUE IN ('foo','bar')). Should we use a CHECK constraint, FK, ENUM or DOMAIN?
blog comments powered by Disqus
Posted Wednesday Jan 21st, 2009 04:05pm
by Joshua Drake | Permalink
|
|
We have a customer that recently asked me to comment on which I would use for a particular problem. This is a simple validating lookup. For example, CHECK(VALUE IN ('foo','bar')). Should we use a CHECK constraint, FK, ENUM or DOMAIN?
A CHECK constraint is easy to apply and has simple syntax. It is also extremely flexible in solving other types of validating problems. If your valid values change you must DROP CONSTRAINT and ADD CONSTRAINT. You can not add an element to the CHECK.
A Foreign Key creates the requirement of a lookup table. It also offers the easiest management of valid values. You just INSERT, UPDATE or DELETE from the lookup table. If you are a smart monkey and using natural keys versus artificial ones, you can avoid the JOIN on SELECT as well.
ENUM registers as a type in PostgreSQL. This means if you use an ENUM extensively you are basically locking yourself into the use of the type. In short if you need to modify an ENUM you drop the ENUM and recreate it. You can't drop an ENUM if a relation is using it. There are some interesting functions available with ENUM but I am having a hard time seeing a use case for the type as a whole. An ENUM type in theory lends itself specifically to this type of problem so I have included it.
A DOMAIN for this problem suffers from the same problems as ENUM as it registers as a type. However a DOMAIN is more flexible as you can apply complex logic to the validation (just as you can with a CHECK). For example a DOMAIN could contain the regex to validate if a email address is correctly formed. I have used domains many times in the past to create complex validating types. They are useful.
So what does all this boil down to? I have listed the pros and cons of managing each method above but what I haven't mentioned is performance. What is the particular performance bottleneck for each method? Read on, to find out for yourself.
Read more...
Categories: PostgreSQL, OpenSource
blog comments powered by Disqus
Replicator meeting log for 01-08-09 is up
Posted Tuesday Jan 13th, 2009 08:47am
by Joshua Drake | Permalink
The PostgreSQL + Replicator meeting logs for 01-08-09 are up. This was a long meeting held on #replicator using the Freenode IRC service. Topics covered were the removal of the Single Point of Failure of the MCP which is partially done. The new in the PostgreSQL backend forwarder works but now the question is how to safely handle failover. Take a look, maybe you have an idea.
blog comments powered by Disqus
Posted Tuesday Jan 13th, 2009 08:47am
by Joshua Drake | Permalink
|
|
The PostgreSQL + Replicator meeting logs for 01-08-09 are up. This was a long meeting held on #replicator using the Freenode IRC service. Topics covered were the removal of the Single Point of Failure of the MCP which is partially done. The new in the PostgreSQL backend forwarder works but now the question is how to safely handle failover. Take a look, maybe you have an idea.
blog comments powered by Disqus
PostgreSQL Conference / PgCon.US update
Posted Sunday Jan 11th, 2009 11:55am
by Joshua Drake | Permalink
In an attempt to ensure the continued positive growth of the community, PostgreSQL Conference, U.S. is going to change its current policy toward the domain PgCon.US. Read more...
blog comments powered by Disqus
Posted Sunday Jan 11th, 2009 11:55am
by Joshua Drake | Permalink
|
|
In an attempt to ensure the continued positive growth of the community, PostgreSQL Conference, U.S. is going to change its current policy toward the domain PgCon.US. Read more...
blog comments powered by Disqus
PITRTools: Multiple slave support
Posted Friday Jan 9th, 2009 03:43pm
by Joshua Drake | Permalink
I gave a lightning talk at Pg Conference: West 08 about a tool that I have developed call PITRTools. PITRTools is a python based log shipping utility. Essentially it wraps itself around standard tools such as rsync, and pg_standby to provide a usable experience. Some of the features is provides are:
blog comments powered by Disqus
Posted Friday Jan 9th, 2009 03:43pm
by Joshua Drake | Permalink
|
|
I gave a lightning talk at Pg Conference: West 08 about a tool that I have developed call PITRTools. PITRTools is a python based log shipping utility. Essentially it wraps itself around standard tools such as rsync, and pg_standby to provide a usable experience. Some of the features is provides are:
In all it is by far the simplest and most effective tool for PostgreSQL standby that I have used (of course I wrote it so...). Read more...Auto initialization of environment Simple base backups Monitoring of Master Arbitrary alerts Failover Failover actions etc...
blog comments powered by Disqus
PostgreSQL Replicator Update 01.06.09
Posted Tuesday Jan 6th, 2009 12:25pm
by Joshua Drake | Permalink
mcp | | ----------------- | | | s0 s1 s2
The mcp would handle all communication and data transfer between the master and the slaves. The idea behind the architecture was to achieve maximum efficiency for the master, meaning that the number of the slaves never affected the performance of the master. However this architecture came with a cost. A single point of failure. If the mcp were to ever crash, replication would stop.
When we started down the path of 1.9 it was made very clear to me by Alvaro and Alexey that this was not acceptable. In return I made it very clear that any architectural change we made must not suffer from the Slony problem (performance degradation based on number of subscribers). Together we were all very clear to each other and Alvaro came up with a new architecture.
The new architecture calls for a "Forwarder" process and from a topological view doesn't look much different than the MCP. It does however offer us a great deal more flexibility and stability. Here is the new architecture:
How is this different? It is different in a couple of ways. First, the forwarder is now integrated into the PostgreSQL backend. This removes the mcp binary entirely. It also greatly decreases the redundancy of the code. Secondly if the primary forwarder goes down a slave can become the forwarder. This removes the single point of failure. You can read more information on the forwarder here.
Other items up for idle thoughts on the possibilities of this new architecture is a single monitoring point. With versions of replicator <= 1.8, you can have a clear idea of which replication transactions have been received and transfered to each slave but you must access each slave individually to see what transaction has actually been restored.
So what else is coming with Replicator 1.9?
In continuing our cleanup of the architecture we are completely rewriting the ROLE and GRANT/REVOKE replication. This is actually the first step of the other half of the Major Feature of Replicator 1.9, DDL replication. We are currently investigating the opportunity to have certain DDL operations automatically replicate. The most obvious of these would be:
We decided to pass on replicating CREATE FUNCTION due to complexity in dealing with externally linked libraries as well as various dependency problems. We may look at this again in the future. For more information on this feature please visit the thread.
If you are interested in testing you can grab the 1.8 Beta or 1.9 from SVN. You can also get the 1.8 Beta from The pgsqlrpms project.
The next developer meeting is on 01.08.09 at 11:00AM PST. We are holding in in the #replicator channel on Freenode. All are welcome. --> I know it appears that it has been pretty quiet since we open sourced Replicator but that isn't the case. We have been actively working on 1.9 and fixing 1.8 Beta as bug reports have come in, including a bug to sequence replication. However, 1.9 is the true milestone release where we have finally moved away from the original architecture. For those that don't know, the original architecture looked like this:
blog comments powered by Disqus
Posted Tuesday Jan 6th, 2009 12:25pm
by Joshua Drake | Permalink
|
|
mcp | | ----------------- | | | s0 s1 s2
The mcp would handle all communication and data transfer between the master and the slaves. The idea behind the architecture was to achieve maximum efficiency for the master, meaning that the number of the slaves never affected the performance of the master. However this architecture came with a cost. A single point of failure. If the mcp were to ever crash, replication would stop.
When we started down the path of 1.9 it was made very clear to me by Alvaro and Alexey that this was not acceptable. In return I made it very clear that any architectural change we made must not suffer from the Slony problem (performance degradation based on number of subscribers). Together we were all very clear to each other and Alvaro came up with a new architecture.
The new architecture calls for a "Forwarder" process and from a topological view doesn't look much different than the MCP. It does however offer us a great deal more flexibility and stability. Here is the new architecture:
master-->forwarder0
|
|
-----------------
| | |
s0 s1 s2
How is this different? It is different in a couple of ways. First, the forwarder is now integrated into the PostgreSQL backend. This removes the mcp binary entirely. It also greatly decreases the redundancy of the code. Secondly if the primary forwarder goes down a slave can become the forwarder. This removes the single point of failure. You can read more information on the forwarder here.
Other items up for idle thoughts on the possibilities of this new architecture is a single monitoring point. With versions of replicator <= 1.8, you can have a clear idea of which replication transactions have been received and transfered to each slave but you must access each slave individually to see what transaction has actually been restored.
So what else is coming with Replicator 1.9?
In continuing our cleanup of the architecture we are completely rewriting the ROLE and GRANT/REVOKE replication. This is actually the first step of the other half of the Major Feature of Replicator 1.9, DDL replication. We are currently investigating the opportunity to have certain DDL operations automatically replicate. The most obvious of these would be:
CREATE TABLE
ALTER TABLE
CREATE DOMAIN
etc...
We decided to pass on replicating CREATE FUNCTION due to complexity in dealing with externally linked libraries as well as various dependency problems. We may look at this again in the future. For more information on this feature please visit the thread.
If you are interested in testing you can grab the 1.8 Beta or 1.9 from SVN. You can also get the 1.8 Beta from The pgsqlrpms project.
The next developer meeting is on 01.08.09 at 11:00AM PST. We are holding in in the #replicator channel on Freenode. All are welcome. --> I know it appears that it has been pretty quiet since we open sourced Replicator but that isn't the case. We have been actively working on 1.9 and fixing 1.8 Beta as bug reports have come in, including a bug to sequence replication. However, 1.9 is the true milestone release where we have finally moved away from the original architecture. For those that don't know, the original architecture looked like this:
master-->mcp
|
|
-----------------
| | |
s0 s1 s2
The mcp would handle all communication and data transfer between the master and the slaves. The idea behind the architecture was to achieve maximum efficiency for the master, meaning that the number of the slaves never affected the performance of the master. However this architecture came with a cost. A single point of failure. If the mcp were to ever crash, replication would stop.
When we started down the path of 1.9 it was made very clear to me by Alvaro and Alexey that this was not acceptable. In return I made it very clear that any architectural change we made must not suffer from the Slony problem (performance degradation based on number of subscribers). Together we were all very clear to each other and Alvaro came up with a new architecture.
The new architecture calls for a "Forwarder" process and from a topological view doesn't look much different than the MCP. It does however offer us a great deal more flexibility and stability. Here is the new architecture:
master-->forwarder0
|
|
-----------------
| | |
s0 s1 s2
How is this different? It is different in a couple of ways. First, the forwarder is now integrated into the PostgreSQL backend. This removes the mcp binary entirely. It also greatly decreases the redundancy of the code. Secondly if the primary forwarder goes down a slave can become the forwarder. This removes the single point of failure. You can read more information on the forwarder here.
Other items up for idle thoughts on the possibilities of this new architecture is a single monitoring point. With versions of replicator <= 1.8, you can have a clear idea of which replication transactions have been received and transfered to each slave but you must access each slave individually to see what transaction has actually been restored.
So what else is coming with Replicator 1.9?
In continuing our cleanup of the architecture we are completely rewriting the ROLE and GRANT/REVOKE replication. This is actually the first step of the other half of the Major Feature of Replicator 1.9, DDL replication. We are currently investigating the opportunity to have certain DDL operations automatically replicate. The most obvious of these would be:
We decided to pass on replicating CREATE FUNCTION due to complexity in dealing with externally linked libraries as well as various dependency problems. We may look at this again in the future. For more information on this feature please visit the thread. If you are interested in testing you can grab the 1.8 Beta or 1.9 from SVN. You can also get the 1.8 Beta from The pgsqlrpms project. The next developer meeting is on 01.08.09 at 11:00AM PST. We are holding in in the #replicator channel on Freenode. All are welcome.CREATE TABLE ALTER TABLE CREATE DOMAIN etc...
blog comments powered by Disqus
PostgreSQL Conference: West 2007, More video up
Posted Monday Jan 5th, 2009 01:15pm
by Joshua Drake | Permalink
I have gotten a couple more of the West 2007 Videos.
blog comments powered by Disqus
Posted Monday Jan 5th, 2009 01:15pm
by Joshua Drake | Permalink
|
|
I have gotten a couple more of the West 2007 Videos.
PostgreSQL Partitioning - Robert Treat Babel of PLs - David Fetter
Categories: PostgreSQL, OpenSource
blog comments powered by Disqus
PgDay LFNW: Call for Papers! (04/25/09)
Posted Monday Jan 5th, 2009 11:53am
by Joshua Drake | Permalink
PostgreSQL Conference, U.S. is having a PgDay at LinuxFest Northwest in Bellingham Washington on April 25th, 2009. The PgDay (and LinuxFest Northwest) is a free event. We are holding the PgDAY on the first day of the event (a Saturday) parallel with LFNW. There was over 700 attendees to LFNW last year. LFNW is hoping for even more this year! In short, we are looking for some PostgreSQL talks (45 minutes each) to fill out the day. Please click here to submit your talk.
blog comments powered by Disqus
Posted Monday Jan 5th, 2009 11:53am
by Joshua Drake | Permalink
|
|
PostgreSQL Conference, U.S. is having a PgDay at LinuxFest Northwest in Bellingham Washington on April 25th, 2009. The PgDay (and LinuxFest Northwest) is a free event. We are holding the PgDAY on the first day of the event (a Saturday) parallel with LFNW. There was over 700 attendees to LFNW last year. LFNW is hoping for even more this year! In short, we are looking for some PostgreSQL talks (45 minutes each) to fill out the day. Please click here to submit your talk.
blog comments powered by Disqus
Copyright © 2000-2013 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.