Why Slow CSV Downloads Can Keep PostgreSQL Transactions Open in Spring Boot

A lab plan for measuring JDBC streaming, snapshot retention, and the trade-offs of generating a file before serving it.

A slow CSV download can keep a PostgreSQL transaction open long after a Spring Boot endpoint starts returning data.

That happens when the same transaction covers both reading database rows and writing the HTTP response. Once downstream buffers fill, the export waits for the client while its connection and database snapshot remain open.

Replacing a large Java List with JDBC batch fetching can reduce retained heap. The heap graph still leaves one question unanswered: what remains open while the customer downloads the file?

This article sets out a controlled lab investigation. The reproduction has not been run, and there are no measured results yet. The roughly hour-long download below is a test target.

The lab compares direct streaming with generating a file first, then serving it after the database transaction ends. The aim is to measure what each approach costs in connection time, snapshot retention, and user waiting time.

A smaller heap does not mean a shorter transaction

Imagine an account activity report containing one million rows. The existing endpoint reads everything into a List, then generates the CSV.

The developer replaces that list with a forward-only JDBC result set and a fetch size of 500.

That can reduce retained application memory. It does not, by itself, shorten the database operation.

Three parts of the export matter here:

• Batch fetching: the driver retrieves a limited group of rows, consumes them, and asks for another group.
• Backpressure: when downstream buffers fill, the producer has to wait before it can write more data.
• Snapshot: the database view used to decide which row versions a query can see.

The direct export connects them like this:

PostgreSQL cursor
    → JDBC batch
    → CSV writer
    → servlet / TCP buffers
    → customer's download

The database transaction surrounds the entire read-and-write loop.

Once response writes block, that loop cannot fetch its next batch. Unless a timeout or failure ends the work, its database resources remain open.

The application might be retaining fewer rows while PostgreSQL retains the history needed by the report.

Set up a controlled Spring Boot and PostgreSQL lab

Use a disposable database and connect directly to embedded Tomcat on localhost. The first experiment has no reverse proxy, no TLS termination, and no HTTP compression. Add the real delivery infrastructure in a separate comparison.

For a fixed historical lab baseline, use Java 21.0.7, Spring Boot 3.5.0, pgJDBC 42.7.7, and PostgreSQL 15.13. These are experiment pins, not a recommendation to deploy old patch versions. Record the actual JDK vendor/build and resolved dependencies when running the test. Release references: Java, Spring Boot, pgJDBC, and PostgreSQL.

Keep these inputs the same across variants:

The row shape gives an estimated CSV size of roughly 210–220 MB. At 64 KiB/second, delivery would take approximately 55 minutes. These are calculations, not measurements; check the actual byte count before scheduling the test.

The acceptable generation time is still a decision for the report owner. Record that requirement before choosing a production transaction budget.

Create the database and update workload

The shell examples use Bash, such as a Linux terminal or WSL, with Docker, Maven, and Java available. Save the following as seed.sql:

CREATE TABLE public.export_lab (
    id bigint PRIMARY KEY,
    amount_cents bigint NOT NULL,
    note text NOT NULL
);

INSERT INTO public.export_lab (id, amount_cents, note)
SELECT g, 10000, repeat('x', 200)
FROM generate_series(1, 1000000) AS g;

ANALYZE public.export_lab;

SELECT count(*) AS rows,
       avg(pg_column_size(e)) AS average_record_bytes,
       pg_size_pretty(pg_total_relation_size('public.export_lab')) AS total_size
FROM public.export_lab AS e;

The primary key is the only index. The width query measures PostgreSQL’s record representation; it is not the CSV width or total storage per row. The generated text also keeps quoting and character encoding predictable during the first test.

Save the update workload as update.sql:

\set first_id random(1, 999901)
BEGIN;
UPDATE public.export_lab
SET amount_cents = amount_cents + 1
WHERE id BETWEEN :first_id AND :first_id + 99;
COMMIT;

Each transaction changes 100 existing rows and commits. The updater therefore creates committed row versions while the export is still reading its earlier view.

Start the database and copy both scripts into it:

docker run --name csv-export-lab \
  -e POSTGRES_DB=exportlab \
  -e POSTGRES_USER=lab \
  -e POSTGRES_PASSWORD=lab \
  -p 127.0.0.1:5432:5432 \
  -d postgres:15.13

docker exec csv-export-lab pg_isready -U lab -d exportlab
docker cp seed.sql csv-export-lab:/tmp/seed.sql
docker cp update.sql csv-export-lab:/tmp/update.sql
docker exec csv-export-lab psql -X -v ON_ERROR_STOP=1 \
  -U lab -d exportlab -f /tmp/seed.sql

Wait until pg_isready reports that the server accepts connections before running the seed command. These credentials belong only to the disposable lab, whose published database port is bound to localhost.

Later, after confirming the export has established its snapshot, run this in a separate terminal:

docker exec csv-export-lab pgbench -U lab -d exportlab \
  -n -c 1 -j 1 -R 10 -T 4200 -P 10 -f /tmp/update.sql

The target is approximately 1,000 row updates per second, not a guaranteed achieved rate. Save the reported throughput and scheduling lag. Keep this process in the foreground so it can be stopped for the vacuum comparison. The rate and custom-script options are documented in PostgreSQL’s pgbench reference.

Add the Spring Boot dependencies

Create a Maven project with this pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.5.0</version>
    <relativePath/>
  </parent>
  <groupId>example</groupId>
  <artifactId>csv-lab</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <properties>
    <java.version>21</java.version>
    <postgresql.version>42.7.7</postgresql.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

The JDBC starter supplies the pool and Spring JDBC transaction support. Actuator supplies the JVM, HTTP, and connection-pool measurements. Keep the resolved Tomcat and HikariCP versions in the experiment record with mvn dependency:tree.

Add src/main/resources/application.yml:

server:
  address: 127.0.0.1
  compression:
    enabled: false

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/exportlab?ApplicationName=csv-lab
    username: lab
    password: lab
    hikari:
      pool-name: export-lab
      maximum-pool-size: 4
      minimum-idle: 4
      connection-timeout: 2000

management:
  endpoints:
    web:
      exposure:
        include: health,metrics

The two-second pool setting limits how long a caller waits to acquire a connection. It does not limit the duration of an export already using one.

The database starts with its image defaults. Record SHOW statement_timeout and SHOW idle_in_transaction_session_timeout; both should be zero for this long lab comparison. Keep the operating system’s socket buffers at their defaults and record the host platform. No assumption about their exact size is needed: the experiment must establish whether they eventually fill.

Give streaming a bounded worker pool

Put the Java classes in the example.csvlab package under src/main/java/example/csvlab. Start with CsvLabApplication.java:

package example.csvlab;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class CsvLabApplication {
    public static void main(String[] args) {
        SpringApplication.run(CsvLabApplication.class, args);
    }
}

This starts the web application and discovers the configuration, exporter, and controllers in the same package.

Configure a bounded MVC executor in MvcConfig.java:

package example.csvlab;

import java.time.Duration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.web.servlet.config.annotation.AsyncSupportConfigurer;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class MvcConfig implements WebMvcConfigurer {
    @Bean
    public ThreadPoolTaskExecutor exportExecutor() {
        var executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(4);
        executor.setMaxPoolSize(4);
        executor.setQueueCapacity(0);
        executor.setThreadNamePrefix("csv-");
        return executor;
    }

    @Override
    public void configureAsyncSupport(AsyncSupportConfigurer configurer) {
        configurer.setTaskExecutor(exportExecutor());
        configurer.setDefaultTimeout(Duration.ofMinutes(90).toMillis());
    }
}

Spring runs StreamingResponseBody through asynchronous request processing. An ordinary Spring transaction is associated with the executing thread, so annotating only the controller method is insufficient to wrap work performed later by the callback. Open the transaction inside that callback. See StreamingResponseBody and Spring’s transaction semantics.

The 90-minute limit leaves room for the proposed slow-client test. Production report generation needs its own, much shorter budget. Async processing also still occupies a worker while it writes the response; this executor permits four active tasks and no queued tasks. Test rejection behavior before treating it as production admission control.

Open the transaction inside the streaming callback

Create CsvExporter.java:

package example.csvlab;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.ResultSet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;

@Service
public class CsvExporter {
    private static final Logger log = LoggerFactory.getLogger(CsvExporter.class);
    private final JdbcTemplate jdbc;
    private final TransactionTemplate transaction;

    public CsvExporter(JdbcTemplate jdbc, PlatformTransactionManager manager) {
        this.jdbc = jdbc;
        this.transaction = new TransactionTemplate(manager);
        transaction.setReadOnly(true);
        transaction.setIsolationLevel(
            TransactionDefinition.ISOLATION_REPEATABLE_READ);
        transaction.setPropagationBehavior(
            TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    }

    public void write(OutputStream destination, String label) throws IOException {
        long started = System.nanoTime();
        var measured = new MeasuredOutput(destination);
        try {
            transaction.executeWithoutResult(status ->
                jdbc.execute((ConnectionCallback<Void>) connection -> {
                    // Spring's JDBC transaction manager disables autocommit.
                    if (connection.getAutoCommit()) {
                        throw new IllegalStateException("Cursor needs a transaction");
                    }

                    try (var tag = connection.prepareStatement(
                            "SELECT set_config('application_name', ?, true), pg_backend_pid()")) {
                        tag.setString(1, label);
                        try (var result = tag.executeQuery()) {
                            result.next();
                            log.info("export={} pid={}", label, result.getInt(2));
                        }
                    }

                    try (var statement = connection.prepareStatement(
                            "SELECT id, amount_cents, note FROM public.export_lab ORDER BY id",
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY)) {
                        statement.setFetchSize(500);
                        try (var rows = statement.executeQuery()) {
                            var writer = new BufferedWriter(
                                new OutputStreamWriter(measured, StandardCharsets.UTF_8),
                                64 * 1024);
                            writer.write("id,amount_cents,note\r\n");
                            long count = 0;
                            while (rows.next()) {
                                if (Thread.currentThread().isInterrupted()) {
                                    throw new IOException("Export interrupted");
                                }
                                writer.write(Long.toString(rows.getLong(1)));
                                writer.write(',');
                                writer.write(Long.toString(rows.getLong(2)));
                                writer.write(',');
                                writer.write(quote(rows.getString(3)));
                                writer.write("\r\n");
                                if (++count % 500 == 0) {
                                    writer.flush();
                                }
                            }
                            writer.flush();
                        }
                    } catch (IOException failure) {
                        throw new UncheckedIOException(failure);
                    }
                    return null;
                })
            );
        } catch (UncheckedIOException failure) {
            throw failure.getCause();
        } finally {
            log.info("export={} callMs={} outputMs={} maxOutputCallMs={}",
                label, (System.nanoTime() - started) / 1_000_000,
                measured.totalNanos / 1_000_000,
                measured.maxNanos / 1_000_000);
        }
    }

    private static String quote(String value) {
        return "\"" + value.replace("\"", "\"\"") + "\"";
    }

    private static final class MeasuredOutput extends FilterOutputStream {
        long totalNanos;
        long maxNanos;

        MeasuredOutput(OutputStream destination) {
            super(destination);
        }

        @Override
        public void write(byte[] bytes, int offset, int length) throws IOException {
            long start = System.nanoTime();
            try {
                out.write(bytes, offset, length);
            } finally {
                record(System.nanoTime() - start);
            }
        }

        @Override
        public void flush() throws IOException {
            long start = System.nanoTime();
            try {
                out.flush();
            } finally {
                record(System.nanoTime() - start);
            }
        }

        private void record(long elapsed) {
            totalNanos += elapsed;
            maxNanos = Math.max(maxNanos, elapsed);
        }
    }
}

The transaction starts when write executes. JdbcTemplate uses its bound connection, the result set closes before the statement, and TransactionTemplate commits or rolls back before returning. Converting IOException into an unchecked exception inside the callback makes output failure trigger rollback; the caller receives the original I/O exception afterward. The HTTP stream belongs to the servlet container, so this method flushes it without closing it. Spring describes connection participation in its JDBC transaction documentation.

The cursor requirements are visible: autocommit is disabled by the transaction manager, the result set is forward-only, fetch size is positive, and the export query is a single statement. pgJDBC documents these requirements and warns that unsupported configurations can fall back to collecting the entire result. pgJDBC cursor documentation

The transaction-local application name lets monitoring identify each export and reverts when the transaction ends. The service also logs the backend PID. The first SELECT, including the tagging query here, establishes the transaction’s snapshot.

outputMs measures elapsed time inside downstream writes and flushes. It includes work as well as waiting. A large increase with the slow client is useful evidence; repeated thread dumps showing the export worker inside Tomcat’s response-write path provide additional confirmation. Capture those while the request is stalled, since the summary log appears only when the method exits.

jcmd -l
# Replace JAVA_PID with this application's PID, then repeat during the stall.
jcmd JAVA_PID Thread.print > slow-thread-dump.txt

Inspect threads named csv- and save each dump under a different filename. A Java thread shown as RUNNABLE can still be waiting inside native socket I/O; use its stack and the output timings together.

callMs also includes connection acquisition and transaction completion. Do not label it database transaction duration. Use database samples for that measurement.

Share the pool with an ordinary API request

Create ExportController.java:

package example.csvlab;

import java.util.UUID;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

@RestController
public class ExportController {
    private final CsvExporter exporter;
    private final JdbcTemplate jdbc;

    public ExportController(CsvExporter exporter, JdbcTemplate jdbc) {
        this.exporter = exporter;
        this.jdbc = jdbc;
    }

    @GetMapping("/exports/direct")
    public ResponseEntity<StreamingResponseBody> direct() {
        String label = "csv-direct-" + UUID.randomUUID();
        StreamingResponseBody body = output -> exporter.write(output, label);
        return ResponseEntity.ok()
            .header("Content-Type", "text/csv;charset=UTF-8")
            .header("Content-Disposition", "attachment; filename=report.csv")
            .header("Cache-Control", "no-store")
            .header("X-Export-Id", label)
            .body(body);
    }

    @GetMapping("/api/amount/{id}")
    public Long amount(@PathVariable long id) {
        return jdbc.queryForObject(
            "SELECT amount_cents FROM public.export_lab WHERE id = ?",
            Long.class, id);
    }
}

The direct endpoint starts its transaction inside the streaming worker. The ordinary API performs a small indexed lookup using the same four-connection pool, giving the experiment a separate request whose latency can be observed.

On a detected client disconnect, a response write normally raises an I/O failure, which unwinds the JDBC resources. Test that path explicitly. A blocked write or an undetected broken network can delay cleanup; the finally blocks run only after control returns. Neither a JDBC query timeout nor an interrupt check guarantees an immediate end to an HTTP write already blocked in the container.
There is also a file-integrity consequence. Once the response has been flushed, its status and headers are committed. A later failure can leave the client with a partial CSV and a 200 status already sent. Record curl’s exit status as well as its timing, and validate the saved file; --fail alone does not prove the report is complete. See the servlet response contract.

Compare a fast download with a slow one

Build and start the application with a fixed heap:

mvn package
java -Xms256m -Xmx256m -jar target/csv-lab-0.0.1-SNAPSHOT.jar

The small heap is an experimental constraint, not a sizing recommendation. If the application fails, record that outcome before changing the limit.

Run a fast download first, and retain the CSV once to check the output:

curl --fail --http1.1 -D fast.headers \
  -o fast.csv \
  -w 'bytes=%{size_download} seconds=%{time_total}\n' \
  http://localhost:8080/exports/direct

wc -l fast.csv
wc -c fast.csv
head -n 3 fast.csv

With this generated dataset, the expected count is 1,000,001 lines, including the header. That line-count check relies on the seed containing no embedded newlines. Validate real CSV with a parser when fields may contain line breaks.

For the controlled fast/slow comparison, send both responses to the same destination. Run these in separate trials:

curl --fail --http1.1 -o /dev/null \
  -w 'bytes=%{size_download} seconds=%{time_total}\n' \
  http://localhost:8080/exports/direct

curl --fail --http1.1 --limit-rate 64K -D slow.headers -o /dev/null \
  -w 'bytes=%{size_download} seconds=%{time_total}\n' \
  http://localhost:8080/exports/direct

For each trial, begin the same updater after the export PID appears with a non-null backend_xmin. Keep ordinary API traffic running throughout the comparison. If the fast export ends before the updater can start, automate that coordination or increase the dataset for every variant and restart the comparison. Do not describe runs without equivalent overlap as equivalent workloads.

Use a fixed observation window, such as 70 minutes, for each workload trial. Record the export’s active interval separately. For the fast variant, most of that window may occur after the export has finished; that distinction belongs in the results.

An ordinary-request probe can run in another terminal:

for i in $(seq 1 4200); do
  curl -sS --max-time 5 -o /dev/null \
    -w '%{http_code},%{time_total}\n' \
    http://localhost:8080/api/amount/500000
  sleep 1
done > api-latency.csv

This is a sequential probe, with one second between completed requests. It exposes stalls and failures but reduces its offered rate when requests slow down. Use the same external fixed-arrival-rate load test across variants before making capacity claims.

Reset the disposable dataset between independent trials, and use the same warm-up procedure. One practical reset is stopping all clients and the updater, verifying that no export transaction remains, truncating only public.export_lab in this lab, and rerunning the seed’s INSERT and ANALYZE. Record cache conditions and repeat trials; ordering the fast test first can otherwise bias the comparison.

The important question is whether the throttled download actually delays application output. If outputMs stays low and the transaction finishes quickly, inspect buffering before drawing a conclusion. A proxy that stores the whole response may already be separating generation from delivery, while consuming its own memory or disk.

Check the database before blaming autovacuum

Open a separate psql connection without BEGIN. Use the lab owner account for visibility, and sample this query with \watch 1:

SELECT clock_timestamp() AS sampled_at,
       pid,
       application_name,
       xact_start,
       clock_timestamp() - xact_start AS transaction_age,
       state,
       backend_xmin,
       age(backend_xmin) AS xmin_age_in_transactions,
       wait_event_type,
       wait_event
FROM pg_stat_activity
WHERE datname = current_database()
  AND xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start;

Match application_name and PID to the export log. Follow transaction age and backend_xmin over time. The backend may alternate between fetching rows and waiting for the application; it need not remain idle in transaction throughout. age(backend_xmin) counts transaction IDs, not seconds.

Also sample the table:

SELECT n_live_tup,
       n_dead_tup,
       n_tup_upd,
       last_autovacuum,
       autovacuum_count,
       last_vacuum,
       vacuum_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND relname = 'export_lab';

An old transaction is a clue. n_dead_tup is an estimate, and last_autovacuum does not establish that the versions under investigation were removed. Keep monitoring in autocommit mode to avoid reusing a transaction’s cached statistics. PostgreSQL monitoring documentation

Alongside those SQL samples, collect JVM heap usage, Hikari active/idle/pending connections, connection acquisition time, connection usage time, and ordinary API latency. Spring Boot exposes JVM and HTTP meters and provides Hikari-specific meters with the hikaricp prefix. Inspect /actuator/metrics for the names available in this build. Spring Boot metrics documentation

For example:

curl -s http://localhost:8080/actuator/metrics
curl -s 'http://localhost:8080/actuator/metrics/jvm.memory.used?tag=area:heap'
curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.active
curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.pending
curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.acquire
curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.usage

These calls provide snapshots, not a time series or a ready-made p95. Poll gauges at a fixed interval. Timer count and total-time deltas can give a mean for completed events, but cannot reconstruct a p95. For percentiles, collect individual durations or configure percentile or histogram recording in a suitable metrics backend. Micrometer percentile documentation A usage timer typically records a completed checkout when the connection returns; use active connections and database samples to observe a checkout still in progress. Hikari describes acquisition and usage measurements in its metrics documentation.

With one export and four connections, ordinary API latency might barely change. That is a valid result. If testing pool saturation, repeat all variants with export concurrency four and label it as a separate experiment. Do not change concurrency only for the slow trial.

An old snapshot does not automatically explain a slow API. Connection waiting, export scanning, CPU, database I/O, and cache effects need their own evidence.

Test whether releasing the snapshot changes cleanup

Use a separate, isolated retention experiment for the vacuum comparison. To prevent a scheduled autovacuum from racing between the two manual passes, disable it only for this disposable table before starting this experiment:

ALTER TABLE public.export_lab SET (autovacuum_enabled = false);

This change is an experimental control. Keep autovacuum enabled in the ordinary performance trials. Check that no vacuum is already running before beginning; anti-wraparound vacuum is not disabled by this table setting. PostgreSQL routine vacuuming

Start the slow export, identify its snapshot, and allow the update workload to commit changes. Then:

1. Stop the updater and verify its database session has disappeared. If interrupting docker exec leaves pgbench running, terminate that identified lab backend so the client exits; do not proceed while it can commit more updates.
2. Confirm the export transaction and backend_xmin are still present.
3. From a separate autocommit connection, run the first vacuum and save its full output.
4. Stop the slow client. Wait until monitoring confirms the export transaction has ended.
5. Without restarting updates or changing vacuum settings, run the second vacuum.

The command for each pass is:

docker exec csv-export-lab psql -X -v ON_ERROR_STOP=1 \
  -U lab -d exportlab \
  -c 'VACUUM (VERBOSE) public.export_lab;' > vacuum-before.log 2>&1

Use vacuum-after.log for the second pass. The redirection captures the detailed messages as well as standard output. Inspect both the command’s exit status and the saved report.

Look for versions reported as dead but not yet removable in the first pass, followed by removal after the export ends. Compare the reported counts with the update interval and snapshot lifetime; do not assume every update contributes one retained version.

Run this in a database with no other long transactions, prepared transactions, replication-slot retention, or standby-feedback horizon holding cleanup back. Otherwise, ending one export may leave another reason for retention.

Ordinary vacuum generally makes space reusable inside the relation; immediate file shrinkage is not the success criterion. Compare the removal details, not just file size. PostgreSQL VACUUM documentation

Restore the table setting after the isolated experiment:

ALTER TABLE public.export_lab RESET (autovacuum_enabled);

The proposed explanation is that the export still needs an older database view. More frequent vacuum cannot remove versions that remain necessary to that view. PostgreSQL specifically documents the cleanup impact of sessions retaining an open transaction. PostgreSQL client connection settings

If cleanup does not change after release, report that outcome and investigate the remaining retention holders and vacuum output. Do not fill the gap with an assumption about autovacuum.

Separate CSV generation from file delivery

The first redesign keeps incremental JDBC reads but changes their destination:

Generate:
database transaction → JDBC batches → local spool file
close result set → complete transaction → close file

Deliver:
completed file → HTTP response → customer

The database can finish its work before the customer begins downloading. Slow local storage can still prolong generation, but customer download speed no longer belongs inside the export transaction.

For a small lab comparison, create SpoolController.java. It generates the file during a POST and exposes a separate GET afterward:

package example.csvlab;

import java.io.IOException;
import java.nio.file.*;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.server.ResponseStatusException;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

@RestController
public class SpoolController {
    private final CsvExporter exporter;
    private final Map<UUID, Path> completed = new ConcurrentHashMap<>();
    private final Path directory;

    public SpoolController(CsvExporter exporter) throws IOException {
        this.exporter = exporter;
        this.directory = Files.createDirectories(Path.of("spool"));
    }

    @PostMapping("/exports/spool")
    public Map<String, String> generate() throws IOException {
        UUID id = UUID.randomUUID();
        Path file = Files.createTempFile(directory, "report-", ".part");
        boolean ready = false;
        try {
            try (var output = Files.newOutputStream(file)) {
                exporter.write(output, "csv-spool-" + id);
                // The export transaction has completed here.
            }
            completed.put(id, file); // Only closed, successful files are visible.
            ready = true;
            return Map.of("id", id.toString(), "status", "READY");
        } finally {
            if (!ready) {
                Files.deleteIfExists(file);
            }
        }
    }

    @GetMapping("/exports/spool/{id}")
    public ResponseEntity<StreamingResponseBody> download(@PathVariable UUID id)
            throws IOException {
        Path file = completed.get(id);
        if (file == null) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND);
        }
        StreamingResponseBody body = output -> {
            try (var input = Files.newInputStream(file)) {
                input.transferTo(output);
            }
        };
        return ResponseEntity.ok()
            .header("Content-Type", "text/csv;charset=UTF-8")
            .header("Content-Disposition", "attachment; filename=report.csv")
            .header("Cache-Control", "no-store")
            .contentLength(Files.size(file))
            .body(body);
    }
}

The POST calls the same exporter, so the SQL, fetch size, CSV encoding, and snapshot policy stay the same. Successful generation commits before the file is published in the in-memory map. The GET opens no database connection, and its file input closes on success or I/O failure. Completed files remain available for retrying a download.

This is a lab adapter, not a durable job system. It loses its registry on restart, keeps successful files until manually removed, has no admission control, and performs generation on the POST request thread. Use it only for a few localhost trials; clean the lab’s completed spool files after downloads finish. Process crashes can also leave partial files behind.

Test it with:

curl --fail -X POST \
  -w '\ngeneration_request_seconds=%{time_total}\n' \
  http://localhost:8080/exports/spool

# Replace UUID_FROM_POST with the returned id.
curl --fail --limit-rate 64K -o /dev/null \
  -w 'bytes=%{size_download} download_seconds=%{time_total}\n' \
  http://localhost:8080/exports/spool/UUID_FROM_POST

Confirm that the tagged generation transaction is absent before starting the GET. Repeat the same updater and ordinary API workload, with the same observation window, to compare the effects of generation and delivery separately.

For this variant, total user waiting time includes both requests. Spooling also delays the first CSV byte until generation finishes and adds a disk write followed by a disk read.

Move production generation to a job worker

A background worker can generate locally, upload the completed file, and then publish a download location. The ordering should remain explicit. The following is orchestration pseudocode; jobs and storage represent application-specific durable services:

void process(Job job) {
    // Claim with a lease so concurrent workers cannot publish the same attempt.
    jobs.claim(job.id());
    Path file = createPrivateSpoolFile();
    try {
        jobs.markGenerating(job.id());
        try (OutputStream output = openSpool(file)) {
            exporter.write(output, "csv-job-" + job.id());
        } // File closed; export transaction already completed.

        long bytes = size(file);
        String hash = checksum(file);
        jobs.markUploading(job.id(), job.objectKey(), bytes, hash);
        storage.uploadOrVerify(job.objectKey(), file, hash);
        jobs.markReady(job.id(), job.objectKey(), bytes, hash);
    } catch (Exception failure) {
        jobs.recordFailureForRetry(job.id(), failure);
        throw propagate(failure);
    } finally {
        deleteSpoolOrScheduleCleanup(file);
    }
}

Do not wrap this worker in one database transaction. Each job-status transition uses a short transaction, and the exporter owns the report-reading transaction. Upload begins only after that transaction and the file writer have finished.

Retry behavior needs a precise contract. Give each generation attempt a stable, unique object key. If upload succeeds but publishing READY fails, a retry can verify that object’s size and checksum, then finish publishing it. An upload failure can be retried from the existing file while the worker still has it; if cleanup or a crash removes it, regeneration creates a new attempt with a new snapshot and object key.

Before using this design for customer reports, add disk reservations and a job concurrency limit. Check available space during generation, cap individual output size, and expire both completed artifacts and abandoned .part files. Cancellation should stop database reads and file writes, record a terminal job state, and arrange cleanup. A cancelled generation must never become READY later through an upload retry.

Keep spool files private. Authorize job status and download access for the report owner, and issue expiring object-storage URLs only for completed, authorized reports. CSV quoting handles delimiters and quotes; exported user input also needs a deliberate spreadsheet-formula policy before customers open it in Excel or similar tools.

Decide what “consistent report” means

The experiment deliberately uses one REPEATABLE READ transaction. PostgreSQL retains that transaction’s snapshot across its queries. Reading chunks in separate transactions permits different snapshots. PostgreSQL transaction isolation

For example, suppose an account report has a total of $100 and two detail rows: A contains $60 and B contains $40.

The export reads A as $60. Another transaction transfers $10 from A to B and commits. A later chunk reads B as $50.

The exported details now add up to $110, even though the actual total was $100 before and after the transfer.

Stable ordering did not prevent that mismatch. Neither would recording a maximum ID: the same rows changed values between reads.

The developer should ask the report owner whether the file must represent one consistent view or may combine values collected over a generation interval. For financial reconciliation, that distinction may determine whether the report is usable at all.

If one snapshot is required, spooling preserves it while shortening the transaction to generation time. If mixed-time data is acceptable, short transactions with keyset pagination may be an option. Historical versioned data or a precomputed reporting dataset can support other designs, but those are additional data-model decisions.

Changing to READ COMMITTED alone is not a general fix for a long cursor fetch. A cursor’s query can still retain a view while it is consumed. Test the actual cursor and transaction lifetime rather than assuming the isolation-level name removed the coupling. PostgreSQL describes cursor visibility in its DECLARE reference.

Record observations before drawing conclusions

These are predictions to verify, not results:

Use the following worksheet after running the trials. “Pending” deliberately means no measurement exists.

For transaction duration, record xact_start and when the transaction disappears, with the sampling interval as the uncertainty. For a fast export shorter than that interval, use finer sampling or transaction-completion instrumentation. Correlate pool usage with the export identity; a shared pool’s overall timer also includes ordinary API requests.

The three streaming variants do not measure the original List implementation. Include that implementation as a separate baseline if publishing a numerical heap-reduction claim. A fixed-heap streaming run alone cannot establish how much memory was saved.

Publish unexpected outcomes alongside the worksheet. No measurable API slowdown with three spare pool connections is useful information. A transaction that ends before the slow client finishes suggests the delivery path buffered enough data to break the expected coupling. Unchanged vacuum removal requires investigation rather than a stronger claim.

Finally, this seed has fixed-width, highly compressible text and a simple ordered query. Real reports may contain wide values, joins, sorts, and different execution plans. Fetch size bounds rows held by the driver under the cursor configuration; it does not bound memory used by an individual wide row or by PostgreSQL’s query execution.

Follow the transaction beyond the heap graph

The lab now defines two paths through the same exporter: stream JDBC rows directly to the HTTP response, or write a spool file and serve it after the transaction completes. Both preserve one database snapshot during generation. The comparison isolates whether customer download speed extends database resource use.

Four checks make that comparison useful:

• Measure transaction age and connection occupancy alongside heap usage.
• Compare vacuum removal before and after the export releases its snapshot.
• Verify that generation commits before upload or download begins.
• Agree on report consistency before splitting reads into separate transactions.

If slow delivery extends the transaction, spooling gives the database a finish line independent of the customer’s connection. It also adds storage, cleanup, retries, and a longer wait for the first CSV byte. Generation still needs a duration budget.

The remaining work is to run the trials and record the results. A lower heap footprint answers only part of the question: while this export waits, what remains open?