Just a few days ago I was surprised by what someone was doing with their database, and not in the typical horrifying travesty against mankind. Rather, it was a feature that while familiar with I’d never seen anyone fully take proper advantage of - COMMENT or describing tables. Postgres has a nice facility for you to provide a description for just about anything:

• Table
• Column
• Function
• Schema
• View
• Index
• Etc.

If you’re deciding what to put in Postgres and what not to, consider that Postgres can be a perfectly good schema-less database. Of course as soon as people realized this then the common comes a question, is hstore or JSON better. Which do I use and in what cases. Well first, if you’re not familiar check out some previous material on them:

• hstore on PostgresGuide
• hstore in Postgres docs
• hstore with Django
• JSON datatype
• JavaScript support in Postgres

If you’re already up to date with both of them, but still wondering which to use lets dig in.

Earlier today on an internal Heroku group alias there was a dataclip shared. The dataclip listed off some data grouped by a category, there was a reply a few minutes later with a modification to the query that used the crosstab function to pivot directly in SQL. There were immediately several reactions on the list that went something like this:

While a mostly simple function in Postgres (there are a few rough edges), it really is all too handy. So here it is in action. Taking some data that looks like

• row identifier, in this case date
• category grouping, in this case OS
• value

Even from intial reviews of my previous post on expression based indexes I received a lot of questions and feedback around many different parts of indexing in Postgres. Here’s a mixed collection of valuable tips and guides around much of that.

Unused Indexes

In an earlier tweet I joked about some SQL that would generate the SQL to add an index to every column:

# SELECT 'CREATE INDEX idx_' 
    || table_name || '_' 
    || column_name || ' ON ' 
    || table_name || ' ("' 
    || column_name || '");' 
  FROM information_schema.columns;
                      ?column?
---------------------------------------------------------------------
 CREATE INDEX idx_pg_proc_proname ON pg_proc ("proname");
 CREATE INDEX idx_pg_proc_pronamespace ON pg_proc ("pronamespace");
 CREATE INDEX idx_pg_proc_proowner ON pg_proc ("proowner");

The reasoning behind this is guessing whether an index will be helpful can be a bit hard within Postgres. So the easy solution is to add indexes to everything, then just observe if they’re being used. Of course you want to add it to all tables/columns because you never know if core of Postgres may be missing some needed ones

Update theres a more recent post that expands further on where to start optimizing specific queries, and of course if you want to dig into optimizing your infrastructure High Performance PostgreSQL is still a great read

For many application developers their database is a black box. Data goes in, comes back out and in between there developers hope its a pretty short time span. Without becoming a DBA there’s a few pieces of data that most application developers can easily grok which will help them understand if their database is performing adequately. This post will provide some quick tips that allow you to determine whether your database performance is slowing down your app, and if so what you can do about it.