Saving ActiveRecord in PostgreSQL without id

I have a small table that is used to temporarily queue requests, as such it does not have an id field (or any other primary key), because it doesn't make sense to have one.

Sure about that? You won't be accessing specific/individual entries for any reason whatsoever..?

How will you be looking them up without a key, and what do you gain by not having one?

Isak

yaman 666 wrote:

How will you be looking them up without a key, and what do you gain by not having one?

There're 2 unique keys for that. Since entries are continuously entered/deleted - that would be continuous auto-incrementing and deletion in the sequence table, thousands times a day, which is really not needed.

So is there any way to actually make ActiveRecord save without needing an id?

http://compositekeys.rubyforge.org/

class TempRequest < ActiveRecord::Base   # set_primary_keys *keys - turns on composite key functionality   set_primary_keys :pk1, :pk2 end

> How will you be looking them up without a key, and what do you gain by > not having one?

There're 2 unique keys for that. Since entries are continuously entered/deleted - that would be continuous auto-incrementing and deletion in the sequence table, thousands times a day, which is really not needed.

Sequences aren't that expensive. If you did benchmark and find a bottleneck, I doubt the sequence and one additional index is why.

If you aren't already, make sure you bulk all statements that belong together in a transaction block.

If the amount of insert/deletes is large, consider tweaking autovacuum settings for that table, and perhaps increase max_fsm_pages to keep track of all the dead tuples.

So is there any way to actually make ActiveRecord save without needing an id?

You don't need an id column no, but you do need a PK. See AR::Base.set_primary_key. Be aware that you need to use YourModel#id to refer that column.

There are also various plugins you could try if you want a composite PK.

IMO you really shouldn't go off the beaten path unless you have a really good reason to, but YMMV..

Isak