How Big is My Database?

Here’s a nice snippet that can be used in a PostgreSQL database to output the current database’s size. SELECT pg_size_pretty(pg_database_size(current_database())) AS human_size,        pg_database_size(current_database())                 AS raw_size; That statement will output something along the lines of: human_size | raw_size ————+————– 181 GB | 193841573064

A Toy Story Based Mnemonic Device for has_one

Here’s an awesome Toy Story inspired mnemonic device for remembering the proper use of Rails’ `has_one` associations. Andy “has_one” Woody. Woody “belongs_to” Andy. Where is the foreign key? On Woody’s sole! Credit goes to Stack Overflow user “mike” from a comment on a related topic.

Getting the pg Gem Installed with Postgres.app

I use Postgres.app to run a local Postgres server while doing development work and it’s great. Postgres.app basically provides a completely contained pg server that’s ready to go out of the box — just launch the app and you’re good to go. Unfortunately because it’s a self contained app things are installed in non-standard places. […]

Hartl’s Description of a Database Index

Today at AppAcademy we have gone through almost all of Michael Hartl’s rails tutorial.  I just wanted to quote Hartl explanation of what a database index is and how it works.  I love the analogy because it’s probably the most simplistic examples I’ve probably ever seen: Putting an index on the email column fixes the problem. To […]

PostgreSQL, Postgres.app and a Gotcha on Mac OSX Lion

I recently setup my development system from scratch and one of the tasks is installing PostgreSQL as my apps are almost always deployed to Heroku and they <3 PG. As you may or may not know, Lion ships with a castrated Postgres installation which (if you don’t already know this (!!!!)) can make it challenging […]

PostgreSQL Backup and Restore Commands

Just a quick note: Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql} Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql} Lifted from this great post @ TGS

The Parallel Gem and PostgreSQL (oh, and rails.)

Some Preface An app I’m working on has a rake that that basically gathers data from the web.  Doing things serially is really REALLY slow and to get around this I started using the (fantastic) parallel gem by Michael Grosser. This gem is great — it’s simple to use and in something like 3 minutes […]

Migrating Data From MySQL to PostgreSQL

A Little Back Story… Migrating from MySQL to PostgreSQL is not as straight forward as it sounds.  As you may or may not know, MySQL’s ‘mysqldump’ utility comes with a “compatible” option where you can specific “postgresql” but the catch is this:  the resulting SQL is not properly escaped and will need it’s data types […]