11
votes
Accepted
UPDATE on big table in PostgreSQL randomly takes too long
GIN indexes have a "fastupdate" mechanism where new data gets written to a specific section in a linear fashion. Once that exceeds some set size (gin_pending_list_limit, which is set ...
- 37.6k
8
votes
Accepted
AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?
My answer is not specific to AWS Aurora PostgreSQL Serverless, but for Postgres in general.
Simple alternative
In your related comment, you hinted that you only need rows from the last 24 hours. So ...
- 170k
6
votes
Accepted
Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?
One problem stands out (in all query plans) and is easy to fix:
Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089 (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.005 rows=1 loops=...
- 170k
6
votes
Accepted
How to prevent read replica restarts during high replication lag
I think this is what they would call "working as designed". It is stated in the documentation for Aurora MySQL (emphasis mine):
The tradeoff with having multiple Aurora Replicas is that ...
- 24.8k
5
votes
Does changing the character set from utf8 to ascii improve consumed space of a CHAR field on mysql?
UTF-8 is a variable-length character encoding. For characters in the ASCII range, it only takes 1 byte per character. It only uses 2, 3, or 4 bytes per character for those characters that require it.
...
- 13.1k
5
votes
Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?
Sorting 2 billion rows will always need a lot of space.
If you know that there will only be a few groups, you could try to get a hash aggregate and avoid the sort. There are two things you can try:
...
- 45.7k
4
votes
Could not start WAL streaming, replication slot is active
The issue was that the wal_(receiver|sender)_timeout were too low...
I changed the value from one minute to ten minutes and it works.
- 161
4
votes
Accepted
ERROR: improper qualified name (too many dotted names) when trying ALTER FUNCTION
Response from AWS Support:
On further researching, I was able to find that this was a known issue in Aurora Version 2.0 (compatible with 10.4) and the issue has been fixed on Aurora Version 2.1 ( ...
- 710
4
votes
Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)?
It turns out the 10.0 solution was backported (apparently) to 9.6.8, so:
pg_dumpall --host <aws_endpoint> --globals-only --no-role-passwords \
--username masteruser >roles.sql
Then run ...
- 229
4
votes
AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?
I think you're out of luck on #1. The release notes for PG 11 note the addition of that feature. And I'm not familiar with Aurora, don't have any idea about #2.
For #3, it all depends on what the &...
- 922
3
votes
Difference between Aurora Global database and Aurora regional database with multiple read replicas?
Aurora uses MySQL replication to replicate to a secondary data
while Aurora Global database uses underlying hardware to replicate your data from another region which doesn't affect your database ...
- 31
3
votes
Why SQL inserts are slower in AWS Aurora Serverless than in AWS RDS
Is it possible your Aurora Serverless instance isn't autoscaling appropriately because your cron job is a continuously long running query and / or transaction? According to Autoscaling for Aurora ...
- 34k
3
votes
Creating indexes in an amazon aws aurora mysql does have a cost?
Every type of optimization improves one type of query, at the expense of other types of queries.
You can create an index that will reduce I/O operations because it reduces the set of examined rows ...
- 13.1k
2
votes
Accepted
Which database is suitable for serving big timeseries metrics with rollups?
Amazon Athena would be a good choice for this application. However, queries might not complete in a few seconds. So, the solution might be to use Athena to generate aggregates and then load the ...
- 333
2
votes
Downgrading from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6
I think exporting to a SQL file first would work:
export to a SQL file (in S3 or elsewhere)
import to a 5.6 instance
See this: Amazon Aurora Can Export Data into Amazon S3, where it says:
You can ...
- 96.8k
2
votes
Accepted
Speed up UPDATE on large table
HOT update solution
Since the "id2" column being updated is not itself indexed, your updates are eligible for HOT (heap-only-tuple) updates. This would spare you all of the work of maintaining the ...
- 37.6k
2
votes
Accepted
Find queries causing a lot of read IO
Is your concern performance, or that you are charged per IO?
Activate "pg_stat_statements" and turn on "track_io_timing". Then you can look in "blk_read_time" column to find queries that spend a lot ...
- 37.6k
2
votes
Difference between Aurora Global database and Aurora regional database with multiple read replicas?
From aws faq
Physical replication, called Aurora Global Database, uses dedicated
infrastructure that leaves your databases entirely available to serve
your application, and can replicate to up to ...
2
votes
Accepted
Can AWS Aurora High Availability allow different Schema on Replicas?
AWS Aurora's replication is more akin to Always On Availability Group. The primary pushes storage changes to other replicas. You don't get to make schema changes on the subscriber.
More details are ...
- 42.6k
2
votes
Accepted
Growing database pains
100M rows is not scary.
Partitioning -- No. It is unlikely to add any performance benefit. If, however, you need to purge "old" data, there might be a use for partitioning.
If recordId is ...
- 76.3k
2
votes
MySQL - Can I have quick queries in a table with 200 million rows?
If you don't need to use the ORDER BY clause then yes that should help a little bit, additionally stop using SELECT * and provide the actual column list of only the columns you'll need. It'll reduce ...
- 34k
2
votes
Accepted
Does changing the character set from utf8 to ascii improve consumed space of a CHAR field on mysql?
Short answer: Yes, switch to ASCII.
Looooong answer: There are many aspects to this question.
If the strings actually vary in length, use VARCHAR instead of CHAR.
If the strings are fixed in length,...
- 76.3k
2
votes
What's the advantage of sharding in AWS Aurora?
What's the advantage of sharding in AWS Aurora?
What's the advantage of sharding in general?
It allows you to distribute your workload across multiple compute resources, each working with its own ...
- 24.8k
2
votes
Is it safe to enable Fast DDL in Production
Just shooting from the hip, I would not enable it since Aurora has its own universe of options and operations that could cause the garden variety MySQL DBA to break things in their environment.
One ...
- 181k
2
votes
Is it safe to enable Fast DDL in Production
I'm obviously speculating, but "lab mode" sounds like a way to enable experimental or "beta1" features. By definition, such features have not been thoroughly tested and can have ...
- 24.8k
2
votes
Time zone conversion in DMS from DB2 to Aurora Postgres
Regarding:
DMS is connecting to PG and appears to be setting the connection time zone to UTC. We have tried changing the PG server config time zone to America/New_York to try and get the DMS ...
- 21
1
vote
Accepted
Load text column to postgres
The mysql command you show seems (experimentally, not from actual knowledge) to generate output which is compatible with PostgreSQL's TEXT format, not the CSV format. Newlines are represented by the ...
- 37.6k
1
vote
Limiting user access by subnet to Amazon Aurora PostgreSQL
here are two ideas that can be tried..
Run a script or other little app in the background that scans through the killing connections that do not match the established rules
it fires off every few ...
- 858
1
vote
AWS Aurora RDS 2nd reader gets no queries
We have also faced the same issue and it got resolve after reduced the DNS TTL value in our Java application.
Amazon Aurora for MySQL or PostgreSQL distribute workload between reader nodes as below (...
- 398
Only top scored, non community-wiki answers of a minimum length are eligible