4

Context:

Out of curiosity, I'm doing load testing for my application. And then the result there's a lot of concurrent inserts happened.

After doing the load testing on create-endpoint, I'm trying to do load testing on the Fetch endpoint, including testing the pagination. For the pagination, I'm combining two columns, id (PK with UUID v4) and created_time. Also, I've added an index for faster sorting. I'm following these solutions from here.

Problem:

Since the data was inserted concurrently, there are a few rows that have the same created_time, in my case up to 100(rows) in the same timestamp.

This is my table schema, an example

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS "payment_with_uuid";

CREATE TABLE "payment_with_uuid" (
 id VARCHAR(255) PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
 amount integer NULL,
 name varchar(255) default NULL,
 created_time TIMESTAMPTZ NOT NULL DEFAULT (now() AT TIME ZONE 'utc')
);

CREATE INDEX idx_payment_pagination ON payment_with_uuid (created_time, id);

COMMIT;

This is my query,

SELECT  * from payment_with_uuid ORDER BY  created_time DESC, id DESC LIMIT 10;

It will return 10 rows of payment, assume the data will look like this, and assume the timestamp is same until the 100th row

+-------------------------------------+--------+------------+---------------------+
| id                                  | amount | name       | created_time        |
+-------------------------------------+--------+------------+---------------------+
| ffffa567-e95a-4c8b-826c-e2be6acaeb6d| 32003  | Allistair  | 2020-05-24 21:27:10 | 
| ffff2dd6-3872-4acc-afec-7a568935f729| 32003  | James      | 2020-05-24 21:27:10 | 
| fffe3477-1710-45c4-b554-b539a9ee8fa7| 32003  | Kane       | 2020-05-24 21:27:10 | 

And for fetching the next page, this is my query looks like,

SELECT * FROM payment_with_uuid 
WHERE 
created_time <= '2020-05-24 21:27:10' :: timestamp
AND 
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7' 
ORDER BY created_time DESC, id DESC LIMIT 10;

And because of that, the pagination messed up, like some records that exist on the 1st page, may exist on 2nd, or 3rd, or any pages. And sometimes the records are missing.

Questions and Notes:

  • Is there any way to do this in a more elegant way?

  • I know using auto-increment will solve this, but choosing auto-increment id is not an option for us, because we're trying to make everything is consistent across microservice, many services already using UUID as the PK.

  • Using offset and limit will also solve this, but it's not a good practice as far as I know as this article explained https://use-the-index-luke.com/no-offset

  • I'm using Postgres 11.4

4
  • The query you give doesn't match the table structure you give.
    – jjanes
    May 24, 2020 at 13:55
  • Ah yes, I'll update it! May 24, 2020 at 14:09
  • I think now I know my problem. For the same exact timestamp, I can't use the less than equal query. So I need to add 1 second so the same exact timestamp is filtered. May 25, 2020 at 3:09
  • For example 2020-05-24 21:27:10, if I want to query for the next page that may include the same timestamp or less than that timestamp, I need to add 1 second to my query, 2020-05-24 21:27:11, is this expected? May 25, 2020 at 3:14

1 Answer 1

7
SELECT * FROM payment_with_uuid 
WHERE 
created_time <= '2020-05-24 21:27:10' :: timestamp
AND 
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7' 
ORDER BY created_time DESC, id DESC LIMIT 10;

This is wrong, but it shouldn't lead to the problem you indicate of the same row showing up on page 1, 2, etc. Rather it would result in most rows failing to show up at all, because the two filters are implemented independently. You need to implement the id filter only within ties of the created_time filter. Elegance I guess is a matter of opinion, but it seems to me that the most elegant solution is the tuple comparator similar to what you had attempted to include in your original question.

SELECT * FROM payment_with_uuid 
WHERE 
(created_time,id) < ('2020-05-24 21:27:10' :: timestamp, 'fffe3477-1710-45c4-b554-b539a9ee8fa7') 
ORDER BY created_time DESC, id DESC LIMIT 10;

Now the timestamp should really be exact, it doesn't look like yours is. How is it getting rounded to the nearest second? In my hands it looks more like 2020-05-25 09:16:29.380925-04

If for some reason you don't want to use the tuple comparator, then you need to include the timestamp twice, once for less than and once for equal to:

WHERE 
created_time < '2020-05-24 21:27:10' :: timestamp
OR  
(
    created_time = '2020-05-24 21:27:10' :: timestamp 
    AND 
    id <'fffe3477-1710-45c4-b554-b539a9ee8fa7' 
)

In addition to not being very elegant, this will probably not use the index very effectively. You could use boolean reasoning to re-write it to avoid that top-level OR, so that it can use the index, but then it will get even harder to read and understand.

5
  • Gotcha. Now I know my problem. 2020-05-25 09:16:29.380925-04 this is should be how the data looks like. I made a mistake from my application level, I formatted the timestamp rounded to the nearest second. After removing the format, the query works. Thanks for your help. May 25, 2020 at 16:39
  • @jjanes why do we need to compare the timestamp twice in the second example? Would WHERE created_time <= x AND id = y result in the same?
    – lucbas
    Mar 15, 2022 at 12:53
  • 1
    @lucbas Yes, it needs to be compared twice. Your alternative would give quite different results.
    – jjanes
    Mar 15, 2022 at 15:22
  • @jjanes IIUC your solution relies on possibility to do < comparison on UUID string/datatype. I don't know postgres, but if values in not uuid v1 or v2 type, but v3-v5, this cannot work as there is no temporal part to compare. No? Please explain. Feb 21, 2023 at 16:36
  • You can order UUIDs of any 'v'. The order might not be meaningful, but it doesn't need to be meaningful, it just needs to be stable. You only need to pick up where the previous query left off, and it doesn't matter if that is in the time dimension or just in some arbitrary ordering of random strings. Since he is already explicitly ordering by a timestamp, it wouldn't make much sense to expect the UUID to also order by time.
    – jjanes
    Feb 21, 2023 at 17:38

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.