I'm implementing a messages table with postgres (aws-rds) and I'm using golang as a backend to query the table.
CREATE TABLE:
CREATE TABLE IF NOT EXISTS msg.Messages(
id SERIAL PRIMARY KEY,
content BYTEA,
timestamp DATE
);
Here is the INSERT query:
INSERT INTO msg.Messages (content,timestamp) VALUES ('blob', 'date')
RETURNING id;
Now I want to be able to fetch a specific message, like this:
specific SELECT query:
SELECT id, content,timestamp
FROM msg.Messages
WHERE id = $1
Now let's say the user was offline for a long time and he needs to get a lot of messages from this table, let's say 10M messages, I don't want to return all results because it might explode the app memory.
each user saves his last message.id that he fetched, so the query will be:
SELECT id, content, timestamp
FROM msg.Messages
WHERE id > $1
Implementing paging in this query is feeling like inventing the wheel again, there must be out of the box solution for that.
I'm using sqlx, here is a rough example of my code:
query := `
SELECT id, content, timestamp
FROM msg.Messages
WHERE id > $0
`
args = 5
query = ado.db.Rebind(query)
rows, err := ado.db.Queryx(query, args...)
var res []Message
for rows.Next() {
msg := Message{}
err = rows.StructScan(&msg)
if err != nil {
return nil, err
}
res = append(res, msg)
}
return res, nil
How can I convert this code to be with lazy loading, that only on rows.next() will fetch the next item (and not loading all items in advance), and what about the garbage collector, will it release the memory on each iteration of the row.next()??
