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 constraintThe 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 constraintThe 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.
