Java – Hibernate: automatically split tables every month

I'm working on a spring MVC application with PostgreSQL and hibernate. I have several tables with more than 100000 rows For these tables, I only access the latest data 95% of the time, and filtering all these rows is time-consuming Many times the query planner doesn't even use indexes (I don't know why)

Then I think of splitting the database table every week, so we can access the latest month database first, and then if the user requests, more results in the previous table will be added directly to the request

Most of the queries we execute require us to use join because the table is one - to - many mapped

One of the model files with a row count of > exceeds 100000

model:

@Entity
@Table(name = "groupnotehistory")
public class GroupNoteHistory {

    @Id
    @Column(name = "mhistoryid")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "mhistory_gen")
    @SequenceGenerator(name = "mhistory_gen",sequenceName = "mhistory_seq")
    private int mhistoryid;


    @Column(name = "mnoteeditdate",columnDeFinition = "timestamp without time zone")
    private Timestamp mnoteEditDate;

    @Column(name = "oldheadline",columnDeFinition = "character varying")
    @Type(type="jasyptHibernateEncryptor")
    private String oldHeadLine;

    @Column(name = "oldtext",columnDeFinition = "character varying")
    @Type(type="jasyptHibernateEncryptor")
    private String oldText;

    @Column(name = "newnotetext",columnDeFinition = "character varying")
    @Type(type="jasyptHibernateEncryptor")
    private String newNoteText;

    @Column(name = "newnoteheadline",columnDeFinition = "character varying")
    @Type(type="jasyptHibernateEncryptor")
    private String newNoteHeadline;


@JsonIgnore
    @ManyToOne
    @JoinColumn(name = "mnoteid",nullable = false)
    private GroupNotes mhistory;
// Getters and setters

}

Does hibernate provide any strategies or methods to solve this problem? thank you.

Solution

How to divide data into several tables, such as:

>Current value (up to one week) – table latest_ Values > is also an older value (up to one year) – table year_ Values > all values (full history) – table all_ values

Always insert latest_ Values and push the rule to other values

Run "delete from latest_values where insert_date < current_date - 7" daily or weekly Run "delete from year_values where insert_date < current_date - 365" every month (leap years are irrelevant) In this way, you can keep a smaller table, easily retrieve more current values, and still get all the values if the query needs them Splitting each week into a single table (that's what I understand you think) will leave you too many tables and complex - so slow - if you need several weeks of data, the union operation