Início / dba / Perguntas / 267794
Accepted
Iman Tumorang
Asked: 2020-05-25 05:34:21 +0800 CST

Como fazer paginação com UUID v4 e hora criada em dados inseridos simultâneos?

  • 772

Contexto:

Por curiosidade, estou fazendo testes de carga para minha aplicação. E então o resultado é que muitas inserções simultâneas aconteceram.

Depois de fazer o teste de carga no create-endpoint, estou tentando fazer o teste de carga no Fetch endpoint, incluindo testar a paginação. Para a paginação, estou combinando duas colunas, id (PK com UUID v4) e created_time. Além disso, adicionei um índice para uma classificação mais rápida. Estou seguindo essas soluções daqui.

Problema:

Como os dados foram inseridos simultaneamente, existem algumas linhas que possuem o mesmo created_time, no meu caso até 100(linhas) no mesmo timestamp.

Este é o meu esquema de tabela, um exemplo

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;

Esta é a minha dúvida,

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

Ele retornará 10 linhas de pagamento, assumirá que os dados ficarão assim e assumirá que o carimbo de data/hora é o mesmo até a 100ª linha 

+-------------------------------------+--------+------------+---------------------+
| 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 |

E para buscar a próxima página, esta é a aparência da minha consulta,

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;

E por causa disso, a paginação bagunçada, como alguns registros que existem na 1ª página, podem existir na 2ª, ou 3ª, ou em qualquer página. E às vezes os registros estão faltando.

Dúvidas e Notas:

  • Existe alguma maneira de fazer isso de uma maneira mais elegante?

  • Eu sei que usar o incremento automático resolverá isso, mas escolher o id de incremento automático não é uma opção para nós, porque estamos tentando fazer com que tudo seja consistente no microsserviço, muitos serviços já usam UUID como PK.

  • Usar deslocamento e limite também resolverá isso, mas não é uma boa prática até onde eu sei, pois este artigo explicou https://use-the-index-luke.com/no-offset

  • Estou usando o Postgres 11.4

postgresql pagination

1 respostas

  1. Best Answer
    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;

    Isso está errado, mas não deve levar ao problema que você indica da mesma linha aparecendo na página 1, 2, etc. Em vez disso, faria com que a maioria das linhas não aparecesse, porque os dois filtros são implementados independentemente . Você precisa implementar o filtro id apenas dentro dos laços do filtro created_time. Elegância eu acho que é uma questão de opinião, mas me parece que a solução mais elegante é o comparador de tuplas semelhante ao que você tentou incluir em sua pergunta original.

    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;

    Agora o timestamp deve ser realmente exato, não parece que o seu é. Como ele está sendo arredondado para o segundo mais próximo? Em minhas mãos parece mais2020-05-25 09:16:29.380925-04

    Se, por algum motivo, você não quiser usar o comparador de tuplas, precisará incluir o carimbo de data e hora duas vezes, uma para menor que e outra para igual a:

    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' 
)

    Além de não ser muito elegante, isso provavelmente não usará o índice de forma muito eficaz. Você pode usar o raciocínio booleano para reescrevê-lo para evitar esse OR de nível superior, para que ele possa usar o índice, mas ficará ainda mais difícil de ler e entender.

    • 6

relate perguntas