Re: Search in historical table

Hello guys,
I've going to design a bitemporal table.

The implementation foresees 2 tables:
- current_table
- historical_table
I store 2 different time dimensions: user and db.
"User time dimension" could be set by sql instructions.
"Db time dimension" is alway managed and replaced by trigger function.

Each insert of update on current_table stores the previous record on
hisorical_table.

Delete isn't allowed because I need to store some closing information, so,
the user must perform update setting close=true and pass the other
mandatory information (User must update the corresponding view_current_data
instead of current_table).

Ok, for now, it seems to be a quiet standard approach.

Question 1:
Which is the right approach? use 2 timestamp range fileds (one for "user
time" and the other for "db time") or 4 timestamp fields (a couple for each
dimension)?

Question 2:
How to create an index that allows query to extract records contained (also
partially contained) in a period?
I mean: give me each record valid from user point of view between
2023-01-01 and 2023-03-15 AND valid from db point of view between
2023-02-01 and 2023-05-15

Thank's in advance

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

On Mon, Jun 5, 2023 at 2:12 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
> Question 1:
> Which is the right approach? use 2 timestamp range fileds (one for "user time" and the other for "db time") or 4 timestamp fields (a couple for each dimension)?
>

I don't think there is a right approach, rather it depends on which
granularity you need. As you described, I suspect one timestamp for
every fact will suffice, assuming the last timestamp is also the valid
record.
If you are going to store a validity in terms of "since now to then",
a range could be your friend.

> Question 2:
> How to create an index that allows query to extract records contained (also partially contained) in a period?
> I mean: give me each record valid from user point of view between 2023-01-01 and 2023-03-15 AND valid from db point of view between 2023-02-01 and 2023-05-15

What's wrong with something like "WHERE ts >= '2023-01-01' and ts <=
'2023-03-05' ? Are you scared about performances (that you probably
haven't measured yet)?
It could also be a good idea to start with a partitioning on the
timestamp if you expect to store a lot of records.
Or even better, use a timescale like extension.

Question 3: why english on an italian mailing list?

Luca

Ciao Luca,
perché sono p***a, volevo mandarla a quella generale in inglese. :-)

Rispondo alle tue altre domande.
No un unico timestamp non è sufficiente, mi servono i periodi di validità,
in accordo con l'approccio bitemporale. In realtà mi sto chiedendo se non
debba gestire la tri-temporalità.

Ad ogni modo dopo aver memorizzato i dati, uno usecase che DEVO garantire è
una estrazione che rigeneri tutti i passaggi di un dato elemento, cioè
tutte le versioni valide in un certo spazio temporale (bidemensionale) X
che è input utente.

Ogni record è valida da una momento ad un altro dal punto di vista utente,
e da un momento ad un altro dal punto di vista del db.
Immagina di dover registrare lo stipendio dei dipendenti (rossi dal 01/05
prende 100, prima prendeva 95).
Lo stipendio può essere inserito prima o (per dimenticanza) dopo il momento
cui bisogna dare i soldi al dipendente. Il tempo utente conterrà comunque
dal 01/05 - sinedie
Il tempo db conterrà dal momento vero di variazione - sinedie.
I dati storici si adegueranno di conseguenza.

Le misure non le ho fatto su postgresql ma le conosco su altro dbms e
parlando di miliardi di record non sono un granché, soprattutto nelle
ricerche temporali.

Ho iniziato a guardare Gist, ma devo dire che non ci ho capito tantissimo.

Il giorno lun 5 giu 2023 alle ore 08:30 Luca Ferrari <fluca1978(at)gmail(dot)com>
ha scritto:

> On Mon, Jun 5, 2023 at 2:12 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com>
> wrote:
> > Question 1:
> > Which is the right approach? use 2 timestamp range fileds (one for "user
> time" and the other for "db time") or 4 timestamp fields (a couple for each
> dimension)?
> >
>
> I don't think there is a right approach, rather it depends on which
> granularity you need. As you described, I suspect one timestamp for
> every fact will suffice, assuming the last timestamp is also the valid
> record.
> If you are going to store a validity in terms of "since now to then",
> a range could be your friend.
>
> > Question 2:
> > How to create an index that allows query to extract records contained
> (also partially contained) in a period?
> > I mean: give me each record valid from user point of view between
> 2023-01-01 and 2023-03-15 AND valid from db point of view between
> 2023-02-01 and 2023-05-15
>
> What's wrong with something like "WHERE ts >= '2023-01-01' and ts <=
> '2023-03-05' ? Are you scared about performances (that you probably
> haven't measured yet)?
> It could also be a good idea to start with a partitioning on the
> timestamp if you expect to store a lot of records.
> Or even better, use a timescale like extension.
>
> Question 3: why english on an italian mailing list?
>
> Luca
>

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

On Mon, Jun 5, 2023 at 11:35 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
> No un unico timestamp non è sufficiente, mi servono i periodi di validità, in accordo con l'approccio bitemporale. In realtà mi sto chiedendo se non debba gestire la tri-temporalità.

"Marty, tu non pensi quadrimensionalmente" (cit., Ritorno al Futuro 3).

> Ogni record è valida da una momento ad un altro dal punto di vista utente, e da un momento ad un altro dal punto di vista del db.
> Immagina di dover registrare lo stipendio dei dipendenti (rossi dal 01/05 prende 100, prima prendeva 95).

Da come lo descrivi, a me viene in mente di usare un range (due valori
timestamp, o meglio _un valore di validità_) e di conseguenza fare le
query con "sovrapposizione" del timestamp voluto nel range. Sempre che
questo sia lo usecase che ti serve.
Ma sono anche abbastanza sicuro che il partizionamento delle tabelle
così diventerà molto complesso,.
Continuo anche a non capire perché ti servono due (tre?) timestamp,
visto che con un timestamp avresti una sorta di linkedlist che
ripercorre la storia di ogni variazione.

Possibile che non ci sia una estensione temporale che ricopra il tuo caso?

Luca

eheheh grande citazione :-)
duenque esistono due estensioni.
Una gestisce la bitemporalità ma non posso installarla e inoltre sembra
prevedere di chiamare una funzione per ogni insert o update (
https://github.com/scalegenius/pg_bitemporal) l'altra gestisce solo una
temporalità (https://github.com/nearform/temporal_tables) ma potrei
"agevolmente" farla evolvere per includere quello che mi serve.

Ora queste soluzioni usano i timestamp_range, quindi sarebbe ragionevole
pensare che sia la scelta migliore, ma appunto ho qualche dubbio su altri
aspetti come il partizionamento, e in più non mi è chiaro come usare gli
indici Gist con questi oggetti

Il giorno lun 5 giu 2023 alle ore 14:56 Luca Ferrari <fluca1978(at)gmail(dot)com>
ha scritto:

> On Mon, Jun 5, 2023 at 11:35 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com>
> wrote:
> > No un unico timestamp non è sufficiente, mi servono i periodi di
> validità, in accordo con l'approccio bitemporale. In realtà mi sto
> chiedendo se non debba gestire la tri-temporalità.
>
> "Marty, tu non pensi quadrimensionalmente" (cit., Ritorno al Futuro 3).
>
> > Ogni record è valida da una momento ad un altro dal punto di vista
> utente, e da un momento ad un altro dal punto di vista del db.
> > Immagina di dover registrare lo stipendio dei dipendenti (rossi dal
> 01/05 prende 100, prima prendeva 95).
>
> Da come lo descrivi, a me viene in mente di usare un range (due valori
> timestamp, o meglio _un valore di validità_) e di conseguenza fare le
> query con "sovrapposizione" del timestamp voluto nel range. Sempre che
> questo sia lo usecase che ti serve.
> Ma sono anche abbastanza sicuro che il partizionamento delle tabelle
> così diventerà molto complesso,.
> Continuo anche a non capire perché ti servono due (tre?) timestamp,
> visto che con un timestamp avresti una sorta di linkedlist che
> ripercorre la storia di ogni variazione.
>
> Possibile che non ci sia una estensione temporale che ricopra il tuo caso?
>
> Luca
>

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

On Mon, Jun 5, 2023 at 4:59 PM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
> Ora queste soluzioni usano i timestamp_range, quindi sarebbe ragionevole pensare che sia la scelta migliore, ma appunto ho qualche dubbio su altri aspetti come il partizionamento, e in più non mi è chiaro come usare gli indici Gist con questi oggetti

"Si può fare!" (cit., Frankenstein Junior).
Non avendolo però mai usato in produzione, non so cosa succeda a
livello di performance. E comunque per partizionare devi avere dei
periodi non sovrapposti.

testdb=> create table test ( a text, validity tsrange ) partition by
range( validity);
CREATE TABLE
testdb=> create table m05 partition of test for values from (
'[2023-05-01, 2023-05-01]' ) to ( '[2023-
05-31, 2025-05-31]' );
CREATE TABLE
testdb=> create table m06 partition of test for values from (
'[2023-06-01, 2023-06-01]' ) to ( '[2023-
06-30, 2025-06-30]' );
CREATE TABLE

testdb=> insert into test values( 'a', '[2023-05-02, 2023-05-10]' );
INSERT 0 1
testdb=> insert into test values( 'b', '[2023-06-02, 2023-06-10]' );
INSERT 0 1
testdb=> select * from test;
a | validity
---+-----------------------------------------------
a | ["2023-05-02 00:00:00","2023-05-10 00:00:00"]
b | ["2023-06-02 00:00:00","2023-06-10 00:00:00"]
(2 rows)

testdb=> select * from only m05;
a | validity
---+-----------------------------------------------
a | ["2023-05-02 00:00:00","2023-05-10 00:00:00"]
(1 row)

testdb=> select * from only m06;
a | validity
---+-----------------------------------------------
b | ["2023-06-02 00:00:00","2023-06-10 00:00:00"]
(1 row)
testdb=> explain select * from test where validity @> '[2023-05-01,
2023-06-19]';
QUERY PLAN
----------------------------------------------------------------------------------------
Append (cost=0.00..42.04 rows=8 width=64)
-> Seq Scan on m05 test_1 (cost=0.00..21.00 rows=4 width=64)
Filter: (validity @> '["2023-05-01 00:00:00","2023-06-19
00:00:00"]'::tsrange)
-> Seq Scan on m06 test_2 (cost=0.00..21.00 rows=4 width=64)
Filter: (validity @> '["2023-05-01 00:00:00","2023-06-19
00:00:00"]'::tsrange)
(5 rows)

Cioè non si può partizionare su una espressione?

Tipo lower (tsrange)
Il mar 6 giu 2023, 08:34 Luca Ferrari <fluca1978(at)gmail(dot)com> ha scritto:

> On Mon, Jun 5, 2023 at 4:59 PM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com>
> wrote:
> > Ora queste soluzioni usano i timestamp_range, quindi sarebbe ragionevole
> pensare che sia la scelta migliore, ma appunto ho qualche dubbio su altri
> aspetti come il partizionamento, e in più non mi è chiaro come usare gli
> indici Gist con questi oggetti
>
> "Si può fare!" (cit., Frankenstein Junior).
> Non avendolo però mai usato in produzione, non so cosa succeda a
> livello di performance. E comunque per partizionare devi avere dei
> periodi non sovrapposti.
>
> testdb=> create table test ( a text, validity tsrange ) partition by
> range( validity);
> CREATE TABLE
> testdb=> create table m05 partition of test for values from (
> '[2023-05-01, 2023-05-01]' ) to ( '[2023-
> 05-31, 2025-05-31]' );
> CREATE TABLE
> testdb=> create table m06 partition of test for values from (
> '[2023-06-01, 2023-06-01]' ) to ( '[2023-
> 06-30, 2025-06-30]' );
> CREATE TABLE
>
> testdb=> insert into test values( 'a', '[2023-05-02, 2023-05-10]' );
> INSERT 0 1
> testdb=> insert into test values( 'b', '[2023-06-02, 2023-06-10]' );
> INSERT 0 1
> testdb=> select * from test;
> a | validity
> ---+-----------------------------------------------
> a | ["2023-05-02 00:00:00","2023-05-10 00:00:00"]
> b | ["2023-06-02 00:00:00","2023-06-10 00:00:00"]
> (2 rows)
>
> testdb=> select * from only m05;
> a | validity
> ---+-----------------------------------------------
> a | ["2023-05-02 00:00:00","2023-05-10 00:00:00"]
> (1 row)
>
> testdb=> select * from only m06;
> a | validity
> ---+-----------------------------------------------
> b | ["2023-06-02 00:00:00","2023-06-10 00:00:00"]
> (1 row)
> testdb=> explain select * from test where validity @> '[2023-05-01,
> 2023-06-19]';
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------
> Append (cost=0.00..42.04 rows=8 width=64)
> -> Seq Scan on m05 test_1 (cost=0.00..21.00 rows=4 width=64)
> Filter: (validity @> '["2023-05-01 00:00:00","2023-06-19
> 00:00:00"]'::tsrange)
> -> Seq Scan on m06 test_2 (cost=0.00..21.00 rows=4 width=64)
> Filter: (validity @> '["2023-05-01 00:00:00","2023-06-19
> 00:00:00"]'::tsrange)
> (5 rows)
>

On Tue, Jun 6, 2023 at 8:55 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
>
> Cioè non si può partizionare su una espressione?
>
> Tipo lower (tsrange)

Si può anche partizionare su una espressione, ma allora forse non ho
capito la tua esigenza. A parte che lower di un timestamp (range) non
avrebbe senso, io avevo capito tu volessi partizionare per "intervalli
di validità" (nel mio esempio un mese).
A questo punto devi sperimentare o spiegarci meglio la tua esigenza pratica.

Luca

Prima ho una domanda filosofica, per rappresentare un periodo temporale
cosa è meglio? 2 timestamp o un timestamp range?

Dopo di che devo usare questi periodi temporali per fare ricerche (in
letteratura si parla di funzione di copertura dello spazio (bi temporale).
cioè insomma, una query potrebbe essere secondo le informazioni del db da
tempo T1 a tempo T2 come è stata l'evoluzione del dato rispetto per come la
vedeva l'utente nel periodo che va da T3 a T4.

Questo è il caso limite e se non i due periodi non sono sovrapposti non
estrae righe.... a meno che non esistano le correzioni o le prenotazioni.

Ma al di là di questo, la domanda è come usare un indice che sappia
intersecare periodi temporali? ho visto che si parla di GIST ma non lo
conosco...
Il partizionamento è poi un'ulteriore livello di organizzazione

Il giorno mar 6 giu 2023 alle ore 09:03 Luca Ferrari <fluca1978(at)gmail(dot)com>
ha scritto:

> On Tue, Jun 6, 2023 at 8:55 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com>
> wrote:
> >
> > Cioè non si può partizionare su una espressione?
> >
> > Tipo lower (tsrange)
>
> Si può anche partizionare su una espressione, ma allora forse non ho
> capito la tua esigenza. A parte che lower di un timestamp (range) non
> avrebbe senso, io avevo capito tu volessi partizionare per "intervalli
> di validità" (nel mio esempio un mese).
> A questo punto devi sperimentare o spiegarci meglio la tua esigenza
> pratica.
>
> Luca
>

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

On Tue, Jun 6, 2023 at 11:04 PM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
>
> Prima ho una domanda filosofica, per rappresentare un periodo temporale cosa è meglio? 2 timestamp o un timestamp range?
>

Premessa: non sono un esporto delle serie temporali, qui forse
potrebbe intervenire qualche altra persona.
Detto questo, il range è di fatto una coppia di dati, nel tuo caso di
timestamp. Lato teorico, non vi è quindi differenza. Lato pratico, un
range ti forza a usarlo correttamente, ad esempio ad impostare gli
estremi assieme senza correre il rischio di non impostarne uno o di
impostare la fine prima dell'inizio e cose analoghe.

> Dopo di che devo usare questi periodi temporali per fare ricerche (in letteratura si parla di funzione di copertura dello spazio (bi temporale). cioè insomma, una query potrebbe essere secondo le informazioni del db da tempo T1 a tempo T2 come è stata l'evoluzione del dato rispetto per come la vedeva l'utente nel periodo che va da T3 a T4.
>

Chiaro (credo), ma quindi tu memorizzeresti solo (T1, T2) mentre (T3,
T4) sarebbe il criterio di ricerca. Se ho capito bene.

> Questo è il caso limite e se non i due periodi non sono sovrapposti non estrae righe.... a meno che non esistano le correzioni o le prenotazioni.
>
Quando parlavo di sovrapposizione intendevo rispetto al
partizionamento: non sapendo come sarà il tuo flusso dati, devi
cercare di fare in modo che T1,T2 cadano sempre e solo in una tabella.
Ad esempio se partizioni per anno-mese, T1,T2 devono stare nello
stesso mese.

> Ma al di là di questo, la domanda è come usare un indice che sappia intersecare periodi temporali? ho visto che si parla di GIST ma non lo conosco...

GiST è una delle cose che conosco veramente poco, quindi qui non posso aiutarti.
Ma il mio sospetto è che un tsrange con un btree sopra possa renderti
comunque soddisfatto.

Luca