The technique for such requirements is called cursor. You start a transaction and use your API's functions or the SQL command DECLARE to create a cursor.
Then the query is only executed once, but you can FETCH the results in chunks.
Have you ever tested something similar in production?
As a general rule, I don't test things in production if I can avoid it. I have certainly tested them in test, however.
Example 2: add the btree_gin extension and create a composite index on created_at and tags. The problem is the same as above: I think that PostgreSQL cannot use ordering since the ...
There are two approaches:
create an index on the array:
CREATE INDEX ON items USING gin (tags);
That allows the database to quickly find the matching rows, but then it has to perform a top-n sort.
create a B-tree index on created_at:
CREATE INDEX ON items (created_at);
That will allow the database to avoid the sort, but it has to scan and discard the ...
Generate the current explain plan using explain select * from table_name;
Create a gin index on tags column and btree index on created at column. Generate the new query/explain plan post index creation to notice the cost difference and execution times.
Unless you have insert triggers that do complex stuff, an INSERT is probably bottlenecked by either CPU or I/O.
Use vmstat 1 to determine what is the case: High CPU utilization is obvious, high I/O load manifests as iowait% at 10 or higher. This assumes that you have not set CPU or I/O limits on the container.
If both values look fine, you are probably not ...
This is not a horrible load for modern server hardware.
On a write heavy DB its going to be dependent on the Hard-disk layer, triggers, functions, indexes and constraints that have to be processed/checked.
An easy way to get to the information you want is to install pgAdmin, and use the dashboard to see what is going on. Number of connections, ...
You can see that your second query has Rows Removed by Filter: 18562, while at an average, the first query has Rows Removed by Filter: 1875532.
If you run 50 individual queries, the optimizer will optimize each of them individually, and in the cases where only few rows satisfy the filter condition, it will probably choose a different and better execution ...
There are two indices on (insert_time) and (insert_time DESC). B-tree indices can be scanned backwards at practically the same speed. And insert_time is NOT NULL, so there is no point whatsoever. Drop one of those in any case.
I made some assumptions where info is missing:
Current Postgres 12.
You are free to redesign the table and lock the table ...