Do the members allow this?

Question

PostgreSQL

I read the documentation. But questions remain. I have a large database that needs to be accelerated. there are brakes when inserting new data (tested with and without indexes).

The documentation shows examples. When the base table table_base is created and partitions are created further from it, let's say by date of the form: table_base_2015, table_base_2016, table_base_2017, table_base_2018, and so on.

And then, at the time of Insert, you need to specify the correct table for the data (did I understand correctly?) INSERT INTO table_base_2018 ...

And at the time of sampling, you can select from the main table and it will take into account data from its parshiten. Right?

The question is the following. Is it possible to create a partichen called
table_base_latest where would the data get only for the last 365 days, and the rest and the rest would be stored without in some table_base_tail that would contain all the remaining data?

Answer 1

Inserting data without indexes should work as fast as possible , do you have a lot of read requests?
ps disk at least ssd?
In order for partitioning to speed up work, it is necessary that the condition of dividing data into partitions divides the load, ideally evenly, and the tablespace files themselves are placed on different physical devices so that the load on some sections does not affect the speed of working with others

table_base_latest where would the data get only for the last 365 days, and the rest and the rest would be stored without in some table_base_tail that would contain all the remaining data?

As far as I know, a regular tool cannot do this, because the data will have to be somehow moved between partitions.
But if you implement everything yourself, i.e. instead of using a regular tool, spread data across tables on your own, monitor their obsolescence and move between them, and rebuild queries depending on the date.
IMHO 'the game is not worth the candle', more precisely, there are very few cases when implementation crap will give an adequate performance increase.

Answer 2

Is it possible to create a partition ... where only the data for the last 365 days gets

No, dynamic partition boundaries are impossible in principle.

The documentation shows examples. When the base table table_base is created and partitions are created further from it

Do not climb into high matters without knowing the basics. Start with the most common RANGE PARTITIONING. One table - a bunch of sections, and no supertables.

Answer 3

And then at the moment of Insert'a you need to specify the correct table for the data (did I understand correctly?) INSERT INTO table_base_2018

Misunderstood. You can (and should) insert it into the main one (you can also insert it into partitions - but why?).

And at the time of sampling, it will be possible to select from the main table and it will take into account the data from its parshiten. Right?

of course.

I have a large database that needs to be accelerated. there are brakes when inserting new data (tested with and without indexes).

Partitioning will not help to speed up the insertion if the partitions are on the same physical disk (it can slow it down).
Partitioning can be useful in the following cases:
- selection with a condition on the partitioning column (to scan less data)
- deleting data in large blocks (for example, historical data for past years).