As it turns out, PostgreSQL has a number of ways of storing loosely structured data — documents — in a column on a table.
[hstore](http://www.postgresql.org/docs/current/static/hstore.html)is a data type available as a contrib package that allows you to store key/value structures just like a dictionary or hash.- You can store data in JSON format on a
textfield, and then use PLV8 toJSON.parse()it right in the database. - There is a native
xmldata type, along with a few interesting query functions that allow you to extract and operate on data that sits deep in an XML structure.
XML Storage is the topic of this post.
The XML data type has been
Postgres core since version 8.3 (and as part of a contrib package prior to
that). Documents that span more than one data block will be
TOASTed as
usual, so storing big documents should not be a problem. In essence, it is a
text data type, but it does bring a few features to the table.
For starters, it will perform basic balance integrity checks on the data. After all, this is Postgres, where data integrity is high up in the priority.
Postgres also supports a number of utility functions for extracting data from XML data. Most notably, you can use XPath expressions to extract portions of the XML document. You can use the extracted data in queries as you would anything else. For example:
xml_test=# CREATE TABLE beers(
id serial primary key,
data xml
)
[...]
CREATE TABLE
xml_test=# \d beers
Table "public.beers"
Column | Type | Modifiers
--------+---------+----------------------------------------------------
id | integer | not null default nextval('beers_id_seq'::regclass)
data | xml |
Indexes:
"beers_pkey" PRIMARY KEY, btree (id)
xml_test=# INSERT INTO beers (data) values ('<beer><name>Harpoon</name><location>Boston, USA</location></beer>');
INSERT 0 1
xml_test=# INSERT INTO beers (data) values ('<beer><name>Guinness</name><location>Dublin, Ireland</location></beer>');
INSERT 0 1
xml_test=# INSERT INTO beers (data) values ('<beer><name>Polar</name><location>Caracas, Venezuela</location><type>Pilsner</type></beer>');
INSERT 0 1
xml_test=# select * from beers;
id | data
----+--------------------------------------------------------------------------------------------
1 | <beer><name>Harpoon</name><location>Boston, USA</location></beer>
2 | <beer><name>Guinness</name><location>Dublin, Ireland</location></beer>
3 | <beer><name>Polar</name><location>Caracas, Venezuela</location><type>Pilsner</type></beer>
(3 rows)
We’ve basically created a beers table with three entries containing some XML data. You could use that data in a number of ways, including bringing it into your application, deserializing and parsing it there. This may be acceptable in many cases, especially if you have also included foreign keys to other relations in your data model, or any other data that you can use to filter data down. However Postgres offers the ability to use XPath to extract data from the XML type directly:
xml_test=# SELECT xpath('//beer/name', data) from beers;
xpath
------------------------
{<name>Harpoon</name>}
{<name>Guinness</name>}
{<name>Polar</name>}
(3 rows)
The curly braces in Postgres indicate an array, so the xpath function returns an array of elements that match your query. You can index the first element of that array with square bracket notation:
xml_test=# SELECT (xpath('//beer/name', data))[1]::text from beers;
xpath
----------------------
<name>Harpoon</name>
<name>Guinness</name>
<name>Polar</name>
(3 rows)
And further, use the XPath text() function to extract the text within the
matched XML nodes:
xml_test=# SELECT (xpath('//beer/name/text()', data))[1]::text from beers;
xpath
---------
Harpoon
Guinness
Polar
(3 rows)
This is useful for extracting data from your XML. Now let’s use it to query for beers in Venezuela:
xml_test=# SELECT * from beers where (xpath('//beer/location/text()', data))[1]::text = 'Caracas, Venezuela'::text;
id | data
----+--------------------------------------------------------------------------------------------
3 | <beer><name>Polar</name><location>Caracas, Venezuela</location><type>Pilsner</type></beer>
(1 row)
Now we’re getting somewhere. With a bit of creativity we can create a generic finder for your ORM of choice that finds records by XPath expression. Here’s a simple one for ActiveRecord
class Beer < ActiveRecord::Base
def self.by_xpath(xpaths)
xpaths.inject(scoped) do |s, (xpath, value)|
s.where("(xpath(?, data))[1]::text = ?", xpath, value)
end
end
end
Use it:
Beer.by_xpath('//beer/location' => 'Caracas, Venezuela', '//beer/name' => 'Polar')
You may be thinking this can’t possibly be fast to query. Postgres doesn’t allow
you to add any indexes on the XML data type itself, but on the other hand any SQL expression can be used in an index.
Therefore you can choose to index part of the XML document using the xpath function:
xml_test=# create index index_beers_on_location ON beers USING GIN ( CAST(xpath('//beer/location/text()', data) as TEXT[]) );
CREATE INDEX
Because we’re indexing an array value, we use a Generalized Inverted index (GIN).
When should I use this
Some may argue that it does not make any sense to store XML data directly in Postgres, after all it is a relational database and there are databases like CouchDB or MongoDB that are designed to store document data. Storing it in your main data store has its advantages though:
- You don’t need to maintain a new database in your infrastructure. Any service that is required to run your app adds complexity that is not always justified.
- XML data backups continue to occur as part of your main database backups.
- The XML data can be used to reference and JOIN other data in your data model.
Anecdotically, I recently used this strategy for storing raw XML data that we are receiving in the background from an API. We parse it, normalize some of it out to our own data model, but keep the raw XML around. This is useful because we can easily refer back to the source data to verify things when they seem off, or to extract and normalize more data out of it. However we don’t use it to display data back to the user or for reporting — we just keep it around and refer back to it from the backend. It’s working great!
