3 Awesome Things Postgres Can Do

I gave this as a lightning talk at Steel CIty Ruby Conference 2012.

PostgreSQL is a great database and can do more than just store relational data. It can store key-values, display pivot data, and has great full text search implementations.

Transcript

1. 3 Awesome Things That PostgreSQL Can Do
2. Where I use these awesome things
3. HStore • It’s a key-value column • It’s queryable • It’s in Rails 4 • You can use it now with activerecord- postgres-hstore
4. http://www.postgresql.org/docs/9.1/static/hstore.html
5. Trigram Matching • Breaks strings up into 3 character chunks • cat => [‘ c’, ‘ca’, ‘cat’, ‘at ‘] • Used as a human friendly search • Doesn’t rely on breaking out words so .coms in merchant names match • Helps with misspellings: ‘eharmoney’ find eHarmony
6. Trigram with ActiveRecord scope :search, ->(query) { where("merchants.name % ? or merchants.description % ?", query, query) }
7. tablefunc • normal_rand • Normally distributed random values (Gaussian distribution) • crosstab • Pivot displays • connectby • Displays hierarchical data
8. Disclaimer YMMV
9. Thanks! Jesse Dearing @jessedearing