Skip to main content

Craig Kerstiens

Category: PostgreSQL

To all categories

Documenting your PostgreSQL database

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.
2013-07-29

hstore vs. JSON - Which to use in Postgres

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:

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

2013-07-03

Pivoting in Postgres

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:

mindblown

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
2013-06-27

Postgres Indexing - A collection of indexing tips

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.
2013-05-30

Understanding Postgres Performance

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.
2012-10-01