Skip to main content
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 ...
jjanes's user avatar
  • 40.2k
10 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 ...
Erwin Brandstetter's user avatar
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=...
Erwin Brandstetter's user avatar
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 ...
mustaccio's user avatar
  • 26.2k
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. ...
Bill Karwin's user avatar
  • 15.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: ...
Laurenz Albe's user avatar
  • 53.4k
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.
yokoloko's user avatar
  • 171
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 ( ...
Paul Draper's user avatar
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 ...
Mark McWiggins's user avatar
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 &...
AdamKG's user avatar
  • 932
4 votes
Accepted

Most efficient way to UPDATE a billion rows in AWS Aurora PostgreSQL?

The fastest way is probably CREATE TABLE xy AS SELECT NULLIF(metadata, 'null') AS metadata, ... FROM level1_dataset; DROP TABLE level1_dataset; ALTER TABLE xy RENAME TO level1_dataset; But that ...
Laurenz Albe's user avatar
  • 53.4k
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 ...
Chad's user avatar
  • 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 ...
J.D.'s user avatar
  • 38k
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 ...
Bill Karwin's user avatar
  • 15.1k
3 votes
Accepted

Why would reads block other reads in Aurora PostgreSQL?

This is about table locks and streaming replication conflicts. My answer is about PostgreSQL and will apply to Amazon Aurora only to the (unknown) extent that it behaves like PostgreSQL. pg_dump has ...
Laurenz Albe's user avatar
  • 53.4k
3 votes

Extremely slow COUNT query on Aurora Postgress Serverless v2 with two tables

count() is no slower than other aggregates. But I don't understand how people can assume it to be fast. Counting the socks in your drawer isn't fast either, if you got lots of them. See here for the ...
Laurenz Albe's user avatar
  • 53.4k
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 ...
Zerodf's user avatar
  • 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 ...
ypercubeᵀᴹ's user avatar
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 ...
jjanes's user avatar
  • 40.2k
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 ...
jjanes's user avatar
  • 40.2k
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 ...
Shahrear Bin Amin's user avatar
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 ...
Brent Ozar's user avatar
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 ...
Rick James's user avatar
  • 78.5k
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 ...
J.D.'s user avatar
  • 38k
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,...
Rick James's user avatar
  • 78.5k
2 votes

How can I delete latest 2 binlog files from RDS MYSQL Aurora

"I want to have binlog identical to the master." Why would you want this? The binlogs are already not identical between these two instances, even besides the last two, because the filenames ...
Bill Karwin's user avatar
  • 15.1k
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 ...
mustaccio's user avatar
  • 26.2k
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 ...
RolandoMySQLDBA's user avatar
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 ...
mustaccio's user avatar
  • 26.2k

Only top scored, non community-wiki answers of a minimum length are eligible