In Salesforce SOQL, we cannot set the Offset value more than 2000.

In order to overcome the Offset limitation, we can avoid using it and order the records based on the Id using ORDER BY Clause in the SOQL and retrieve them by filtering in subsequent requests.

Example:

Request 1: Fetch 50 records. Make a note of the last record Id.

Request 2: Fetch next 50 records where Id greater than the last record Id.

Request n: Request for the last batch of records where Id greater than the last record Id.

Sample Apex Code:

@RestResource( urlMapping='/OpportunitiesExport/' )
global with sharing class RESTAPIController {
    
    @HttpGet
    global static void fetchOpportunities() {
        
        Integer intLimit = 50;
        String strResponse;
        Map < String, String > mapResponse = new Map < String, String >();
        RestRequest req = RestContext.request;
        String strStageName = req.params.containsKey( 'StageName' ) ?
            req.params.get( 'StageName' ).toString() :
            null;
        String strLastOpptyId = req.params.containsKey( 'LastOpptyId' ) ?
            req.params.get( 'LastOpptyId' ).toString() :
            null;
        Integer intOffset = req.params.containsKey( 'Offset' ) ?
            Integer.valueOf( req.params.get( 'Offset' ) ) :
            0;
        
        try {
            
            List < Opportunity > listOpptys = new List < Opportunity >();
            JSONGenerator gen = JSON.createGenerator( true );
            gen.writeStartObject();
            Integer opptyCount = [
                SELECT COUNT()
                FROM Opportunity
                WHERE StageName =: strStageName
            ];
            gen.writeNumberField(
                'totalCount', 
                opptyCount
            );
            
            if ( String.isBlank( strLastOpptyId ) ) {
                
                listOpptys = [
                    SELECT Id, Name
                    FROM Opportunity
                    WHERE StageName =: strStageName
                    ORDER BY Id 
                    LIMIT :intLimit
                ];
                
            } else {
                
                listOpptys = [
                    SELECT Id, Name
                    FROM Opportunity
                    WHERE StageName =: strStageName
                    AND Id > :strLastOpptyId
                    ORDER BY Id 
                    LIMIT :intLimit
                ];
                
            }
            
            gen.writeNumberField(
                'retrievedCount', 
                listOpptys.size()
            );
            
            if ( opptyCount - intOffset > intLimit ) {
                
                gen.writeBooleanField(
                    'queryCompleted', 
                    false
                );            
                gen.writeStringField(
                    'LastOpptyId', 
                    listOpptys.get(
                        listOpptys.size() - 1
                    ).Id
                );
                intOffset = intOffset + intLimit;                
                gen.writeStringField(
                    'nextRequestURL', 
                    '/services/apexrest/OpportunitiesExport?Offset='
                    + intOffset + '&StageName=Prospecting&LastOpptyId=' +
                    listOpptys.get(
                        listOpptys.size() - 1
                    ).Id
                );
                
            } else {
                
                gen.writeBooleanField(
                    'queryCompleted', 
                    true
                );               
                gen.writeNullField(
                    'nextRequestURL'
                );
                
            }
            
            Map < String, List < Opportunity > > mapRecords = new Map < String, List < Opportunity > > {
                'records' => listOpptys
            };        
            gen.writeFieldName( 'records' );
            gen.writeObject(
                listOpptys
            );                       
            gen.writeEndObject();
            strResponse = gen.getAsString();
            
        } catch( Exception e ) {
            
            mapResponse.put(
                'errorMessage', 
                e.getMessage() 
            );
            strResponse = JSON.serialize(
                mapResponse
            );     
            
        }
           
        RestContext.response.addHeader(
            'Content-Type', 
            'application/json'
        );
        RestContext.response.responseBody = Blob.valueOf(
            strResponse
        ); 
        
    }
    
}