Postgres (Supabase) SQL - Save the number of records between dates

Databases & SQL — incorrectly specified categories?

22 USD

Project translated automatically. Log in or register, to view the original by 0.There is a table of users, where id = id of the user (uuid).1 .There is a usages table where all API requests are stored (because the API has to limit the number of available requests depending on the signature).It has created_at.The 2ndThere is a subscriptions table where for each user_id has (or does not have) to be a record with status = 'active'♰ 'trialing'.Each record has current_period_start, current_period_end.3 .There is a user_activity table where the fields are executions_amount_used, executions_amount_granted.Tables 1-3 have foreign key user_id to users.What to do: 1 .It is necessary to effectively insert in usages (it is when you triggered the API and recorded activity), by user_id in the user_activity table to record the number of usages that have created_at between current_period_start and current_period_end from the subscription with the status active or trialing.If there is no such signature, you need to remove all usages of the user (so that if he has canceled the signature, then executions_amount_used has been taken for the entire period that is in the usages table).You do not need to use count(), you need to use estimates and the function https://wiki.postgresql.org/wiki/Count_estimate. This feature is already available and it works, but the request somehow releases random data when the filter is on a date.This may be quick fix, but you may need to re-create the trigger logic (it is also a matter of fact).If there are the best suggestions to do this, it would be great to hear.But we don’t need to count on each API call by usages (the table is big), so the API call looks only on the user_activity table and there must be relevant numbers and everything.Look carefully on the screen change of the sign < > does not work at all, but it does not take into account the date.It can also issue usage not from the beginning of the signature date, although all dates in UTC in the database.Therefore, there is a disynchron, if the signature is updated, it draws usages from an irrelevant date to current_period_start, and should only be after current_period_start.This should also be fixed.

Proposals 3 Discussions 6

Aleksandr D.

22 0

Budget: 1000 UAH Deadline: 1 day

Good day .
Databases are the main specialization. There are ideas to solve your problem.

The list does not show proposals concealed by the client or freelancer with a Plus profile, as well as proposals violating rules

Vitalii Valchuk 18 February 2024

Якщо вам не потрібен точний підрахунок, поточна статистика з таблиці каталогу pg_classможе бути достатньо хорошою, і її набагато швидше отримати для великих таблиць. 
Воно і буде показувати не точний підрахунок. Краще вірно побудувати індекси, тоді і підрахунок буде швидким.

Vitalii Valchuk 18 February 2024

Кількість живих рядків у таблиці. Це лише оцінка, яку використовує планувальник. Вона оновлюється командами VACUUM, ANALYZE та деякими командами DDL, такими як CREATE INDEX.

Vitalii Valchuk 18 February 2024

Але нам не потрібно на кожен API call робити count по usages (таблиця велика),  
ну так зробіть таблицю, де буде пораховано на початок і при кожній вставці - оновляти дані. буде більш-меньш точно і швидко

Maxim M

18 February 2024

Дякую! Тобто після створення індексів вже буде можливо робити select count(1) на кожен insert, не використовуючи count_estimate? Я так розумію, вона взагалі не підходить для даного завдання.

Vitalii Valchuk 18 February 2024

можете взагалі зробити окрему таблицю зі статусами і обновляти після кожного insert дані. тобто якщо вам потрібно просто кількість користувачів зі статусом active - то вам не потрібно знати їх id, достатньо порахувати кількість, записати таблиці і в тригері додати оновлення. як варіант

Olexandr Basalaiev 21 February 2024

Я бы использовал MATERIALIZED VIEW что обновляется по расписанию

Add Comment

Current freelance projects in the category Databases & SQL

Development of the CRM module "Requests" in custom 1C (bus center)

Client Management & CRM 12 proposals 31 July

Not specified
Construction of an automatic scheme for the movement of goods and monetary flows

31 proposals 30 July

Not specified
Support for Samotur and its servers

17 proposals 27 July

22 USD
Cleaning the client database in SIMLA CRM: phones, duplicates, cities

Client Management & CRM 41 proposals 27 July

178 USD
Development of an analytical system with a dashboard

Enterprise Resource Planning (ERP) 43 proposals 20 July

Not specified

Maxim M
Kyiv, Ukraine

Projects 3
Rating -
Rating 95

Aleksandr D.

Proposals are currently absent

Current freelance projects in the category Databases & SQL

Development of the CRM module "Requests" in custom 1C (bus center)

Construction of an automatic scheme for the movement of goods and monetary flows

Support for Samotur and its servers

Cleaning the client database in SIMLA CRM: phones, duplicates, cities

Development of an analytical system with a dashboard