Exploring the Power of Postgres for Document Storage: A Viewer’s Perspective


Reading Time: 14 minutes

How about more insights? Check out the video on this topic.


Hey there, fellow tech enthusiasts! Today, I’m super excited to dive into a fascinating world that many of us might not have associated with Postgres before—document storage. I recently watched Bruce Momjian’s presentation on “Flexible Document Storage with PostgreSQL,” and trust me, it’s a game-changer.

Bruce kicks off his talk by highlighting the incredible journey of Postgres over the past decade or so. It’s been on a meteoric rise, thanks in no small part to its remarkable ability to handle document storage. The way he tailored this presentation for the Document Database Community was a breath of fresh air.

As someone who’s been knee-deep in the tech world for a while, I couldn’t help but appreciate Bruce’s credentials. With 27 years of experience, working for EnterpriseDB and curating a treasure trove of Postgres presentations and blog entries, he’s a true authority on the subject.

Bruce’s presentation was a unique blend of two separate discussions available on his website. The first part focused on non-relational Postgres, while the second delved into the intriguing world of flexible indexing with Postgres. The goal? To give us a crystal-clear understanding of just how capable Postgres is when it comes to handling documents. Sure, most of us know Postgres as a relational powerhouse, but as Bruce would show, it’s got some serious tricks up its sleeve for non-relational data too.

The presentation was divided into three main sections, and I’ll take you through them, offering my insights along the way.

Relational Limitations

Bruce started by shedding light on the limitations of traditional relational systems. These systems, which have been the backbone of tasks like order entry and customer tracking, aren’t always the best fit for today’s diverse workloads. Bruce delved deep into the specifics of where these relational systems fall short.

Now, don’t get me wrong; relational databases have been around for more than half a century and have proved incredibly resilient. Their ability to analyze data in myriad ways is undeniably impressive. But they’re not without their flaws.

Relational systems store data in tables with rows and columns, enforcing constraints and often requiring normalization and joins. While this approach is excellent for certain tasks, it doesn’t always play nicely with documents. Bruce highlighted how normalization, a term synonymous with relational databases, doesn’t always fit well with documents. It can lead to performance issues, inflexible storage, complex queries, and indexing limitations.

However, Postgres is a different beast altogether. It’s not bound by the conventions of traditional relational databases. Thanks to its inherent flexibility, it can handle both relational and non-relational tasks with ease. This adaptability is a result of its initial design back in 1986 as an extendable system, a design philosophy that still empowers Postgres today.

Document-Style Data Types

The next four sections of the presentation were all about the document-style data types that Postgres supports. Bruce took us on a journey through XML, often considered the pioneer of document databases despite its age in technology. He then delved into JSON and JSONB and even explored character strings. What became apparent was Postgres’ powerful capabilities in these areas.

What fascinated me the most was how seamlessly Postgres handles these document-oriented tasks. XML, JSON, and JSONB—Postgres can work with them like a pro. It’s like watching a seasoned chef whip up a gourmet meal; Postgres knows how to make these data types sing.

Specialized Indexing for Documents

In the final two sections, Bruce delved into a topic that might not have crossed your mind initially. To enable document storage in a relational system like Postgres, standard B-tree, and hash indexes might not cut it. You need specialized indexing tailored for non-relational or document-heavy requirements. And guess what? Postgres has us covered here too.

Bruce elaborated on this crucial aspect during the last section of the presentation, and it was a revelation. Postgres doesn’t just store your documents; it helps you find them efficiently with the right indexing in place. It’s like having a meticulously organized library where you can instantly locate any book you want.

In conclusion, Bruce’s presentation opened my eyes to the incredible capabilities of Postgres in the realm of document storage. It’s not just a relational powerhouse; it’s a Swiss Army knife that can adapt to a multitude of tasks. If you’re dealing with document storage, whether you’re a seasoned developer or just curious about the possibilities, Postgres deserves a closer look.

In the next part, I’ll share Bruce’s concluding thoughts and wrap up this enlightening journey into flexible document storage with PostgreSQL. Read below!

Exploring Postgres’ JSON and XML Capabilities

Now that we’ve laid the groundwork for understanding the incredible versatility of Postgres, let’s dive right into the nitty-gritty of how it handles XML and JSON data. Trust me; this is where the magic happens.

XML: A Brief Glimpse

Okay, let’s start with XML. I know it might not be everyone’s cup of tea, but bear with me; it’s worth knowing what Postgres can do with it. Bruce kicked things off by showcasing how Postgres handles XML data.

He began with an example of loading XML data from a website and using XPath queries in Postgres. XPath, for the uninitiated, is like a GPS for XML documents, letting you navigate and extract specific parts efficiently.

XPath query

What’s remarkable here is how Postgres can wear many hats when it comes to XML. It can convert data to arrays, index arrays within XML, convert data to text, and even run SQL queries on XML data. Plus, it lets you perform non-root queries, which means you can search within the document without starting from the top. And here’s the kicker—you can convert the document into a relational format. Imagine the possibilities!

Bruce also gave us a sneak peek into another cool trick—performing a string search within the XML document.

search XML text

In a nutshell, Postgres provides robust support for XML, aligning with many of the SQL standard XML formats. While we won’t dwell on XML extensively, it’s worth knowing that Postgres is a powerhouse when it comes to handling XML data.

JSON Data Type: The Modern Choice

Now, let’s shift gears and talk about JSON, the modern-day superstar of document storage in relational databases. Postgres offers two flavors of JSON, and we’ll start with the standard JSON type. It’s essentially a text field that isn’t processed in any particular way, but don’t let that fool you. Postgres equips it with a robust set of support functions for wrangling JSON data.

Bruce walked us through an example where a document from a website was loaded into a JSON column, and then the real fun began. 

load JSON data

We saw how you can convert the JSON document into a more human-readable format, access specific keys within the document, and even concatenate multiple JSON fields together. But it doesn’t stop there; you can also compare fields to specific values, either directly or by using the contains operator with a key-value combination in JSON format. Impressive, right?

JSON calculations

Now, while you can create indexes on JSON documents, there’s a star waiting in the wings—JSONB, which we’ll explore shortly. JSONB offers more efficient indexing capabilities, but standard JSON is no slouch either.

JSONB: The Game-Changer

Finally, let’s focus on JSONB, the real game-changer that’s catapulted Postgres to new heights. JSONB is like JSON’s cooler sibling. It’s similar in many ways, but it comes with some serious advantages.

JSONB isn’t just stored as plain text; it’s processed in a much more efficient manner. The keys and values are separated, sorted, and compressed, resulting in a highly efficient format. This not only makes it faster but also means it plays nicely with JavaScript data types like numbers, Booleans, and nested objects. For handling complex documents, JSONB is the weapon of choice.

But where JSONB truly shines is in indexing. It allows for lightning-fast lookups of keys within JSONB documents. In contrast, the standard JSON data type, being stored as text, has to parse the entire document to find a specific key, which can slow things down.

However, there’s a trade-off; JSONB doesn’t preserve key order, whitespace, or duplicates. Its preprocessing might tweak the document subtly, depending on how it’s constructed.

JSONB index

Using JSONB for performance reasons is pretty standard practice, but it also offers indexing flexibility. The GIN (Generalized Inverted Index) index, for instance, is a game-changer when it comes to efficient searching and querying in JSONB fields.

JSON index queries 1
JSON index queries
JSON index queries 3

So there you have it, folks—an eye-opening journey into Postgres’ prowess with XML and JSON data. It’s like having a Swiss Army knife for handling documents, whether they’re XML or JSON. The next time you’re dealing with document storage, remember, Postgres has your back with its powerful functionalities and indexing wizardry. Let’s stay curious!

Unleashing the Power of Character Strings in Postgres

To resume today’s discourse, I wanted to revisit some takeaways from the thoughtful presentation we analyzed in the previous session where we’ve covered XML and JSON, but now it’s time to dive into something seemingly simple yet incredibly crucial—character strings. They are the unsung heroes when it comes to document processing, and Postgres knows how to make the most of them.

Character Strings: The Foundation

Character strings are like the building blocks of documents. Whether you’re dealing with plain text, XML, JSON, or any other format, Postgres has your back when it comes to handling character strings efficiently.

When you’re dealing with a text field, Postgres offers various ways to view and process the data. You can consider it as a whole string, break it down into individual words, or even dissect it letter by letter. This approach influences how you can optimize operations on character strings, and Bruce Momjian took us on a tour of these capabilities.

Example Time: Text Processing

In one of the examples, Bruce showed us how to load the “fortune” file from FreeBSD into Postgres and work some magic with it. We witnessed case-insensitive searches in action, where both lowercase and uppercase queries produced results, showcasing Postgres’ versatility.

case folding & prefix
Indexed String Prefix

But that’s not all. We also explored string prefix searches, a common operation when you’re dealing with character strings. For super-efficient indexing, Postgres used the textpattern_ops index type, which significantly improved the performance of these prefix searches.

Going Full Text: The Power of Full-Text Search

Postgres doesn’t stop at basic text operations; it takes things up a notch with full-text search. This feature breaks down words into their base forms (stemming) and supports various search operations like AND, OR, and NOT searches, prefix searches, handling stop words, and even understanding synonyms. It’s like having your very own search engine within Postgres!

 To enable this powerful full-text search capability, Bruce demonstrated the creation of a GIN index on the text field using to_tsvector. This index unleashes the kind of search capabilities you’d typically find in dedicated tools like Solr or Elasticsearch.

Going Even Deeper: Advanced Searches

But wait, there’s more! For those times when you need to perform truly advanced searches, Postgres has you covered with the PG trigram extension. This nifty tool allows you to perform partial word searches and similarity searches, which can be a game-changer for certain document processing tasks.

Creating a GIN index on the trigram field optimizes the performance of these specific searches, making them lightning-fast and incredibly efficient.

Summing It Up: The Power of Indexes

So, to summarize, throughout this presentation, we’ve seen a variety of indexes at work, each tailored to specific character string processing needs:

    1. Uppercase/lowercase case-insensitive searches: Standard B-tree index.
    2. Full-text search: GIN index with to_tsvector.
    3. String prefix searches: textpattern_ops index on the text field.
    4. Partial word searches: PG trigram extension with GIN index on the trigram field.
    5. Similarity searches: PG trigram extension with GIN index for similarity searches.

These indexes are the unsung heroes of character string processing in Postgres. They’re the gears and levers that make complex document handling a breeze. So, whether you’re dealing with plain text, complex documents, or anything in between, Postgres has the tools and indexes to make your life easier. Keep exploring the possibilities!

Unleashing the Power of Non-B-Tree Index Types in Postgres

As we’ve already dived deep into the world of Postgres and its impressive capabilities with XML, JSON, and character strings, now, it’s time to unveil another set of superheroes in the Postgres universe—non-B-tree index types.

The Need for Non-B-Tree Index Types

Now, let’s talk about B-tree and hash indexes. These are your go-to heroes in the world of relational databases. They’re efficient at handling unique values and are pretty nifty at their job. However, they might not be the best fit for documents, and here’s why.

Documents, especially those in formats like text, JSON, or arrays, often contain a fair amount of duplication. B-tree indexes excel at dealing with atomic data—think of individual values in columns. But for documents, we need to explore what’s inside them and index the elements within the field itself. This is where B-tree indexes lose their shine.

Enter the GIN index, short for Generalized Inverted Index. Think of it as the rebel hero that flips the B-tree on its head. Instead of having a key and a single value, GIN has a key and multiple values that correspond to matching entries. This makes GIN an ideal choice for handling duplicates and multi-row matches efficiently. What’s more, GIN indexes store the key only once, optimizing space usage compared to B-tree indexes.

Postgres does have some tricks up its sleeve for handling duplicates in B-tree indexes, but GIN outperforms B-tree when it comes to these scenarios. It brings batch mode updates, compression, multi-key filtering, and even index support for distance calculations to the table. But GIN isn’t the only non-B-tree index type in Postgres.

The Lineup of Non-B-Tree Index Types

Postgres has several non-B-tree index types that have become an integral part of its arsenal. They are standard index types with extensive support, and here are the primary ones:

    1. GIN: Ideal for efficiently handling duplicates and multi-row matches.
    2. GIST (Generalized Search Tree): Used for full-text search and data warehouse applications. For example, the PG trigram extension uses GIST.
    3. SP-GiST: Designed to efficiently index prefixes.
    4. GIN+GiST: Reserved for specialized use cases not covered by the other index types.

Data Type Support for Index Types

Now, let’s take a peek at which data types each of these non-B-tree index types supports.

GIN: This index type offers efficient indexing capabilities for arrays, JSONB, and TS vectors. JSONB, in particular, comes with two types of operations: JSON path ops, which is faster for full path searches, and regular JSONB indexing.

GIST: While it primarily caters to geometric types like circle, box, point, and polygon, GIST also supports full-text search and JSONB data types. It’s versatile, but its heart lies with geometric data.

Examples in Action

Okay, enough theory; let’s see these heroes in action. With TS vectors and GIN, you can perform full-text searches efficiently. For instance, searching for the words “the fox is sick” would result in index entries for “the,” “fox,” “is,” and “sick.”

GIN example
GIN example

And then, there’s JSONB. GIN creates index entries for both keys and values. In an example, you’d find entries for keys like “active” and “name” and values like “true,” “Bill,” and “Jack.” But there’s another trick up JSONB’s sleeve—the JSONB path ops. Instead of storing individual keys and values, it stores a hash of the entire document, allowing for efficient lookups of nested documents based on their paths.

GIN example using JSON

SP-GiST: The Prefix Genius

Last but not least, we have SP-GiST, also known as the Space-Partitioned Generalized Search Tree. This index type excels at handling long strings with many duplications. It indexes common prefixes among the fields in the document, making it perfect for scenarios where you need to search for strings that begin with specific prefixes—like URLs.


And there you have it, folks—a whirlwind tour of Postgres’ non-B-tree index types and their superpowers. These specialized capabilities for different data types are what make Postgres a versatile and powerful database system. It can efficiently handle an array of use cases and data structures, making it a true superhero in the tech world. So, as always, stay curious and keep exploring the endless possibilities that Postgres offers!

Closing Thoughts: PostgreSQL’s Remarkable Versatility

Dear fellow explorers and data enthusiasts, as we wrap up our journey through the world of PostgreSQL, let’s reflect briefly.

We began by acknowledging that relational databases, while reliable, don’t always meet the demands of today’s diverse data landscape. PostgreSQL emerged as our versatile ally.

We delved into text document processing, spotlighting JSON, JSONB, and XML, witnessing PostgreSQL’s finesse in handling them—much like a maestro crafting symphonies from intricate notes.

Our climax arrived when we uncovered the unsung heroes—indexes. B-tree indexing had its place, but the GIN index, especially Jin, proved to be the game-changer, catapulting PostgreSQL to compete with specialized databases.

As we bid adieu, let’s express our gratitude to Bruce Momjian for guiding us. PostgreSQL’s adaptability, optimization, and conquest across diverse data types and use cases stand as a testament to its enduring relevance.

In conclusion, PostgreSQL isn’t just a database; it’s a beacon of versatility. Let’s continue our journey with curiosity, embracing the power of PostgreSQL’s adaptability and exploration. 


Related posts

Subscribe to Updates

Privacy Policy