About Trigger no postgres

Asked

Viewed 187 times

0

Good guys, my problem is this, I want to make a table partitioning in postgres because a table has zillions of records and searching for it takes a lot (this table tends to grow considerably in the next months) and one solution I found was to make table partitioning (if anyone has another solution) using Trigger only that so far so good, but by the time I insert a die into my table it calls the kitten, I can get this information, only all the data comes in one piece. Some fields I can convert to text, but others like the date it does not convert neither to date nor to string, the question is how do I leave it with its standard data types? If there is no way to leave this date field with the date type.

database postgresql oracle
  • 1

    Rarely is partitioning the solution, it is taking longer has something else wrong. Indexes make access almost equal regardless of table size. Even if you insist on this error, Trigger probably the worst resource to do this. The rest seems too confusing, and probably unnecessary. See: http://answall.com/q/35088/101, http://answall.com/q/101065/101, http://answall.com/q/32052/101, http://answall.com/q/23348/101, http://use-the-index-luke.com/

  • The database already has almost 1 Tera of data, only this table is 4 gigas and its trend is double in the next few months. The indexes help, but over time it gets slower. If you have another solution.

  • 1

    I think you’re wrong. My experience and other people’s experience is different. And the data on how they work shows this. When the table doubles in size (no matter the size) when using index it is like adding a single new row to search for what you want. It’s so tiny, you can barely measure it. If you do what you’re trying to do, it’ll make all the recordings more expensive, which at a high volume can be tragic. I reaffirm that if you are having performance problems with index it is because you are doing something wrong.

  • No matter how many rows I have in a table, the indexes will always improve, because this table has over 500 million records. What you suggest I improve, this table already has indices that in the beginning was good, but now it is not advancing much.

  • It would be good to put the structure of the tables in the question. Great chance to be modeling problem. As for partitioning with Trigger, I find misuse of the two features (Trigger, and partitioning).

  • @Johnatandantas, because you do not create a clone of the same table( I say call create table without the constraints and indices ) and make select only the data you want or scan a certain amount of data using limit?

Show 1 more comment

1 answer

0

Friend, combining the heritage feature, Rigger and check, you can partition the table automatically creating a new table without having to do manual. The Particionada table will have indices and it will also help in the performance. Partitioning is indicated for tables that have large volumes loaded in a few times. If it’s them talking in lines, it would be 50 million lines per table, at least. Your modeling needs to be reviewed and if you are using Iberian, it will be difficult to insert in Sert because it will not identify the entered record.

To partition, first identify the best criterion (year, account id, etc). Then create a script to create the tables with the Inherits command. Thus the daughter table will inherit all the columns of the parent table and still the later changes that there are. create a check() in the daughter table indicating the insertion/partitioning criterion (e.g. Year). All sql queries must contain the checked key field(year), otherwise there will be no performance improvement.

  • I made a script to create table partitioning using inheritance, partitioning became legal and I checked the client id. I wanted to see if it was right because I didn’t want to touch the application, delegate that responsibility to the bank. I use Ibernate, will it give problems like you said? If not to do will be the way to delegate this responsibility to java.

Browser other questions tagged database postgresql oracle

You are not signed in. Login or sign up in order to post.