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 -

  1. SELECT where userid= X and timesent >= X and timesent <= X
  2. 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?"

  • you an index for the select only ? or you want also for a delete while archive? also to specify the index we need to know the primary key or the conditions used in the qyery
    – moudiz
    Dec 29, 2016 at 12:24
  • 1
    for the first query an index on (userid, timesent) should help, for the second an index on id. I don't see any reason why the acknowledgements should be stored in a separate table.
    – user1822
    Dec 29, 2016 at 17:47
  • Following @a_horse_with_no_name: Your indexes need only = and < comparisons, I'd go for B-Tree. The other index methods won't give you any advantage for this kind of usage.
    – joanolo
    Dec 29, 2016 at 22:10
  • @Johan: I'd take a look also at Efficient Use of PostgreSQL Indexes
    – joanolo
    Dec 31, 2016 at 10:56


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.