On Wed, Feb 5, 2020 at 9:12 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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.
