I want to have a table in PostgreSQL with these properties:
- One column with an integer
- Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be deleted by a WHERE clause on the integer. The table is used like a set.
- Efficient ORDER BY+LIMIT on the column/rows, and DELETING with a WHERE-clause for that integer
- INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID. (That's why duplicates must be allowed, the check for primary keys would cause more disc seeks and can fail transactions and cause deadlocks)
- INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks.
- There shall not be additional btree files for this table, so no secondary indexes
- The rows should occupy not much space, e.g. have no OIDs, so that many fit in one page.
I cannot think of a solution that solves all of this.
The easiest thing would be a PRIMARY INDEX on that one column. But this won't allow duplicates.
Also I could use a secondary index, but this would cause more operations per INSERT and more disc access. I am really bound by disc seeks here.
Currently my best solution would compromise on the last bullet point: I could just a PRIMARY KEY covering the integer column and also a dummy column, like an OID, a timestamp column or a SERIAL column. Then every row (=primary key) would be unique. But this would increase disc space and reduce the amount of rows per page.
Is there any chance of having a kind of primary key together with duplicates? Can I control what the main btree file on disc is organized by?
(I posted a related question like this question on StackOverflow, but I was very unclear about my goals. I hope this doesn't count as spam, this question here is completely different)