PHSgeek – Learning Postgres

If you  want to do anything meaningful with modern web frameworks such as Django, you have to develop some familiarity with databases.  A few goals to start off with are:

  • Work through a tutorial, so you gain a little experience using SQL directly:
    • Create a database
    • Fill your database with information
    • Query your database to get information back out
  • Read a little about database design
    • Understand the goals of relational databases
    • Understand the first few normalization forms

Why Postgres?

Briefly, everyone is using it for new projects.  Postgres is an open source database, and there are a significant number of resources for using it well.  A few years ago we would have used MySQL, but since MySQL was taken over by Oracle it has lost some favor in the open source world.  It doesn’t make a whole lot of difference at this point; if you work in the programming field, you will work with many different databases during your career.

Installing Postgres, and setting up your system

These instructions are adapted from here.  If you are just learning about databases in general, you shouldn’t need to visit that site.  If you are planning to use your postgres installation with django, you will want to read the rest of those instructions.

sudo apt-get install postgresql

This will install Postgres 9.1 on Ubuntu 12.04.  We need to create a password for the root user of postgres.

sudo passwd postgres

Now we can switch users.  Normally we use an ubuntu machine as the user we created when we installed ubuntu.  We need to “become” the postgres root user.  Once we have become the postgres user, we can switch into a postgres terminal session, called a template:

su postgres
psql template1

When you are finished using postgres, you will need to quit the postgres session and switch back to your normal user account.  You can exit the postgres session by pressing ctrl-D, or by entering “\q”.  Then enter “su your_ubuntu_username” and you will be in a standard terminal session.

A Postgres Tutorial

The official Postgres site has a decent tutorial to get you started.  If you have never used a database before, a lot of it will probably go over your head.  But don’t be afraid of playing with the commands; you will see some SQL and read about some database concepts.

Database Concepts

You can spend your life learning the ins and outs of database structures.  I will aim to curate a good set of introductory articles here.

What is a database?  Most people have heard about databases, but very few people understand what they look like internally, or how they are designed.  It is important to understand what kind of thinking you need to do in order to design a database.

http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html:  This site uses a simple shop as an example for thinking about data.

Normalization:  Normalization refers to the process of refining your database structure.  Briefly, we want to make sure information is not repeated in our database.  These concepts are not obvious, but once you start to design a few databases they begin to make sense.

http://phlonx.com/resources/nf3/:  This site uses an invoice example to cover the first three normal forms.

ACID:  Databases aim to be atomic, consistent, isolated, and durable.  Extremely large databases can not have all of these characteristics perfectly while maintaining speed.  Figuring out how to balance these needs in large databases is interesting work!

http://en.wikipedia.org/wiki/ACID:  Wikipedia has a decent introduction to ACID characteristics of databases.

Conclusion

If you come across better resources for beginners, please let me know.  If you are learning about databases, use this as a starting point, but try to find someone who has designed databases who will be happy to answer your questions.  There are a lot of deep concepts here, and having someone who can help give you a perspective on this can be really helpful.

Advertisements

About ehmatthes

Teacher, hacker, new dad, outdoor guy
This entry was posted in programming and tagged , , , , , . Bookmark the permalink.

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