pgtune | Pgtune - tuning PostgreSQL config by your hardware | Database library

There is not much you can configure here. The exceptions are:

• shared_buffers, work_mem and max_connections: these three determine your memory usage. You need to divide shared_buffers and max_connections or work_mem between the clusters.

• effective_cache_size: split that as well.

• effective_io_concurrency should be split as well.

Most resources cannot be controlled effectively from the configuration. You may want to give pg_cgroups a try (disclaimer: I am the author).

Timescale person here. Will need to understand more about your setup (which version of Timescale, which platform, which OS, etc.) to be able to help.

Please see the info we ask on our github: https://github.com/timescale/timescaledb/issues/new/choose

But in general, the proper way in PG to remove an extension in DROP EXTENSION, not mucking with things internally in the pg_extension table (presumably with super-user permissions)? I'm not sure what state you've left the database.

You are filtering your data on status and tool_id, and sorting on updated_at but you have no single index for all three of those columns.

Add an index, like so:

CREATE INDEX ON projects_toolresult (status, tool_id, updated_at);

Your query is already optimal. There is no way to avoid scanning the whole table to get the information you need, and a sequential scan is the best way to do that.

Make sure that work_mem is big enough that the aggregationcan be done in memory – you can set log_temp_files to monitor if temporary files are used (which makes things much slower).

The best load test is with a real-world load; the closer to that your load test is, the better.

If you have many concurrent requests, connection pooling is a must and pgBouncer is the standard answer.

It is impossible to do performance tuning in the scope of an answer, and indeed this question will probably be closed for being too broad, but I'll give you some general leads:

The goal is to find the bottlenecks, that is resources where your system is at the limit. Narrow it down: is it the application, the web server, or the database? Once you know which component limits you, find the individual limiting resource. Is it I/O? CPU time? Memory? The time it takes to establish a database connection? Locks?

One important rule is not to start twiddling knobs at random before you know where the problem is. That is likely to give you a misconfigured system. Find a theory, try a solution, and if it does not have the desired effect, reset the setting to the value it had before.

I don't understand your setup: First you say that you have a machine for the database and one for the application, then you say that you tried local socket connections.