Thread: Sequences vs statistics
Hi, I'm in the process of deciding whether to use.
-1 sequence per table containing the number of rows
-Using the collected statistics and functions like pg_relation_size
This is my use case:
I have several hundred tables in one database that I would like to
constantly monitor as far as size or number of records.
When a certain threshold is met, I will not allow any further inserts
into that table.
The threshold-met-event can be approximate.
How will I affect the performance of the server if I chose to query
for the system statistics data say, every 5 seconds as opposed to
just reading the latest_value of a sequence that gets updated every time
a row is added to its corresponding table?
Thank you
Herbey Eric Zepeda wrote > Hi, I'm in the process of deciding whether to use. > > -1 sequence per table containing the number of rows > -Using the collected statistics and functions like pg_relation_size > > This is my use case: > > I have several hundred tables in one database that I would like to > constantly monitor as far as size or number of records. > > When a certain threshold is met, I will not allow any further inserts > into that table. > The threshold-met-event can be approximate. > > How will I affect the performance of the server if I chose to query > for the system statistics data say, every 5 seconds as opposed to > just reading the latest_value of a sequence that gets updated every time > a row is added to its corresponding table? > > Thank you Assuming you are using "serial" primary keys on these tables, or just add one if you are not (though you wouldn't have to make it a PK), why not add a check constraint on the column that the serial values cannot be larger than a given number? I get the feeling I handing over a loaded gun without any idea how you plan to use it... The use-case is one I have not encountered before. Sequences have well-documented limitations. Where do you plan on doing this monitoring? How large are your thresholds going to be? Do you plan to allow for deleting of data on these tables - thus making the sequence considerably less accurate? And if they delete enough records do you want to re-enable insertions? Maybe someone can answer the performance comparison question but if you really care likely you should setup test-case yourself since given the unusualness of your need I'm doubting anything official has been done in this area and so you may just get "gut feel" answers from some of the more knowledgeable persons on the list. I'm guessing you doing multi-tenant data hosting and want to try and impose some form of quota on your users - where each user having a single set of dedicated tables. If so (or even if not) it is generally recommended to be as open as to "why" you are doing something and not just say what you are trying to do. You'll get much better help the more of the big-picture you provide. If so disk-space is likely more important than record count. For many tables you can calculate a reasonable ratio of rows-to-size but for some tables (document storage for instance) that is impossible and you will have to monitor system attributes and not just a sequence counter. I guess hard-limits to avoid denial-of-service attacks (or mistakes) has some merit but I would have an alternative set of thresholds, and a corresponding monitoring system, to issue soft warnings. And note that filling up a table is only one of many vectors that need consideration (again, this said not knowing why you feel you need to institute this limit). So, in the end you probably should just figure out how to monitor the system (catalogs and function calls) and only if the performance hit becomes too much consider ways to get more resources. Likely via some kind of sharding instead of figuring out a faster way to monitor. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Sequences-vs-statistics-tp5776207p5776211.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
On 2013-10-28 23:56, David Johnston wrote: > Herbey Eric Zepeda wrote >> Hi, I'm in the process of deciding whether to use. >> >> -1 sequence per table containing the number of rows >> -Using the collected statistics and functions like pg_relation_size >> >> This is my use case: >> >> I have several hundred tables in one database that I would like to >> constantly monitor as far as size or number of records. >> >> When a certain threshold is met, I will not allow any further inserts >> into that table. >> The threshold-met-event can be approximate. >> >> How will I affect the performance of the server if I chose to query >> for the system statistics data say, every 5 seconds as opposed to >> just reading the latest_value of a sequence that gets updated every time >> a row is added to its corresponding table? >> >> Thank you > Assuming you are using "serial" primary keys on these tables, or just add > one if you are not (though you wouldn't have to make it a PK), why not add a > check constraint on the column that the serial values cannot be larger than > a given number? > > I get the feeling I handing over a loaded gun without any idea how you plan > to use it... > > The use-case is one I have not encountered before. Sequences have > well-documented limitations. Where do you plan on doing this monitoring? > How large are your thresholds going to be? Do you plan to allow for > deleting of data on these tables - thus making the sequence considerably > less accurate? And if they delete enough records do you want to re-enable > insertions? > > Maybe someone can answer the performance comparison question but if you > really care likely you should setup test-case yourself since given the > unusualness of your need I'm doubting anything official has been done in > this area and so you may just get "gut feel" answers from some of the more > knowledgeable persons on the list. > > I'm guessing you doing multi-tenant data hosting and want to try and impose > some form of quota on your users - where each user having a single set of > dedicated tables. If so (or even if not) it is generally recommended to be > as open as to "why" you are doing something and not just say what you are > trying to do. You'll get much better help the more of the big-picture you > provide. If so disk-space is likely more important than record count. For > many tables you can calculate a reasonable ratio of rows-to-size but for > some tables (document storage for instance) that is impossible and you will > have to monitor system attributes and not just a sequence counter. > > I guess hard-limits to avoid denial-of-service attacks (or mistakes) has > some merit but I would have an alternative set of thresholds, and a > corresponding monitoring system, to issue soft warnings. And note that > filling up a table is only one of many vectors that need consideration > (again, this said not knowing why you feel you need to institute this > limit). > > So, in the end you probably should just figure out how to monitor the system > (catalogs and function calls) and only if the performance hit becomes too > much consider ways to get more resources. Likely via some kind of sharding > instead of figuring out a faster way to monitor. > > David J. > > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Sequences-vs-statistics-tp5776207p5776211.html > Sent from the PostgreSQL - admin mailing list archive at Nabble.com. > > Just some ideas here. You could maybe have a generic trigger function that looks up the statistics using TG_RELID (so that the same table-agnostic function can be used across all tables for which you want this monitoring) and rejects inserts if the estimated row count is over some threshold. You could also add in a "raise notice" if the row count is lower than that threshold but larger than some secondary threshold (to enable warnings before hitting your limit) - probably wouldn't hurt to raise a similar notice for inserts that are altogether blocked so that you have a way of identifying outside of the database when that happens. Your use case is definitely going to play a big part in the decision making, though. It's pretty easy to bypass the restrictions this approach gives you (and probably any approach): if your limit were 100 rows, then how do you stop a query that's trying to insert 10,000 rows in one shot? You might couple that with some post hoc checking with something like the check_postgres nagios plugin (http://bucardo.org/check_postgres/check_postgres.pl.html). Depending on what you can get away with, you might also consider not even using the trigger for the automatic insert prevention and instead just use the plugin to watch table sizes; especially for newer developers coming on board later on it can be confusing why an insert claims that zero rows were successfully inserted when a trigger blocks the insert. To address your actual question, unless you've got a relatively large number of table in your database, looking up statistics values probably isn't going to be too expensive. Similarly, checking the current value of a sequence probably isn't too expensive either, but as was pointed out this approach can fall out of sync with reality pretty quickly (if you were to delete rows or manually alter the sequence to a different value). I'd emphasize maintainability over performance in deciding how to do this. I think the simplest approach would just be using check_postgres to watch table sizes, warn if an estimated row count is over a certain threshold, and notify critical if its over an even higher threshold.