Recommended Sequence Cache Size

[usli06]

Hi,

The default size of a sequence cache is 250,000. That seems awful high to me considering if I only insert a few rows per session I'll lose a lot of the sequence values and they'll be huge gaps in my primary key. Does anyone have a recommendation on a cache size? I've tried no cache, but the performance of that is awful

Thanks!

[JimKnicely]

Hi,

For fun I did some testing with different sequence cache sizes.

I creates simple table named JIM:

Code: Select all

dbadmin=> \d jim;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | jim   | c      | int  |    8 |         | f        | f           | 
(1 row)

Then I created a sequence named JIM_SEQ having varying cache sizes and ran the following insert statement with the timing option turned on:

Code: Select all

insert into jim select jim_seq.nextval from tables cross join columns cross join tables t cross join columns c;

Here are the results:

Code: Select all

CACHE SIZE     TIMING
----------  ------------
250000         98.639 ms
10000         100.439 ms
1000          137.220 ms
500           190.552 ms
250           217.527 ms
100           490.846 ms
50            663.952 ms
10           2804.818 ms
1           27312.546 ms
NO CACHE    27158.361 ms

Seems like 500 might be the sweet spot.

Note that I think you should have the cache size set to the number of sequences values you'll need per session.

Thanks!

[usli06]

Thanks, Jim! Awesome time tests!