PostgreSQL "likes" table design - selecting in order of incrementing ID is optimal?

Question

I’m brand new to SQL today and I'm designing a sole table which will be used to load likes of a post, in order, n at a time. Ex: Load the first 10 likes for a post, later load the next 10, etc.

I'm curious if this table design and query will be optimal? (All other data is in a NoSQL database ~ total count of likes is not needed).

To be more specific; will ORDER BY likeID and WHERE likeID > (starting point) slow down the query or use unnecessary resources? (the likeID will auto-increment, but some likes may be deleted/removed from the table at some point. There may be millions of likes recorded in this table).

The postLikes table:

postID: string
userID: string
username: string
timestamp: int
likeID: uniqueID (int) - increments every like

A user loads the first 2 likes for a post:

SELECT username, userID, likeID 
FROM postLikes 
WHERE (postID = “a1b767eae” AND likeID > 0)
ORDER BY likeID ASC 
LIMIT 2

returns:
[
   {username: "user6", userID: "SHi29s29", likeID: 324},
   {username: "user33", userID: "bsSU4s83", likeID: 1089}
]

Then the user loads the next two likes for the same post:

...

WHERE (postID = “a1b767eae” AND likeID > 1089)
ORDER BY likeID ASC LIMIT 2

returns:
[
   {username: "user8", userID: "Bsh292he", likeID: 2934},
   {username: "user543", userID: "sjXks28S", likeID: 10354}
]

Looks mostly OK to me. You might consider using OFFSET in conjunction with LIMIT to page through results so you don't have to keep track of the likeID. See docs [here](https://www.postgresql.org/docs/current/queries-limit.html). — bfris, Jan 28 '22 at 22:42

Erwin Brandstetter · Accepted Answer · 2022-01-29T02:06:26.623

The pivotal ingredient for performance will be a matching multicolumn index:

CREATE INDEX ON post_likes (post_id, like_id);

With index columns in this order. See:

If the only other column in the SELECT list will be username, consider a covering index (requires Postgres 11 or later) like:

CREATE INDEX ON post_likes (post_id, like_id) INCLUDE (username);

And keep your table vacuumed to allow index-only scans. See:

Oh, and don't use CaMeL-case identifiers in Postgres. See:

Are PostgreSQL column names case-sensitive?

PostgreSQL "likes" table design - selecting in order of incrementing ID is optimal?

1 Answers1