PostgreSQL as a Schemaless Database
Speaker: | Chris Pettus |
Date: | 2013-02-22 |
Site: | thebuild.com |
Twitter: | @xof |
Basics
- Requires PostgreSQL 9.2
- NoSQL == Schemaless
- Documents vs. rows
- Persistent object storage
Types
- XML, JSON, hstore, relational
XML
- Documents up to 2GB
- Supports Xpath
- No indexing :(
hstore
- Heirarchial (docments link to documents)
- Key/value store (strongs or other hstore objects/values)
- GiST or GIN indexing on hstore values
JSON
- Validates JSON going in
- Indexable as text for strict comparison
- Uses PL/V8
- PL/V8 Pro-tips:
- Use V8 that comes w/ PL/V8
- Functions are not compiled by PL/V8 until first use
- JSON injection is possible, stay vigilant
- PL invocation is non-trivial (tangible overhead)
Comparison
- Side-by-side comparison of various types
- Stock 9.2.2 install
- Will compare against MongoDB
- No tuning on app or system
Strategy
- Single-field tables w/ single type
- Wrap queries in object-mapper
- Index the column
- Profit!
Conclusions
- Stock MongoDB is doesn’t seem to be more performant than PostgreSQL at
querying documents when using accessor functions
- Be realistic, test w/ real data
- Index your accessor functions!
- Avoid full-table scans
- Consider hstore (most flexible)
- GiST + GIN rock on high-entropy data (b-tree)
- GIN outperforms GiST as dataset grows