The trigger will never move rows that are already in the table when you create the trigger, if that is what you mean.
Apart from that, your trigger works just fine.
INSERT INTO title_basics VALUES ('dsadas', 2020, 'T', 'dsadsa', 2021, 'tit', 42, 'min', 'type', 'orig');
INSERT 0 0
No row was inserted, because the trigger function returned NULL.
SELECT * ...
Few Suggestions for yur setup
1) Use a temporary (staging) table to gather new records. Preferably you should range partition
this table with the interval of your finest aggregation (e.g. minute) using the insert timestamp. Insert timestamp contrary to the timestamp of the measurement (called sensor timestamp) is important to distinct - see the ...
The main problems I see:
The size of the table containing the detailed data. It sounds like it will take 2-3 terabytes per year
The ingestion speed of 2000 rows per second. Are you using SSD drives?
Purging 'old' data -- big DELETEs are a big cost.
Do not store all the data in a database. You don't need it. Instead write it to a plain file if ...
I have included a code sample for you to look at (and only now notice how old this question is. I hope it finds you well). There are two things that you need to keep in mind if you are switching related tables using partitioning.
1 - you need to have the same partition key (the date is fine) in both and it needs to be the same value.
2 - When switching, ...
the other possible solution requires modifying the table. This more simple approach using simple equality method and uses composite index
create table plrange ( id serial primary key,
notify_start date default now()::date,
notify_end date default now()::date + 30);
Asked to give a sample of how to use RANGE type. I do not think it possible to implement this solution without adding/modifying column to the table, during writing this i thought of another solution that would not use Range Type but still requires modifying the table will add it as another answer
There are a few gotchas with range type with indexes and ...
Converted from a comment:
I tried something similar that brought me up to the 1000 partition limit and I found the IO was just not possible with so many open file handles. This was done on a 2008r2 SSEE with direct attached storage. I would recommend looking hard at the specs for your io sub system and working with the vendor on the settings and tuning. You ...
You can't work on the partition <=> files relationship. Partitions are just affected to a Filegroup. However, splitting your Filegroup into different files will help with performance.
The numbers I always use is to have the number of partitions per Filegroup set to the MAXDOP value. I got this looking at how SQL Server split the tempdb into different ...