postgresql search_path with ruby on rails

I studied (amongh other things) electrical engineering at Univsrity. One of the lectures said that you only need one forumal to master the subject: V=IR. However, the lecture warned, you have to know that forumla inside out.

Indeed, you could argue that V=IR is a simplification of Maxwells law, so V=IR not only covers you for EE, but a large chunk of physics (astro, plasma for example) as well.

Some times, I think MVC frameworks are similar.

Anyways... .I'm finding myself using Rails today. I've used it before with some success but didn't like the way it wanted to dominate the DB. My current thinking is that this approach is alright unless you want to integrate a legacy DB.

I'm integrating a legacy DB. It's in PostgreSQL and it uses schema namespaces. Namespaces are really worth the effort. They look great if you use PgAdminIII.

I struggled for a hour or so with my namespaces and then I discovered that I could include a search_path in ./config/database.yml. Now everything is working well again.

discovering namespaces in postgresql

I've been working on a project with a friend. We're using PostgreSQL for our db...

I thoughtfully prepared the DB locally. I then did a complete dump of it and gave it to my chum...

However, I hadn't anticipated a few things:

1. I didn't tell my friend the username I had used when creating the database. The PostgreSQL dump had helpfully included a bunch of 'ALTER TABLE, OWNER TO' commands. If you haven't anticipated this - and the user doesn't exist - you will end up with a bunch of problems when ingesting the dump.
   Proposed solutions:
   
   
   1. Create a user with the name recorded in the dump file before ingestion
   2. when doing a pg_dump, add the --no-owner flag.
      
   
   
   
2.  I didn't tell my friend that I have used schema in the db. Once they had got over the first problem, they were greeted with an apprently empty database! If you don't set your search_path to add the relevant schema names, \dt will tell you 'no relations found'
   Proposed solutions:
   
   
   
   1. Tell my friend the names of the schema that i've used - maybe even in the form of a SET search_path=newschema,"$user",public; command.
   2. Let my friend grep the dump file for 'search_path'.
      
   3. Let my friend figure out the relevant names of the schema by having them do a SELECT * FROM pg_namespaces;
      
   
   
   

I think I could improve on the namespace discovery query, possibably by just suggesting that my friend does a SELECT * FROM pg_tables; which seems to reveal more useful information.

UPDATE: after some more playing around, I've discovered that I'll will save myself alot of time by editing the dump file to remove all the 'alter to' commands. One it is ingested, the resulting db requires alot of effort to manipulate all the permissions.

PostgreSQL row overhead

As mentioned previously, I'm in currently planning a +1 billion row ingestion. I've begun to examine the on-disk storage requirements. Memory will be treated in a future post.

One of the properties of the dataset which I'm ingesting is that it is time stamped. Infact, there is not too much else other than timestamps. With one obvious representation, total cost of the data in a row is ~ 8+4+4+4+4 = 22 bytes.

However, what I didn't appreciate was that the overhead which PostgreSQL imposes on a row is ~34bytes! This figure has improved slightly in 8.3, but not significantly enough.

So a couple of options occur to me: use a data base with a smaller row overhead (MySQL?) or redesign the tables.

I've plumped for the latter. Basically I'm working the tables into a form where they are much squarer. Reducing the row count and increasing the amount of data per row. I'm using arrays to increase the amount of data.

This redesign has a cost, and without exact requirements from our users it's too early to tell if this decision will return to haunt me...

perl DBI vs c libpq PostgreSQL ingestion

I'm planning to ingest ~1B rows into a PostgreSQL data base.

There are some interesting storage requirements here, but in the first instance I've been working on getting ingestion throughput up.

For my trial ingestion of 5M rows, i've found libPQ to be roughly twice as fast as Perl DBI.

Some notes:

• indexing is omitted during ingestion.


• libpq included data type hints, my Perl code, using DBI, didn't


• each row ingested included a timestamp, and a few (<5) numeric values


• running multiple ingestion process improved throughput, although less than linearly (for <4 cpus). I.e. 2 CPUs ~ doubled throughput, but it tailed off after that.

another day another incantation

I want to use namespaces within my PostgreSQL database (schema in PostgreSQL world). However, they are not currently supported within the Catalyst scripts which will automatically build catalyst::schema automatically.

So the solution is to call Schema::Loader 'manually' with this incantation:

perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("AddressBook::Addresses", { debug => 1, db_schema => "addressStuff" }, [ "dbi:Pg:host=localhost;dbname=testdb", "testuser", "123" ])'

so there you have it: thanks to the irc crew for sorting this out... and they pointed out that cross schema querying will require some jiggery pokery when I get to that...