Re: Sequences vs statistics - Mailing list pgsql-admin

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.