How To Do Pagination in Postgres with Golang in 4 Common Ways

A few examples of pagination on Postgres, with Benchmark, written on Golang

Iman Tumorang
May 26 · 9 min read
Image for post
Image for post
Photo by Ergita Sela on Unsplash

Pagination On REST API

GET /payments
GET /payments?page=1 // to fetch payments in page 1
GET /payments?page=2 // to fetch payments in page 2
GET /payments?page=3 // to fetch payments in page 3
... etc
GET /payments?limit=10 // initial request for fetch payment
GET /payments?limit=10&cursor=randomCursorString // with cursor
GET /payments?limit=10&cursor=newrandomCursorString // for next page
GET /payments?limit=10&cursor=anotherNewrandomCursorString
... etc

1. Pagination with Page Number

GET /payments?page=1 // to fetch payments in page 1
GET /payments?page=2 // to fetch payments in page 2
GET /payments?page=3 // to fetch payments in page 3
... etc
Benchmark Result using Go for PageNumber pagination

2. Pagination with Offset and Limit

GET /payments?limit=10 // initial 
GET /payments?limit=10&offset=10 //fetch the next 10 items
GET /payments?limit=10&offset=20 //fetch the next 10 items again
... etc
SELECT
*
FROM
payments
ORDER BY created_time
LIMIT 10
OFFSET 20;
Benchmark Result using Go for LimitOffset pagination

3. Pagination with Auto Incremental PK of the ID

GET /payments?limit=10
GET /payments?limit=10&cursor=last_id_from_previous_fetch
GET /payments?limit=10&cursor=last_id_from_previous_fetch
... etc
SELECT
*
FROM
payments
WHERE
Id > 10
LIMIT 20
SELECT
*
FROM
payments
WHERE
Id < 100
ORDER BY Id DESC
LIMIT 20
Benchmark Result using Go for AutoIncrement pagination

4. Pagination with UUID Combined with Created Timestamp

payment.sql schema with UUID and timestamp
GET /payments?limit=10
GET /payments?limit=10&cursor=base64_string_from_previous_result
GET /payments?limit=10&cursor=base64_string_from_previous_result
... etc
SELECT *
FROM payments
WHERE created_time <= '2020-05-16 03:15:06' // created timestamp
AND id < '2a1aa856-ad26-4760-9bd9-b2fe1c1ca5aa' // this is UUID
ORDER BY created_time DESC
LIMIT 2
Benchmark Result using Go for Keyset pagination

Conclusions

1. Performances: Faster to Slower

2. Development: Faster to Slower

Code artifacts

The issues that I face when doing this

Author Suggestion


Reference


Easyread

Easy read, easy understanding.

Iman Tumorang

Written by

Software Engineer - Writer - Open Source Enthusiast - Startup Enthusiast. Reach me out in https://bxcodec.io for fast response :)

Easyread

Easyread

Easy read, easy understanding. A good writing is a writing that can be understood in easy ways

Iman Tumorang

Written by

Software Engineer - Writer - Open Source Enthusiast - Startup Enthusiast. Reach me out in https://bxcodec.io for fast response :)

Easyread

Easyread

Easy read, easy understanding. A good writing is a writing that can be understood in easy ways

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store