Tag: PostgreSQL

PostgreSQL not starting after Yosemite upgrade?

You had brewed PostgreSQL and can’t get it to start after the Yosemite upgrade? Check the logs and see if you get the following error.

FATAL:  could not open directory "pg_tblspc": No such file or directory

If so, then you need to create a few directories that went missing as part of the upgrade.

cd /usr/local/var/postgres
mkdir pg_tblspc pg_twophase pg_stat_tmp

After running the above commands try starting PostgreSQL and it should start. Thanks to this Stack Overflow post for the details.

Executing a lot of SQL queries with Django’s db API? Watch out for one thing.

I recently had the need to write an indexer that processes a lot of text files and creates a huge number of database rows (19 million+). This is a background script for an internal django website so I ended up using the standard django psycopg2 library, but tried to minimize the overhead by executing queries directly using the db cursor. Even after this consideration and special ‘del’ statements to clean things out after regular intervals, to my surprise, the process was still taking up a lot of memory. So, I tweeted about this experience. Daniele Varrazzo, one of the authors of psycopg2, tweeted back with more info.

Read full post...

Migrating MySQL data to PostgreSQL

I just finished posting a new article about migrating a django application database from MySQL to PostgreSQL. This article is very technical and only covers the actual migration steps (i.e. does not explain why I made the move). Check it out if you are interested or curious.

Migrating MySQL data to PostgreSQL

Performance difference between varchar and text in PostgreSQL

I am one of the people that does read the documentation. My business partner, Jake, asked why use varchar with an artificial limit for fields that don’t really have a limit. My initial thought was because of performance benefits, but I had never verified this. So, I spent a few minutes to verify this and guess what I find in the PostgreSQL documentation.

… If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)

It continues on and says…

Tip: There are no performance differences between these three types, apart from increased storage size when using the blank-padded type, and a few extra cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead.

Exactly what I was looking for! This is what I call great documentation.

I have used MySQL in the past, but my experience with PostgreSQL has been awesome. Great database that scales relatively well (we will find that out soon :)), supports most of what you expect from a db, and has good documentation!