Previously I wrote about how to create foreign key pointing to partitioned table.

Final solution in there required four separate functions and four triggers for each key between two tables.

Let's see how fast it is, and if it's possible to make it simpler.

First bit – performance test (for making it simpler you will have to wait some more).

To do it, I will create a schema with non-partitioned tables, copy and insert some rows. Then I will redo it on partitioned table, and we'll see how fast (or slow) it really is.

I figured I will skip doing the tests of updates and deletes, because usually one doesn't change fkey column, and delete on table that has fkeys pointing to is also rather infrequent.

Schema:

=$ CREATE TABLE users (
    id             serial PRIMARY KEY,
    username       text NOT NULL,
    UNIQUE (username)
);
=$ CREATE TABLE accounts (
    id serial PRIMARY KEY,
    user_id int4 NOT NULL REFERENCES users (id),
    balance int4 NOT NULL DEFAULT 0
);

to make the test sensible, I need to have rows in users. Let's say – 1 million rows.

Quick ruby script (saved as generate.users.rb):

#!/usr/bin/env ruby
# encoding: utf-8
 
seen = {}
File.open('/usr/share/dict/words', 'r').each_line do |l|
  l.chomp!
  l.downcase!
  next unless l.match %r{^[a-z]+$}
  seen[l]=1
end
words = seen.keys
seen = {}
 
generated = 0
 
while generated < 1000000
  new_name = words.sample(2).join(' ')
  next if seen.include? new_name
  seen[new_name] = 1
  generated += 1
  printf "%d\t%s\n", generated, new_name
end

And then:

=$ ruby generate.users.rb | gzip -9c > users.txt.gz

Generated data file with 1 million usernames, which I then loaded using

=$ \copy users FROM program 'zcat users.txt.gz'
=$ SELECT SETVAL('users_id_seq', 1000000);

With data in place, I need some tests. Luckily, it should be simple.

There will be 2 tests:

• copy : insert many (100,000) rows into accounts, using copy command
• insert : insert few (2,000) rows into accounts, using single-row insert commands

Each test is in its own file, and you can download whole set.

Finally I will run the tests files like this:

• recreate test database from scratch
• run vacuum on all tables
• run test, via: time psql -qAtX -f …, in order: copy, insert

this was repeated 5 times, and I got only the best results. Which are:

• copy test: 0.912 seconds.
• insert test: 3.334 seconds.

And now, after all this, let's redo the whole thing, but this time – using partitioned users.

Instead of simple create table, create table, copy, I now have 28 create tables, copy, 2 create functions, and 4 create triggers.

With this setup, tests took, obviously, longer:

• copy test: 10.925 seconds.
• insert test: 3.977 seconds.

Sooo.. copy was ~ 3 times slower. Insert were slower by ~ 20%.

Whether it's acceptable depends on your case.