All Questions
Tagged with amazon-rds postgresql-performance
27 questions
1
vote
1
answer
699
views
Improving write performance for 100 million writes daily on Postgres [closed]
We have posgres 15 in RDS and the instance is db.m6g.4xlarge with 64Gb RAM.
We want to write to a table that is date partitioned and has two UUID fields (lets call them id1, id2) and few other ...
- 11
0
votes
1
answer
129
views
Select query with 90+ parameters intermittently 20x slower on RDS PostgreSQL instance when using connection pool
I'm running a select statement on a postgresql table (on an RDS instance) in order to sum up values across 2 mil rows. The select statement has about 100 parameters (mostly different countries we want ...
- 101
0
votes
1
answer
106
views
Improving bandwidth from RDS postgres
When I do $ time psql -c "select repeat('a', 123456789)" -o /dev/null -U postgres -h hostname I reliably get about 10 seconds, making the transfer speed ~12 MB/sec. I'd like to do better.
I ...
- 103
0
votes
0
answers
414
views
AWS RDS Postgres: How to see which query/process is using memory
Running a RDS Aurora Postgres cluster with two r6g.2xlarge instances that started fluctuating in memory usage around 6:50pm EST yesterday. Typically we are running with ~16gb headroom but it dips to ~...
- 101
0
votes
0
answers
446
views
How to debug this tuple await events in RDS PostgreSQL
I have been noticing that sometimes we get a lot of open connections for a long time. Enabling enhanced metrics in RDS PostgreSQL I saw the following:
It seems this tuple wait event is making a lot ...
0
votes
1
answer
177
views
RDS Postgres v14.4 CPU utilization not going below 5% when idle
I have an RDS Postgres instance on a t3.small and am trying to drive down the CPU usage of my clients connecting to it (I have a few API services connected).
I've stopped all my services connecting to ...
- 101
3
votes
1
answer
974
views
Faster alter table column type in RDS Postgres?
has anyone figured out faster way of doing postgres 14 column type change in RDS? We currently have a table that is running out of int4 space and multiple columns needs to be converted to int8. Table ...
- 31
0
votes
1
answer
2k
views
AWS RDS Postgresql and max_worker_processes
AWS RDS Postgresql 12.10
According to https://www.enterprisedb.com/postgres-tutorials/comprehensive-guide-how-tune-database-parameters-and-configuration-postgresql,
max_worker_processes Set this to ...
- 658
0
votes
0
answers
610
views
How to prevent sequences from getting out of sequence?
I have several sequences for tables (dynamic information and crosswalks) that get out of sync from the table primary keys during testing. While I can fix them, what can I do to prevent the sequences ...
2
votes
1
answer
2k
views
PostgreSQL RDS: Slower than expected performance when large table / index is not in cache
I have a fairly large table, 43GB and a 14GB index, that consists of time series cost data. I am querying by date and summing the amount. When this data is not in the cache (either OS or Postgres) the ...
- 123
1
vote
0
answers
217
views
AWS RDS Postgres - Same query run on same-ish data on two "identical" servers runs 100x slower [closed]
Slightly noob-ish question/problem: I have a query that behaves differently in two "identical" setups - when run against second server, it is 100x slower.
Setup is AWS RDS Postgres 12 - two ...
- 111
1
vote
0
answers
1k
views
Ltree query performance optimization of Postgres RDS DB
I have a AWS RDS m5.large Postgres 10.13 database that performs a lot of the following queries
SELECT "bundles".* FROM "bundles" WHERE "bundles"."version_id" = $...
- 11
2
votes
2
answers
639
views
Simple query performance on 300M row table is very slow even with beefy hardware
Hardware/PostgreSQL version:
AWS RDS db.r4.xlarge (4vCPU, 30.5GB RAM, Provisioned IOPS (SSD) storage, 2500 IOPS)
PostgreSQL version 11.
Background:
I have an ever-growing table that I feel isn't ...
- 23
0
votes
1
answer
1k
views
How can I determine what's a better configuration for my app? [closed]
I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database.
I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database. ...
- 231
4
votes
1
answer
7k
views
Why are my queries in idle state?
I'm new to postgres , I've aws rds instance running postgresql with engine version 11.5.
All my queries are clientRead has the wait_event. Why do I have all my queries in idle status.Does this mean ...
- 175
1
vote
0
answers
1k
views
Performance issues after PostgreSQL v12 upgrade
Just upgraded our RDS instance to v12.2 from v11.6, and we are seeing some of the queries are performing much slower than v11.6 instance. After the upgrade, we rebuilt the indexes using - REINDEX ...
- 11
1
vote
0
answers
469
views
Difficulty Optimizing Postgres Write Throughput
I'm trying to index a collection of roughly 127K files using a
PostgreSQL 9.6 server on RDS. I had expected the process of writing these
documents to the DB to take about 8 hours, but over time I ...
- 11
1
vote
1
answer
277
views
Optimizing a query with many permutations (using Postgresql 11)
I'm trying to figure out the optimal solution for filtering a result set by every permutation of a given input criteria. We're using Postgresql 11 on AWS RDS
I've created a sql fiddle here that ...
- 187
-2
votes
4
answers
1k
views
Should I spread many connections over a period of time, or hammer the database at same time?
I currently have a fairly small (micro, but can upgrade if necessary) Postgres db instance on Amazon RDS. I think I have a max connections of about 85 (checked through max_connections value in console)...
- 109
2
votes
1
answer
2k
views
PostgreSQL: Running SELECT DISTINCT ON ~700m rows - how to optimize?
I have a table that, if we look at just the relevant parts, has two columns: id and raw_data. id is an integer, and raw_data is a text blob. At this point, the table has no constraints or indexes ...
1
vote
0
answers
475
views
RDS ran out of space
I moved a 2 25 GB databases to an RDS instance with 100 GB of SSD storage allocated.
However, I see that I only have 2 GB of space left!
What can cause this behaviour?
At first I thought logs, but ...
- 211
1
vote
2
answers
2k
views
Why does my query hang when I put one more id in where statement? [closed]
I have a query that looks like
select count(*)
FROM (joins)
WHERE customer_id in (select id from customer limit 117) AND ... (more statements)
It involves tables with several hundred million ...
- 33
0
votes
1
answer
362
views
Postgres: query on huge (11gb ) index does not return
Using Postgres 9.6, I have created a table with 435M rows which is 120GB in size and have added an index which is 11GB in size.
I now want to iterate on the distinct values of the index, but the ...
- 1
3
votes
0
answers
3k
views
Slow query caused by nested loop on simple join?
I've been struggling with the performance on a query. Trying to do a lookup of a large set of ids in a large table (500GB). I create a temporary table of the ids with the ids as a primary key and run ...
- 195
1
vote
0
answers
300
views
Simple join unusably slow on RDS Postgres
I just setup a new AWS RDS postgres server and queries are painfully slow (> 10 hours) such that most of the time I just end up killing the query. I have done some indexing that cut the EXPLAIN cost ...
- 195
1
vote
0
answers
311
views
Amazon RDS Postgresql 9.5 CPU increase
I run a task in my Python application of ~100K jobs which takes around 5 hours, since the beginning my in RDS instance CPU starts growing...
Instance type: Postgresql 9.5 | db.t2.large
I have the ...
- 133
13
votes
1
answer
8k
views
Debug query on big table that is sometimes slow
I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...
- 233