Monitoring your Amazon Aurora PostgreSQL-Compatible and Amazon RDS PostgreSQL from integer sequence overflow

Primary keys and unique identifiers are fundamental to database design, as they provide a reliable way to uniquely identify and reference individual records. There are two main approaches to generating these identifiers – natural keys and surrogate keys. Natural keys are based on inherent attributes of the data, such as a person’s social security number or a product’s serial number. However, natural keys can become cumbersome to work with, especially if the underlying data changes over time. As a result, most databases opt for surrogate keys – synthetic identifiers that are automatically generated and have no inherent meaning. These surrogate keys are typically implemented as randomly generated integers, as they are simple to work with and integrate seamlessly with many software frameworks and libraries. The use of surrogate keys also provides flexibility, as the underlying data can change without affecting the stability of the identifiers.

PostgreSQL supports both natural and surrogate keys natively. It provides identity columns which are special columns that are generated automatically from an implicit sequence. Alternatively, PostgreSQL allows the use of UUIDs (Universally Unique Identifiers) as surrogate keys, offering a globally unique identifier solution that is well-suited for distributed systems. In PostgreSQL, sequences are one technique to generate unique identifiers for primary keys. Sequences are objects that provide auto-incrementing integer value with each access. They are useful when you need auto-incrementing primary keys or a unique identifier where you don’t have a natural key. However, as with any system that deals with numerical data, there’s always the risk of integer overflow, which can lead to unexpected behaviors and errors.

In this post, we discuss integer sequence overflow, its causes, and—most importantly—how to efficiently set up alerts using Amazon Simple Notification Service (Amazon SNS) and use AWS Lambda to resolve such issues in Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL.

Integer sequence overflow in PostgreSQL

Integer overflow occurs when a program attempts to store an integer value that surpasses the maximum value that can be represented by the data type used for storage. This can lead to unexpected and erroneous results, compromising the functionality and reliability of the program.

In PostgreSQL, there are three types of integers available, as summarized in the following table.

The following are common causes of integer sequence overflow:

• Incorrect data type – Using an integer data type without taking into account the possible range of values it may need to allow is incorrect data type usage.
• Bugs in the application – Bugs or logic errors in applications can cause unexpected increments or changes to integer values. In PostgreSQL, sequences increment regardless if a transaction is successfully committed, which could cause an unintended inflation of sequence values.
• Unexpected data growth – It is easy to overlook the risk of integer overflow in databases that undergo sudden expansion or increase rapidly, particularly those with a high write volume.

Sequence generation and sequence increments in PostgreSQL

In PostgreSQL, sequences can be generated in several ways:

• Using IDENTITY COLUMNS: In PostgreSQL 10 and later, you can define identity columns, which are similar to the SERIAL pseudo type (described below) but comply with the SQL standard. Identity columns automatically create a sequence and use it to generate values.
• Using CREATE SEQUENCE Command: You can create a sequence manually using the CREATE SEQUENCE command, specifying options such as increment, start value, min/max value, cache, and cycle. The optional clause CACHE in CREATE SEQUENCE command specifies how many sequence numbers are to be pre-allocated and stored in memory for faster access.
• Using SERIAL Data Type: For PostgreSQL 9.6 and earlier, when defining a column in a table, you can use the SERIAL (or BIGSERIAL) pseudo type, which automatically creates an associated sequence and sets the column’s default value to use nextval from that sequence. SERIAL datatype creates an INT sequence and BIGSERIAL datatype creates a BIGINT
• Using ALTER SEQUENCE: Modify existing sequence properties with the ALTER SEQUENCE command, allowing adjustments to increment value, start value, cache, and more.
• Using nextval, currval, and setval Functions: These functions interact with the sequence, allowing you to get the next value, the current value, and set a specific value, respectively.

What happens when a sequence reaches its max value

Using a 4-byte integer as a primary key when creating a new table is a common practice. However, this can lead to issues if the value to be represented exceeds the capacity of 4 bytes. In such cases, when the sequence reaches its limit, you will encounter an error in your logs resembling the following:

ERROR:  nextval: reached maximum value of sequence "seq_overflow_test_table_id_seq" (2147483647)

To try this out, we connect to an RDS for PostgreSQL database and create a table called seq_overflow_test_table with an integer primary key. We increment the sequence value to near its maximum range at 2,147,483,646, as shown in the following code:

CREATE TABLE seq_overflow_test_table (id serial, series integer);
CREATE TABLE

\d
List of relations
Schema |              Name              |   Type   |  Owner
--------+--------------------------------+----------+----------
public | seq_overflow_test_table        | table    | postgres
public | seq_overflow_test_table_id_seq | sequence | postgres

\d seq_overflow_test_table
Table "public.seq_overflow_test_table"
Column |  Type   | Collation | Nullable |                       Default
--------+---------+-----------+----------+-----------------------------------------------------
id     | integer |           | not null | nextval('seq_overflow_test_table_id_seq'::regclass)
name   | text    |           |          |

As shown in the following example, the newly created sequence acts as a default value and starts to increase as soon as we insert values into the series column. We use generate_series function, a built-in PostgreSQL function to generate a set of numbers or date. In our demo, we increment the values in the series column through this function:

INSERT INTO seq_overflow_test_table (series) VALUES (generate_series(1,1000));

INSERT 0 1000

As a result, the sequence’s current value in this session is 1001 after this insert operation. We can verify the sequence’s current value using a currval function. currval function returns the value most recently obtained by advancing the sequence object to its next value.

SELECT currval('seq_overflow_test_table_id_seq');
currval
---------
1001
(1 row)

How to know if you’re close to overflowing an integer

How can you detect if your integer sequence is close to overflowing? In this next section, we’ll show you how to simulate sequence overflow, which will let us build mechanisms to detect and correct for it. In our example, the integer value 2,147,483,647 is where it terminates. To simulate a sequence hitting its limit, we can use the function “setval” to explicitly set the sequence value at a desired range. In our case, we want this to be at 2,140,000,000:

SELECT setval ('seq_overflow_test_table_id_seq', 2140000000);
setval
------------
2140000000
(1 row)

SELECT currval('seq_overflow_test_table_id_seq');
currval
------------
2140000000
(1 row)

INSERT INTO seq_overflow_test_table (series) VALUES (generate_series(1001,2140000000));
INSERT 0 2139999000

Let’s now see how we can examine the last sequence values using the pg_sequence_last_value function. The following query uses this function to provide the sequence’s percentage value:

SELECT ROUND ((pg_sequence_last_value('seq_overflow_test_id_seq'::regclass)::float*100/2147483647)::numeric, 2) AS sequence_percentage;

sequence_percentage
---------------------
99.65
(1 row)

The preceding sample query helps us understand that the sequence’s percentage value is at 99.65%. This is because the preceding table seq_overflow_test_table was created with an integer primary key and we previously incremented the sequence value to 2,140,000,000, which is 99.65% of its maximum range 2,147,483,647.

Let’s try to simulate the error that occurs when the sequence reaches its limit. To simulate a sequence reaching its limit, we can use the function setval to explicitly set the sequence value at a desired range. In our demo, we want this to be at 2,147,483,646:

SELECT setval ('seq_overflow_test_table_id_seq', 2147483646);
setval
------------
2147483646
(1 row)

SELECT currval('seq_overflow_test_table_id_seq');
currval
------------
2147483646
(1 row)

We are just one insert away from invoking the error:

INSERT INTO seq_overflow_test_table (series) VALUES (2147483646) RETURNING *;
id         | series 
-----------+------
2147483647 | 2147483646
(1 row)
INSERT 0 1

INSERT INTO seq_overflow_test_table (series) VALUES (2147483646);
ERROR:  nextval: reached maximum value of sequence "seq_overflow_test_table_id_seq" (2147483647)
postgres=> SELECT currval('seq_overflow_test_table_id_seq'); 
  currval   
------------
 2147483647
(1 row)

SELECT ROUND ((pg_sequence_last_value('seq_overflow_test_id_seq'::regclass)::float*100/2147483647)::numeric, 2) AS sequence_percentage; 
 sequence_percentage 
---------------------
 100.00
(1 row)

Set up alerts for integer sequence overflow in Amazon RDS for PostgreSQL

Now that we know the precise percentage value of the sequence, we can use the data to get SNS alerts using a Lambda function. The function code shared below establishes a connection to the RDS for PostgreSQL database, runs the previously specified query, verifies the query’s result, and delivers an SNS message according to the topic and subscription. It uses an AWS Identity and Access Management (IAM) role with the following permissions:

1. AWSLambdaBasicExecutionRole
2. AWSLambdaVPCAccessExecutionRole
3. The following custom policy to provide SNS and Secrets Manager permission:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "sns:Publish"
            ],
            "Resource": "arn:aws:sns:ap-south-1:1234XXX8910:blog"
        },
        {
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": "arn:aws:secretsmanager:ap-south-1:1234XXX8910:secret:dbuser-XXXXXX"
        }
    ]
}

To enable security and modularity of the Lambda code, we passed the SECRET_NAME, SNS_TOPIC_ARN, SEQUENCE_PERCENTAGE_THRESHOLD and POSTGRES_SEQUENCE_NAME as environment variables. This allows adjusting function behavior without updating code. We also leverage AWS Secrets Manager to securely obtain database credentials.

Refer to Securely retrieving secrets with AWS Lambda for more details on securely retrieving secrets with AWS Lambda. For more information about creating a topic, creating a subscription to a topic, and publishing a message to a topic, refer to Getting started with Amazon SNS. For the purpose of this demonstration, we’ve chosen a standard topic and a subscription with email as its protocol. In the Lambda function, we use the psycopg3 module to connect to the RDS for PostgreSQL instance. The module can be included in the Lambda function’s deployment package as described in Working with .zip file archives for Python Lambda functions.

import json
import psycopg
from psycopg.rows import dict_row
import boto3
import os

def get_secret(secret_name, region_name):
    client = boto3.client('secretsmanager', region_name=region_name)

    try:
        get_secret_value_response = client.get_secret_value(SecretId=secret_name)
    except Exception as e:
        print(f"Error retrieving secret: {e}")
        raise e

    secret = json.loads(get_secret_value_response['SecretString'])
    return secret

def get_sequence_percentage(conn, pg_sequence_name):
    try:
        with conn.cursor(row_factory=dict_row) as cursor:
            query = "SELECT ROUND ((pg_sequence_last_value('seq_overflow_test_id_seq'::regclass)::float*100/2147483647)::numeric, 2) AS sequence_percentage;"
            cursor.execute(query, {'sequence_name': pg_sequence_name})
            result = cursor.fetchone()
            return result['sequence_percentage'] if result else None
    except Exception as error:
        print(f"Error: {error}")
    finally:
        if conn:
            conn.close()

def send_sns_notification(pg_sequence_name, query_result, sns_topic_arn, region_name):
    sns = boto3.client('sns', region_name=region_name)
    message = f"The last value of the sequence '{pg_sequence_name}' in your RDS PostgreSQL instance is at: {query_result}%"
    sns.publish(TopicArn=sns_topic_arn, Message=message)
    return message

def lambda_handler(event, context):
    # Get database credentials from Secrets Manager
    region_name = os.environ['AWS_REGION']
    secret_name = os.environ['SECRET_NAME']
    sns_topic_arn = os.environ['SNS_TOPIC_ARN']
    pg_sequence_name = os.environ['POSTGRES_SEQUENCE_NAME']
    sequence_percentage_threshold = os.environ['SEQUENCE_PERCENTAGE_THRESHOLD']

    secret = get_secret(secret_name, region_name)

    # Connect to the database instance
    conn = psycopg.connect(**secret)

    # Get the sequence percentage
    query_result = get_sequence_percentage(conn, pg_sequence_name)

    message = ""

    # Check query result and send an SNS notification
    if query_result >= float(sequence_percentage_threshold):
        print(query_result)
        message = send_sns_notification(pg_sequence_name, query_result, sns_topic_arn, region_name)

    return message

We tested running the Lambda code for our demo when the sequence’s percentage value was 99.65%. As shown in the following screenshot, we were able to successfully receive an email notification.

You can also schedule this Lambda function using Amazon EventBridge to run at regular intervals. It will query the database for the last sequence value and send an alert based on the result. For more information about scheduling functions, refer to Schedule AWS Lambda functions using EventBridge.

Alternative approach

An alternate strategy would be to first query the state of sequences depending on a specific time period using pg_cron. Invoke an AWS Lambda function from the RDS for PostgreSQL DB instance only if the sequences are above low risk (50%), medium risk (70%), and high risk (90%). As a result, Lambda invocation will only take place in the event that a threshold breach is detected.

You can use the PostgreSQL pg_cron extension to schedule commands within a PostgreSQL database. To gather details for all of the sequences in your database within the pg_cron job, you can use the following query which extracts necessary details about each sequence from the pg_sequences and pg_sequence system catalogs, then calculates the sequence percentage based on the last value and the minimum and maximum values of the sequence. The result includes the schema name, sequence name, last value, and the percentage of the sequence used so far.

WITH sequence_info AS (
    SELECT 
        schemaname,
        sequencename,
        format('%I.%I', schemaname, sequencename)::regclass AS sequence_oid,
        s.seqstart AS start_value,
        s.seqmin AS minimum_value,
        s.seqmax AS maximum_value,
        pg_sequence_last_value(format('%I.%I', schemaname, sequencename)::regclass) AS last_value
    FROM 
        pg_sequences seq
    JOIN 
        pg_sequence s ON s.seqrelid = format('%I.%I', schemaname, sequencename)::regclass
)
SELECT 
    schemaname,
    sequencename,
    last_value,
    ((last_value::float - minimum_value) / (maximum_value - minimum_value)) * 100 AS sequence_percentage
FROM 
    sequence_info
ORDER BY 
    schemaname, sequencename;

 schemaname |          sequencename           | last_value |  sequence_percentage 
------------+--------------------------------+------------+-----------------------
 public     | seq_overflow_test_id_seq        | 2147483646 |     99.99999995343387
 public     | seq_overflow_test_table1_id_seq |          2 | 4.656612877414201e-08
 public     | seq_overflow_test_table2_id_seq | 1140000002 |    53.085386849088025
(3 rows)

In our demo, this query lists the last value and the sequence percentage for three sequences in the database using the pg_sequence_last_value function.

How to fix integer sequence overflow in PostgreSQL

When encountering the issue of integer sequence overflow in PostgreSQL, there are various approaches you can explore to tackle the problem. These solutions range from quick fixes to more extensive modifications in your database structure. Each approach comes with its own benefits and considerations, and the optimal choice will depend on your specific use case, database size, and application requirements. Let’s explore some potential solutions that may assist you in addressing or preventing integer sequence overflow in your PostgreSQL database.

1. Utilizing negative numbers: The first approach involves utilizing negative numbers, which is a quick fix solution. This method works by instructing the sequence to count down instead of up, starting from -1 and continuing to -2.1 billion. While this is a simple and fast solution that doesn’t require structural changes, it may cause confusion in applications expecting positive integers and is still limited to the same range of numbers, just in the negative direction.
2. Modifying the column data type: The second approach is modifying the column data type. This solution involves changing the data type of your integer column to a bigger integer type, such as BIGINT, using the ALTER TABLE command. This provides a much larger range of values, effectively solving the overflow problem for a very long time. However, it requires exclusive table locking, which can cause large tables to be unavailable to accept writes on busy systems. It may also necessitate changes in application code that interacts with this column.
3. Create a new BIGINT column: The third approach is to create a new BIGINT column and gradually migrate data to it. This process involves adding a new BIGINT column to your table, creating a unique index on it, setting up a new BIGINT sequence, and then backfilling the new column with data from the old column in batches. Once backfilled, you validate constraints, convert the unique index to a primary key, and drop the old column. This method provides a long-term fix with a much larger range of values and allows for continued use of the table during most of the migration process. However, it requires careful planning and execution, may necessitate updates to other tables or application code referencing the old column, and can be a complex process, especially for large tables or busy systems. This approach is more comprehensive and allows for a smoother transition compared to simply altering the existing column, especially for large or heavily-used tables.

Integer sequence overflow best practices:

In PostgreSQL, an integer overflow can result in inability to insert new rows and application failures. Hence, if you are approaching sequence overflow, consider the following best practices to avoid the same:

• Selecting data types – Select the right data type for each of your integer columns. PostgreSQL provides a number of integer types, including int2, int4, and int8, with varying ranges. Decide which one best suit your requirements. We recommend to start with BIGINT (or BIGSERIAL) data type.
• Applying logic checks in the application – Prior to inserting or changing integer columns, validate incoming data to make sure it falls within permissible ranges. Use MERGE/UPSERT logic if there is another UNIQUE KEY which may lead to conflict when inserting.
• Frequent monitoring – Pay attention to how your integer sequence values are increasing. As we’ve demonstrated, you can use scripts or monitoring tools to get alerts when values approach the maximum limit.
• Utilization of UUIDs – PostgreSQL comes with built-in functionality for creating UUIDs, which can also be generated with sequential properties. UUID Version 7 (UUIDv7) was introduced to improve the randomness of UUIDv4.

Remember, it’s crucial to carefully plan and test any changes to the sequence to avoid disrupting your application’s functionality. Additionally, ensure that you have appropriate backup and recovery procedures in place in case any issues arise during the modification process.

Clean up

In order to prevent any charges, clean up the AWS resources if you have created them for testing purposes for this blogpost. Refer to the following documentation for steps to clean up your resources and avoid unnecessary costs. Make sure you select suitable resources based on your environment setup and use case.

• Deleting a DB instance
• Deleting an AWS Lambda function
• Deleting an Amazon SNS topic and subscription
• Disabling or deleting an Amazon EventBridge rule
• Deleting an IAM role
• Deleting the AWS CloudWatch logs

Conclusion

In databases such as Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible, integer sequence overflow is a possible issue that can be effectively handled with proper planning and preventive approaches. Preventing integer sequence overflow requires caution and diligence. You can maintain the integrity and dependability of your database even as it expands over time by comprehending the reasons behind overflow, putting best practices for data type selection into effect, and using the options discussed in this post.

If you have any comments or questions, please leave them in the comments section.

About the authors

Anshul Punnaivanam is a Software Development Engineer with Amazon Web Services. He works with the Amazon RDS team to improve the development and release processes for open source database engines and Amazon RDS zero-ETL orchestrations.

Mohammed Asadulla Baig is a Senior Technical Account Manager with AWS Enterprise Support. Asad helps customers architect scalable, resilient, and secure solutions. He is a trusted advisor, guiding customers on leveraging advanced AWS services like Amazon Aurora and Amazon RDS for PostgreSQL to drive operational excellence. With a keen eye for innovation and a passion for delivering customer success, Asad has established himself as a thought leader in the industry, helping enterprises navigate their cloud transformation journeys with confidence and ease.

Kanchan Bhattacharyya is a Senior Technical Account Manager at Amazon Web Services. He works with enterprise customers providing technical assistance on database operational performance and sharing database best practices. He specializes in Amazon RDS for SQL Server, Amazon RDS for PostgreSQL, Amazon RDS for MySQL, and Amazon Aurora.