I ma trying to compress a chunk using the compress_chunk and I got the “sequence id overflow” error.
Any ideas as to what it could be or how to fix it?
SET client_min_messages TO DEBUG1;
SELECT compress_chunk('timescaledb_internal.hyper_1_2_chunk’);
DEBUG: building index “pg_toast_29929263_index” on table “pg_toast_29929263” serially
DEBUG: building index "compress_hyper_3_12_chunk__compressed_hypertable_3_ivehicleid" on table “compress_hyper_3_12_chunk” serially
DEBUG: building index “compress_hyper_3_12_chunk__compressed_hypertable_3_gid__ts_meta” on table “compress_hyper_3_12_chunk” serially
DEBUG: building index “compress_hyper_3_12_chunk__compressed_hypertable_3_irowversion_” on table “compress_hyper_3_12_chunk” serially
DEBUG: building index “compress_hyper_3_12_chunk__compressed_hypertable_3_gdiagnostici” on table “compress_hyper_3_12_chunk” serially
DEBUG: building index “compress_hyper_3_12_chunk__compressed_hypertable_3_gcontrolleri” on table “compress_hyper_3_12_chunk” serially
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.58”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.86”, size 103432192
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.85”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.84”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.83”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.82”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.81”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.80”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.79”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.78”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.77”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.76”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.75”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.74”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.73”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.72”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.71”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.70”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.69”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.68”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.67”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.66”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.65”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.64”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.63”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.62”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.61”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.60”, size 1073741824
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp713.59”, size 1073741824
ERROR: sequence id overflow
Time: 1105092.862 ms (18:25.093)
@AbrahimSA,
Nothing immediately jumps out. What version of TimescaleDB are you running, and how big is the hypertable/chunk in question?
Thanks @ryanbooz ,
The timescaledb version is 2.7.2, and the PostgreSQL version is 12.11 (Ubuntu 12.11-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
@AbrahimSA,
I happened upon your other post here that had a bit more detail in addition to what you copied above.
Can you provide a bit more detail about your database, how big the chunks are, etc. From the output you shared, it looks like you’re compressing with 4 segmentby columns (??) and the chunk must be fairly large if the job for the first chunk ran for 18 minutes.
There’s a chance you’ve run up against some resource limitation but I’m not sure how to test/replicate.
@ryanbooz , Thanks for attention. Now I am compressing with 5 segmentby columns, one interger, one bitint, and 3 uuid types.
I compressed with success the exactly same table before with 3 segmentby columns, one interger, and two uuid types.
Table total size:
table_size | index_size | toast_size | total_size
843 GB | 2260 GB | 488 kB | 3103 GB
There are currently 86 chunks, one per week, with the largest compressed chunk being 64GB (143,465,791 rows), the smallest uncompressed chunk being 94GB (260,776,341 rows), and the largest uncompressed chunk being 138GB (378,610,103).
Note: This is a test server with no other sessions running.
Let me know if you need any additional information please.
@AbrahimSA,
I’d be curious why you’re segmenting by so many columns. When you do this, TimescaleDB has to (essentially) sort all rows in a chunk by each of those columns so that the other columns can be grouped accordingly. Remember, when you don’t specify an orderby column, compression will use the time partitioning column by default.
Additionally, as you add segmentby columns, your compression ratio will likely start to drop.
While I’d love to help you understand why you’re getting the error (still not sure on that), I’d also like to help you figure out what you’re hoping to achieve with compression/columns and if there’s a better overall configuration.
@ryanbooz,
This is my first time using TimescaleDB. I added more columns to segmenting by because some queries that use these columns in the WHERE clause do not perform well with compression.
The error occurs on the largest chunks using chunk time interval 7 day, so I started a new test with 4 day to see if there is a relationship between chunk size and error.
This will take some time to complete, but I will post an update as soon as possible.
Thanks for the follow-up @AbrahimSA.
I did hear back from the team internally and it turns out that the error message you were seeing is a bug that can occur on very large chunks as a multiplication of rows and segmantby columns. That PR has been merged and will be released with an upcoming version. (possibly 2.7.3 or 2.8, depending on the timing of other commits). It’s the first time I’ve seen it, so this doesn’t happen often. 
That said, adding segmentby columns does two things. It can (possibly) reduce the efficiency of compression because once you group all of the rows together, you might not have at least 1,000 rows of each combination, so you’re getting fewer raw rows added to the overall compressed row for each segment. It is true that adding segmentby columns will create columns that are indexed for each segmentby, but that doesn’t always mean increased query speed either.
Second, it will probably take longer to compress each chunk because TimescaleDB has to use more memory to sort all of the data appropriately before it can begin compressing rows. So, there’s definitely a tradeoff, and I’d love to help you figure out if there’s a good medium ground.
If you want to continue the discussion around the queries themselves and how the compression (and the hypertable) are setup, let’s start another thread and dig in on those issues so that the title of this issue can stay as it is.
Thanks!
1 Like
@ryanbooz , thanks! I really appreciate your attention.