PostgreSQL Basics by Example | Hacker News Reader

PostgreSQL Basics by Example

darthdeus.github.io

217 pointsdarthdeus54 comments

Craig Kerstiens from Heroku periodically posts great articles about PostreSQL [1]. They're all really worth a read, especially [2] which is in the nature of the original link.

[1] http://www.craigkerstiens.com/content/

[2] http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...

First, thanks for the callout. Happy to shamelessly accept plugs, and to add to it there's a guide I curate and a weekly newsletter with interesting articles as well:

http://www.postgresguide.com

http://www.postgresweekly.com

Using Sublime as your editor for Postgres I had to add this to my ~/.zshrc:

export EDITOR='subl -w'

Also worth noting for those who are starting out with PostgreSQL: The easiest way to install it on your Mac is using Postgres.app (http://postgresapp.com).

As much I like mattt's work, I dont understand, why is this needed. brew install postgres is just as easy and in my experience Postgres.app is not well maintained and uses an older version.

I've personally helped 4 people setup Postgres on a recent model macbook with OSX Mountain Lion for Rails dev in the last 6 months. Every one has been terrible. Issue with sockets, issues with file permissions, issues with previous attempted installs, issues with setting up database users. Postgres.app just works and it is awesome.

Thats interesting, I've never had an issue with the homebrew installation. The readme details 1 or 2 lines to get everything set up for the first time.

I suppose osx built-in installation may cause conflicts.

> I suppose osx built-in installation may cause conflicts.

This is a consistent source of trouble when installing Postgres. Usually, this involves looking up the right places to modify the path and sometimes needing to change permissions/ports.

here is the one I was having,

http://stackoverflow.com/questions/12472988/postgres-could-n...

postgres app fixed it

To be honest I've spent about 4 hours last week trying to install homebrew PostgreSQL on a friends MacBook ... it took me forever to figure out why it wasn't connecting to the right socket, just because he had some things out of date :\

While it is really easy to install most of the time, I'd say the Postgres.app works well for people who aren't developers but need to use PostgreSQL.

Did you see the post installation instructions to initialize a db?

initdb /usr/local/var/postgres -E utf8

Also if you are working with rails, add host: localhost in your database.yml file and remove the user password. That should be all you need.

i have had multiple problems with brew install postgres. it installs the code just fine but you get weird errors when trying to start the server and connect to it with the psql client

Postgres.app uses the latest stable version of Postgres (9.2.4), so I don't know what you mean.

http://postgresapp.com/

Not everyone is comfortable with the command line, so this is great for people like them. Also, and it may be isolated to me, but my brew formulae get screwed up all the time, and it's a decent sized challenge to get back to working if you're only a git novice. And sometimes, all you need is Postgres, and not the overhead of brew. No sense introducing stuff you don't need.

> Not everyone is comfortable with the command line, so this is great for people like them.

This is probably "elitist" of me but I feel like someone who isn't comfortable with the command line should be learning that before they tackle Postgres.

It's a pain to upgrade using this.

For my Mac, I typically use the enterprise graphical install[1].

If you're comfortable with command line, you really can't go wrong on most Linux flavors with the great documentation on the project website[2].

[1] http://www.enterprisedb.com/products-services-training/pgdow...

[2] http://www.postgresql.org/docs/9.2/static/install-procedure....

That may be the easiest way, but if your intention is to do serious, heavy-duty work with PostgreSQL, please do yourself a favor and learn how to compile and install from source.

I've done some benchmarks with millions of rows, and a default PostgreSQL installed via apt-get on Ubuntu works fine. I doubt whether the majority of PostgreSQL users really need to install from source (especially considering how much harder it makes upgrades).

I didn't say it was faster, and I didn't say the majority needed to do it.

The reason is to be able to customize your build and/or apply patches. Tom Lane fixed a production bug I reported in hours and this got me running fairly quickly.

Where do people get this weird idea that typing "make" creates some sort of magic that makes the software faster or more stable or somehow better than having someone else type "make"?

Where did I say it was faster or more stable?

I said: "faster or more stable or somehow better". Obviously you must believe that typing make causes one of those things, or you wouldn't have made your comment.

> Obviously you must believe that typing make causes one of those things, or you wouldn't have made your comment.

Or: https://news.ycombinator.com/item?id=6246393

That doesn't support your original statement at all. If you made the statement in error, then simply say so.

It would be much better to keep it all inside a local VM.

If your purpose is to emulate your production environment fully, then yes. But if you're in the development phase weeks or months before going live, a virtual machine often has no real purpose and takes up a lot of RAM.

I disagree. Just look at the people in this thread asking how to install it. In a VM you will mimic what you will eventually do, and installation is an apt-get or yum away.

If someone is learning off of, say, Windows, their experience may possibly be quite different to what they will experience if they run this within a VM.

Just look at the people in this thread asking how to install it

They are talking about how other installation methods than Postgres.app can be difficult. Which is exactly my point: If you're on Mac and you want to get started with PostgreSQL easily, Postgres.app is the way to go.

On a somewhat related note, I'd love to see a more intermediate resource on how to really leverage PostgreSQL in your applications.

As someone who primarily works with Rails, I feel like I use about 1% of PostgreSQL's power.

Which is how Rails wants you to use PostgreSQL. Their opinion is that logic belongs in the app, not the database.

Yes, that's true. The approach has it's drawbacks though (validates_uniqueness_of being a prime example).

As much as I like Rails, I sometimes wish it were designed with the database playing more of a role than just dumb storage. Defining things like validations in Ruby is certainly nice and easy, but the database is much better suited to actually enforcing such constraints.

In general this is true, but with Rails 4, we added a lot of Postgres specific features to ActiveRecord, so you can actually take advantage of all the awesomeness Postgres has to offer.

Check out this blog post: http://blog.remarkablelabs.com/2012/12/a-love-affair-with-po...

Has the actual philosophy changed though?

When I first got to Rails it seemed that databases were considered as a bothersome but unavoidable necessity; flat files with a funny accent, instead of an essential and powerful ally in the fight against entropy and error.

In the past several years Postgres and NoSQL data stores have gained in popularity and become mainstream, and Rails now supports them well. The Rails ecosystem historically focused on MySQL, but that's not the case anymore. For example, Engine Yard now uses Postgres by default, and Heroku always has.

I basically remember head-scratching about the worthiness of exotic features like foreign keys.

Some poking around in the current documentation seems to suggest that FKs have been absorbed into ActiveRecord.

That looks mostly like you've added support for postgres "extras", not (inherent) "postgres goodness". Not that adding support for "extras" is a bad thing -- but in the context of "adding support for postgres sql" -- that seems somewhat orthogonal? (I'm not saying it is wrong for rails to approach postgres this way, just that the approach doesn't seem to change the way rails (afaik) approach databases: logic should be in rails, not in stored procedures and views (which is where it would make sense to place logic, if you design with "database first")).

Interesting link, either way.

Can I a question I haven't been able to answer myself?

In your webapp using POSTGRESQL, are you actually using SQL statements in your code to retrieve the data? Or is there some other way to get at it?

I think the search term you are looking for is O.R.M. Object Relational Mapping.

Rather than me explaining it here, the rails guides[1] give you a good overview.

[1] http://guides.rubyonrails.org/active_record_querying.html

A few ways I leverage Postgres in Rails:

- CHECK constraints. I have a gem for setting up foreign keys and CHECK constraints in Rails:

    https://github.com/pjungwir/db_leftovers

- named scopes. You can include SQL snippets like this:

    class Publisher
      scope :with_no_books, -> {
        where <<-EOQ
          NOT EXISTS (SELECT 1
                      FROM   books b
                      WHERE  publishers.id = b.publisher_id)
        EOQ
      }
    end

Of course you can do this with MySQL too, but it's one way to nicely incorporate arbitrary SQL with your ORM, so that you can take advantage of any Postgres feature you want.

- PostGIS: http://blog.daniel-azuma.com/archives/60

- hstore/json columns.

Nice intro. One great trick if you want to learn more about the system tables is `psql -e`. This will show the queries used internally by all the \d, \u, etc. commands.

Whilst not perfect, pgadmin can also be helpful. I find it particularly useful for keeping an overall view of multiple databases/servers.

http://pgadmin.org/

As someone who has spent the last few days trying to become familiar with pgsql: yes.

Don't be intimidated by all the options it offers. It makes things really easy and - more importantly imho - shows you the SQL it runs for every command.

So far it seems a great way to learn pgsql coming with some background in SQL, if you want it to be.

Bookmarked. Definitely could've used this post when I was just starting with PostgreSQL!

I'm glad to hear that :) I've had problems with this for so long that I finally decided to put all of the information together.

I'm planning to cover more things about PostgreSQL, mostly using pg_dump, pg_restore, pg_upgrade, initdb etc., just the regular things you should know when using it on your own VPS.

I would definitely follow this then. I was just about to say that I hope this develops into part 1 of a queue of topics covered in a similar way. I just started with PostgreSQL and this is very helpful.

As far as mac installs are concerned while i am fond of OSX for day to day usage you are way better putting your postgres install in a linux VM,it will reflect production better and be far easier to install.

Repeat after me: set work_mem to 'XXXGB'; set maintenance_work_mem to 'YYYGB'; swing away!

This annoys me so much about PostgreSQL. Why not have useful defaults?!

The defaults are useful in that they reflect a configuration that is appropriate to get the system running on a large variety of hardware. It's not really the responsibility of the Pg developers to guess at the "best" settings for any specific environment, because environments and configurations vary greatly.

I agree to a point, but it would be nice if there were some provided default configs for various classes of machines.

Especially defaults for the most popular EC2 instance types.

Its two dimensional because of "various classes of applications"

work_mem is a per-sort setting. So if you have 20 users connected, running a single query with 4 sorts each, that's 80 x work_mem. If it's set to GB anything, that's going to get out of hand very fast.

I recommend this guide.. it's a good start: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serve...

More like, "set shared_buffers", "set effective_cache_size"..

Also, seconding rgbrenner re: "Tuning your PostgreSQL server" article.

Reply on news.ycombinator.com