Over-Engineered on Purpose — Part 10: Unlogged Tables, JSONB, and Why I Tried the Wrong Tool on Purpose.
This is Part 10 of a series where I'm building a microservice platform from scratch. Part 9 covers config server and distributed tracing. Full codebase on GitHub.
The system is working end to end now. Users can register, browse machines, book them, leave reviews. Categories, machines, images, maintenance records — the whole thing. With all the infrastructure from the previous posts in place, we have a functioning platform.
Which means we have data. And with data comes the next question: how do we make reads faster?
In most applications, reads far outnumber writes. Someone browsing the machine catalog, looking at categories, checking machine details — these are all reads, and they all hit the database. The Catalog Service is the most read-heavy service in the system. Every time someone opens the app, they're querying machines.
The obvious answer is caching. And when you type "caching" into a search bar in the context of application development, one name comes up: Redis.
I didn't use Redis.
The Zerodha Rabbit Hole
I was on one of my usual YouTube detours when I stumbled on a talk by an engineer at Zerodha — an Indian online stock trading platform. They were talking about how they've optimized their Postgres setup. No master-slave replication. Backups go to S3. And the thing that caught my eye: they use a second Postgres server as a caching layer.
Postgres as a cache? That sounded wrong. The whole point of a cache is that it's faster than your database. Using a database as a cache for your database felt like using a bicycle to speed up your car.
But it got me thinking. What does a cache actually need to do?
- •
Expiration — remove data after a certain time
- •
Eviction — clear old data when space is limited
- •
Invalidation — overwrite when the source data changes
- •
Key-value storage — look up data by a key
- •
Performance — faster than the primary source
- •
No persistence guarantees — if the cache dies, you just rebuild it
Postgres can do most of these. The question is whether it can do them fast enough.
Unlogged Tables: Postgres Without the Safety Net
Here's the trick. Postgres is reliable because of WAL — Write-Ahead Logging. Every operation is logged before it's committed. If the server crashes mid-transaction, it replays the log on startup and recovers. This is where durability comes from. It's also where a lot of the write overhead comes from.
But if your data is a cache — disposable by definition — you don't need durability. If the server crashes and the cache is gone, you just rebuild it from the primary database.
Postgres has a feature for exactly this: unlogged tables. They skip WAL entirely. No write-ahead logging means faster writes. The tradeoff is explicit — if the server crashes, the data is lost. For a cache, that's not a tradeoff. That's the design.
I created a separate Postgres instance as my cache, with unlogged tables for each entity I want to cache:
CREATE UNLOGGED TABLE category_cache(
id UUID PRIMARY KEY,
value JSONB NOT NULL,
inserted_at TIMESTAMP DEFAULT NOW()
);
CREATE UNLOGGED TABLE machine_cache(
id UUID PRIMARY KEY,
value JSONB NOT NULL,
inserted_at TIMESTAMP DEFAULT NOW()
);
CREATE UNLOGGED TABLE machine_images_cache(
machine_id UUID PRIMARY KEY,
value JSONB NOT NULL,
inserted_at TIMESTAMP DEFAULT NOW()
);Three columns per table. The key is the UUID of the entity. The value stores the data as JSONB — a binary JSON format in Postgres that's significantly faster to query than regular JSON because it doesn't need to be re-parsed on every read. JSONB also supports GIN indexes for searching nested values. The inserted_at timestamp is for expiration.
For cache expiration, I created a stored procedure:
CREATE OR REPLACE PROCEDURE expire_cache(
target_table TEXT,
retention_period INTERVAL
) AS $$
BEGIN
EXECUTE format(
'DELETE FROM %I WHERE inserted_at < NOW() - $1',
target_table
) USING retention_period;
END;
$$ LANGUAGE plpgsql;A Spring @Scheduled task calls this every hour to clean out stale entries:
@Scheduled(fixedRate = 360000)
public void evictCache() {
cacheJdbcTemplate.execute(
"CALL expire_cache('category_cache','60 minutes')");
cacheJdbcTemplate.execute(
"CALL expire_cache('machine_cache','60 minutes')");
cacheJdbcTemplate.execute(
"CALL expire_cache('machine_images_cache','60 minutes')");
}I looked into using pg_cron to schedule this at the database level but decided to keep it in the application layer for now. One less extension to manage.
Wiring Up a Second DataSource
This is where it got interesting from a Spring perspective. My Catalog Service already has a primary Postgres database for its data. Now I'm adding a second Postgres instance for caching. Spring Boot auto-configures one datasource by default. The moment you add a second, the auto-configuration backs off because it doesn't know which one to use.
The fix: explicitly mark the primary datasource as @Primary so Spring knows it's the default for JPA, Flyway, and everything else. The cache datasource gets its own bean:
@Bean
@Primary
public DataSource dataSource(
@Value("${spring.datasource.url}") String url,
@Value("${spring.datasource.username}") String username,
@Value("${spring.datasource.password}") String password) {
return DataSourceBuilder.create()
.url(url).username(username).password(password)
.build();
}
@Bean
@ConfigurationProperties("cache-db")
public DataSource cacheDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public JdbcTemplate cacheJdbcTemplate(
@Qualifier("cacheDataSource") DataSource ds) {
return new JdbcTemplate(ds);
}One gotcha here: the primary datasource uses @Value instead of @ConfigurationProperties. That's because Spring's spring.datasource properties use url as the key name, but DataSourceBuilder with @ConfigurationProperties expects jdbc-url. The binding fails silently. The cache datasource config uses jdbc-url in its YAML, so @ConfigurationProperties works directly.
Small thing. Cost me an hour.
The Cache Service
With the datasource wired, the cache operations are straightforward JDBC. ObjectMapper converts entities to JSON strings, and JSONB casting handles the rest:
public void putCategory(CategoryEntity category) {
String json = objectMapper.writeValueAsString(category);
cacheJdbcTemplate.update("""
INSERT INTO category_cache (id, value, inserted_at)
VALUES (?::uuid, ?::jsonb, NOW())
ON CONFLICT (id) DO UPDATE
SET value = EXCLUDED.value, inserted_at = NOW()
""",
category.getId(), json);
}
public Optional<CategoryEntity> getCategory(UUID key) {
try {
String result = cacheJdbcTemplate.queryForObject(
"SELECT value FROM category_cache WHERE id = ?::uuid",
String.class, key);
return Optional.of(
objectMapper.readValue(result, CategoryEntity.class));
} catch (EmptyResultDataAccessException ex) {
return Optional.empty();
}
}The ON CONFLICT ... DO UPDATE on the put means that if the cache already has this entity, it overwrites it with fresh data and resets the timestamp. This handles invalidation — when you update a machine's details, the next put overwrites the stale cache entry.
Integration with the service layer follows the standard cache-aside pattern:
@Override
public CategoryEntity getCategoryById(UUID id) {
Optional<CategoryEntity> cached = cacheService.getCategory(id);
if (cached.isPresent()) {
log.info("Cache hit for category id={}", id);
return cached.get();
}
log.info("Cache miss for category id={}", id);
CategoryEntity category = categoryRepository.findById(id)
.orElseThrow(() -> new NotFoundException(
"Category not found: " + id));
cacheService.putCategory(category);
return category;
}Check the cache first. If it's there, return it. If not, query the primary database, store the result in cache, and return it. On writes (create, update, delete), put the new data in the cache or evict the old entry. It works. Cache hits are in the logs. Reads that hit the cache skip the primary database entirely.
But Is It Actually Fast?
Here's where honesty matters.
I went looking for benchmarks. A Medium article — "Can Postgres Replace Redis as a Cache?" — ran comparisons between Redis and Postgres unlogged tables. The results are what you'd expect:
Redis is significantly faster. Unlogged tables help with write performance, but reads are still standard Postgres reads — going through the query parser, planner, and executor. Redis serves reads directly from memory with minimal overhead. The latency difference is meaningful.
For my project at its current scale — a learning exercise with one user (me) — the Postgres cache is more than fast enough. Cache hits return in single-digit milliseconds. It works.
But would I use this in production? Probably not. Redis exists for a reason. It's purpose-built for this. In-memory by design, sub-millisecond latency, built-in expiration, pub/sub for cache invalidation across instances. Trying to replicate all of that with Postgres is fighting the tool.
Why I Did It Anyway
The same reason I used gRPC when REST would work. The same reason I used Eureka when I'm running everything locally. The same reason this series is called "Over-Engineered on Purpose."
Everyone says "just use Redis." And they're right. But before this, I couldn't have told you why they're right beyond "it's faster." Now I have a much better picture. It's not just one thing — it's layers of overhead that Postgres carries by design.
There's the WAL overhead on writes (which unlogged tables remove, to be fair). There's the query parser and planner running on every read, even simple key lookups. But the one that surprised me most was the shared buffers problem. Even if you ran Postgres entirely in RAM, it wouldn't match Redis. Postgres always checks its shared buffers first, and accessing data there incurs costs from managing locks and internal processes needed for data integrity and concurrent access. If the data isn't in shared buffers, Postgres copies it there before serving it. Redis doesn't have any of that — it reads the value from memory and gives it to you. No buffer management, no lock coordination, no query planning.
The gap isn't just "disk vs memory." It's "a database engine designed for correctness and durability" vs "a data store designed purely for speed." Removing WAL gets you part of the way there, but Postgres is still doing Postgres things under the hood.
I didn't learn this by reading the Redis docs. I learned it by building the worse version and then reading articles from people who benchmarked both. The articles landed differently because I had context. When they said "Postgres still goes through the query planner on every read," I knew exactly what that meant because I'd just written those queries.
Also — and this is a smaller point but it matters — the CacheService is a clean abstraction. The service layer calls getCategory() and putCategory(). It doesn't know or care what's behind it. If I switch to Redis later, I swap the implementation. The interface stays the same. The unconventional choice didn't create technical debt. It created understanding.
Resources That Helped
- •
Zerodha's Postgres Setup (Perfology)— The talk that planted the idea. Watching how a real company optimizes Postgres for their specific needs instead of defaulting to the "standard" architecture.
- •
You Don't Need a Dedicated Cache Service — PostgreSQL as a Cache — The blog that walked through the unlogged tables approach. Straight to the point, no fluff.
- •
PostgreSQL Unlogged Tables (Crunchy Data) — Deeper dive into how unlogged tables work and when they're appropriate.
- •
Can Postgres Replace Redis as a Cache? — The honest benchmark comparison. Spoiler: Redis wins. But the margin depends on your use case.