Re: Writing 1100 rows per second

Поиск
Список
Период
Сортировка
От Ogden Brash
Тема Re: Writing 1100 rows per second
Дата
Msg-id CAFCR_K020_MBCHCOH6TGwuXD4YAQBcnSdent0ONHODhsykPngA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Writing 1100 rows per second  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-performance


On Wed, Feb 5, 2020 at 9:12 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
One idea I can come up with is a table that is partitioned by a column that appears
in a selective search condition, but have no indexes on the table, so that you always get
away with a sequential scan of a single partition.


This is an approach that I am currently using successfully. We have a large dataset that continues to be computed and so insert speed is of importance to us. The DB currently has about 45 billion rows. There are three columns that are involved in all searches of the data. We have separate tables for all unique combination of those 3 values (which gives us about 2000 tables). Thus, we were able to save the space for having to store those columns (since the name of the table defines what those 3 columns are in that table). We don't have any indices on those tables (except for the default one which gets created for the pk serial number). As a result all searches only involve 1 table and a sequential scan of that table. The logic to choose the correct tables for insertionse or searches lives in our application code and not in SQL.

The size of the 2000 tables forms a gaussian distirbution, so our largest table is about a billion rows and there are many tables that have hundreds of millions of rows. The ongoing insertions form the same distribution, so the bulk of insertions is happening into the largest tables. It is not a speed demon and I have not run tests recently but back of the envelope calculations give me confidence that we are definitely inserting more than 1100 per second. And that is running the server on an old puny i5 processor with regular HDDs and  only 32Gb of memory.

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

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: Slow performance with trivial self-joins
Следующее
От: Asya Nevra Buyuksoy
Дата:
Сообщение: TOAST table performance problem