How to delete records automatically with postgresql?

Question

Recently manage to catch a project that is a blog, the user will perform the posts of various news, however I am using the server Heroku with the free account that entitles there are only 512 Megas of space, above this limit he start to charge.

At the time the user was performing the posts, the bank would obviously start to be more used, my idea would be that when the bank reached 500 Mega space used it would start to automatically delete the records starting from the last records, I’m almost sure this is done with a Procedure or a Triggers, I already tried to create an algorithm, but it doesn’t seem to work, I did some research and found this;

This algorithm below is only to know how much space my database has;

SELECT pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb 
FROM pg_database
WHERE pg_database.datname = 'seu_banco'

And I found this source of research, but I don’t understand how in practice this can work

Postgresql VACUUM: Cleaning the Database

What I need only a Trigger or Procedure that is running in my database and can do this for me, please someone has a suggestion or a ready algorithm that could pass me ?

Answer 1

The example below has as object my table tabular from my database time. My database time has 13 MB.

The purpose here is to delete the oldest record from the user table if the database exceeds a given value in size. The amount I will arbitrate is 10 MB.

I make, first, a function (TRIGGER FUNCTION), via the Pgadmin console.

Uma TRIGGER FUNCTION é o que guarda o mecanismo de execução de algum procedimento, uma instrução SQL normalmente.

As we will see, within the FUNCTION TRIGGER is a set of instructions that effectively does something productive in the database.

In your case, the procedure is deletion.

The function below asks the size of my database. If it is larger than 10 MB (10,000 bytes), have an SQL run to delete the record that has the lower number ID (auto_increment), which, of course, is the oldest.

It only allows deleting 1 record, because of the clause LIMIT 1.

In the Pgadmin console:

CREATE OR REPLACE FUNCTION 
fun_deletaregistrousuarios()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF(SELECT pg_database_size ('tempo') > 
'10000000') THEN
    DELETE FROM tabusuarios WHERE id = (SELECT id 
tabusuarios FROM tabusuarios ORDER BY id LIMIT 1);
    END IF;
    RETURN NULL;
END;
$$;

Encoded the function and executed the SQL of its creation, I do the TRIGGER.

Um TRIGGER é um bloco de instruções pelo qual se vigia um evento qualquer numa tabela, disparando uma FUNCTION TRIGGER. Funciona como um despertador, que toca a campainha numa hora determinada. No nosso caso, vai 'despertar a função' caso alguma coisa aconteça com os dados da tabela.

In the Pgadmin console:

CREATE TRIGGER tg_vigia_usuarios AFTER INSERT 
ON  tabusuarios 
FOR EACH STATEMENT EXECUTE PROCEDURE 
fun_deletaregistrousuarios();

In our case, the event is AFTER INSERT, that is, if there is the inclusion of a single record that is in my user table, it will trigger the mechanism.

O *trigger* é associado à tabela, porque é sobre ela que se vigia qualquer inserção.

Out of curiosity, in mine schema public, I can see a new entry in the 'Function Triggers' folder, named fun_deletaregistrousuarios;

Also, within the same schema, now in the 'folder' Tables, in the 'sub-folder' of the table tabular, I have one more entry, which is Triggers. There, I find tg_vigia_usuarios.

Por fim, estando tudo pronto, no momento da inserção de um novo registro na tabela tabusuarios, um outro mais antigo foi deletado, uma vez que meu banco de dados tem 13 MB e o limite para o trigger provocar a função é de 10 MB.

In other words, as my database (13 MB) already exceeds the block test value in size IF of my function - which is caused by Trigger if the database is greater than 10 MB - the instruction is executed DELETE on an old record.

Of course my approach does not exactly solve the proposed problem, because it deletes only one record and it may be insufficient to bring the size of the database to the desired limit, but this is an adjustment in the TRIGGER FUNCTION. As a suggestion, loop the deletion or other way to span more records and test after each deletion the size of the database may be an output.

It’s just a way to start.

Adjust the values - such as the name and SQL statement of the function core and the event in Trigger - to any other need.