Specialized Databases

Time-series, search, vector and spatial databases: the special index each one is built around, how it works, and when PostgreSQL with the right extension is enough.

Overview

A general-purpose database such as PostgreSQL stores rows and finds them with B-tree indexes. That works for most queries, but some workloads ask questions a B-tree cannot answer well. Which documents contain these words, ranked by relevance? Which vectors are closest to this one? Which shops are within 2 km? What was the average CPU per host each minute for the last week?

Specialized databases are built around an index for one of those questions: a time-partitioned column store, an inverted index, a vector graph or a spatial tree. For their question they can be much faster and smaller than a row store with B-trees. The price is one more system to run, and one more copy of the data to keep in step.

In interviews the skill is judgment. PostgreSQL covers each of these workloads with an extension or a built-in feature (partitioning and TimescaleDB, full-text search, pgvector, PostGIS), and that is often enough. A dedicated system earns its place when the scale, the features or the query load go beyond that.

What you will be able to do

  • Explain the index behind each kind of specialized database and why it suits its queries.
  • Design a time-series table with time partitions, rollups and retention.
  • Explain how Elasticsearch indexes, refreshes and ranks documents.
  • Explain exact and approximate nearest-neighbor search, and the recall-speed trade-off of HNSW and IVF.
  • Choose between PostgreSQL with an extension and a dedicated system, and keep the copies in step with change data capture.

Key concepts

  • Time partitioning
  • Time-series compression
  • Inverted index
  • BM25 ranking
  • Near-real-time refresh
  • Embeddings and distance
  • HNSW and IVF
  • R-tree and GiST
  • Geohash and H3
  • Change data capture

Interview questions

  • How do time-series databases handle heavy writes and long retention?
  • How does Elasticsearch index a document and make it searchable?
  • Explain HNSW and the recall-speed trade-off.
  • Would you use pgvector or a dedicated vector database?
  • How would you find the restaurants within 2 km of a user?
  • How do you keep a search index or vector store in sync with the main database?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio