Last month our team hit a wall with S3 Select. The task looked dead simple: query a multi-GB CSV in S3, skip the first 1000 rows, return the next 200. My first thought? LIMIT + OFFSET. Standard SQL stuff.
Turns out, S3 Select doesn’t support OFFSET.
I spent the next two weeks digging into this. The docs are garbage on this part. The community is pissed. And the “solutions” AWS offers are half-baked at best. Here’s everything I found, including the production fix we eventually shipped.
Why No OFFSET? The Architecture Problem
Before you rage at AWS, understand why this limitation exists.
S3 Select is a streaming query engine. It reads data from S3, applies predicate pushdown and column projection, then streams results back to you. There’s no table, no index, no row numbers.
When you run SELECT * FROM s3object s LIMIT 10, here’s what happens:
- Start scanning from byte 0 of the file
- Match rows against WHERE conditions
- After finding 10 matches, abort the scan immediately and close the connection
This is fundamentally different from PostgreSQL, which calculates the full result set and then slices it. Streaming engines prioritize low latency and memory efficiency. The downside? You can’t know where row N lives in the file.
An OFFSET 1000 LIMIT 200 query would require scanning the first 1200 rows — but only returning the last 200. That defeats the entire purpose of LIMIT’s early-termination optimization.
The ScanRange Trap
AWS documentation mentions a ScanRange parameter:
Amazon S3 Select scan range requests are available to use with the AWS CLI, Amazon S3 API, and AWS SDKs. You can use the ScanRange parameter in the Amazon S3 …
I got excited. Then I read the fine print.
ScanRange is byte-level offset, not row-level. You specify start and end byte positions in the file. For fixed-width formats like Parquet row groups, this works beautifully. For CSV? It’s a disaster.
Consider this CSV:
id,name,age
1,Alice,30
2,Bob,25
3,Charlie,35
4,Diana,28
If you set ScanRange: {Start: 20, End: 60}, S3 Select returns:
2,Bob,25
3,Charlie,35
Why? Because byte 20 lands in the middle of the line “2,Bob,25\n”. S3 Select skips the incomplete row and starts from the next complete one.
The only scenario where ScanRange works for pagination is when you know the exact byte boundaries — which means you need fixed-width rows. Variable-length CSV? Forget it.
Community Workarounds (That Kinda Work)
I scoured Reddit and GitHub for solutions. Here’s what other engineers are doing:
Solution 1: LIMIT + Client-Side Skip
SELECT * FROM s3object s LIMIT 1200
Then discard the first 1000 rows in your application code. This works but wastes bandwidth — you’re paying to scan and transfer data you’ll never use.
Solution 2: WHERE Clause on Monotonic Keys
If your data has an auto-incrementing ID or timestamp:
SELECT * FROM s3object s WHERE id > 1000 AND id <= 1200
This works if you have a monotonic key and know the mapping between key values and row positions. For randomly distributed data, it’s useless.
Solution 3: Approximate ScanRange
Some folks estimate byte offsets based on file size and average row length:
file_size = head_object('myfile.csv').content_length
estimated_row_size = file_size / total_rows
start_byte = int(skip_rows * estimated_row_size)
end_byte = int((skip_rows + limit_rows) * estimated_row_size)
I tested this on a 10-column CSV with variable-length strings. The error margin was ~30%. Some rows were all nulls, others had URLs stretching to 500 characters. The byte estimates were consistently wrong.
Production Solution: The Row-Number Column
After two weeks of trial and error, here’s what we shipped. It’s not elegant, but it works.
Step 1: Preprocess — Add a Row Number Column
Before uploading to S3, add a sequential row number:
import csv
with open('input.csv', 'r') as infile, open('output.csv', 'w', newline='') as outfile:
reader = csv.reader(infile)
writer = csv.writer(outfile)
header = next(reader)
writer.writerow(['row_num'] + header)
for i, row in enumerate(reader, start=1):
writer.writerow([i] + row)
Your data becomes:
row_num,id,name,age
1,1,Alice,30
2,2,Bob,25
3,3,Charlie,35
Step 2: Query with WHERE on Row Number
SELECT * FROM s3object s WHERE s."row_num" > 1000 AND s."row_num" <= 1200
CSV column names need double quotes in S3 Select syntax. Don’t forget that.
Step 3: Add LIMIT as Safety Net
SELECT * FROM s3object s WHERE s."row_num" > 1000 LIMIT 200
The LIMIT prevents a full table scan if your WHERE condition is wrong. S3 Select charges by data scanned, and full scans hurt.
CLI Example
aws s3api select-object-content \
--bucket my-data-bucket \
--key logs/2026-06-01.csv \
--expression "SELECT * FROM s3object s WHERE s.\"row_num\" > 1000 LIMIT 200" \
--expression-type SQL \
--input-serialization '{"CSV": {"FileHeaderInfo": "USE"}}' \
--output-serialization '{"CSV": {}}' \
"output.csv"
Performance Comparison
| Approach | Data Scanned | Cost | Accuracy | Best For |
|---|---|---|---|---|
| LIMIT + client skip | Full | High | Exact | Small files |
| WHERE on monotonic key | Partial | Medium | Exact | Data with IDs |
| ScanRange byte offset | Partial | Low | Approximate | Fixed-width files |
| Row-number column + WHERE | Partial | Low | Exact | Production |
Cost Reality Check
Let’s talk money. S3 Select pricing is per GB scanned:
- $0.002 per GB of data scanned
- $0.0007 per GB of data returned
A 5GB CSV costs ~$0.01 per full scan. Doesn’t sound bad until you run 100 queries a day. That’s $30/month just for S3 Select, not counting Lambda invocations or data transfer.
The killer? You pay for scanned data even if LIMIT terminates early. A poorly written WHERE clause that scans 4GB before finding matches costs the same as a full scan.
My recommendations based on file size:
- < 100MB: Use Athena. Better SQL support, including
OFFSET. - 100MB - 1GB: S3 Select with row-number column for precise pagination.
- > 1GB: Preprocess with EMR or Glue, convert to Parquet, query with Athena.
Alternatives Worth Considering
Amazon Athena
Athena runs on Presto and has full SQL support:
SELECT * FROM my_table
ORDER BY id
OFFSET 1000 ROWS
FETCH NEXT 200 ROWS ONLY
Cold start latency is higher than S3 Select, and the query optimizer sometimes scans full tables for simple queries. We’ve seen bills spike when Athena decides to scan 10GB for a 10-row result.
Redshift Spectrum
If you’re already on Redshift, Spectrum uses the Redshift query engine. More stable performance than Athena, but higher cost and requires cluster management.
Community Sentiment
The Reddit thread on r/aws sums it up: “S3 Select is a joke, use Athena.” The GitHub issue aws/aws-sdk-net#1674 requesting offset support has been open since 2018 with zero official response. AWS seems to have put S3 Select in maintenance mode, focusing resources on AI/ML services instead.
FAQ
Q: Does S3 Select support OFFSET? A: No. S3 Select uses a SQLite subset that doesn’t include OFFSET.
Q: Can ScanRange be used for row-level pagination? A: No. ScanRange is byte-level, not row-level. It only works for fixed-width formats.
Q: What’s wrong with using LIMIT for pagination? A: LIMIT controls returned rows but can’t skip rows. You waste bandwidth and compute on data you discard.
Q: Is there a way to simulate OFFSET in S3 Select? A: Yes — add a row-number column during preprocessing, then use WHERE to filter by row number.
Q: When should I use S3 Select? A: Small files and simple filtering. For complex queries or large datasets, use Athena or Redshift Spectrum.
Q: How is S3 Select priced? A: $0.002 per GB scanned, $0.0007 per GB returned. You pay for scanned data even with LIMIT early termination.
References & Community Insights
This analysis synthesizes discussions from:
- Reddit r/aws community threads on S3 Select pagination limitations
- GitHub issue aws/aws-sdk-net#1674 requesting offset support
- AWS official documentation on S3 Select ScanRange parameter
- Multiple engineering blogs with S3 Select performance benchmarks
The consensus is clear: S3 Select works for simple use cases but falls apart for anything requiring pagination. If you’re stuck with it, the row-number column hack is your best bet. Otherwise, migrate to Athena.
