postgres rolling update index time series - ghdrako/doc_snipets GitHub Wiki

• https://stackoverflow.com/questions/42487658/partial-index-on-timestamp-against-current-time Ciekawy sposob bez potrzeby indeksu jesli na id tranzakcji jest sekwencja i da sie przewidziec ile wartosci wstecz potrzebujemy - go trx_id jest zaindekspowana jako pk

select now()- inserted_at >= '5 minutes'::interval
from events 
where id > (currval('events_id_seq') - 5*(1000000/30))

Partial index on last 5 minutes will be in need of rebuild every some time. You can build it concurrently (as you relation is in intensive use) with cron, dropping old indexes. Such approach would give you faster selects on last inserted data of course, but consider the fact that at least every 5 minutes you have to rescan table to build short partial index.

The workaround is math - you can split index build in stages (as function):

select now()- inserted_at >= '5 minutes'::interval
from events 
where id > (currval('events_id_seq') - 5*(1000000/30))

that is get id lower then last id value minus approximate inserted in last 5 minutes.

If the result is true then build index in dynamic query with same math, if not, enlarge the step.

This way you scan only PK to build index on timestamp - will be much cheaper.

Another point - if you apply such calculations, you might not need partial index at all?..

Problem potrzebujemy dla kart cred i charge wygenerowac tx za poprzedni dzien (tx_date) ewentualnie jeszcze do 30 dni sie cofnac jeli sie cos nie udalo

Schemat działania wygląda tak:

1. Stary indeks zostaje aktywny

• Zapytania nadal korzystają z niego.
• Nie blokujesz wstawiania ani replikacji.

2. Tworzysz nowy indeks CONCURRENTLY - przesuwamy punkt odcięcia poprzedni był na '2025-05-01' a teraz będzie '2025-08-01'

CREATE INDEX CONCURRENTLY idx_transactions_recent_charge_credit_new
ON transactions(tx_date, card_type)
WHERE tx_type IN ('charge','credit')
  AND tx_date >= '2025-08-01';

• Nowy indeks buduje się równolegle, nie blokując tabeli. Wazne bo tabela docelowa w replikacji logicznej.
• Zapytania i wstawienia działają normalnie.

3. Testujesz i przełączasz zapytania

• Sprawdzasz, że nowy indeks faktycznie przyspiesza raporty.
• Możesz np. wymusić użycie nowego indeksu w zapytaniach przez hint (SET enable_indexscan = on/off) jeśli potrzebne.

4. Usuwasz stary indeks

DROP INDEX CONCURRENTLY idx_transactions_recent_charge_credit;