Massive insert vs heavy contention in LWLock:buffer_content

I'm working on an application which performs a lot of inserts in 2 large
tables.
Previously we didn't know about lwlocks, but we're now testing in Amazon RDS
Aurora - PostgreSQL (9.6.3).
In previous load tests, both local servers and classic Amazon RDS, there was
some scalability limit we couldn't find - CPU / memory / IO were all low,
but still there was contention that wasn't visible in PostgreSQL views.
Now with Aurora it shows that most of the sessions are blocking on
LWLock:buffer_content.

I would like some insights, as we have 2 tables with ~35 million rows each,
and they have several indexes (shown below).
This request is a crucial operation for our system, and each application
request must insert on those 2 large tables in a single transaction, plus
some other selects.

I've searched a lot and found nothing on how to mitigate this issue. Just
found that it might be related to inserts.

Any tips?

For reference, here are the descriptions of both tables:

\d transactions
Tabela
"public.transactions"
Coluna | Tipo
| Modificadores
---------------------------------------------------+-----------------------------+-------------------------------------------------------------------
id | bigint
| não nulo valor padrão de nextval('transactions_id_seq'::regclass)
subclass | character varying(31)
|
amount | numeric
| não nulo
authorization_status | character varying(255)
|
date | timestamp without time
zone | não nulo
description | text
|
transaction_feedback_expiration_notified | boolean
|
transaction_feedback_expiration_reminder_notified | boolean
|
transaction_feedback_reminder_notified | boolean
|
by_id | bigint
|
channel_id | bigint
| não nulo
feedback_id | bigint
|
from_user_id | bigint
|
next_authorization_level_id | bigint
|
to_user_id | bigint
|
type_id | bigint
| não nulo
order_id | bigint
|
status | character varying(255)
|
received | boolean
|
principal_type_id | bigint
|
access_client_id | bigint
|
original_transfer_id | bigint
|
show_to_receiver | boolean
|
expiration_date | timestamp without time
zone |
scheduled | boolean
|
first_installment_immediate | boolean
|
installments_count | integer
|
process_date | timestamp without time
zone |
comments | text
|
transaction_id | bigint
|
sms_code | character varying(255)
|
external_principal_value | character varying(255)
|
external_principal_type_id | bigint
|
received_by_id | bigint
|
from_name | character varying(255)
|
to_name | character varying(255)
|
next_occurrence_date | timestamp without time
zone |
occurrences_count | integer
|
occurrence_interval_amount | integer
|
occurrence_interval_field | character varying(255)
|
last_occurrence_failure_id | bigint
|
last_occurrence_success_id | bigint
|
by_self | boolean
|
from_system | boolean
|
to_system | boolean
|
ticket_number | character varying(255)
|
cancel_url | character varying(255)
|
success_url | character varying(255)
|
transaction_number | character varying(255)
|
expiration_date_comments | text
|
Índices:
"transactions_pkey" PRIMARY KEY, btree (id)
"ix_external_principal_value" btree (external_principal_value) WHERE
external_principal_value IS NOT NULL
"ix_recurring_next_occurrence_date" btree (next_occurrence_date) WHERE
next_occurrence_date IS NOT NULL
"ix_ticket_number" btree (lower(ticket_number::text)) WHERE
ticket_number IS NOT NULL
"ix_transactions_amount" btree (amount)
"ix_transactions_date" btree (date)
"ix_transactions_fk_transactions_access_client_id" btree
(access_client_id) WHERE access_client_id IS NOT NULL
"ix_transactions_fk_transactions_by_id" btree (by_id) WHERE by_id IS NOT
NULL
"ix_transactions_fk_transactions_channel_id" btree (channel_id)
"ix_transactions_fk_transactions_external_principal_type_id" btree
(external_principal_type_id) WHERE external_principal_type_id IS NOT NULL
"ix_transactions_fk_transactions_feedback_id" btree (feedback_id) WHERE
feedback_id IS NOT NULL
"ix_transactions_fk_transactions_from_user_id" btree (from_user_id)
WHERE from_user_id IS NOT NULL
"ix_transactions_fk_transactions_last_occurrence_failure_id" btree
(last_occurrence_failure_id) WHERE last_occurrence_failure_id IS NOT NULL
"ix_transactions_fk_transactions_last_occurrence_success_id" btree
(last_occurrence_success_id) WHERE last_occurrence_success_id IS NOT NULL
"ix_transactions_fk_transactions_next_authorization_level_id" btree
(next_authorization_level_id) WHERE next_authorization_level_id IS NOT NULL
"ix_transactions_fk_transactions_order_id" btree (order_id) WHERE
order_id IS NOT NULL
"ix_transactions_fk_transactions_original_transfer_id" btree
(original_transfer_id) WHERE original_transfer_id IS NOT NULL
"ix_transactions_fk_transactions_principal_type_id" btree
(principal_type_id) WHERE principal_type_id IS NOT NULL
"ix_transactions_fk_transactions_received_by_id" btree (received_by_id)
WHERE received_by_id IS NOT NULL
"ix_transactions_fk_transactions_to_user_id" btree (to_user_id) WHERE
to_user_id IS NOT NULL
"ix_transactions_fk_transactions_transaction_id" btree (transaction_id)
WHERE transaction_id IS NOT NULL
"ix_transactions_fk_transactions_type_id" btree (type_id)
"ix_transactions_subclass" btree (subclass)
"ix_transactions_transaction_number" btree
(lower(transaction_number::text)) WHERE transaction_number IS NOT NULL
"next_occurrence_date" btree (next_occurrence_date)
Restrições de chave estrangeira:
"fk_transactions_access_client_id" FOREIGN KEY (access_client_id)
REFERENCES access_clients(id)
"fk_transactions_by_id" FOREIGN KEY (by_id) REFERENCES users(id)
"fk_transactions_channel_id" FOREIGN KEY (channel_id) REFERENCES
channels(id)
"fk_transactions_external_principal_type_id" FOREIGN KEY
(external_principal_type_id) REFERENCES principal_types(id)
"fk_transactions_feedback_id" FOREIGN KEY (feedback_id) REFERENCES
refs(id)
"fk_transactions_from_user_id" FOREIGN KEY (from_user_id) REFERENCES
users(id)
"fk_transactions_last_occurrence_failure_id" FOREIGN KEY
(last_occurrence_failure_id) REFERENCES failed_payment_occurrences(id)
"fk_transactions_last_occurrence_success_id" FOREIGN KEY
(last_occurrence_success_id) REFERENCES transfers(id)
"fk_transactions_next_authorization_level_id" FOREIGN KEY
(next_authorization_level_id) REFERENCES authorization_levels(id)
"fk_transactions_order_id" FOREIGN KEY (order_id) REFERENCES
ad_orders(id)
"fk_transactions_original_transfer_id" FOREIGN KEY
(original_transfer_id) REFERENCES transfers(id)
"fk_transactions_principal_type_id" FOREIGN KEY (principal_type_id)
REFERENCES principal_types(id)
"fk_transactions_received_by_id" FOREIGN KEY (received_by_id) REFERENCES
users(id)
"fk_transactions_to_user_id" FOREIGN KEY (to_user_id) REFERENCES
users(id)
"fk_transactions_transaction_id" FOREIGN KEY (transaction_id) REFERENCES
transactions(id)
"fk_transactions_type_id" FOREIGN KEY (type_id) REFERENCES
transfer_types(id)
Referenciada por:
TABLE "amount_reservations" CONSTRAINT
"fk_amount_reservations_external_payment_id" FOREIGN KEY
(external_payment_id) REFERENCES transactions(id)
TABLE "amount_reservations" CONSTRAINT
"fk_amount_reservations_scheduled_payment_id" FOREIGN KEY
(scheduled_payment_id) REFERENCES transactions(id)
TABLE "amount_reservations" CONSTRAINT
"fk_amount_reservations_transaction_id" FOREIGN KEY (transaction_id)
REFERENCES transactions(id)
TABLE "failed_payment_occurrences" CONSTRAINT
"fk_failed_payment_occurrences_recurring_payment_id" FOREIGN KEY
(recurring_payment_id) REFERENCES transactions(id)
TABLE "refs" CONSTRAINT "fk_refs_transaction_id" FOREIGN KEY
(transaction_id) REFERENCES transactions(id)
TABLE "scheduled_payment_installments" CONSTRAINT
"fk_scheduled_payment_installments_scheduled_payment_id" FOREIGN KEY
(scheduled_payment_id) REFERENCES transactions(id)
TABLE "transaction_authorizations" CONSTRAINT
"fk_transaction_authorizations_transaction_id" FOREIGN KEY (transaction_id)
REFERENCES transactions(id)
TABLE "transaction_custom_field_values" CONSTRAINT
"fk_transaction_custom_field_values_owner_id" FOREIGN KEY (owner_id)
REFERENCES transactions(id)
TABLE "transactions" CONSTRAINT "fk_transactions_transaction_id" FOREIGN
KEY (transaction_id) REFERENCES transactions(id)
TABLE "transfers" CONSTRAINT "fk_transfers_transaction_id" FOREIGN KEY
(transaction_id) REFERENCES transactions(id)
TABLE "voucher_packs" CONSTRAINT "fk_voucher_packs_buy_id" FOREIGN KEY
(buy_id) REFERENCES transactions(id)
TABLE "vouchers" CONSTRAINT "fk_vouchers_redeem_id" FOREIGN KEY
(redeem_id) REFERENCES transactions(id)

------------------------------------------------------------------

\d transfers
Tabela
"public.transfers"
Coluna | Tipo |
Modificadores
----------------------------------+-----------------------------+----------------------------------------------------------------
id | bigint | não nulo
valor padrão de nextval('transfers_id_seq'::regclass)
subclass | character varying(31) |
amount | numeric | não nulo
date | timestamp without time zone | não nulo
emission_date | timestamp without time zone |
expiration_date | timestamp without time zone |
from_id | bigint | não nulo
parent_id | bigint |
to_id | bigint | não nulo
type_id | bigint | não nulo
charged_back_by_id | bigint |
user_account_fee_log_id | bigint |
chargeback_of_id | bigint |
transaction_id | bigint |
scheduled_payment_installment_id | bigint |
transfer_fee_id | bigint |
number | integer |
by_id | bigint |
transaction_number | character varying(255) |
Índices:
"transfers_pkey" PRIMARY KEY, btree (id)
"ix_transfers_amount" btree (amount)
"ix_transfers_date" btree (date)
"ix_transfers_fk_transfers_by_id" btree (by_id) WHERE by_id IS NOT NULL
"ix_transfers_fk_transfers_chargeback_of_id" btree (chargeback_of_id)
WHERE chargeback_of_id IS NOT NULL
"ix_transfers_fk_transfers_charged_back_by_id" btree
(charged_back_by_id) WHERE charged_back_by_id IS NOT NULL
"ix_transfers_fk_transfers_from_id" btree (from_id)
"ix_transfers_fk_transfers_parent_id" btree (parent_id) WHERE parent_id
IS NOT NULL
"ix_transfers_fk_transfers_scheduled_payment_installment_id" btree
(scheduled_payment_installment_id) WHERE scheduled_payment_installment_id IS
NOT NULL
"ix_transfers_fk_transfers_to_id" btree (to_id)
"ix_transfers_fk_transfers_transaction_id" btree (transaction_id) WHERE
transaction_id IS NOT NULL
"ix_transfers_fk_transfers_transfer_fee_id" btree (transfer_fee_id)
WHERE transfer_fee_id IS NOT NULL
"ix_transfers_fk_transfers_type_id" btree (type_id)
"ix_transfers_fk_transfers_user_account_fee_log_id" btree
(user_account_fee_log_id) WHERE user_account_fee_log_id IS NOT NULL
"ix_transfers_transaction_number" btree
(lower(transaction_number::text)) WHERE transaction_number IS NOT NULL
Restrições de chave estrangeira:
"fk_transfers_by_id" FOREIGN KEY (by_id) REFERENCES users(id)
"fk_transfers_chargeback_of_id" FOREIGN KEY (chargeback_of_id)
REFERENCES transfers(id)
"fk_transfers_charged_back_by_id" FOREIGN KEY (charged_back_by_id)
REFERENCES transfers(id)
"fk_transfers_from_id" FOREIGN KEY (from_id) REFERENCES accounts(id)
"fk_transfers_parent_id" FOREIGN KEY (parent_id) REFERENCES
transfers(id)
"fk_transfers_scheduled_payment_installment_id" FOREIGN KEY
(scheduled_payment_installment_id) REFERENCES
scheduled_payment_installments(id)
"fk_transfers_to_id" FOREIGN KEY (to_id) REFERENCES accounts(id)
"fk_transfers_transaction_id" FOREIGN KEY (transaction_id) REFERENCES
transactions(id)
"fk_transfers_transfer_fee_id" FOREIGN KEY (transfer_fee_id) REFERENCES
transfer_fees(id)
"fk_transfers_type_id" FOREIGN KEY (type_id) REFERENCES
transfer_types(id)
"fk_transfers_user_account_fee_log_id" FOREIGN KEY
(user_account_fee_log_id) REFERENCES user_account_fee_logs(id)
Referenciada por:
TABLE "account_balances" CONSTRAINT "fk_account_balances_transfer_id"
FOREIGN KEY (transfer_id) REFERENCES transfers(id)
TABLE "failed_payment_occurrences" CONSTRAINT
"fk_failed_payment_occurrences_transfer_id" FOREIGN KEY (transfer_id)
REFERENCES transfers(id)
TABLE "transactions" CONSTRAINT
"fk_transactions_last_occurrence_success_id" FOREIGN KEY
(last_occurrence_success_id) REFERENCES transfers(id)
TABLE "transactions" CONSTRAINT "fk_transactions_original_transfer_id"
FOREIGN KEY (original_transfer_id) REFERENCES transfers(id)
TABLE "transfer_status_logs" CONSTRAINT
"fk_transfer_status_logs_transfer_id" FOREIGN KEY (transfer_id) REFERENCES
transfers(id)
TABLE "transfers" CONSTRAINT "fk_transfers_chargeback_of_id" FOREIGN KEY
(chargeback_of_id) REFERENCES transfers(id)
TABLE "transfers" CONSTRAINT "fk_transfers_charged_back_by_id" FOREIGN
KEY (charged_back_by_id) REFERENCES transfers(id)
TABLE "transfers" CONSTRAINT "fk_transfers_parent_id" FOREIGN KEY
(parent_id) REFERENCES transfers(id)
TABLE "transfers_transfer_status_flows" CONSTRAINT
"fk_transfers_transfer_status_flows_transfer_id" FOREIGN KEY (transfer_id)
REFERENCES transfers(id)

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html