Postgres Does Everything: Queue, Search, Vector in One Database
1. Overview
A typical system often has many components: PostgreSQL stores data, a message broker handles queues, a search engine powers search, and recently a vector database for AI features. Each component brings its own operational overhead—monitoring, backups, data syncing.
The “Postgres does everything” trend flips the question: if PostgreSQL can already do most of those tasks well enough, do you really need yet another system? In this article, you’ll use PostgreSQL as a job queue, full-text search, and vector search with Spring Boot 3.x. You’ll also learn when to stop and reach for specialized tools.
2. Why This Trend Matters
There are a few key reasons:
- Reduced operational complexity. Each new system is one more thing that can break at 3 AM. For small teams, the cost often outweighs the performance gains.
- Transactional consistency. When an order and an email-sending job live in the same database, you save both in one transaction. No situation where the order is saved but the message is lost, or vice versa.
- A powerful extension ecosystem. PostgreSQL has a strong extension mechanism. For example,
pgvectoradds a vector data type and nearest-neighbor search indexes. - AI features pulling vector storage needs. Many teams want to add semantic search without spinning up yet another database just for embeddings.
The spirit of this trend is start simple, split out when metrics prove you need to. It doesn’t mean PostgreSQL replaces every system.
3. Setup
3.1. Maven Dependencies
You need JDBC, the PostgreSQL driver, and Testcontainers for testing. Spring Boot manages versions, so you don’t need to specify them:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-testcontainers</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>junit-jupiter</artifactId>
<scope>test</scope>
</dependency>
</dependencies>3.2. Running PostgreSQL with pgvector
The pgvector/pgvector image bundles PostgreSQL and the vector extension. Pick the tag matching your PostgreSQL major version:
services:
postgres:
image: pgvector/pgvector:pg16
environment:
POSTGRES_DB: shop
POSTGRES_USER: shop
POSTGRES_PASSWORD: secret
ports:
- "5432:5432"Configure Spring Boot to connect and automatically run schema.sql on startup:
spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=shop
spring.datasource.password=secret
spring.sql.init.mode=alwaysIn production, use Flyway or Liquibase instead of schema.sql.
4. Job Queue with FOR UPDATE SKIP LOCKED
4.1. Schema
A minimal queue needs just one table:
CREATE TABLE IF NOT EXISTS job_queue (
id BIGSERIAL PRIMARY KEY,
job_type TEXT NOT NULL,
payload JSONB NOT NULL,
run_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_job_queue_run_at ON job_queue (run_at);The run_at column lets you schedule jobs for later. An index on run_at helps workers fetch the earliest job without scanning the whole table.
4.2. Getting and Processing Jobs
The key is the FOR UPDATE SKIP LOCKED clause. Multiple workers run the same query. Each worker locks one row, and other workers skip over locked rows instead of waiting:
public record Job(long id, String type, String payload) {}
@Repository
public class JobQueue {
private final JdbcTemplate jdbc;
public JobQueue(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
public void enqueue(String type, String payloadJson) {
jdbc.update("INSERT INTO job_queue (job_type, payload) VALUES (?, ?::jsonb)",
type, payloadJson);
}
@Transactional
public boolean processNext(Consumer<Job> handler) {
List<Job> jobs = jdbc.query("""
SELECT id, job_type, payload::text
FROM job_queue
WHERE run_at <= now()
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED
""",
(rs, i) -> new Job(rs.getLong(1), rs.getString(2), rs.getString(3)));
if (jobs.isEmpty()) {
return false;
}
Job job = jobs.getFirst();
handler.accept(job);
jdbc.update("DELETE FROM job_queue WHERE id = ?", job.id());
return true;
}
}Explanation:
enqueueinserts a job.?::jsonbcasts the JSON string toJSONBtype.processNextruns in a transaction. TheSELECT ... FOR UPDATE SKIP LOCKEDquery locks exactly one unprocessed job.- If
handlersucceeds, the job is deleted and the transaction commits. - If
handlerthrows an exception, the transaction rolls back. The job stays in the queue and will be retried. If the worker crashes mid-process, PostgreSQL automatically releases the lock when the connection closes.
A worker can be a simple @Scheduled method:
@Component
public class EmailWorker {
private final JobQueue jobQueue;
public EmailWorker(JobQueue jobQueue) {
this.jobQueue = jobQueue;
}
@Scheduled(fixedDelay = 1000)
public void poll() {
while (jobQueue.processNext(job ->
System.out.println("Sending email for job " + job.id() + ": " + job.payload()))) {
// Keep going until no more jobs
}
}
}Remember to add @EnableScheduling to a configuration class. When jobs arrive, the console shows:
Sending email for job 1: {"orderId": 42, "email": "an@example.com"}Note: Keeping a transaction open during job processing is very simple and safe if the worker crashes. The tradeoff is that transactions hang for the duration of processing. If jobs run long (slow API calls, large file processing), you should switch to a model with a
statuscolumn andlocked_until, or move to a dedicated queue library. You should also add anattemptscolumn to avoid endlessly retrying a broken job.
4.3. The Real Benefit: Enqueue within Business Transaction
This is the strongest reason to use Postgres as a queue:
@Service
public class OrderService {
private final JdbcTemplate jdbc;
private final JobQueue jobQueue;
public OrderService(JdbcTemplate jdbc, JobQueue jobQueue) {
this.jdbc = jdbc;
this.jobQueue = jobQueue;
}
@Transactional
public void placeOrder(long orderId, String customerEmail) {
jdbc.update("INSERT INTO orders (id, customer_email) VALUES (?, ?)", orderId, customerEmail);
jobQueue.enqueue("SEND_CONFIRMATION_EMAIL",
"{\"orderId\": %d, \"email\": \"%s\"}".formatted(orderId, customerEmail));
}
}The order and the job either both commit or both roll back. With a separate broker, you’d have to implement the transactional outbox pattern to get this guarantee. (Using formatted for brevity; production code should serialize the payload with Jackson to properly escape characters.)
If you don’t want workers polling constantly, you can combine PostgreSQL’s LISTEN/NOTIFY to wake up workers when new jobs arrive. Polling should still be kept as a fallback.
5. Full-text Search with tsvector and GIN Index
5.1. Schema
PostgreSQL has full-text search built in. Use a generated column to automatically update the search vector whenever data changes:
CREATE TABLE IF NOT EXISTS product (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
description TEXT NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('simple', name), 'A') ||
setweight(to_tsvector('simple', description), 'B')
) STORED
);
CREATE INDEX IF NOT EXISTS idx_product_search ON product USING GIN (search_vector);Explanation:
to_tsvector('simple', ...)breaks text into tokens. Thesimpleconfiguration only converts to lowercase; no stemming. This choice works well for Vietnamese since PostgreSQL doesn’t have a built-in Vietnamese dictionary.setweightassigns weightAto the name andBto the description. Matches in the name rank higher.- A GIN index speeds up queries on the
tsvectorcolumn.
5.2. Queries
public record ProductHit(long id, String name, double rank) {}
@Repository
public class ProductSearch {
private final JdbcTemplate jdbc;
public ProductSearch(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
public List<ProductHit> search(String userQuery) {
return jdbc.query("""
SELECT id, name, ts_rank(search_vector, q) AS rank
FROM product, websearch_to_tsquery('simple', ?) AS q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 10
""",
(rs, i) -> new ProductHit(rs.getLong("id"), rs.getString("name"), rs.getDouble("rank")),
userQuery);
}
}websearch_to_tsquery accepts syntax like a regular search box: "jacket" -brown means include “jacket” and exclude “brown”. The @@ operator checks if the vector matches the query. ts_rank orders results.
With sample data containing “Jacket” and “Jeans” (description includes “weatherproof”), searching for jacket returns:
ProductHit[id=1, name=Jacket, rank=0.6079271]
ProductHit[id=2, name=Jeans, rank=0.24317084]Note: Users often type without accents. You can use the
unaccentextension. By default,unaccentisn’t markedIMMUTABLE, so to use it in a generated column or index, wrap it in your ownIMMUTABLEfunction. For typo-tolerant search, addpg_trgm.
6. Vector Search with pgvector
6.1. Schema
An embedding is an array of numbers generated by an AI model. Text with similar meanings produce nearby vectors. Enable the extension and add a column:
CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE product ADD COLUMN IF NOT EXISTS embedding vector(3);
CREATE INDEX IF NOT EXISTS idx_product_embedding
ON product USING hnsw (embedding vector_cosine_ops);This example uses 3 dimensions for readability. In practice, the dimension count must exactly match your embedding model—usually hundreds to thousands. An HNSW index enables very fast approximate nearest-neighbor search. vector_cosine_ops tells the index to serve cosine distance.
6.2. Save and Search by Vector
No extra libraries needed. Convert float[] to a literal string like [0.1,0.2,0.3] then cast to ::vector:
@Repository
public class ProductVectorSearch {
private final JdbcTemplate jdbc;
public ProductVectorSearch(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
public void saveEmbedding(long productId, float[] embedding) {
jdbc.update("UPDATE product SET embedding = ?::vector WHERE id = ?",
toLiteral(embedding), productId);
}
public List<String> findSimilar(float[] queryEmbedding, int limit) {
return jdbc.queryForList("""
SELECT name FROM product
WHERE embedding IS NOT NULL
ORDER BY embedding <=> ?::vector
LIMIT ?
""", String.class, toLiteral(queryEmbedding), limit);
}
private static String toLiteral(float[] v) {
StringJoiner joiner = new StringJoiner(",", "[", "]");
for (float f : v) {
joiner.add(Float.toString(f));
}
return joiner.toString();
}
}The <=> operator computes cosine distance. ORDER BY ... LIMIT retrieves the nearest products. The query vector comes from the same model that generated product embeddings, e.g., when a user searches “clothes for rainy days”.
Because products and embeddings live in the same table, you combine filtering easily: WHERE category = 'outerwear' AND price < 500000 ORDER BY embedding <=> ?. You can also blend ts_rank scores with vector distance for hybrid search. With a separate vector database, you’d have to sync those attributes over to it.
Note: When combining a highly selective
WHEREwith an HNSW index, the returned results might be fewer thanLIMIT. The reason is that the index filters after scanning a limited candidate set. Test carefully with real data and review pgvector tuning parameters.
7. Writing Tests with Testcontainers
Tests run on real PostgreSQL with the pgvector image. @ServiceConnection auto-configures the datasource:
@SpringBootTest
@Testcontainers
class PostgresFeaturesTest {
@Container
@ServiceConnection
static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>(
DockerImageName.parse("pgvector/pgvector:pg16").asCompatibleSubstituteFor("postgres"));
@Autowired JobQueue jobQueue;
@Autowired ProductSearch productSearch;
@Autowired ProductVectorSearch vectorSearch;
@Autowired JdbcTemplate jdbc;
@Test
void processesEachJobExactlyOnce() {
jobQueue.enqueue("SEND_EMAIL", "{\"orderId\": 1}");
jobQueue.enqueue("SEND_EMAIL", "{\"orderId\": 2}");
List<Long> processed = new ArrayList<>();
while (jobQueue.processNext(job -> processed.add(job.id()))) { }
assertThat(processed).hasSize(2).doesNotHaveDuplicates();
assertThat(jdbc.queryForObject("SELECT count(*) FROM job_queue", Long.class)).isZero();
}
@Test
void failedJobStaysInQueue() {
jobQueue.enqueue("SEND_EMAIL", "{\"orderId\": 3}");
assertThatThrownBy(() -> jobQueue.processNext(job -> {
throw new IllegalStateException("SMTP down");
})).isInstanceOf(IllegalStateException.class);
assertThat(jdbc.queryForObject("SELECT count(*) FROM job_queue", Long.class)).isEqualTo(1);
}
@Test
void searchesByTextAndVector() {
jdbc.update("INSERT INTO product (name, description) VALUES ('Jacket', 'Waterproof')");
jdbc.update("INSERT INTO product (name, description) VALUES ('Jeans', 'Thick denim')");
long jacketId = jdbc.queryForObject("SELECT id FROM product WHERE name = 'Jacket'", Long.class);
long jeansId = jdbc.queryForObject("SELECT id FROM product WHERE name = 'Jeans'", Long.class);
vectorSearch.saveEmbedding(jacketId, new float[]{0.9f, 0.1f, 0.0f});
vectorSearch.saveEmbedding(jeansId, new float[]{0.0f, 0.2f, 0.9f});
assertThat(productSearch.search("jacket"))
.extracting(ProductHit::name)
.containsExactly("Jacket");
assertThat(vectorSearch.findSimilar(new float[]{1.0f, 0.0f, 0.0f}, 1))
.containsExactly("Jacket");
}
}The second test checks the most important queue behavior: when a handler fails, the job isn’t lost. Tests insert data right in each method. In a real project, clean tables in @BeforeEach so tests are independent.
8. When NOT to Use Postgres for Everything
Consolidating everything has costs. Consider specialized systems when:
- Queue throughput is very high or you need streaming. If you need to replay an event stream, have multiple independent consumer groups, or keep messages long-term, use Kafka or similar. A Postgres-based queue creates many dead tuples from constant
DELETE/UPDATE. You must monitor autovacuum closely. - Search is complex. Multi-dimensional faceting, typeahead suggestions, good language support, detailed relevance tuning: a search engine does this better.
- Vector volume is huge. HNSW indexes consume a lot of RAM. When vectors dominate your storage, they compete with your main transactional workload.
- Uneven load causes interference. A heavy vector query or a surge of jobs can slow down payments API. Read replicas help split read load, but don’t solve everything.
- Your team already runs a specialized system well. If the infrastructure exists and runs smoothly, moving everything to Postgres just to be “simpler” might not be worth it.
Tip: A sound strategy is to wrap each feature behind an interface (
JobQueue,ProductSearch, …). When monitoring shows Postgres is a bottleneck, swap the implementation underneath without touching business logic.
9. Conclusion
PostgreSQL can handle job queues thanks to FOR UPDATE SKIP LOCKED, full-text search with tsvector and GIN indexes, and vector search via the pgvector extension. The biggest win is fewer systems to operate and the ability to write business data, jobs, and embeddings in one transaction.
Think of this as a strong default for small-to-medium systems, not a permanent destination. Start with Postgres, measure carefully, and only split to specialized tools when the data demands it.
AI code review: how to use effectively and safely
Understand what AI code review is, integrate it into review processes and CI pipelines, hide secrets before sending diffs, prevent prompt injection, and know when not to use it.
Local-first software: offline apps and sync with CRDT
Learn about local-first software, why CRDTs let apps work offline and merge data automatically, with Java 21 and Spring Boot 3 examples.
Observability for LLM Applications: Tracking Prompts, Costs, and Quality
How to measure latency, tokens, costs, and response quality of LLM applications using Micrometer, OpenTelemetry, and Spring Boot 3.
Are Vector Databases Still Necessary With Longer Context Windows?
Longer context windows let you fit more documents in prompts, but they don't fully replace retrieval. This analysis shows when you need a vector database and when you don't.
Documentation beats memory: building repos for humans and AI
Coding agents forget context between sessions. This guide shows how to embed conventions in your repo using AGENTS.md, verification scripts, ArchUnit, and tests that both humans and AI agents can read.