DEV Community: Rabinarayan Patra

PostgreSQL 18 Temporal Foreign Keys with Spring Boot JPA

Rabinarayan Patra — Thu, 21 May 2026 18:30:00 +0000

PostgreSQL 18 shipped temporal foreign keys. The kind of feature SQL standards committees promised back in SQL:2011 and most database vendors quietly ignored. Now it's in mainline PG and the Java ecosystem has almost no tutorials on how to use it from Spring Boot.

I went looking for "Spring Boot temporal foreign key" guides last week. The top results were 2018 Baeldung posts on hand-rolled validFrom/validTo columns with zero database-level constraints. Plenty of BETWEEN queries. Plenty of "remember to add an index". No one talks about PG18 yet. So I built a working example, hit every gotcha, and wrote it up.

This post walks the full path: what temporal FKs actually solve, the PostgreSQL 18 syntax, how to map range columns in Hibernate, the Spring Data repository patterns that work, and the ON DELETE behavior that will absolutely surprise you if you skip the docs.

What problem do temporal foreign keys solve?

A temporal foreign key enforces that a child row's time range fits entirely inside its parent row's time range, at the database level, on every insert and update. That's the part regular foreign keys can't do.

Take a classic example. An employee changes departments three times in five years. A project assignment references the employee. The business rule is: a project assignment can only exist during a period when the employee was actually employed.

The hand-rolled approach looks like this:

CREATE TABLE employees (
    emp_id BIGINT,
    valid_from DATE NOT NULL,
    valid_to DATE,
    PRIMARY KEY (emp_id, valid_from)
);

CREATE TABLE project_assignments (
    assignment_id BIGINT PRIMARY KEY,
    emp_id BIGINT NOT NULL,
    assignment_start DATE NOT NULL,
    assignment_end DATE,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

Every Spring shop I've worked at had a variation of this. And every one of them had bugs. Project assignments referencing employee IDs whose valid period ended six months ago. Manual BETWEEN checks in service layers that someone forgot to update. Audit failures during quarterly reviews. The data model lied about what it was.

PG18 fixes this at the constraint level. The constraint is the truth, not a service-layer hope.

How does PostgreSQL 18 implement WITHOUT OVERLAPS and PERIOD?

PG18 introduces two new clauses: WITHOUT OVERLAPS for primary and unique keys, and PERIOD for foreign keys. Both rely on range types and GiST indexes under the hood.

Here's the employees table redone the right way:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE employees (
    emp_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary NUMERIC(10,2) NOT NULL,
    valid_period daterange NOT NULL,
    PRIMARY KEY (emp_id, valid_period WITHOUT OVERLAPS)
);

A few things worth pointing out.

The btree_gist extension is required because the primary key mixes a regular BIGINT column with a range column. PG needs a GiST index that can handle both, and btree_gist provides the btree operator support inside GiST. If you forget it, the CREATE TABLE will fail with a confusing operator error.

The valid_period column uses daterange, one of PostgreSQL's built-in range types. You can also use tstzrange for timestamp ranges or int4range for numeric ranges. The constraint creates a GiST index automatically. Try to insert two rows for the same emp_id with overlapping valid_period values and PG rejects it.

Now the project assignments table with a real temporal FK:

CREATE TABLE project_assignments (
    assignment_id BIGSERIAL PRIMARY KEY,
    emp_id BIGINT NOT NULL,
    project_name VARCHAR(100) NOT NULL,
    assignment_period daterange NOT NULL,

    FOREIGN KEY (emp_id, PERIOD assignment_period)
        REFERENCES employees (emp_id, PERIOD valid_period)
);

The PERIOD keyword tells PG that the last column is a range. The check is not equality. The check is containment: the parent's matching rows must, in combination, fully cover the child's range. If the employee's valid_period ends June 1 2024 and the project assignment runs March 1 to August 1 2024, the insert fails because June 1 to August 1 has no parent row covering it.

You can verify this with a deliberate bad insert:

INSERT INTO employees (emp_id, name, department, salary, valid_period)
VALUES (1, 'Alice', 'Engineering', 80000, daterange('2024-01-01', '2024-06-01'));

INSERT INTO project_assignments (emp_id, project_name, assignment_period)
VALUES (1, 'Migration', daterange('2024-03-01', '2024-08-01'));
-- ERROR: insert or update on table "project_assignments" violates foreign key constraint

The constraint catches it. No service code needed.

How do you map daterange columns in Hibernate?

Hibernate 6 added native support for PostgreSQL range types through PostgreSQLRangeJdbcType, but the cleanest path for a Spring Boot app is still the hypersistence-utils library. It's the rebranded version of what most of us used as hibernate-types-52 for years, maintained by Vlad Mihalcea.

Add the dependency to your pom.xml:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-63</artifactId>
    <version>3.10.7</version>
</dependency>

If you're on Spring Boot 3.5+, you'll be on Hibernate 6.6, so use the hypersistence-utils-hibernate-63 artifact. Older Spring Boot 3.x versions use -62. The version numbers track Hibernate ORM, not Spring Boot.

Now the entity:

import io.hypersistence.utils.hibernate.type.range.Range;
import io.hypersistence.utils.hibernate.type.range.PostgreSQLRangeType;
import jakarta.persistence.*;
import org.hibernate.annotations.Type;

import java.math.BigDecimal;
import java.time.LocalDate;

@Entity
@Table(name = "employees")
@IdClass(EmployeeId.class)
public class Employee {

    @Id
    @Column(name = "emp_id")
    private Long empId;

    @Id
    @Type(PostgreSQLRangeType.class)
    @Column(name = "valid_period", columnDefinition = "daterange")
    private Range<LocalDate> validPeriod;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private String department;

    @Column(nullable = false)
    private BigDecimal salary;

    // getters, setters, constructors
}

A few things to flag.

The @IdClass(EmployeeId.class) is needed because the primary key is composite. The EmployeeId class is a plain Java record or class with the two ID fields and equals/hashCode.

public record EmployeeId(Long empId, Range<LocalDate> validPeriod) implements Serializable {}

The @Type(PostgreSQLRangeType.class) annotation is what makes Hibernate emit and read daterange correctly. The columnDefinition = "daterange" part is what makes JPA's schema generation produce the right column type if you let JPA create the schema. In production, you should use Flyway or Liquibase, not JPA schema generation, but it's worth being explicit either way.

You construct ranges like this:

Range<LocalDate> period = Range.closedOpen(
    LocalDate.of(2024, 1, 1),
    LocalDate.of(2024, 6, 1)
);

closedOpen matches PostgreSQL's [) notation, which is the most common range form for temporal data. Half-open intervals make adjacent ranges easy to reason about: [Jan 1, Jun 1) and [Jun 1, Dec 1) are adjacent, not overlapping.

How do you write the entity and repository?

Spring Data JPA does most of the work, but you need a custom query for the overlap check at the application level. Database constraints catch invalid inserts, but the app still needs to ask "who was employed during this period?"

The repository:

public interface EmployeeRepository extends JpaRepository<Employee, EmployeeId> {

    @Query(value = """
        SELECT *
        FROM employees
        WHERE emp_id = :empId
          AND valid_period && :period
        """, nativeQuery = true)
    List<Employee> findOverlapping(
        @Param("empId") Long empId,
        @Param("period") String period
    );
}

The && operator is PostgreSQL's range-overlap operator. You pass the range as a string in PG range literal form: [2024-01-01,2024-06-01). JPA doesn't have a native range-binding mechanism, so the string approach is the pragmatic move. If you want strong typing, Vlad's library offers Range.toString() which formats correctly.

Here's a service method that finds the employee record valid for a given date:

@Service
public class EmploymentService {

    private final EmployeeRepository repo;

    public EmploymentService(EmployeeRepository repo) {
        this.repo = repo;
    }

    public Optional<Employee> findAt(Long empId, LocalDate at) {
        String singleDay = String.format("[%s,%s]", at, at);
        return repo.findOverlapping(empId, singleDay).stream().findFirst();
    }
}

For the project assignment side, the insert just works. Hibernate sends the daterange, PG checks the temporal FK, and if the assignment period isn't fully covered by some employee period, the transaction rolls back.

@Transactional
public ProjectAssignment assign(Long empId, String projectName, Range<LocalDate> period) {
    ProjectAssignment pa = new ProjectAssignment();
    pa.setEmpId(empId);
    pa.setProjectName(projectName);
    pa.setAssignmentPeriod(period);
    return assignmentRepo.save(pa);
}

If the period extends past the employee's validity, you get a DataIntegrityViolationException wrapping the PG error. Catch it where your error handling needs it.

What are the gotchas with ON DELETE and temporal foreign keys?

This is the one that will burn you if you skip the docs. PostgreSQL 18 does not support CASCADE, RESTRICT, SET NULL, or SET DEFAULT referential actions on temporal foreign keys. Only NO ACTION is allowed.

From the official PG18 CREATE TABLE docs: "In a temporal foreign key, this option is not supported." That sentence appears under every action except NO ACTION.

What does this mean in practice? If you delete an employee row that has dependent project assignments, PG raises a foreign key violation. You have to delete the assignments first, manually, in application code or in a database trigger you write yourself.

-- This will fail at the second statement
DELETE FROM employees WHERE emp_id = 1;
-- ERROR: update or delete on table "employees" violates foreign key constraint

The workaround patterns I've seen:

The first is application-level deletion. Spring service methods that delete child rows before parent rows, wrapped in a @Transactional boundary. This is fine for small graphs, but it doesn't scale to deep hierarchies.

The second is soft delete on the parent. Add a deleted_at column, never actually delete rows, and let the temporal FK stay intact forever. Most enterprise systems do this anyway for audit reasons.

The third is to flip the model: instead of deleting the parent, you close the parent's period by updating valid_period to end at today's date. Future child inserts will fail. Existing child rows stay valid because their periods were covered when they were created.

The third approach is closest to the spirit of temporal modeling. You don't lose history. You just declare "this record stopped being valid at this point" and let the constraints enforce that going forward.

If you absolutely need cascading deletes, you can write a trigger that does the deletion manually before the parent delete fires. But at that point you're rebuilding what the standard wanted to give you, and the SQL spec authors decided cascade semantics on overlapping periods were too ambiguous to specify. They might be right.

When should you NOT use temporal foreign keys?

Temporal foreign keys are powerful, and like most powerful features they're easy to overuse. I've seen teams reach for them in places where regular FKs would be simpler and just as correct.

Skip temporal FKs when:

You only need audit history. If the question is "what changed when?" and not "what was the state of the world on date X?", a separate audit log table with regular FKs is simpler. Frameworks like Hibernate Envers handle this well.

The child rows don't have an independent time dimension. If a project assignment is just "associated with employee 1 forever", you don't need temporal FKs. A regular FK with a created_at timestamp is fine.

You're modeling a single current state. CRUD apps where the latest row is the only thing that matters don't need temporal modeling. Add a valid_period column and you've created complexity you'll have to pay for in every query.

You can't pay the GiST index cost. GiST indexes are slower for point lookups than btree indexes. For high-throughput single-row reads, you'll feel it. Benchmark first.

When the model genuinely is temporal, the constraint-enforced version is a huge improvement over the hand-rolled version. The number of bugs you avoid by having PG check every insert is real. The cost of writing the queries against ranges instead of timestamps is real too, but it's a one-time cost. The bugs are forever.

Conclusion

PostgreSQL 18 temporal foreign keys are one of the most under-discussed major-version features I've seen in a while. The Spring Boot ecosystem has barely caught up. If you're modeling employment, contracts, pricing tiers, policies, or anything else where state has duration, the new WITHOUT OVERLAPS and PERIOD clauses are worth learning even if you don't ship them tomorrow.

The piece I want more people to understand: this changes what your database can be the source of truth for. Before PG18, "this assignment must fall inside an employment period" was a service-layer concern that drifted out of sync. Now it's a constraint. The database tells the truth.

For more on the PG18 release, see the PostgreSQL 18.0 release notes, the Neon writeup on temporal constraints, and the CREATE TABLE syntax docs.

Keep Reading

Spring Boot Testcontainers Guide. Real PG18 in tests beats mocked databases for catching constraint bugs early.
Hibernate Lazy Init Guide. The other Hibernate gotcha that bites every Spring team.
PgBouncer Survival Guide. Connection pooling matters more when GiST indexes are doing real work.

Amazon S3 Files: AWS Just Turned Object Storage Into a File System

Rabinarayan Patra — Thu, 09 Apr 2026 18:30:00 +0000

I've been running EFS-to-S3 sync jobs for two years. Cron schedules, lifecycle policies, rsync scripts that break every time someone changes a directory structure. All because S3 couldn't speak file system.

That changed this week.

On April 7, 2026, AWS announced Amazon S3 Files, a feature that lets you mount any S3 bucket as a shared NFS file system. No gateway. No third-party tool. No data copies. Your applications read and write files, and S3 stores them. That's it.

And quietly, on April 6, AWS also started disabling SSE-C encryption by default on all new S3 buckets. If you're managing encryption keys manually, that one needs your attention too.

Let me walk through both changes.

What is Amazon S3 Files and how does it work?

Amazon S3 Files gives S3 buckets a fully-featured file system interface using NFS v4.2. You can mount a bucket on EC2, Lambda, EKS, ECS, Fargate, or AWS Batch and interact with your data using standard file operations: open(), read(), write(), ls, cp, mv. No SDK. No API calls. Just a mount point.

Architecture overview of Amazon S3 Files. Source: AWS News Blog

S3 is now the first and only cloud object store that provides native file system access while keeping all data in object storage. Your objects don't move to a separate file system. They stay in S3 with all the durability, lifecycle policies, and access controls you already have.

S3 Files is generally available in 34 AWS Regions as of launch day.

The stage and commit model

This is the part that surprised me. Instead of translating every file write into an immediate S3 PUT, S3 Files batches changes and commits them to S3 roughly every 60 seconds. AWS borrowed this concept from version control.

Here's what that means in practice:

You write a file through the NFS mount
The write lands in a caching layer (built on EFS infrastructure)
S3 Files aggregates writes within a 60-second window
Multiple writes to the same file become a single S3 PUT
The committed object appears in S3 with full consistency

This batching has two practical benefits. First, it cuts your S3 request costs because ten rapid writes to the same file become one PUT, not ten. Second, if you're using S3 versioning, you don't end up with ten versions of a file that changed in under a minute.

But it also means there's a ~60-second lag before file changes are visible on the S3 side. If your workflow needs immediate S3 API visibility of written data, you need to account for that delay.

When there's a conflict (say, someone writes to a file through NFS while another process updates the same object via the S3 API), S3 remains authoritative. The file-side version gets moved to a lost+found directory with metrics for visibility. No silent data loss.

The caching layer under the hood

When you create an S3 Files file system, AWS provisions a caching layer backed by EFS infrastructure. This cache holds three things:

Recently read files : Hot reads come from cache with sub-millisecond latency
Recently written files : Staged writes waiting for the next commit cycle
Metadata : Directory listings, file attributes, timestamps

Small file reads are served entirely from the cache. Large file reads (over 1MB) stream directly from S3 and don't incur S3 Files charges. The aggregate read throughput can reach multiple terabytes per second.

This design is what makes S3 Files cost-effective. You pay file system rates ($0.30/GB-month) only on the data that's actively cached. A petabyte bucket where only 500GB is actively read? You're paying S3 rates for the petabyte and file system rates for the 500GB.

How do you set up S3 Files on an EC2 instance?

The setup is straightforward. Here's the full flow:

# Step 1: Create an S3 Files file system linked to your bucket
aws s3api create-file-system \
  --bucket my-data-bucket \
  --file-system-name my-fs

# Step 2: Get the mount target DNS name
aws s3api describe-file-system \
  --bucket my-data-bucket \
  --file-system-name my-fs \
  --query 'FileSystem.MountTargets[0].DnsName' \
  --output text

# Step 3: Mount on your EC2 instance
sudo mount -t nfs4 \
  -o nfsvers=4.2,rsize=1048576,wsize=1048576 \
  fs-mount-target.efs.us-east-1.amazonaws.com:/ \
  /mnt/s3data

# Step 4: Use it like any filesystem
ls /mnt/s3data
cp local-file.csv /mnt/s3data/uploads/
cat /mnt/s3data/reports/quarterly.json

Once mounted, every application on that instance can access S3 data through normal file I/O. Python scripts, Java apps, shell scripts, legacy C++ binaries. Nothing needs to know it's talking to S3.

For persistent mounts across reboots, add it to /etc/fstab:

# /etc/fstab entry for S3 Files
fs-mount-target.efs.us-east-1.amazonaws.com:/ /mnt/s3data nfs4 nfsvers=4.2,rsize=1048576,wsize=1048576,_netdev 0 0

Where does S3 Files beat EFS (and where does it not)?

This is the question I've been testing all week. S3 Files isn't a drop-in EFS replacement for every workload, but for specific patterns it's clearly better.

S3 Files wins

Scenario	Why S3 Files
Large datasets, small active working set	Pay S3 rates on cold data, file system rates only on hot data
Legacy app migration to S3	Zero code changes needed, just mount and go
AI/ML training data pipelines	Read training data as files, store as S3 objects
Agentic AI workloads	Shared workspace across multiple compute instances
Multi-service data sharing	Multiple EKS pods or Lambda functions reading the same dataset

EFS still wins

Scenario	Why EFS
All data is hot, constant read/write	EFS avoids the commit delay and S3 request overhead
Sub-second write visibility needed	S3 Files has a ~60-second commit lag
Windows workloads (SMB)	S3 Files only supports NFS, no SMB
Hard link requirements	S3 Files doesn't support hard links
Bucket exceeds 50 million objects	AWS warns about performance at this scale

Pricing comparison

The pricing math depends entirely on your access pattern:

S3 Files cached storage : $0.30/GB-month (only for actively cached data)
S3 Files reads (small files): $0.03/GB from cache
S3 Files reads (large files, 1MB+): $0 from S3 Files (standard S3 GET charges apply)
S3 Files writes : $0.06/GB

Compare that to EFS Performance-optimized at $0.30/GB for standard storage and $0.03/GB for reads. The difference shows up at scale: if you have 10TB in a bucket but only touch 200GB regularly, S3 Files costs a fraction of what an equivalent EFS setup would cost.

What are the gotchas you should know before adopting S3 Files?

I ran into a few things during my initial testing that are worth flagging.

The 60-second commit window is real. If you write a file via NFS and immediately try to read it through the S3 API (using aws s3 cp or a direct GET), it won't be there yet. Your application logic needs to handle this. For workflows that do writes via NFS and reads via S3 API, consider adding a short wait or checking for object existence.

NFS file locks don't protect against S3 API access. If you lock a file through NFS, that lock only applies to other NFS clients. Someone using the S3 API directly can still modify the object. This isn't a bug. It's how the boundary between file system and object store works. But it can bite you if mixed access isn't on your radar.

The 50-million object warning is something to watch. AWS recommends caution when a mounted bucket contains more than 50 million objects. Directory listings and metadata operations can slow down at that scale. If you're dealing with buckets that large, consider using S3 prefixes to scope your mount.

No pNFS, Kerberos, or nconnect support. If your NFS setup depends on parallel NFS, Kerberos authentication, NFSv4 data retention, or the nconnect mount option, those aren't available yet at GA. Standard NFS v4.2 features work fine.

SMB is not supported. Windows workloads that need file system access to S3 still need FSx or a gateway solution.

Why did AWS disable SSE-C encryption by default?

This change flew under the radar next to S3 Files, but it affects every new bucket created after April 6, 2026.

SSE-C (Server-Side Encryption with Customer-Provided Keys) lets you bring your own encryption key on every PUT and GET request. S3 encrypts and decrypts using your key but never stores it. The idea is maximum control. The reality is operational risk. Lose the key, lose the data forever. AWS can't recover it for you. There's no "forgot my password" option.

AWS KMS solved this years ago with customer-managed keys (CMKs) that give you full ownership and control, plus key rotation, auditing through CloudTrail, and recovery options. For most workloads, KMS does everything SSE-C does, minus the footgun.

So AWS made SSE-C opt-in instead of opt-out. Here's how the rollout works:

What changes for new buckets

Every new general-purpose S3 bucket created after April 6, 2026 has SSE-C disabled by default. If you try to upload an object with SSE-C headers, you'll get an access denied error unless you explicitly enable SSE-C first.

To enable SSE-C on a new bucket:

# Explicitly allow SSE-C on a new bucket
aws s3api put-bucket-encryption \
  --bucket my-new-bucket \
  --server-side-encryption-configuration '{
    "Rules": [{
      "ApplyServerSideEncryptionByDefault": {
        "SSEAlgorithm": "AES256"
      },
      "BucketKeyEnabled": false
    }],
    "AllowSSEC": true
  }'

What changes for existing buckets

This is the part that might catch people off guard. AWS is also disabling SSE-C on existing buckets that have zero SSE-C encrypted objects. If you created a bucket, never used SSE-C, but your automation code includes SSE-C headers "just in case," those writes will start failing.

Existing buckets that actually contain SSE-C objects? No changes. AWS won't touch those.

Who this affects

If you're using AWS KMS (SSE-KMS) or S3-managed keys (SSE-S3) for encryption, this change does nothing to you. Your buckets already don't use SSE-C.

If you're one of the teams still on SSE-C, you'll want to:

Audit which buckets actually use SSE-C (aws s3api get-bucket-encryption)
Plan a migration to KMS for buckets that don't strictly need SSE-C
Explicitly re-enable SSE-C on new buckets where it's genuinely required

The rollout covers 37 AWS Regions, including GovCloud and China regions, and will complete over the next few weeks.

What do these changes tell us about where S3 is heading?

If I look at S3 Files and the SSE-C default together, they tell the same story: AWS is reducing the reasons you'd reach for anything other than S3.

Need file system access? You used to need EFS plus sync scripts. Now you mount S3 directly. Need encryption with your own keys? You used to reach for SSE-C. Now AWS is steering you toward KMS, which handles key management for you.

S3 now stores over 500 trillion objects and handles roughly 200 million requests per second. It turned 20 years old last month. And instead of letting it coast, AWS gave it the most significant capability upgrade since S3 Intelligent-Tiering launched in 2018.

For my own projects, I'm already replacing two EFS-backed data pipelines with S3 Files mounts. The sync cron jobs are gone. The drift alerts are gone. One mount point, one storage bill, and a 60-second commit window I can easily live with.

If you've been running parallel storage systems just to get file access to your S3 data, this week is a good week to rethink that architecture.

For the full details, see the official S3 Files announcement, the S3 Files product page, and the SSE-C security default announcement.

Keep Reading

AI-Driven Anomaly Detection for Security - How cloud infrastructure and AI work together for real-time threat detection.
Implementing the Outbox Pattern with CDC in Microservices - Storage design patterns that affect reliability in distributed systems.
The Day a React Patch Broke the Internet - Another deep dive into an infrastructure event that caught everyone off guard.