Design multi-format storage strategies on GCS and PostgreSQL

Introduction

When you extract content from PDFs, slide decks, and scanned reports, you end up with a mixed bag: text paragraphs, page images, tabular dumps, code blocks, and embedding vectors. Teams that funnel all of it into one store get burned in predictable ways — relational tables bloat under multi-megabyte images, object stores can't answer "find the top-5 most similar passages," and orphan references between the two surface as 404s in production retrieval. Picking the wrong storage strategy quietly inflates cost, slows every query, and makes document reconstruction unreliable.

By the end of this lesson you'll be able to design a two-tier storage strategy that places binary assets in GCS while keeping queryable metadata, text, and embeddings in PostgreSQL with pgvector, and you'll know how to keep referential integrity across both tiers.

Key Terminology

• Two-tier storage — binary assets (images, table exports, code files) live in GCS while their metadata, text, and embeddings live in PostgreSQL — referenced by gs:// URI. It matters here because no single store handles both blobs and vector search efficiently.
• Content block — a single typed unit extracted from a multi-format document (text, image, table, or code) carrying its page number, optional gcs_uri, and embedding vector. It is the unit at which you embed, retrieve, and reconstruct.
• pgvector embedding column — a fixed-dimension Vector(d) column on the content-block table that enables similarity search alongside relational filters in a single query.
• GCS lifecycle rules — bucket-scoped policies that automatically transition or delete objects by age or storage class; they let you age out cold extraction artifacts without custom cleanup jobs.
• Path prefix layout — a deliberate object-key naming scheme (content/images/..., content/tables/...) that lets lifecycle rules and IAM policies target each asset class independently.

Concepts

Why a two-tier split

Binary artifacts produced by extraction — page images, serialized tables, code blocks — are large, opaque to SQL, and read once per retrieval. Metadata and embeddings are small, structured, and queried hot. Putting binaries in PostgreSQL bloats the table and slows every index scan; putting metadata in GCS makes filtering and joining impossible. The split puts each class where it performs well: GCS for cheap durable blob storage, PostgreSQL for transactional metadata and vector search.

Path-prefix layout in GCS

The object-key prefix is the only structural handle GCS gives you. Group by content type (content/images/, content/tables/, content/code/) rather than by document, so per-type lifecycle rules (e.g., "delete intermediate table JSON after 30 days") and per-type IAM grants can be applied without affecting other classes. The full gs:// URI returned at upload is what you persist into PostgreSQL to bridge the two tiers (see Code Walkthrough).

Content blocks and embeddings in PostgreSQL

Every extracted unit gets one row: its type, its inline text (if textual), the gcs_uri (if binary), its page, and its embedding vector. With pgvector, a single SQL query can filter by document, page range, or block type AND rank by embedding distance — no cross-system joins. Document reconstruction is then an ordered SELECT by document_id (see Code Walkthrough).

Code Walkthrough

Now that you have seen the concepts above, the walkthrough below turns them into working code.

The two snippets below demonstrate the split named in Concepts: first the GCS uploader that enforces the path-prefix layout, then the pgvector-backed schema with a reconstructor that joins blocks by document.

Configuring GCS Storage

The MultiFormatStore class organizes uploads into content-type-specific path prefixes so that GCS lifecycle rules, IAM policies, and listing operations can target each content type independently. Each stored object returns its full gs:// URI, which is recorded in PostgreSQL metadata to link binary assets back to their parent document and page.

1from google.cloud import storage
2
3class MultiFormatStore:
4    def __init__(self, bucket_name: str):
5        self.client = storage.Client()
6        self.bucket = self.client.bucket(bucket_name)
7
8    def store_image(self, doc_id: str, image_bytes: bytes, page: int) -> str:
9        path = f"content/images/{doc_id}/page_{page:04d}.png"
10        blob = self.bucket.blob(path)
11        blob.upload_from_string(image_bytes, content_type="image/png")
12        return f"gs://{self.bucket.name}/{path}"
13
14    def store_table(self, doc_id: str, table_json: str, table_idx: int) -> str:
15        path = f"content/tables/{doc_id}/table_{table_idx:04d}.json"
16        blob = self.bucket.blob(path)
17        blob.upload_from_string(table_json, content_type="application/json")
18        return f"gs://{self.bucket.name}/{path}"
19
20    def store_code(self, doc_id: str, code: str, language: str, block_idx: int) -> str:
21        ext = {"python": "py", "javascript": "js", "sql": "sql"}.get(language, "txt")
22        path = f"content/code/{doc_id}/block_{block_idx:04d}.{ext}"
23        blob = self.bucket.blob(path)
24        blob.upload_from_string(code, content_type="text/plain")
25        return f"gs://{self.bucket.name}/{path}"

PostgreSQL Schema with pgvector

The schema below uses SQLAlchemy with pgvector to define a ContentBlock table that stores 1024-dimensional embeddings alongside metadata, enabling both similarity search and relational queries in a single database. The DocumentReconstructor then joins these blocks by document ID to reassemble the full multi-format document from distributed storage.

1from sqlalchemy import Column, String, Integer, Text
2from sqlalchemy.orm import declarative_base, Session
3from pgvector.sqlalchemy import Vector
4
5Base = declarative_base()
6
7class ContentBlock(Base):
8    __tablename__ = "content_blocks"
9
10    block_id = Column(String(16), primary_key=True)
11    document_id = Column(String(16), nullable=False)
12    block_type = Column(String(20), nullable=False)  # text, image, table, code
13    content_text = Column(Text, nullable=True)
14    gcs_uri = Column(String(2048), nullable=True)
15    page_number = Column(Integer, nullable=True)
16    language = Column(String(20), nullable=True)
17    embedding = Column(Vector(1024), nullable=True)
18
19class DocumentReconstructor:
20    def __init__(self, engine, store: MultiFormatStore):
21        self.engine = engine
22        self.store = store
23
24    def get_full_document(self, document_id: str) -> list[dict]:
25        with Session(self.engine) as session:
26            blocks = session.query(ContentBlock).filter_by(
27                document_id=document_id
28            ).order_by(ContentBlock.page_number).all()
29
30            result = []
31            for block in blocks:
32                entry = {
33                    "block_type": block.block_type,
34                    "page": block.page_number,
35                    "text": block.content_text,
36                }
37                if block.gcs_uri:
38                    entry["binary_uri"] = block.gcs_uri
39                result.append(entry)
40            return result

You'll know it works when a single document_id lookup returns an ordered list mixing text content and gs:// URIs, and a similarity search against embedding returns blocks from any content type without a separate vector store.

Do's and Don'ts

Now that you have worked through the implementation, the practices below separate a durable approach from a fragile one.

Do's

1. ✓Do partition GCS uploads by content type — content/images/, content/tables/, content/code/ lets lifecycle rules and IAM target each format independently.
2. ✓Do persist the full gs:// URI on the ContentBlock row — reconstruction never has to guess the bucket or path layout.
3. ✓Do co-locate embeddings with metadata in PostgreSQL — pgvector keeps similarity search in the same transaction as your filters, removing a class of cross-store consistency bugs.

Don'ts

1. ✗Don't inline large binary assets in PostgreSQL — page images and table exports as bytes drag binary payloads through every scan and inflate backup size.
2. ✗Don't bypass the path-prefix convention for one-off uploads — ad-hoc keys defeat lifecycle and IAM targeting and quietly leak storage cost.
3. ✗Don't let GCS objects and PostgreSQL rows drift — write the row only after the upload succeeds, and delete the row before deleting the object, so orphans are impossible by construction.