Re: Maximum number of sequences that can be created

On Sun, May 13, 2012 at 1:12 AM, Віталій Тимчишин <tivv00(at)gmail(dot)com> wrote:

>
>
> 2012/5/11 Robert Klemme <shortcutter(at)googlemail(dot)com>
>
>> On Fri, May 11, 2012 at 12:50 PM, Vidhya Bondre <meetvbondre(at)gmail(dot)com>
>> wrote:
>> > Is there any max limit set on sequences that can be created on the
>> database
>> > ? Also would like to know if we create millions of sequences in a
>> single db
>> > what is the downside of it.
>>
>
> The sequences AFAIK are accounted as relations. Large list of relations
> may slowdown different system utilities like vacuuming (or may not, depends
> on queries and indexes on pg_class).
>

Not "may slow down." Change that to "will slow down and possibly corrupt"
your system.

In my experience (PG 8.4.x), the system can handle in the neighborhood of
100,000 relations pretty well. Somewhere over 1,000,000 relations, the
system becomes unusable. It's not that it stops working -- day-to-day
operations such as querying your tables and running your applications
continue to work. But system operations that have to scan for table
information seem to freeze (maybe they run out of memory, or are
encountering an O(N^2) operation and simply cease to complete).

For example, pg_dump fails altogether. After 24 hours, it won't even start
writing to its output file. The auto-completion in psql of table and
column names freezes the system. It takes minutes to drop one table.
Stuff like that. You'll have a system that works, but can't be backed up,
dumped, repaired or managed.

As I said, this was 8.4.x. Things may have changed in 9.x.

Craig