Re: Logical Replication speed-up initial data

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Logical Replication speed-up initial data
Дата
Msg-id CAMkU=1wyY7hzqSTZA=Yp7FRJUP-PPbJPoG3z+71iD7X44GatJg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Logical Replication speed-up initial data  (Nikhil Shetty <nikhil.dba04@gmail.com>)
Список pgsql-performance
On Thu, Aug 5, 2021 at 12:57 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi,

Thank you for the suggestion.

We tried by dropping indexes and it worked faster compared to what we saw earlier. We wanted to know if anybody has done any other changes that helps speed-up initial data load without dropping indexes.

If index maintenance is the bottleneck, nothing but dropping the indexes is likely to be very effective.  Just make sure not to drop the replica identity index.  If you do that, then the entire sync will abort and rollback once it gets to the end, if the master had had any UPDATE or DELETE activity on that table during the sync period.  (v14 will remove that problem--replication still won't proceed until you have the index, but previous synced work will not be lost while it waits for you to build the index.)

Syncing with the index still in place might go faster if shared_buffers is large enough to hold the entire incipient index(es) simultaneously.  It might be worthwhile to make shared_buffers be a large fraction of RAM (like 90%) if doing so will enable the entire index to fit into shared_buffers and if nothing else significant is running on the server.  You probably wouldn't want that as a permanent setting though.

Cheers,

Jeff

В списке pgsql-performance по дате отправления:

Предыдущее
От: Vijaykumar Jain
Дата:
Сообщение: Re: Logical Replication speed-up initial data
Следующее
От: Alex
Дата:
Сообщение: Slow query because lexeme index not used