Pulizia (e sostenibilità) del DB

[pperliti]

Necessità:
Prevedere un task (o una guida all'interno della documentazione) per l'eliminazione di pendenze obsolete, al fine di contenere la crescita delle dimensioni del DB.
Il conferimento continuo di pendenze all'interno di GovPay, la memorizzazione come BLOB dei flussi XML (RPT, RT, FdR) legati al colloquio col Nodo e gli eventi registrati da GovPay (in particolare quelli legati all'invio di tracciati per l'inserimento massivo di pendenze) fanno crescere rapidamente il DB (soprattutto per EC che movimentano molto o per i PT che intermediano più EC).

Soluzione:
Sarebbe opportuno poter abilitare un task che si occupi di eliminare le entità non più "utili" secondo una politica di retention personalizzabile (es. pendenze annullate oppure rendicontate/riconciliate da più di N giorni/settimane/mesi). E' evidente che il concetto di "non più utile" sia soggettivo (es. dipendente dal contesto) e che occorra tener conto del compromesso tra dimensione DB liberata vs. eventualità che un domani occorra andare a recuperare dati ormai rimossi, tuttavia anche una crescita incontrollata delle tabelle potrebbe diventare presto un problema.

Alternative:
In alternativa a un task, più semplicemente si potrebbe prevedere un'apposita sezione della documentazione che riporti i passaggi che ogni EC/PT potrebbe implementare in maniera autonoma (e seguendo la propria "sensibilità" :-) per rimuovere le entità ritenute "obsolete". Già oggi queste operazioni possono essere svolte studiandosi lo schema E/R di GovPay, tuttavia avere delle "best practices" aiuterebbe a non commettere passi falsi (o, quantomeno, ad accettare il rischio).

Note:
Lo stesso potrebbe valere per l'anonimizzazione dei dati (sostanzialmente i dati anagrafici dei soggetti debitori). Per le pendenze più "obsolete" (annullate e rendicontate/riconciliate da più di N giorni/settimane/mesi) si potrebbero "mascherare" (es. con asterischi o encodando in base64, come già oggi avviene per la causale) i dati personali (quantomeno quelli sul DB).

[nardil]

Ciao Paolo,

si, certamente sono da documentare degli script di svecchiamento dati.

Credo sia opportuno mantenere l'applicativo incapace di eliminare i dati da DB e delegare questa funzione a script SQL da eseguire separatamente con utenze appositamente autorizzate.

Possiamo procedere valutando in questa issue le entita' DB che necessitano di essere svecchiate e gli script che realizzano lo svecchiamento.

Di seguito i primi script per Postgres da testare e verificare con una proposta di data retention che poi dovranno essere modificati per gli altri DBMS.

• svecchiamento-tracciati

\set retention_tracciati '\'7 days\''
\set end_tracciati 'CURRENT_DATE - interval :retention_tracciati '

delete from eventi where id_tracciato in (select id from tracciati where data_completamento < :end_tracciati);
delete from operazioni where id_tracciato in (select id from tracciati where data_completamento <  :end_tracciati);
select lo_unlink(zip_stampe) from tracciati where data_completamento < :end_tracciati;
delete from tracciati where data_completamento < :end_tracciati;

• svecchiamento-eventi

\set retention_eventi '\'180 days\''
\set end_eventi 'CURRENT_DATE - interval :retention_eventi '

delete from eventi where data < :end_eventi;

• svecchiamento-rendicontazioni.sql (tbd)
• svecchiamento-pendenze.sql (tbd)
• svecchiamento-riconciliazioni.sql (tbd)

[nardil]

Eliminazione metadati di messaggi AppIO, promemoria mail e notifiche applicative consegnati o non consegnabili piu' vecchi di 1 mese.

\set retention '\'1 month\''
\set end 'CURRENT_DATE - interval :retention '

-- Notifiche IO
delete from notifiche_app_io where stato in ('SPEDITO', 'ANNULLATA') and data_creazione < :end;

-- Notifiche mail
delete from promemoria where stato in ('SPEDITO', 'FALLITO') and data_creazione < :end ;

-- Notifiche applicative
delete from notifiche where stato='SPEDITO' and data_creazione < :end;

[pperliti]

Ciao Paolo,

si, certamente sono da documentare degli script di svecchiamento dati.

Credo sia opportuno mantenere l'applicativo incapace di eliminare i dati da DB e delegare questa funzione a script SQL da eseguire separatamente con utenze appositamente autorizzate.

Possiamo procedere valutando in questa issue le entita' DB che necessitano di essere svecchiate e gli script che realizzano lo svecchiamento.

Di seguito i primi script per Postgres da testare e verificare con una proposta di data retention che poi dovranno essere modificati per gli altri DBMS.

• svecchiamento-tracciati

\set retention_tracciati '\'7 days\''
\set end_tracciati 'CURRENT_DATE - interval :retention_tracciati '

delete from eventi where id_tracciato in (select id from tracciati where data_completamento < :end_tracciati);
delete from operazioni where id_tracciato in (select id from tracciati where data_completamento <  :end_tracciati);
select lo_unlink(zip_stampe) from tracciati where data_completamento < :end_tracciati;
delete from tracciati where data_completamento < :end_tracciati;

• svecchiamento-eventi

\set retention_eventi '\'180 days\''
\set end_eventi 'CURRENT_DATE - interval :retention_eventi '

delete from eventi where data < :end_eventi;

• svecchiamento-rendicontazioni.sql (tbd)
• svecchiamento-pendenze.sql (tbd)
• svecchiamento-riconciliazioni.sql (tbd)

Ciao,
per quanto riguarda la parte di svecchiamento dei tracciati posso confermare il corretto funzionamento per SQL Server (2019).
Volendo utilizzare una stored procedure (che potrebbe essere schedulata direttamente su DB oppure lanciata da un task Java, per esempio), è sufficiente prendere il codice da te suggerito e wrapparlo nel seguente modo:

IF OBJECT_ID('sp_govpay_maintenance', 'P') IS NOT NULL
    DROP PROCEDURE sp_govpay_maintenance;
GO;
CREATE PROCEDURE sp_govpay_maintenance
    @retention_tracciati INT = 7,
    @retention_eventi INT = 180
AS
BEGIN
    SET NOCOUNT ON;

    -- Calculate the cutoff dates based on retention periods
    DECLARE @end_tracciati DATETIME = DATEADD(DAY, -@retention_tracciati, GETDATE());
    DECLARE @end_eventi DATETIME = DATEADD(DAY, -@retention_eventi, GETDATE());

    -- Delete from eventi where id_tracciato matches condition
    DELETE FROM eventi
	WHERE id_tracciato IN (
        SELECT id FROM tracciati WHERE data_completamento < @end_tracciati
    );

    -- Delete from operazioni where id_tracciato matches condition
    DELETE FROM operazioni
    WHERE id_tracciato IN (
        SELECT id FROM tracciati WHERE data_completamento < @end_tracciati
    );

    -- Delete from tracciati where data_completamento matches condition
    DELETE FROM tracciati 
    WHERE data_completamento < @end_tracciati;

    -- Delete from eventi where data matches condition
    DELETE FROM eventi 
    WHERE data < @end_eventi;
END;

L'unica nota è che l'esecuzione è piuttosto lentina, soprattutto se le tabelle chiamate in causa contengono molte righe (cosa altamente probabile).
Io ho risolto creando 3 indici sulle tabelle in questione:

-- index
CREATE INDEX idx_trc_data_completamento ON tracciati (data_completamento);
CREATE INDEX idx_evt_fk_trc ON eventi (id_tracciato);
CREATE INDEX idx_ope_fk_trc ON operazioni (id_tracciato);

Così facendo l'esecuzione è istantanea. L'unico dubbio rimane il rebuild degli indici (che potrebbe essere altrettanto lento).
Ad esempio:

-- Rebuild indexes (optional but recommended)
ALTER INDEX ALL ON eventi REBUILD;
ALTER INDEX ALL ON operazioni REBUILD;
ALTER INDEX ALL ON tracciati REBUILD;

-- Update statistics
UPDATE STATISTICS eventi;
UPDATE STATISTICS operazioni;
UPDATE STATISTICS tracciati;

Inizialmente avevo incluso queste istruzioni nella stored procedure ma poi ho deciso di commentarle, per la loro lentezza (che può aver delle ripercussioni, dato che la ricostruzione di un indice in genere implica un lock su tabella).

[pperliti]

Per le restanti tabelle, rpt e fr sono quelle a mio avviso più critiche. I contenuti blob tendono a far crescere le tabelle piuttosto in fretta. Le possibili soluzioni che vedo sono:

1. eliminazione delle righe oltre una certa retention (ovviamente questo implica non poterle più recuperare, ma può essere un'alternativa valida se tali documenti sono stati anche archiviati altrove dagli utilizzatori di GovPay);
2. sostituzione/troncamento dei contenuti blob (vale la stessa osservazione del punto precedente, tuttavia in questo caso le righe delle tabelle rpt e fr non vengono realmente eliminate e questo può 'semplificarne' la gestione, in quanto le tabelle che fanno riferimento alle tabelle rpt e fr non devono essere a loro volta svecchiate; rimane il fatto che la parte di API/UI potrebbe andare in errore se si attende un oggetto XML su cui fare unmarshall).

Stiamo ovviamente parlando di pendenze già pagate o annullate (quelle ancora pagabili non vengono toccate, indipendentemente dalla retention).

[pperliti]

Provo a condividere un possibile script (per SQL Server), provato su un ambiente di test.
E' solo un inizio di contributo, sicuramente c'è ancora parecchio da sistemare e sconsiglio vivamente a chiunque di provarlo su un ambiente in esercizio (e in ogni caso non prima di un backup).

Si tratta di una stored procedure per:

1. svecchiamento-tracciati (tracciati di inserimenti/cancellazioni massive più vecchi di ...)
2. svecchiamento-eventi (eventi più vecchi di ...)
3. svecchiamento-notifiche (promemoria mail/notifiche applicative consegnate o non consegnabili più vecchie di ...)
4. svecchiamento-pendenze.sql (pendenze PAGATE/ANNULLATE più vecchie di ... + eliminazione entità correlate)

Note sullo script:
a. in fase di selezione delle pendenze pagate (stato ESEGUITO) non si verifica l'eventuale rendicontazione: potrebbero quindi essercene alcune non ancora rendicontate (improbabile per retention superiore a 30 giorni, ma non impossibile).
b. le righe di fr andrebbero rimosse solo se TUTTE le pendenze rendicontate sono state eliminate, in caso contrario si potrebbero perdere dei riferimenti.

+++DISCLAIMER: NON USARE IN PRODUZIONE!+++

-- -----------------------------------------------------------
-- [GovPay] Stored Procedure for maintenance purpose
-- Use this script to create tha stored procedure on GovPay DB
-- -----------------------------------------------------------
-- Create indexes to speed-up selects used during maintenance
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_trc_data_completamento' AND object_id = OBJECT_ID('tracciati'))
    CREATE INDEX idx_trc_data_completamento ON tracciati (data_completamento);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_evt_fk_trc' AND object_id = OBJECT_ID('eventi'))
    CREATE INDEX idx_evt_fk_trc ON eventi (id_tracciato);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_ope_fk_trc' AND object_id = OBJECT_ID('operazioni'))
    CREATE INDEX idx_ope_fk_trc ON operazioni (id_tracciato);

-- Drop stored procedure if already exists
IF OBJECT_ID('sp_govpay_maintenance', 'P') IS NOT NULL
    DROP PROCEDURE sp_govpay_maintenance;

-- Create stored procedure
CREATE PROCEDURE sp_govpay_maintenance
    @retention_tracciati INT = 7,   -- default value
    @retention_eventi INT = 180,    -- default value
    @retention_notifiche INT = 31,  -- default value
    @retention_versamenti INT = 270 -- default value
AS
BEGIN
    SET NOCOUNT ON;

    -- Calculate the cutoff dates based on retention periods
    DECLARE @end_tracciati DATETIME = DATEADD(DAY, -@retention_tracciati, GETDATE());
    DECLARE @end_eventi DATETIME = DATEADD(DAY, -@retention_eventi, GETDATE());
    DECLARE @end_notifiche DATETIME = DATEADD(DAY, -@retention_notifiche, GETDATE());
    DECLARE @end_versamenti DATETIME = DATEADD(DAY, -@retention_versamenti, GETDATE());

    -- Delete from eventi where id_tracciato matches condition
    DELETE FROM eventi
    WHERE id_tracciato IN (
        SELECT id FROM tracciati WHERE data_completamento < @end_tracciati
    );

    -- Delete from operazioni where id_tracciato matches condition
    DELETE FROM operazioni
    WHERE id_tracciato IN (
        SELECT id FROM tracciati WHERE data_completamento < @end_tracciati
    );

    -- Delete from tracciati where data_completamento matches condition
    DELETE FROM tracciati
    WHERE data_completamento < @end_tracciati;

    -- Delete from eventi where data matches condition
    DELETE FROM eventi
    WHERE data < @end_eventi;

    -- Notifiche IO
    DELETE FROM notifiche_app_io
    WHERE stato IN ('SPEDITO', 'ANNULLATA') AND data_creazione < @end_notifiche;

    -- Notifiche mail
    DELETE FROM promemoria
    WHERE stato IN ('SPEDITO', 'FALLITO') AND data_creazione < @end_notifiche;

    -- Notifiche applicative
    DELETE FROM notifiche
    WHERE stato='SPEDITO' AND data_creazione < @end_notifiche;

    /*
    ----------------------------------------------------------
    -- VERSAMENTI PAGATI/ANNULLATI
    ----------------------------------------------------------
    DECLARE @id_versamento INT, @id_singolo_versamento INT;

    -- Declare a cursor to iterate over rows that need to be deleted
    DECLARE versamenti_cursor CURSOR FOR
        SELECT id
        FROM versamenti
        WHERE stato_versamento IN ('ESEGUITO', 'ANNULLATO')
          AND data_ora_ultimo_aggiornamento < @end_versamenti;

    OPEN versamenti_cursor;

    FETCH NEXT FROM versamenti_cursor INTO @id_versamento;

    WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Find all id_singolo_versamento for id_versamento
            DECLARE versamenti_singoli_cursor CURSOR FOR
                SELECT id
                FROM singoli_versamenti
                WHERE id_versamento = @id_versamento;

            OPEN versamenti_singoli_cursor;

            FETCH NEXT FROM versamenti_singoli_cursor INTO @id_singolo_versamento;

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    -- Perform all required deletions using @id_singolo_versamento
                    DELETE FROM eventi
                    WHERE id_fr IN (
                        SELECT id_fr FROM rendicontazioni WHERE id_singolo_versamento = @id_singolo_versamento
                    );

                    DELETE FROM rendicontazioni
                    WHERE id_singolo_versamento = @id_singolo_versamento;

                    DELETE FROM pagamenti
                    WHERE id_singolo_versamento = @id_singolo_versamento;

                    -- Fetch the next id_singolo_versamento
                    FETCH NEXT FROM versamenti_singoli_cursor INTO @id_singolo_versamento;
                END

            CLOSE versamenti_singoli_cursor;
            DEALLOCATE versamenti_singoli_cursor;

            -- Perform deletions using @id_versamento
            DELETE FROM operazioni WHERE id_versamento = @id_versamento;
            DELETE FROM notifiche_app_io WHERE id_versamento = @id_versamento;
            DELETE FROM promemoria WHERE id_versamento = @id_versamento;
            DELETE FROM notifiche WHERE id_rpt IN (SELECT id FROM rpt WHERE id_versamento = @id_versamento);
            DELETE FROM allegati WHERE id_versamento = @id_versamento;
            DELETE FROM stampe WHERE id_versamento = @id_versamento;
            DELETE FROM fr WHERE id IN (SELECT id_fr FROM rendicontazioni WHERE id_singolo_versamento IN (SELECT id FROM singoli_versamenti WHERE id_versamento = @id_versamento));
            DELETE FROM rr WHERE id_rpt IN (SELECT id FROM rpt WHERE id_versamento = @id_versamento);
            DELETE FROM rpt WHERE id_versamento = @id_versamento;
            DELETE FROM singoli_versamenti WHERE id_versamento = @id_versamento;
            DELETE FROM versamenti WHERE id = @id_versamento;

            -- Fetch the next id_versamento
            FETCH NEXT FROM versamenti_cursor INTO @id_versamento;
        END

    CLOSE versamenti_cursor;
    DEALLOCATE versamenti_cursor;
    */

    /*
    -- NOTE: Following statements can impact DBMS performance
    -- Rebuild indexes (optional but recommended)
    ALTER INDEX ALL ON eventi REBUILD;
    ALTER INDEX ALL ON operazioni REBUILD;
    ALTER INDEX ALL ON tracciati REBUILD;
    ALTER INDEX ALL ON notifiche_app_io REBUILD;
    ALTER INDEX ALL ON promemoria REBUILD;
    ALTER INDEX ALL ON notifiche REBUILD;
    ALTER INDEX ALL ON pagamenti REBUILD;
    ALTER INDEX ALL ON rendicontazioni REBUILD;
    ALTER INDEX ALL ON allegati REBUILD;
    ALTER INDEX ALL ON stampe REBUILD;
    ALTER INDEX ALL ON fr REBUILD;
    ALTER INDEX ALL ON rr REBUILD;
    ALTER INDEX ALL ON rpt REBUILD;
    ALTER INDEX ALL ON singoli_versamenti REBUILD;
    ALTER INDEX ALL ON versamenti REBUILD;
    */

    /*
    -- Update statistics
    UPDATE STATISTICS eventi;
    UPDATE STATISTICS operazioni;
    UPDATE STATISTICS tracciati;
    UPDATE STATISTICS notifiche_app_io;
    UPDATE STATISTICS promemoria;
    UPDATE STATISTICS pagamenti;
    UPDATE STATISTICS rendicontazioni;
    UPDATE STATISTICS allegati;
    UPDATE STATISTICS stampe;
    UPDATE STATISTICS fr;
    UPDATE STATISTICS rr;
    UPDATE STATISTICS rpt;
    UPDATE STATISTICS singoli_versamenti;
    UPDATE STATISTICS versamenti;
    */
END;