I have a table in which I will store messages (as in chat messages).
The table will be appended to a LOT, rows being inserted as messages fly by, much more often than entries will be updated.
Older entries from the table will be archived daily (rows deleted), hopefully during quiet periods.
The table will also be read from but less than it will be written to - apps only need to read when push notifications went missing, though managers may want to review messages sent by employees for example during legal proceedings)
So what is the right index type for a write heavy table that will be read a fair amount but less often than it is written to, but should also be fairly efficient for delete operations? Which of the standard B-tree, hash, GiST, SP-GiST, GIN, and BRIN is the most optimal for a table that will mostly be INSERTed and also batch DELETEed from, but needs to still perform reasonable for SELECTS and is too large for sequential scans.
The DBMS is PostgreSQL 9.5.
Three columns of the table will be used for select, being the timestamp, the recipient (userid) and also the rowid (artificial PK) column.
id UUID userid UUID FK->users(id) timesent TIMESTAMP message VARCHAR
Rows does not need to be updated once inserted.
As a bonus question: I forsee a future business requirement to also store read acknowledgements for messages, which if stored in the same table would mean updates to existing rows. I'm leaning to storing those in a separate table dedicated to that purpose though, but open to suggestions on how to implement that.
Two type of retrieval are required -
SELECT where userid= X and timesent >= X and timesent <= X
SELECT where id = X
I assume I would want an index on each of id, userid and timestamp. I further think partitioning to ease archival as described in the Postgres Manual and in this blog post appears like it would help very much in this situation.
But now I have even more questions: besides the "What type of Index should I use on each of the select columns" I now also want to know "Do I implement constraints and indexes on the master table or on the partitions?"