Many people are waiting for Postgres 9.0, so here is a status report about the release. Two weeks ago we completed the last 9.0 CommitFest and released 9.0 Alpha 4. We are now working on a 9.0 open items list. There are relatively few open items compared to previous major releases because there are now more developers focused on closing open items.

Once we deal with all the items on that list, and any new items that appear, we can release the first 9.0 beta. Technically, we could release a beta before dealing with all open items as long as we are sure that none of the existing open items will require cluster reinitialization (initdb).

A new version of H2 is available for download. (You may have to click 'Refresh').
For details, see the change log.
For future plans, see the roadmap.

This month we we will be giving two mini-tutorials at PgCon East 2010 on Saturday, March 27th. The topic of the talks will be, you guessed it, PostGIS. We have changed our Beyond talk to PostGIS: Adding spatial support to PostgreSQL to a beginner focus instead of an intermediate focus. Topic content will be more or less the same but focused more on people new to spatial database analysis. Our web applications talk will cater more to the web developer trying to integrate PostGIS in their web applications.

Marcus Rouhani of the Federal Aviation Administation will also be talking about the Airport GIS project and migration from Oracle to PostgreSQL.

On a somewhat related note, we also hope to be finished with all the chapters of our upcoming book this month. We just completed the first draft of our Chapter 10: PostgreSQL Add-ons and ancillary tools. After some back and forth with our editor, this will be up on MEAP, available for read and comments for early book buyers. Still two more chapters to finish after that before we get to the polishing of the text, images, layout and final print version.

Our publisher Manning is running a 50% off sale this Friday (tomorrow or is it today) on any MEAP book and they have a lot of interesting ones in the pipeline (including ours).

Waiting for PostGIS 2.0

The OSGEO just completed a recent coding sprint in New York. The New York sprint was a meeting of the minds of OSGEO people from various projects -- PostGIS, Mapserver, Geoserver, OpenLayers, GDAL, and some others were represented. Sadly we were not able to attend this one. A summary of the sprint with a PostGIS bent can be found on Olivier Courtin's New York sprint summary (Original French Version) and Olivier Courtin's New York sprint summary (Google English translation) and Paul's New York sprint summary.

Continue reading "What is New in PostGIS Land"

This is a rebuttal to depesz's charx, varcharx, varchar, and text and David Fetter's varchar(n) considered harmful. I respect both depesz and David and in fact enjoy reading their blogs. We just have deferring opinions on the topic.

For starters, I am pretty tired of the following sentiments from some PostgreSQL people:

• 99% of the people who choose varchar(x) over text in PostgreSQL in most cases are just ignorant folk and don't realize that text is just as fast if not faster than varchar in PostgreSQL.
• stuff your most despised database here compatibility is not high on my priority list.
• It is unfortunate you have to work with the crappy tools you work with that can't see the beauty in PostgreSQL text implementation. Just get something better that treats PostgreSQL as the superior creature it is.

Continue reading "In Defense of varchar(x)"

Version 1.6.3 of this database design and round-trip engineering software has been released.

I recently was asked how many fsync / sec ( and therefore durable transactions / sec) we can get on FusionIO card. It should be easy to test, let's take sysbench fileio benchmark and run, the next command should make it: ./sysbench --test=fileio --file-num=1 --file-total-size=50G --file-fsync-all=on --file-test-mode=seqrewr --max-time=100 --file-block-size=4096 --max-requests=0 run PLAIN TEXT CODE: Operations performed:  0 Read, 922938 [...]

I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on: - durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ? - do we use binary logs ( [...]

PgMDD maintenance release with several minor improvements, bugfixes, and enhanced WineHQ support.

Oracle recently announcent MySQL-5.5.2-m2 milestone, and I have a predition that MySQL-5.5 will be announced as GA on MySQL UC 2010 in April. So let's make quick on scalability characteristics we should expect from new release. I made sysbench oltp benchmarks on 10 mln rows (worth 2.5GB of data), on our Dell R900 system ( 16 cores, [...]

I have not caused a fist fight in a while, so it's time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL's key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit [...]

A month ago I mentioned a surprising number of Postgres activities in Philadelphia. One month later, those events are past but now there are more.

First, PG East is shaping up to be a big conference:

• The conference agenda looks very full, with both developer and business-oriented talks. The business-oriented focus is rather new for Postgres conferences, and I think signals more mainstream adoption of Postgres.
• It is at the Warwick Hotel. This lobby photo should give you a good idea of how nice the hotel is — we will simply have to adjust to having a Postgres conference in a fancy hotel.
• Noel Yuhanna of Forrester Research will be speaking. He is the person who authored last year's Forrester Wave report that found Ingres and MySQL as the leading open source databases (news report). You can judge for yourself how much "research" went into that report, but we will be nice to him — a bodyguard will be unnecessary.

Continue Reading »

Running many benchmarks on fast storage (FusionIO, SSDs) and multi-cores CPUs system I constantly face two contention problems. So I suspect it's going to be next biggest issues to make InnoDB scaling on high-end system. This is also reason why in benchmarks I posted previously CPU usage is only about 50%, leaving other 50% in idle state. [...]

In many contexts you need to handle user permissions to access, create or change some kind of resources. A common example is a file system, and that's what we are going to dive into in this blog post. We're going to use Ruby bindings for the Neo4j graph database to create a small - but working - example application.

Preparation

To set up the environment for this example on Ubuntu, I used the following commands:

sudo apt-get install jruby
sudo jruby -S gem install neo4j

To import the libraries, the following code was used:

require 'rubygems'
require 'neo4j'
require 'neo4j/extensions/find_path'

Heading for the node space

So user permissions, what are they all about? Obviously it's about users, and usually user groups as well. We'll abstract this away a bit and use the term principals, which can be single users or groups.

The other side of user permissions are the resources which are to be protected. In our case we'll have a file system, so there will be folders and files. Here we'll use the term content.

Let's start out building a graph to support the application from what we have gathered so far! When working with a graph it's beneficial to think in a graphy manner, so that's where we'll begin. Graphs are presumably about connecting things, so our first step is to create some relationships. Neo4j comes with a built-in reference node, which is easily accessible at all times. We use this to create our own "subreference nodes", one for principals and one for content. This is how our graph looks so far:

To create (and get) the subreference nodes, we use this function:

def get_or_create_sub_ref( name )
  result = Neo4j.ref_node.rels.outgoing( name ).nodes.first
  if ( result.nil? )
    result = Neo4j::Node.new :name => name.to_s.capitalize.gsub("_", " ")
    Neo4j.ref_node.rels.outgoing( name ) << result
  end
  return result
end

This function is then called whenever we need to use a subreference node. The important parts here are:

• ref_node: the built-in reference node
• rels: relationships connected to a node
• outgoing: the direction of the relationship (the relationships are always directed, but you can choose to ignore the direction in traversals)
• ( name ): the type of relationships to follow (the type can be ignored in traversals as well, but in our case we want to use it)
• nodes: the nodes in the other end of the relationships
• first: the first node found - there sould only be one subreference node of each type

If the subreference node isn't found, it will be created and connected to the reference node. As you can see, we're adding a property with the key name to the nodes as well, which is there solely for the purpose of visualization (the images in this post are created using Neoclipse).

Basic structure

For the principals part, we are going to connect the top-level ones to the corresponding subreference node using a PRINCIPAL type of relationship. Other than that, there's just users and groups, so let's use a IS_MEMBER_OF_GROUP relationship type to encode that. This is how that looks in the graph:

And here's the code to create it:

def new_principal( name, member_of_groups = [] )
  principal = Neo4j::Node.new
  principal[ :name ] = name
  if member_of_groups.empty?
    get_or_create_sub_ref( :PRINCIPALS ).rels.outgoing( :PRINCIPAL ) << principal
  else
    for group in member_of_groups
      principal.rels.outgoing( :IS_MEMBER_OF_GROUP ) << group
    end
  end
  return principal
end

If a new principal isn't member of any groups, it's added as a top-level principal, connected to the principals subrefererence node. In other case, it's simply added to the groups.

With Neo4j all operations on the graph have to be encapsulated in a transaction, so this is how we'll call the above function:

Neo4j::Transaction.run do
  all_principals = new_principal( "All principals" )
  root = new_principal( "root", [ all_principals ] )
  regular_users = new_principal( "Regular users", [ all_principals ] )
  user1 = new_principal( "user1", [ regular_users ] )
  user2 = new_principal( "user2", [ regular_users ] )
end

For the content part, things are very similar to the principals part. The main difference is that in this case, an item can have only a single parent item. Here's the graphical view on that:

And this is the code to create the structure:

def new_content( name, parent = nil )
  content = Neo4j::Node.new
  content[ :name ] = name
  if ( parent.nil? )
    get_or_create_sub_ref( :CONTENT_ROOTS ).rels.outgoing( :CONTENT_ROOT ) << content
  else
    parent.rels.outgoing( :HAS_CHILD_CONTENT ) << content
  end
  return content
end

Similar to how the principals were created, this is the code to create the content data:

Neo4j::Transaction.run do
  root_folder = new_content( "Root folder" )
  temp_folder = new_content( "Temp", root_folder )
  home_folder = new_content( "Home", root_folder )
  user1_home_folder = new_content( "user1 home", home_folder )
  user2_home_folder = new_content( "user2 home", home_folder )
  a_file = new_content( "MyFile.pdf", user1_home_folder )
end

At the core

Now that we have the basic structure in place, what's left regarding our data is a small but crucial part: the permissions information! We're using a simple scheme: adding security relationships with optional boolean flags for read and write permission. Not much to say here, this is what we want the full graph to look like (click for a bigger version):

A small function will help us add the security information:

def apply_security( content, principal, map_with_flags )
  security_relationship = Neo4j::Relationship.new( :SECURITY, principal, content )
  map_with_flags.each_pair {|key, value| security_relationship[ key ] = value}
end

It's time to add the security data:

Neo4j::Transaction.run do
  apply_security( root_folder, root, { "w" => true } )
  apply_security( root_folder, all_principals, { "r" => true } )
  apply_security( temp_folder, all_principals, { "w" => true } )
  apply_security( user1_home_folder, regular_users, { "r" => false, "w" => false } )
  apply_security( user1_home_folder, user1, { "r" => true, "w" => true } )
  apply_security( user2_home_folder, user2, { "r" => true, "w" => true } )
end

To check the permission for some action by an actual principal for some content, there's some work to do. This is the algorithm we use to retrieve a permission flag:

1. Move from the content node and upwards through the file system structure and investigate each level for permission information.
2. On each level, see if there are any principals related to or identical with the principal concerned.
3. Make sure to use the permission information from the principal closest to the principal concerned.
4. If permission information was found, return it; otherwise, continue traversing to the next level in the file system.

In the code for this, we'll use a function named depth_of_principal() to calculate the distance between the principal we have traversed to and the principal concerned. More on that later, here's the code to check the permissions:

def has_access( content, principal, flag )
  for current_content in content.incoming( :HAS_CHILD_CONTENT ).depth( :all )
    lowest_score = nil
    lowest_modifier = nil
    for rel in current_content.rels.incoming( :SECURITY )
      rel_principal = rel.start_node
      if !rel[ flag ].nil?
        score = depth_of_principal( rel_principal, principal )
        if !score.nil?
          modifier = rel[ flag ]
          if lowest_score.nil? || score < lowest_score ||
            ( score == lowest_score && modifier )
            lowest_score = score
            lowest_modifier = modifier
          end
        end
      end
    end
    if !lowest_modifier.nil?
      return lowest_modifier
    end
  end
  return false
end

Here's our function to check the distance between principals (and to see if they're on the same path at all).

def depth_of_principal( principal, reference_principal )
  result = reference_principal.outgoing( :IS_MEMBER_OF_GROUP ).depth( :all ).path_to( principal )
  return result.nil? ? nil : result.size
end

Finally, we want to see that everything works, so here's a utility function to print permission information:

def print_has_access( content, principal, flag )
  print principal[ :name ] + " +" + flag.upcase + " access to " + content[ :name ] + "? " +
    has_access( content, principal, flag ).to_s + "\n"
end

And here's how to use the function:

Neo4j::Transaction.run do
  print_has_access( home_folder, root, "w" )
  print_has_access( home_folder, user1, "w" )
  print_has_access( a_file, root, "r" )
  print_has_access( a_file, user2, "r" )
  print_has_access( a_file, user1, "w" )
end

Next steps

The full source code is found here

Here's a few useful resources to help you on your way:

• Neo4j Wiki
• Neo4j.rb at github
• mailing lists - good places to get help
• The top 10 ways to get to know Neo4j

Thanks for reading - any feedback is welcome!

Yesterday was the first meeting for the FOSS STHLM "group" - a (very) loose group of FOSS interested people in the Stockholm region. We met in a lecture hall at the Stockholm University in Kista north of Stockholm, for a couple of hours of short presentations. The lineup was very nice: cool embedded stuff? yup, rockbox. General linux? Yup, upstart. Debian-specific? Of course. OpenSource Sweden? yeah. Curl? What else did you expect with Daniel as one of the organizers? PostgreSQL? Yeah, why else would I be blogging this?

Our allocated time was short, so the setup was many short talks. It actually worked a lot better than I thought it would, but it's still very hard to convey something useful in just 20 minutes.

I had a hard time figuring out what I should focus on, so I did a split into two parts (perfect strategy - if you have too little time to do one thing good, do two things in the same time instead...). I started with a section about "things to think about if you're switching to PostgreSQL or trying it out for the first time" - things like the very most basic config parameters that you always have to touch. And of course the classic - "ident authentication failed" issue that hits everybody on RedHat or Debian platforms at least (which is most of the users - definitely in this crowd). It's hard to do much in 12 minutes, hopefully it got some people interested.

I followed that with a very very very short version of "what's coming up in 9.0". Once again, I focused on one of my personal favorite features, which is Exclusion Constraints. While this is often listed as one of the cool things in 9.0, sometimes I feel that too much focus is on streaming replication and hot standby. Don't get me wrong, these are very good and very much needed features. But Exclusion Constraints is a real eye-opener. All databases (including PostgreSQL, of course) have replication - this is "just another way to do it". Yes, a very important and good way to do it, but it's still not something brand new. Exclusion Constraints is something that's fundamentally new. And it's a brilliant example of how PostgreSQL is moving the goalposts forward. Oh, and it's really useful and cool, of course! (and it'll be even better when we have the period datatype, or something similar, in 9.1!)

In summary, I think it was a great event. Big thanks to all those who helped make it happen! Hopefully we can follow it up with many more in similar ways - perhaps mixing these many-short-talks with some more focused discussions on specific areas or technologies. Time will tell...

*Update*: For those who asked, there were somewhere around 150 people in the sessions.

The fourth alpha release for PostgreSQL version 9.0, 9.0alpha4, is now available. This alpha contains several new major features added since the previous alpha. Please download, install, and test it to give us early feedback on the features being developed for the next version of PostgreSQL.