With the final Postgres 9.0 commitfest completed two months ago, you might be wondering what is delaying 9.0 beta 1. Along with the normal cleanup of open items, most of the time has been spent finalizing the behavior of our two big 9.0 features, streaming replication and hot standby (which I mentioned previously;
presentation available).

Someone asked me to explain what that testing process looks like, and the most graphic description I could think of was a massive automobile accident (photo; those fire trucks are our developers analyzing the failure ). It is as ugly as an automobile accident, e.g.:

• server doesn’t start if I stop it while doing a backup with a slave connected
• slave cannot connect if configuration options are not set correctly, but poor feedback is given
• slave shows massive overhead when applying streaming logs
• configuration variables are unsettable and confusing to a normal user

Continue Reading »

It is rare for me to hear about a new replication option, but I heard about one involving pgpool-II last week at the New York PostgreSQL Meetup Group. pgpool-II is not only a connection pooler but supports load balancing by sending data-modification queries to every host, while it load-balances read-only queries across all hosts.

One major problem with this usage is that non-deterministic functions, e.g. random(), CURRENT_TIMESTAMP, can produce different results when sent to multiple hosts. pgpool-II 2.3 and later handles some of these cases, but not all of them. Fully-correct operation requires either developers or administrators to handle unsupported non-deterministic functions. This new replication option involves using pgpool-II in ‘master_slave_mode’, which causes all data modification queries to be sent to the master, and the master replicates those changes (without non-deterministic side-effects) to the other slave nodes in the pgpool cluster. This diagram (page 19) illustrates this setup.

Continue Reading »

One of joys of looking for a new job is the ability to meet people and explore technologies that you may otherwise not. I was completely open to all opportunities: big companies and small, public and private, established and stealth mode startups. I spent some time with the smart folks at Rapid7, a leading provider [...]

Initially the lure of Virtual Machines (VM) was to allow multiple operating systems to run simultaneously on a laptop. This is
particularly useful if you need to run many applications that are only supported certain operating systems, e.g. MS Office. VMs have also
always been popular for testing and development. (This video presents a simplified
explaination of virtualization.)

However, recently VM usage has grown and are now regularly used on production servers. This recent community email
post highlights the advantages of using VMs for production
deployments:

• fewer production servers (e.g. only two servers can host all VMs)
• simpler deployments (administrators can easily copy VMs to production servers)
• more reliable deployments (VMs provide a self-contained environment, with fewer potential problems during deployment)
• better scalability (VMs can be given more server resources as necessary)
• easier testing (VMs can be copied and tested independently of the deployment server)
• improved reliability (VMs can be easily started on standby servers once they become production servers)

Continue Reading »

Solid-state drives (SSD) are getting and more popular. Initially, SSDs appeared to be ideal for databases because they potentially allow fast I/O with non-volatile storage — unfortunately, neither of these is completely true. Let me explain:

Fast I/O: While SSDs offer random I/O speeds far in excess of traditional hard drives (because there are no moving platters or heads),the sequential I/O speed of SSDs is only marginally better than mechanical drives. Database activity that causes random I/O, like index scans that do not fit in RAM, will benefit from SSDs’ superior random I/O speeds, but sequential scans only marginally benefit by using SSDs. (Greg Smith explains the limited use-case for SSDs in this email.) Postgres 9.0 will allow random page cost to be set per tablespace so administrators can indicate that random I/O has the same speed as sequential I/O for SSD-based tablespaces.

Continue Reading »

Yes, you read that right. It is not a typo. I will be attending the MySQL Users Group meeting tomorrow at 6pm at Oracle (NYC Headquarters). Our fearless CEO and Red Sox fan, Ed Boyajian, will bravely enter Yankee territory to discuss PostgreSQL vs MySQL. An event not to be missed!
Do you think we are [...]

Analogy: a similarity between like features of two things, on which a comparison may be based.
One of my Sun colleagues correctly pointed out that I use analogies constantly. So why not start my blog with one One of my favorite things to do is ski. It is an incredible feeling to arrive [...]

There is lots of buzz about NoSQL databases these days, and thanks to my attendance at Emerging
Technologies for the Enterprise Conference, I have learned more about them. I heard a talk
about Cassandra, and
another about MongoDB.

NoSQL databases differ in several ways from relational databases:

• crude or no query language (clients do much of the processing normally done by SQL)
• no joins (data must be joined client-side)
• sacrifice ACID and transaction properties for speed, fault tolerance, or easy addition and
  removal of nodes

Continue Reading »

Having just blogged about NoSQL, I think the primary lesson the Postgres
community can learn from NoSQL is that not everyone needs all our features, and if it can be easily accomplished, we should allow users to
trade features for some of the NoSQL desired behaviors. We already do that in some cases:

• You can improve speed by delaying or eliminating durability
  (synchronous_commit,
  fsync)
• You can reduce consistency overhead by not defining constraints
• You can use prepared queries to avoid parser and optimizer
  overhead
• Arrays can often be used to avoid join overhead
• You can store non-structured data with hstore
• You can serve stale data using multi-master asynchronous replication (Bucardo)

However, there are a few things that will be difficult to allow:

• Non-SQL access to data
• Reduced overhead by eliminating atomicity or isolation

Continue Reading »

Embracing the whole Web 2.0 thing, one of my cow-orkers has been playing with his Mac and produced a nice video walkthrough of pgAdmin, showing how to get started and use a variety of the really cool features it has to offer Postgres users.

So, at risk of this being my shortest blog post ever, here’s the video.

Thanks Gary!