Is there a better way to implement counting in Postgresql?

Background

I'm maintaining a rest api that uses postgres for data storage and currently uses a sliding logs rate limiting method for incoming requests, it also logs each request to the database with the following format:

• id, request uuid
• created_at, timestamp
• user_id, user that made the request
• count, order of request

The definition can be seen bellow:

CREATE TABLE request_logs (
    id uuid NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id uuid NOT NULL,
    counter int4 NOT NULL,
    CONSTRAINT pk_request_logs PRIMARY KEY (id),
    CONSTRAINT fk_request_logs_user FOREIGN KEY (user_id) 
    REFERENCES user(id);
);

The count is used to avoid querying the database for every check that is made for rate limiting purposes, when a new request comes in the difference between the first and the last request counts in each window is the number of requests made in that particular time window. For example in the case of a daily limit:

-- Start of window
SELECT "counter" from request_logs WHERE user_id=<some_user_id> AND created_at>= now() - INTERVAL '1 day' ORDER BY created_at DESC LIMIT 1;

-- Most recent
SELECT "counter" from request_logs WHERE user_id=<some_user_id> AND created_at< now() ORDER BY created_at DESC LIMIT 1;

Also the total count of requests made by the user are accessed very frequently and for the time being this is done by querying the whole table to find the largest count. The query can be seen below:

SELECT "counter" from request_logs WHERE user_id=<some_user_id>
ORDER BY counter DESC LIMIT 1;

It is used in the insertion query by running the following:

INSERT INTO request_logs (id, user_id, counter) VALUES
(
<id>,
<user_id>,
COALESCE((SELECT "counter" from request_logs WHERE user_id=<some_user_id>
ORDER BY counter DESC LIMIT 1), 0) + 1
);

Problem

As you can guess this is pretty slow and also it appears that on high loads duplicate counts are inserted in the database, something like this:

I have tried using redis, for counting requests in every window, and it helped with reducing the problem but still under high load some duplicate entries are inserted. Also tried implementing an exclusive access lock when inserting a new log but that resulted in errors being thrown on concurrent requests.

I have the following questions:

1. Is there a better way to store the total count for every user id to optimize the total counting performance?
2. How do I stop duplicate insertions?