7D7W: Week 1 Summary – PostgreSQL

As discussed, I’m going through the 7 Databases in 7 Weeks book. This week was PostgreSQL.

I went through all the content and did this on a Mac. I am somewhat spoiled coming from a Windows environment used to very mature products like SQL Server Management Studio. I guess that’s the first thing I’d like to talk about. When I call SQL Server “mature”, while PostgreSQL is this workhorse that’s been around for decades – why is its’ tooling clunky?

It runs on tons of platforms, and I couldn’t really find any decent tools for it – again, nothing approaching a SQL Management Studio, with it’s intellisense, etc. On Windows, there is a nice Windows-based editor, but not as fully-featured.

It just seems to me that they did all the hard work – they built a great database, now do the fun and easy stuff of making it easy to use by the developer!  …but I digress.

Ok, so what did I get out of this week? Well, syntax-wise, it’s very similar to most other RDBMS’s. It does require a semi-colon at the end of a statement, but aside from that the CREATE, SELECT, INSERT, UPDATE, DELETE statements were all pretty much the same as SQL Server. Joins are the same. Creating a stored procedure was different, but that’s about all that stood out to me.

What I did really like is they went into some detail about how Fuzzy Searching works (using those plug-ins that the book has us install). Fuzzy Searching is the notion that you can misspell things and the database still somehow find a close match. One of the approaches they described is the from the “pg_trgm” module. It breaks your search string into “trigrams”, or 3-consecutive-letter groups, and uses those tri-grams to try to find matches. For example:

mx3B30

I thought that was pretty slick! Other RDBMS’s do that, but I just didn’t really understand how these sorts of searches worked internally. The last, Day 3 chapter goes into detail for a few other approaches too, and also include “cube” support for visualizing data easier.

Bottom line for me:
If the budget didn’t allow for SQL Server, this seems like the second runner up. The management tool on Windows looks nice, and there is native ADO.NET and Entity Framework support for connecting to it too. Since I mostly working in .NET, that satisfies my needs. And yeah, this is a battle-proven work horse that’s been around in it’s current form since 1996 (and originally developed at Berkeley in the early 1970’s) – so you can’t really go wrong.

PostgreSQL also stands out because you can do some really cool things like add additional languages to it (like Python, Perl, etc). There’s an example in there where they overwrote the functionality of an index to make searching better. Since it’s open-source too, it’s likely to have wilder stuff in there, right!?

So bottom line, this chapter covered a lot of RDBMS stuff I already knew, but exposed me to this particular database server, which is pretty cool! This, in my book is pretty the open-source equivalent of what Microsoft SQL Server is.

Next stop: Riak…
Coming up next week the book has me exploring the Riak database. I’ve never heard of it before this book and know nothing about it – so that should be fun!

Posted in 7D7W, Professional Development, Uncategorized
One comment on “7D7W: Week 1 Summary – PostgreSQL
  1. […] and 7 Languages in 7 Weeks books (I know, I’m way behind – I’ve been busy dangit!). In there, for PostgreSQL, I learned about trigrams and how they use those to do “fuzzy searches”, or inexact […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 5 other followers

%d bloggers like this: