Every architecture discussion that starts with "should we use Postgres or Snowflake or Pinecone" has the question backwards.

The storage technology isn't the decision. The access pattern is the decision. Once you can articulate how data actually moves through your system - how fast reads need to be, how consistent writes need to be, how durable audit trails need to be, how flexibly analysts need to slice things - the storage choice mostly falls out. Skip that step and you end up with a lakehouse serving your recommendation engine at three-second P95 latency, or a specialized vector database holding six months of audit logs it was never designed to retain.

I've watched this pattern play out enough times to be confident about it: teams pick storage first, then design access patterns to fit. The order should be reversed.

The four access patterns in any AI product

If you inventory the actual data movement in a production AI system, you find four distinct patterns. Each has different requirements. Each punishes you differently when you get the storage wrong.

Synchronous serving is latency-critical. Someone typed a question; the model needs a recommendation, a retrieval result, or a feature vector back in under 100 milliseconds. The database has to support point lookups, small range scans, and (for RAG) similarity search, all under a strict deadline. Consistency requirements are moderate: you need the latest committed data, but not to-the-microsecond freshness. Read volume dominates writes.

Batch training is throughput-critical. Every night at 3am, a job needs to scan tens of millions of rows to refresh features, retrain models, or rebuild collaborative-filter matrices. It doesn't care about latency per row; it cares about total wall-clock time and cost-per-scan. It can tolerate slightly stale data as long as it's consistent within the batch.

Audit logging is durability-critical. Every LLM call, every consent event, every recommendation served needs to be written once and stay written. You'll never query most of these rows, but when a regulator asks which consent text a customer agreed to on March 14, you need the answer. Write volume can be enormous; read volume is negligible except during audits. Deletion is the enemy.

Ad-hoc analytics is flexibility-critical. A finance team wants cost-per-close broken down by vertical, model, and week. A product team wants retention curves segmented by feature adoption. Nobody knows what the query will be tomorrow. Latency tolerance is high (a thirty-second query is fine). Flexibility of the query language matters more than any single query being fast.

These four patterns overlap in your product but rarely in your database's sweet spot. That's the whole point.

One tool rarely fits four patterns. Two often do.

The temptation is to pick a single database and force everything through it. Postgres for everything. Or Snowflake for everything. Or Databricks for everything.

It never works cleanly, because the strengths that make a system good at one pattern actively hurt at another. Snowflake's columnar storage and distributed query planner are brilliant for analytics and terrible for point lookups. Postgres's row storage and MVCC are brilliant for serving and mediocre for scanning a hundred million rows. Pinecone's HNSW indexes are brilliant for pure vector similarity and clumsy when you need to join vectors with structured data.

The pattern that actually works in production is two tools, chosen deliberately:

One OLTP system (Postgres, typically) handles synchronous serving and audit logging. Both are row-oriented, point-lookup-friendly workloads. Postgres does them well and does them together, which is a real feature: your recommendation query and your audit-log write can share a transaction.
One analytical system (Snowflake, BigQuery, Databricks) handles batch training and ad-hoc analytics. Both are throughput-oriented, scan-heavy workloads. The analytical engine does them well and does them together.

The mirror between the two - CDC, reverse-ETL, whatever term you prefer - is plumbing, not architecture. It's the thing you build once, monitor forever, and don't overthink.

The pgvector vs. dedicated vector DB decision, framed correctly

Here's where the access-pattern framing gets concrete. Vector search is a real access pattern: similarity search on high-dimensional embeddings. But it's not a separate access pattern from serving. It's a specialized form of serving.

Which means the question isn't "should we run Postgres or Pinecone." The question is: does your serving workload benefit from having vectors and structured data in the same transaction?

For most B2B AI products, the answer is yes. When you fetch a recommendation, you don't just need the nearest vector, you need the customer's consent status, the tenant's active offers, the item's inventory count, the timestamp of the last interaction. Those live in Postgres. If your vector store is elsewhere, every recommendation becomes a distributed query with two failure modes and two latency budgets. If your vectors live in pgvector on the same database, it's one transaction, one connection pool, one consistency model.

At Billity AI and our partner DSC, we run behavioral embeddings in pgvector for exactly this reason. The recommendation engine's serving path pulls candidates, joins consent and inventory, applies tenant scoping, and returns ranked results, all inside a single Postgres query plan. Read latency lives in the tens of milliseconds. We haven't hit a scale where a dedicated vector DB would move that number.

That's the honest answer for most companies operating today. Millions of vectors, not billions. Mixed workloads, not pure vector similarity. The simpler stack wins.

The premature scale-out trap

The most expensive mistake in AI infrastructure isn't picking the wrong storage system. It's picking a storage system for scale you don't have and won't have for a year.

I've seen teams stand up Kafka before their ingestion rate justified a message queue. I've seen teams stand up Pinecone before their vector count justified a specialized index. I've seen teams stand up Snowflake before they had a single dashboard to power. Each decision felt defensible in isolation. Each was expensive, not in dollars primarily, but in engineering time spent operating infrastructure the product didn't need.

The pattern is always the same. The resume-driven-development version of the architecture looks impressive on a diagram. The version that actually ships and serves customers is boring. Kafka gets deferred until the measured ingestion rate genuinely stresses a Postgres LISTEN/NOTIFY or a scheduled batch job. Redis gets treated as optional graceful-degradation cache, not a hard dependency. Kafka becomes a real answer when you have real multi-consumer fan-out with real durability requirements, not because a whiteboard sketch had four boxes and one of them said "queue."

The discipline is asking, for every proposed piece of infrastructure: what specific workload will this handle in the next six months, and what does that workload look like today?

The counterpoint: when specialized vector DBs actually win

I don't want to overstate the case. There are workloads where pgvector is the wrong answer and a dedicated vector database (Pinecone, Qdrant, Weaviate) is the right one.

The clearest signal is scale. When you're indexing billions of vectors, not millions, the specialized indexes and dedicated infrastructure of a purpose-built vector DB start paying off. When your read pattern is pure vector similarity across the entire corpus with sub-fifty-millisecond requirements, and there's no meaningful join with structured data, the specialized tool wins.

The other signal is separation of concerns. If you have an engineering team dedicated to vector operations - tuning indexes, managing shards, optimizing recall - and that team is separate from the team managing your OLTP database, a dedicated system is genuinely easier to operate. That's a real organizational fit.

But most companies aren't at billions of vectors. Most companies don't have separate teams for vector infrastructure. Most companies have a small engineering team, a few million to tens of millions of vectors, and mixed workloads where vectors live next to structured data. For that shape, which is most B2B AI companies operating right now, pgvector isn't a compromise. It's the correct choice.

A decision matrix, and a closing thought

If you want a compact way to think about this, the storage decision reduces to three axes:

Read pattern: point lookup, similarity search, or full-table scan?
Write pattern: transactional, bulk-append, or streaming?
Scale: thousands, millions, billions, or trillions of rows?

For (point-lookup + transactional + millions): Postgres. Add pgvector if you need similarity search. Add Redis if you need graceful-degradation caching.

For (full-scan + bulk-append + billions): Snowflake, BigQuery, or Databricks.

For (similarity search + streaming + billions of vectors, no structured joins): a dedicated vector DB.

Almost every other question resolves once you place your workload in that grid.

The database is the easy part. The access pattern is where the work is. Do that work first, and the storage choice becomes obvious, often boring, often smaller than the industry conversation would suggest, often already sitting inside your existing Postgres instance waiting to be turned on.

Bryan Guy, J.D. is Founder and CEO of DataBillity, Inc. (Billity AI), a Seattle-based AI infrastructure company building multi-tenant, retrieval-first B2B and B2B2C products for regulated industries.

#DataArchitecture #DataEngineering #PostgreSQL #AIEngineering #MultiTenant #ModernDataStack

Stop Asking "What Database Should We Use." Start Asking "What's Our Access Pattern."