Random Access On A Huge Postgres Dataset

INTRODUCTION

The story

I was asked: “Suppose you have a single table in the DB - the logs from your system - that gets updated every 5 minutes over several years. Today, the app showing that table on screen gets very slow. Given that the problem is at the DB level, and we’re in Postgres, what’s your investigation plan”?¹
A curiosity to get my hands on such a scenario grew into a story I’m presenting today. I’ve built a small project that demonstrates how an expected table size growth causes a sudden performance drop, and we’ll be looking at Postgres internals to understand the mechanism of that drop and fix it.

TL;DR

• A single-table observability app that has a “random page access” requirement can significantly slow down the whole DB once the table size grows.
• Using OFFSET for random access pagination with an index scan is an $O(N)$ process, and that also causes cache thrashing (index scan employs the main buffer).
• Deferred join allows you to do index-only scan, preventing the cache overuse, but that is still an $O(N)$ process.
• Observability domain allows monotonic, append-only writes, enabling keyset pagination with $O(N \bmod C)$ reads and modest write overhead, scaling to tens of millions of rows.
• The code is on GitHub, tested to be single digit ms READ up to 10_000_000 records.

Long read - Goals

Goals: show that for some basic queries, Postgres significantly slows down a basic SELECT query on modest dataset growth. Explain the detailed performance for Postgres. Find an efficient query for the same problem, justifying any clarification/change in the requirements. Formally, and on the very top level, find a table description and a SELECT query run with OFFSET $O$ such that $O$ records fit into some constraints (e.g. into a database buffer), but $O + (O \div 10)$ records stop fitting in that buffer.
Non-goals: measuring Postgres performance on cold start. Exploring non-Postgres solutions. Building an example with UI.

A SCENARIO FOUND

Our application

Requirements: a list of logs on a screen, which must be paged with random pagination (e.g. click page 33) and sorted by date DESC (latest first).
At first, the requirement is to support 100_000 rows, with possibility that this amount might grow.
DB design: the trace_id primary key would be ULID ², which has very interesting properties (ULID’s first 40 bits represent a timestamp). There’s a SMALLINT severity value to represent anything we might filter on later. And there’s a TEXT body to represent a bulky JSON.

CREATE TABLE logs (
    trace_id UUID PRIMARY KEY,
    severity SMALLINT,
    body TEXT
);
CREATE INDEX idx_logs_severity_desc ON logs (trace_id DESC, severity DESC);

\d+ logs
  Column  |   Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+----------+-----------+----------+---------+----------+-------------+--------------+-------------
 trace_id | uuid     |           | not null |         | plain    |             |              |
 severity | smallint |           |          |         | plain    |             |              |
 body     | text     |           |          |         | extended |             |              |
Indexes:
    "logs_pkey" PRIMARY KEY, btree (trace_id)
    "idx_logs_severity_desc" btree (trace_id DESC, severity DESC);
Access method: heap

Payload example:

{
  "trace_id": "019ff38b-7e23-7e65-d636-0b0f92861a2e",
  "severity": 1,
  "body": "2026-10-01T15:19:44,440 [main] INFO ... (omitted for brevity)",
}

We run this on Postgres-only instance with 1G RAM (default RAM for one CPU core) and 256MB buffer size. This looks like a reasonable setup for paging and sorting by the primary key. Again, our requirement is 100_000 rows max.

Initial table size and OFFSET

Suppose we have built an app that supports paging. This would be our query to page records:

SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC buffer
LIMIT 30 OFFSET 30 * 1000; -- Page size 30, page count 1000 (careful.. this is the 💣!)

We use 30 and 1000 because 30 is our page, and 1000 pages sets us about 1/3 deep in our 100_000 records, which is plausible as a real paging request.
We decide to run pg_bench on it with 10 concurrent queries, and we get an average latency of 19.371 ms. That seems good enough for 100K rows, therefore, this initial simple query passes the code review. Why it should not, we’ll see soon.

10x table size, larger OFFSET (the 💣 gets ignited)

Suppose that one day, the database grows significantly. We’re now at 1_000_000 rows without any changes.

SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 100 * 380;

This might be a typical production request. I’ve chosen page 380 to satisfy the article’s goal - you would see soon that a small increase in page number would cause a significant slowdown. For now, try putting this through pg_bench, and the average latency is 28.410 ms, which is worse, but not significantly. But let’s grow our page number only a little:

SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 100 * 410;

This time the latency is 150 ms upwards, with up to one second long freezes; what’s more, if you go back to 380 pages, the latency gets back below 30 ms, and stays stable.
So this increase of 8% workload causes performance degradation from 5 times to an order of magnitude. Now, it is likely that a message from your client informing you that the dashboard is very slow would appear, regardless of their patience. So what did just happen?

An unfortunate query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 100 * 410;
----------------------------------------------------------------------------------------------
 Limit  (cost=17226.42..17239.02 rows=30 width=733) (actual time=518.569..518.590 rows=30 loops=1)
   Buffers: shared hit=34678 read=6729
   ->  Index Scan Backward using logs_pkey on logs  (cost=0.42..420399.45 rows=1000602 width=733) (actual time=0.061..517.351) row

EXPLAIN (ANALYZE, BUFFERS)
SELECT l.trace_id, l.severity, l.body
FROM logs l
WHERE (l.trace_id <= '01a10a81-2f90-38f0-32f7-b797dcb046f9')
ORDER BY l.trace_id DESC
LIMIT 30
OFFSET 23456;    s=41030 loops=1)
         Buffers: shared hit=34678 read=6729
 Planning Time: 0.089 ms
 Execution Time: 518.656 ms
(6 rows)

EXPLANATION FOR THE PERFORMANCE DROP

Smaller table sits in the buffer completely

A trap for anyone testing the app on a dataset too small: if your complete dataset fits in the database buffer (256M here), you would not be able to predict what happens when your queries start hitting the disk!

SELECT COUNT(trace_id) FROM logs;
  count
----------
100000

EXPLAIN (ANALYZE, BUFFERS)
SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 38000;
...
    Buffers: shared hit=38026 read=190

EXPLAIN (ANALYZE, BUFFERS)
SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 41000;
...
    Buffers: shared hit=41216 read=15

As you see, very little read figures after buffers hint that every tuple from every page is in your RAM already.

Larger table doesn’t fit into the buffer, requiring more reads

 SELECT COUNT(trace_id) FROM logs;
  count
----------
 1000000

SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 41000;
...
   Buffers: shared hit=32744 read=8701

SELECT trace_id, severity, body FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 38000;
...
   Buffers: shared hit=19685 read=18728

We can see that on 1 million rows, the query engine has started hitting the disk. And the buffer usage tells us, that for 8% more records, we get about twice as many disk reads. Because, subsequent queries on smaller offset start to read buffer-only (no or miniscule Buffers read) Subsequent queries on larger offset do not fit into the buffer (significant Buffers read). See Appendix A for the maths about the buffer maximum capacity.

Denial of resources, as index scans pollute the main buffer

Unfortunately, the SQL expression from above is troublesome not only because it executes slowly. Worse, it’s also prone to denying the resources for the other queries executed by our Postgres process!
If we had simple sequential scans, Postgres would have used an isolated buffer for Limit query driven by a SeqScan. But here, for a Limit query driven by an IndexScan, a main buffer is used, and the unfortunate query above pollutes it, denying resources for other clients. With the pg_buffercache, query, we show how many 8-kb pages are cached in the shared buffers:

CREATE EXTENSION pg_buffercache;
SELECT count(*) FROM pg_buffercache  WHERE relfilenode = pg_relation_filenode('logs'::regclass);
 count
-------
32257

This is because Postgres uses the shared buffers for index scans, as they’re not sequential scans. Please check Appendix B for evidence. Briefly, as the buffer fills completely with the heap pages collected during the index scan, Postgres is forced into clock-sweep eviction loop that constantly removes freshly cached data, with concurrent queries blocking each other as a consequence (cache thrashing).
Therefore, an unfortunate query above is also dangerous for the overall system stability. Let fix that now.

A STOPGAP MEASURE - DEFERRED JOIN

An index-only scan is still O(N), but consumes less CPU per operation

An intuitive first idea is to defer fetching body. Let’s fetch trace_id with offset using our index-only scan, and join body later. If we look at the index-only scan below, we would see that it’s faster:

EXPLAIN (ANALYZE, BUFFERS)
SELECT trace_id, severity
FROM logs
ORDER BY trace_id DESC
LIMIT 30 OFFSET 41000;
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1427.52..1428.57 rows=30 width=18) (actual time=9.791..9.813 rows=30 loops=1)
   Buffers: shared hit=38337 read=223
   ->  Index Only Scan using idx_logs_severity_desc on logs  (cost=0.56..348033.68 rows=9999808 width=18) (actual time=0.270..8.701 rows=41030 loops=1)
         Heap Fetches: 3
         Buffers: shared hit=38337 read=223
 Execution Time: 9.860 ms

The full deferred join query:

WITH target_keys as (
    SELECT trace_id
    FROM logs
    ORDER BY trace_id DESC
    LIMIT 30 OFFSET 41000
    )
SELECT l.trace_id, l.severity, l.body
FROM logs l
JOIN target_keys tk on l.trace_id = tk.trace_id
ORDER BY l.trace_id DESC;

This one is still an $O(N)$ process. To dump our OFFSET number of rows, we must first scan them. If you look at the EXPLAIN statement above, you would see a Limit node. This one does not have random-access capabilities. Instead, it must stream rows sequentially - so that’s $O(N)$. So our deferred join fix simply decreased the multiplier on single operation of $N$, but didn’t eliminate the factor.
The only good news is that the index-only scan uses its own buffer on the index:

SELECT count(*) FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('logs'::regclass);
 count
-------
    40

Before we proceed to a faster solution (keyset pagination), I wanted to take a detour and discuss the app requirements.

Questionable requirement: hard pagination for an observability system.

Modern observability systems (Datadog, AWS CloudWatch, Kibana) forbid random-access page numbers on large datasets. There’s a UX question to it. What getting “page 50 of logs” means for the user? Probably, an intent to see something from long ago that is relevant today. This probably can be substituted with choosing a time window - like “3 months ago”. Time window instantly gives us a trace_id we’d like to filter from, achieving the best performance.

SELECT trace_id, severity, body
  FROM logs
  WHERE trace_id < ':trace_id_prefix_calculated_from_timestamp'
  ORDER BY trace_id DESC
  LIMIT 30;

As well, the above might be the case where time partitioning could prove handy.

But what if the above is not an option? As user, you want exact “page 50” e.g. in e-commerce; and building this kind of random access on a row-oriented storage like Postgres comes with significant trade-offs that are beyond the scope of this article. Let’s pretend we still want a random access pagination on our app, and for that we can relax the requirements not from the UI, but from CREATE / DELETE operations. This would allow us to build very fast random access with keyset pagination.

FULL SOLUTION: KEYSET PAGINATION

Preconditions

Take a look at our trace_id column. It’s ULID, so it’s naturally sorted by time. For this system (observability apps) it may be a natural requirement, that every log record for yesterday should be in the database before any of today’s logs can be loaded.
Formally, ensure that logs table is appended in strict monotonic order, with no new entry allowed to have ULID $\le$ latest ULID in the trace_id column. As well, we explicitly ban hard deletes, preferring tombstoning. These two preconditions allows us to treat logs as append-only, always sorted structure.
As well, we allow ourselves to run database maintenance processes (run a script periodically) in parallel. With these, it turns out that we can maintain an append-only set of trace_ids that point to exact places in our logs ³.

Design: the log checkpoints table

Let’s create a log_checkpoints table:

   Column          |  Type  | Collation | Nullable | Default
-------------------+--------+-----------+----------+---------
 absolute_row_rank | bigint |           | not null |
 trace_id          | uuid   |           | not null |
Indexes:
    "log_checkpoints_pkey" PRIMARY KEY, btree (absolute_row_rank)
    "idx_log_checkpoints_rank" btree (absolute_row_rank)

Its invariant: store every trace_id that points to a pagination checkpoint. That is, every trace_id whose number in the sequence is divisible by 100_000. Periodically, a process takes the last rank_offset and trace_id, and goes up from that trace_id. If it reaches any number n that is divisible by C, it adds this number as rank_offset:

CREATE OR REPLACE FUNCTION calculate_next_checkpoints()
RETURNS void AS $$
DECLARE
    last_checkpoint_rank BIGINT;
    last_checkpoint_id UUID;
    C CONSTANT BIGINT := 100000;
BEGIN
    SELECT COALESCE(MAX(absolute_row_rank), 0) INTO last_checkpoint_rank FROM log_checkpoints;
    SELECT trace_id INTO last_checkpoint_id FROM log_checkpoints WHERE absolute_row_rank = last_checkpoint_rank;
    WITH ordered_logs AS (
        SELECT trace_id, row_number() OVER (ORDER BY trace_id ASC) as rn
        FROM logs
        WHERE trace_id > last_checkpoint_id
    )
    INSERT INTO log_checkpoints (absolute_row_rank, trace_id)
    SELECT last_checkpoint_rank + rn, trace_id
    FROM ordered_logs
    WHERE (last_checkpoint_rank + rn) % C = 0
    ON CONFLICT (absolute_row_rank) DO UPDATE SET trace_id = EXCLUDED.trace_id;
END;
$$ LANGUAGE plpgsql;

We run a calculate_next_checkpoints periodically. This process is O(N) in the worst case (suppose we had loaded a billion rows while calculate_next_checkpoints was waiting for its connection), but normally it’s a O(N % 100_000): never more than 100_000 records to scan.

Design: the nearest checkpoint trace_id and remaining offset

To effectively transform a target_offset that the user is requesting, with this keyset pagination, we need a trace_id that points to the nearest checkpoint from above, and a remaining_offset. To find this specific trace_id, we lookup the absolute_row_rank (ARR) this target_offset is pointing at:

WITH target_rank AS (
    SELECT (SELECT last_value FROM logs_rank_seq) - :target_offset AS num
)
SELECT
    trace_id                       AS anchor_trace_id,
    absolute_row_rank - (SELECT num FROM target_rank) AS remaining_offset
FROM log_checkpoints
WHERE absolute_row_rank >= (SELECT num FROM target_rank) -- From ARRs are above target_absolute_row_rank...
ORDER BY absolute_row_rank ASC -- ...we get the smallest ARR
LIMIT 1

Let’s have it with numbers:

• 90_000 is (SELECT last_value FROM logs_rank_seq)
• target_offset is 12_345
• We have a measure every 10_000 records

1. target_rank.num = 90_000 - 12_345 = 77_655
2. nearest absolute_row_rank in the log_checkpoints is 80_000
3. (a) trace_id for it: 01a10a81-2f90-38f0-32f7-b797dcb046f9 (b) remaining_offset is 12_345 - (90_000 - 80_000) = 2_345

Implementation and trade-offs:

The full query (I had to add a clause for when anchor_trace_id is not found in the log_checkpoints, and a deferred join for performance):

WITH target_rank AS (
    SELECT (SELECT last_value FROM logs_rank_seq) - :target_offset AS num
),
nullable_matched_checkpoint AS (
    SELECT
        trace_id                                          AS anchor_trace_id,
        absolute_row_rank - (SELECT num FROM target_rank) AS remaining_offset
    FROM log_checkpoints
    WHERE absolute_row_rank >= (SELECT num FROM target_rank)
    ORDER BY absolute_row_rank ASC
    LIMIT 1
),
matched_checkpoint AS (
    SELECT anchor_trace_id, remaining_offset
    FROM nullable_matched_checkpoint
    UNION ALL
    SELECT
        (SELECT trace_id FROM logs ORDER BY trace_id DESC LIMIT 1) AS anchor_trace_id,
        :target_offset AS remaining_offset
    WHERE NOT EXISTS (SELECT 1 FROM nullable_matched_checkpoint)
)
SELECT trace_id
FROM logs
WHERE trace_id <= (SELECT anchor_trace_id FROM matched_checkpoint)
ORDER BY trace_id DESC
LIMIT 30 OFFSET (SELECT remaining_offset FROM matched_checkpoint);

This one is finally $O(\log(N/C) + C)$, and because $\log(N/C)$ is small we achieve a much better latency and throughput. On 10_000_000 rows I had about 10ms latency, where deferred join showed its $O(n)$ performance limits by being 170 ms.
The trade-offs are:

• Having to support another process for keyset pagination, that must also be provisioned form, tested and monitored in production
• The query is now significantly more difficult to understand, increasing the maintenance costs
• Guaranteeing that logs are written in append-only, strict monotonic order⁴. Although, for observability app this is natural. Congratulations, this random-access on a single table is good enough for a scale of 10_000_000 rows. That’s 6+ years with 50 write requests per second ⁵
  Let’s walk over the accompanying code setup before we conclude.

FULL SETUP

The infra

Because something has to create the tables and to load the data, I’ve decided that a Java app would be easier, than, say, a shell script. Make sure you have:

• Java 21
• Maven
• Docker

There’s a docker-compose that runs Postgres 16, with 1GB RAM to emulate mid-tier VPS. As well, we’re giving 0.25G for buffers, and 4MB for the storage engine work memory (so larger record sets would have to be sorted with mergesort on disk).

services:
  postgres:
    image: postgres:16-alpine
    container_name: pg-limit-test
    ports:
      - "5432:5432"
    volumes:
      - <PUT YOUR LESS USED DISK FOR POSTGRES FILES HERE>:/var/lib/postgresql/data
      - <THIS FOLDER>/queries:/home/queries
    ...
    deploy:
      resources:
        limits:
          memory: 1g
    command: postgres -c shared_buffers=256MB -c max_connections=10 -c work_mem=4MB

This is how to access the DB:

docker exec -it pg-limit-test bash
psql -U postgres -d logdb

I’ve created scripts (below) for the scenarios discussed in this articles (logs size, and the queries). If you’re interested in the loader command, proceed to Appendix C.

The tests

We’re simulating 10 clients and 8 threads over 4 seconds with pgbench:

docker exec -i pg-limit-test pgbench -h localhost -U postgres -d logdb \
   -c 10 \
   -j 8 \
   -T 4 \
   -f <YOUR SQL FILE>

The SQL files in queries/ repeat the scenarios in the article. I’ve created scripts that loads and runs each scenario in scripts/. To run everything in order:

./scripts/load/01a-load-100K-records.sh
./scripts/run/01b-run-miniscule-offset.sh
./scripts/load/02a-load-1MIL-records.sh
./scripts/run/02b-run-normal-offset.sh
./scripts/run/02c-run-large-offset.sh
./scripts/run/02d-run-deferred-join-large-offset.sh
./scripts/load/03a-load-10MIL-records
./scripts/run/03b-run-keyset-pagination-large-offset
./scripts/run/03c-run-keyset-pagination-huge-offset.sh

THE RESULTS

Unoptimised query, 1 MIL records, 38K offset (scenario before the big slowdown):

./scripts/02b-run-normal-offset.sh
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 4 s
number of transactions actually processed: 1628
number of failed transactions: 0 (0.000%)
latency average = 24.629 ms
initial connection time = 9.007 ms
tps = 406.033437 (without initial connection time)

Unoptimised query, 1 MIL records, 41K offset (notice how the latency is now almost 8x worse!):

./scripts/02c-run-large-offset.sh
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 4 s
number of transactions actually processed: 210
number of failed transactions: 0 (0.000%)
latency average = 197.356 ms
initial connection time = 7.440 ms
tps = 50.669794 (without initial connection time)

Deferred join, 1MIL records, 41K offset:

./scripts/02d-run-deferred-join-large-offset.sh
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 4 s
number of transactions actually processed: 1907
number of failed transactions: 0 (0.000%)
latency average = 21.018 ms
initial connection time = 6.992 ms
tps = 475.784989 (without initial connection time)

Deferred join, 10MUL records, 410K offset:

./scripts/02d-run-deferred-join-huge-offset.sh
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 4 s
number of transactions actually processed: 121
number of failed transactions: 0 (0.000%)
latency average = 351.466 ms
initial connection time = 8.218 ms
tps = 28.452272 (without initial connection time)

Keyset pagination, 10MUL records, 820K offset:

./scripts/run/03c-run-keyset-pagination-huge-offset.sh
transaction type: /home/queries/benchmark/KEYSET_PAGINATION_HUGE_OFFSET.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 4 s
number of transactions actually processed: 4277
number of failed transactions: 0 (0.000%)
latency average = 9.352 ms
initial connection time = 9.117 ms
tps = 1069.285821 (without initial connection time)

So a keyset pagination, optimised by a deferred join, is the only scalable solution (from all of the above) for a random access on huge dataset. A similar pattern is implemented natively in e.g. RocksDB and Cassandra. It was nice to show that good old Postgres is capable of the same.

CONCLUSION

• Knowing how to tell to your DB “Fetch me N logs after M-th row” in SQL isn’t enough, always take into account the database engine level and its memory limitations.
• Relying on long index scans is an anti-pattern in Postgres that pollutes the global buffer, starving concurrent queries. Deferred joins help avoiding that problem, but alone they’re not sufficient to make random access on sorted table perform better than $O(N)$.
• Some requirements can’t be expressed as processes that scale well, so devs need intuition on when to tune not the process side, but the requirements side. If we alter requirements to have an immutable, monotonically appended tables, random access then can be expressed as a close to constant time procedure with the help of keyset pagination.
• The code is on GitHub

APPENDIX A

The shared cache capacity math in Postgres

With 256MB cache and a 8KB page in Postgres:

These 32,768 page slots can provide a little more rows if I receive more than one row per page, therefore I can hit 38413 rows. This is enough for 38030 rows, but not for much more; 41000 rows can’t be provided with the buffer size, forcing the app to read extra pages. When the same is done concurrently, the more and more CPU is lost running the clock-sweep algorithm that evicts less used records from the buffers, causing what’s called “cache thrashing” ⁶.

APPENDIX B

Evidence that index scans in Postgres employ the main buffer

Unfortunately, the Postgres manual doesn’t explicitly say that index scans are cached in the shared buffers. Although they guarantee that large sequential scans use a 256 kb ring buffer⁷. We still can reason by looking at the index lookup semantics. Index lookups were built to efficiently retrieve comparably smaller sets of rows. Thus, they were allowed to use the main shared buffer. We can prove this with the evidence below. By using pg_stat_io, we see that only normal buffer is populated after running an index query:

SELECT * FROM logs ORDER BY trace_id DESC OFFSET 100 LIMIT 10;
...
SELECT context, reads, hits, evictions, reuses
FROM pg_stat_io
WHERE backend_type = 'client backend' AND object = 'relation';
  context  | reads | hits | evictions | reuses
-----------+-------+------+-----------+--------
 bulkread  |     0 |    0 |         0 |      0
 bulkwrite |     0 |    0 |         0 |      0
 normal    | 12119 | 7927 |         0 |
 vacuum    |     0 |    0 |         0 |      0

After SET enable_indexscan = off;, we see the bulkread buffer is now populated:

  context  | reads  | hits | evictions | reuses
-----------+--------+------+-----------+--------
 bulkread  | 161953 | 3949 |         0 | 161921
 bulkwrite |      0 |    0 |         0 |      0
 normal    |  12119 | 9425 |         0 |
 vacuum    |      0 |    0 |         0 |      0
(4 rows)

And that, together with what pg_buffercache told us, proves that index-only scan goes to normal buffer, while a big enough sequential scan receives its own ring buffer ⁸. With that out of the way, we can claim that using a large range for an index scan may be considered an antipattern. In other words, the unfortunate query from this article not only gets slow, but it also blocks all other queries that may be run against this particular database!

APPENDIX C

Running the Java loader and its memory limitations:

java -jar target/db-test-0.0.1-SNAPSHOT.jar \
  --loader.records=500_000 \        # Default is 10_000
  --loader.compression=EXTERNAL \  # Default is EXTENDED
  --loader.body-size=4096 \   # Default 3100 KB
  --loader.batch-size=50_000  # Default is 500_000

The batch size, the records count and the body size dictate how much heap space would Java want to take to prepare a large list of records to be persisted in one command. heap_consumed = l_1.records * l_2.body_size * l_3.batch_size where l_1, l_2, and l_3 are coefficents. To avoid taking all heap space with the large text and crashing the app, tune loader.batch-size for lower amount; to load millions of records with smaller text faster, the batch size can be set higher.

CREDITS

• This post’s image is a collage of European Southern Observatory’s Milky Way and University Of Vienna’s Messier 6 star cluster

FOOTNOTES