Czy można zachować maksymalną liczbę rekordów w postgresql?

9

Zasadniczo część naszej tabeli Postgresql jest używana do przechowywania dzienników dostępu do serwera, i dlatego czasami podczas produkcji może być dość duża. czy jest jakiś sposób na ustawienie w postgresql maksymalnej liczby rekordów, jakie może mieć tabela i wypchnięcie najstarszego rekordu?

postgresql

— Jharwood
źródło

12

Możesz zdefiniować wyzwalacz, aby zachować pożądany numer wiersza:

CREATE OR REPLACE FUNCTION trf_keep_row_number_steady()
RETURNS TRIGGER AS
$body$
BEGIN
    -- delete only where are too many rows
    IF (SELECT count(id) FROM log_table) > rownum_limit
    THEN 
        -- I assume here that id is an auto-incremented value in log_table
        DELETE FROM log_table
        WHERE id = (SELECT min(id) FROM log_table);
    END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_keep_row_number_steady 
AFTER INSERT ON log_table
FOR EACH ROW EXECUTE PROCEDURE trf_keep_row_number_steady();

Nie jest to prawdopodobnie najlepsza opcja, ale po osiągnięciu limitu nigdy nie zostanie przekroczona. Jeśli jest miejsce na fluktuacje, możesz okresowo sprawdzać numer wiersza i usuwać nadmiar wierszy od początku.

EDYCJA: Jeśli masz naprawdę duże dzienniki (powiedzmy milion na miesiąc), partycjonowanie może być najłatwiejszym rozwiązaniem. Następnie możesz po prostu upuścić niepotrzebne tabele (powiedz gdziemax(timestamp) < CURRENT_DATE - 1 year). Możesz użyć swojej sygnatury czasowej (lub daty pochodnej) jako warunku podziału partycji .

Ale należy zachować ostrożność przed odrzuceniem starych dzienników. Czy na pewno nigdy nie będziesz ich potrzebować?

— dezso
źródło

możemy je wykonywać okresowo i jesteśmy pewni, że nie będziemy ich potrzebować, gdy stół będzie wystarczająco duży, aby tego wymagać, po prostu staram się zautomatyzować utrzymanie DB tak bardzo, jak to możliwe :)

— Jharwood

Miałem również nadzieję, że postgres sam będzie wiedział, który z nich jest starszy, ale jeśli nie mamy identyfikatora, mógłby użyć pola datownika „2012-06-22 17: 17: 52.692514”

— Jharwood,

@Jharwood - zredagowałem moją odpowiedź. Powiedz mi, czy potrzebujesz dodatkowych informacji.

— dezso

2

+1 na propozycji podziału. Jeśli chcesz iść z liczeniem bez ogromnego nakładu skanowania tabeli za każdym razem, możesz użyć pg_class.reltuples dla przybliżenia, przy użyciu wyzwalaczy, aby utrzymać liczbę w tabeli „kontrolnej”.

— kgrittn

4

Stworzyłem bardziej ogólną, niezależną od tabeli funkcję.

CREATE OR REPLACE FUNCTION keep_row_number_steady()
RETURNS TRIGGER AS
$body$
DECLARE
    tab text;
    keyfld text;
    nritems INTEGER;
    rnd DOUBLE PRECISION;
BEGIN
    tab := TG_ARGV[0];
    keyfld := TG_ARGV[1];
    nritems := TG_ARGV[2]; 
    rnd := TG_ARGV[3];

    IF random() < rnd
    THEN 
        EXECUTE(format('DELETE FROM %s WHERE %s < (SELECT %s FROM %s ORDER BY %s DESC LIMIT 1 OFFSET %s)', tab, keyfld, keyfld, tab, keyfld, nritems));
    END IF;
    RETURN NULL;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER log_table_keep_row_number_steady_trigger
AFTER INSERT ON log_table
FOR EACH STATEMENT EXECUTE PROCEDURE keep_row_number_steady('log_table', 'id', 1000, 0.1);

Funkcja przyjmuje 4 parametry:

tab: nazwa tabeli
keyfld: numeryczne, progresywne pole klucza
nritems: liczba elementów do zachowania
rnd: liczba losowa, od 0 do 1; im większy, tym częstszy stół będzie czyszczony (0 = nigdy, 1 = zawsze, 0,1 = 10% razy)

W ten sposób możesz stworzyć, ile wyzwalaczy chcesz wywołać tę samą funkcję.

Mam nadzieję że to pomoże.

— Pasy
źródło

0

Utworzyłem ten proc i uruchomiłem go z PG Agent (lub w zależności od zadania Windows lub CRON). Mogę mieć więcej wierszy, dzięki czemu moja tabela dziennika nie jest zbyt duża. Oszczędza narzut wyzwalacza.

CREATE or replace FUNCTION activitylogcleanup(_MaxRows int) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
   minid    int;
BEGIN
    SELECT logid into minid FROM activitylogapplication 
     order by logid desc limit 1 OFFSET _MaxRows;

    if not found then 
        return;
    END IF; 

    Delete from activitylogapplication where logid < minid;
END;
$$;

— Ron H.
źródło