Deep into Postgres

SRUG, Gliwice, 11.04.2014

Michał Krzyżanowski

@krzyzak

Łukasz Strzebińczyk

@KillaPL

Use the right tool for the right job

MySQL

MongoDB

PostgreSQL

HStore

HStore

CREATE EXTENSION "hstore";
					

ALTER TABLE users ADD COLUMN settings hstore;
					

 INSERT INTO "users" values(1, 'admin=>"true",likes_count=>"9"');
					

 SELECT * FROM "users" WHERE settings->'admin' = 'true';
					

 add_column :users, :settings, :hstore
          

User.where("settings -> 'admin' = 'true'")
          

HStore – limitations

• Can’t nest objects
• Stores everything as text

JSON

JSON

ALTER TABLE users ADD COLUMN json_settings json;
					

UPDATE "users" SET json_settings =
 '{
   "likes_count": 9,
   "roles": ["moderator", "super-moderator"],
   "we": {"can": {"nest": "things"} }
  }
 ';
					

SELECT * FROM "users" WHERE
 json_extract_path(json_settings, 'we', 'can', 'nest') = 'things';
					

JSON objects

UPDATE "users" SET json_settings =
 '{
   "likes_count": 9,
   "roles": ["moderator", "super-moderator"],
   "we": {"can": {"nest": "things"} }
  }
 ';
					

SELECT * FROM "users" WHERE
 '"moderator"' IN(
  SELECT value::text FROM json_array_elements(json_settings->'roles')
 );
					

JSON – indexing

SELECT COUNT(*) FROM lodgings;
count
--------
 868922
          

EXPLAIN ANALYZE SELECT raw_data::json->'name' FROM lodgings
 WHERE raw_data::json->>'name' = 'Pousada do Pico';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on lodgings  (cost=0.00..49279.68 rows=1037 width=467) (actual time=0.509..19126.720 rows=1 loops=1)
   Filter: (((raw_data)::json ->> 'name'::text) = 'Pousada do Pico'::text)
   Rows Removed by Filter: 209338
 Total runtime: 19126.747 ms
(4 rows)
          

CREATE INDEX ON lodgings USING btree ( (raw_data::json->>'name') );
          

EXPLAIN ANALYZE SELECT raw_data::json->'name' FROM lodgings
 WHERE raw_data::json->>'name' = 'Pousada do Pico';
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using lodgings_expr_idx on lodgings  (cost=0.43..8.45 rows=1 width=458) (actual time=0.295..0.297 rows=1 loops=1)
   Index Cond: (((raw_data)::json ->> 'name'::text) = 'Pousada do Pico'::text)
 Total runtime: 0.327 ms
          

#to_json

• JBuilder

• Rabl

• ActiveModel::Serializers

• PostgreSQL

Postgresql 9.2

Postgresql 9.3

SELECT Array_to_json(COALESCE(Array_agg(Row_to_json(t)), '{}'))
FROM   (SELECT id,
               title,
               content,
               (SELECT Row_to_json(t)
                FROM   (SELECT id
                        FROM   "authors"
                        WHERE  ( "id" = "author_id" )) t) author
        FROM   "posts") t

gem "surus"

gem "active_model_serializers"

class AuthorSerializer < ActiveModel::Serializer
  attributes :id, :name
end

class PostSerializer < ActiveModel::Serializer
  attributes :id, :title, :content
  has_one :author
end

ActiveModel::ArraySerializer.new(
  Post.all,
  each_serializer: PostSerializer
  ).to_json

Post.all_json(
  columns: [:id, :title, :content],
  include: {
    author: {
      columns: [:id, :name]
    }
  }
  )

def render_json(jobs)
  json = jobs.all_json(
    columns: [
      :id, :system_id,
      :stage, :label,
      :description, :first_reference,
      :second_reference, :cached_plate_names,
      :priority, :notifications_bitmap,
      :updated_at, :hidden
      ],
    include: {
      end_customer: { columns: [:name] },
      client: { columns: [:id, :short_name] },
      print_supplier: { columns: [:short_name] }
      })
  render json: json
end

Full text search

Full text search

• Solr

• Elastic Search

• PostgreSQL

Postgresql 8.3

tsvector

select to_tsvector('Lorem ipsum dolor sit amet');
=> 'amet':5 'dolor':3 'ipsum':2 'lorem':1 'sit':4

tsquery

select to_tsquery('Lorem') || to_tsquery('ipsum');
=> 'lorem' | 'ipsum'

But does it have features?

create_table "posts", force: true do |t|
  t.text "content"
end
            

SELECT * FROM posts WHERE to_tsvector(content) @@
  to_tsquery('simple') && to_tsquery('query');

SELECT * FROM posts WHERE to_tsvector(content) @@
  to_tsquery('simple') || to_tsquery('query');

SELECT * FROM posts WHERE to_tsvector(content) @@
  to_tsquery('simple' || ':*') && to_tsquery('query' || ':*');

Trigram

CREATE EXTENSION "pg_trgm";

select show_trgm('Lorem Ipsum');
=> {"  i","  l"," ip"," lo","em ",ips,lor,ore,psu,rem,sum,"um "}

select similarity('java', 'javascript');
=> 0.333333

select similarity('ruby', 'php');
=> 0

SELECT * FROM posts WHERE content % 'simple query';

Weighting

• Scoping
• Disjunctions
• Conjunctions
• Restrictions
• Pagination
• Ordering
• Grouping
• Joins

• WHERE
• LIMIT
• OFFSET
• ORDER
• GROUP BY
• JOIN

Faceted search

• http://tech.pro/tutorial/1142/building-faceted-search-with-postgresql
• http://www.garysieling.com/blog/gin-vs-gist-for-faceted-search-with-postgres-full-text-indexes
• http://coffeecode.net/archives/270-A-Flask-of-full-text-search-in-PostgreSQL.html

But does it scale?

gem 'pg_search'

gem 'sunspot_rails'

gem 'sunspot_solr'

create_table "posts", force: true do |t|
  t.string   "title"
  t.text     "content"
end

class Post < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search_with_postgres,
    against: [:title, :content], using: :tsearch
end

class Post < ActiveRecord::Base
  searchable do
    text :title, :content
  end
end

Post.create do |post|
  post.title   = Faker::Lorem.sentence
  post.content = Faker::Lorem.sentence(30)
end

Post.search_with_postgres(Faker::Lorem.sentence(3)).limit(30).to_a

Post.search do
  fulltext Faker::Lorem.sentence(3)
end.results

Post.create do |post|
  post.title   = Faker::Lorem.sentence
  post.content = Faker::Lorem.sentence(5)
end

Post.search_with_postgres(Faker::Lorem.sentence(1)).limit(30).to_a

Post.search do
  fulltext Faker::Lorem.sentence(1)
end.results

Post.create do |post|
  post.title   = ""
  post.content = Faker::Name.name
end

Post.search_with_postgres(Faker::Name.first_name).limit(30).to_a

Post.search do
  fulltext Faker::Name.first_name
end.results

pg_search_scope :search,
  against: [:id, :system_id, :description, :first_reference, :second_reference],
  associated_against: {
    print_supplier: [:name],
    end_customer: [:name],
    client: [:name, :short_name]
  }
  using: {
    tsearch: { prefix: true }
  }

UUID as Primary key

						CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();
#=> cf4fadf7-22e0-4f46-bdda-232b7883a1c9
					

create_table('my_table_name', id: :uuid) do |t|
  t.string 'title'
end
					

User.create.attributes
# => {"id"=>"468cdfa4-1c11-4e34-a996-ecc561c0e866", (...) }
					

PostGIS

Spatial and Geographic Objects for PostgreSQL

PostGIS – gentle introduction

PostGIS – gentle introduction

PostGIS – gentle introduction

SELECT ST_Distance(
  ST_GeometryFromText('Point(-118.4079 33.9434)'),  -- Los Angeles
  ST_GeometryFromText('Point(2.5711 49.0039)'))     -- Paris
    AS geometry_distance,
ST_Distance(
  ST_GeographyFromText('Point(-118.4079 33.9434)'), -- Los Angeles
  ST_GeographyFromText('Point(2.5711 49.0039)'))    -- Paris
    AS geography_distance;

 geometry_distance | geography_distance
-------------------+--------------------
  121.912825827515 |   9125804.29716346
 				

Postgis – select POI within 2km

	ALTER TABLE pois ADD COLUMN location geography(Point,4326);
					

INSERT INTO pois(name, location)
 VALUES(
  "Sample",
  ST_MakePoint(52.228, 21.011), 4326)
 );
					

	SELECT * FROM pois WHERE
	 ST_DWithin(
	  location,
	  ST_MakePoint(52.218, 21.019), # desired point (Warsaw)
	  2000 # Meters
	 )
					

Postgis – shapes example

Postgis – shapes example

ALTER TABLE districts ADD COLUMN shape geography(MultiPolygon,4326);
						

INSERT INTO districts(name, shape)
 VALUES(
  "Silesian",
  ST_PolygonFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))')
 );
 					

SELECT name FROM districts WHERE
 ST_CONTAINS(
   shape,
   ST_MakePoint(52.218, 21.019)
 )
 					

Postgis – indexing

SELECT COUNT(*) FROM places;
  count
---------
 8738610
          

EXPLAIN ANALYZE SELECT pl.full_name
 FROM places pl
 WHERE
  ST_DWithin(
   pl.location,
   ST_GeomFromEWKT('SRID=4326;POINT(3.382491 50.609511)'), 1.0899
  );
                                                                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on places pl  (cost=577.43..47434.52 rows=789 width=25) (actual time=6.446..37.533 rows=16613 loops=1)
   Recheck Cond: (location && '0103000020E61000000100000005000000DCD440F33957024028D2FD9C82C24840DCD440F33957024038BBB54C86D94940EEB25F77BAE3114038BBB54C86D94940EEB25F77BAE3114028D2FD9C82C24840DCD440F33957024028D2FD9C82C24840'::geometry)
   Filter: (('0101000020E61000005C1D0071570F0B40B0C6D974044E4940'::geometry && st_expand(location, 1.0899::double precision)) AND _st_dwithin(location, '0101000020E61000005C1D0071570F0B40B0C6D974044E4940'::geometry, 1.0899::double precision))
   Rows Removed by Filter: 2582
   ->  Bitmap Index Scan on places_location_idx  (cost=0.00..577.23 rows=11842 width=0) (actual time=5.869..5.869 rows=19195 loops=1)
         Index Cond: (location && '0103000020E61000000100000005000000DCD440F33957024028D2FD9C82C24840DCD440F33957024038BBB54C86D94940EEB25F77BAE3114038BBB54C86D94940EEB25F77BAE3114028D2FD9C82C24840DCD440F33957024028D2FD9C82C24840'::geometry)
 Total runtime: 38.362 ms
          

But there’s more!

• ST_Area – Returns the area of the surface if it is a polygon or multi-polygon.
• ST_Centroid — Returns the geometric center of a geometry.
• ST_Touches — Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect.
• ST_ShortestLine — Returns the 2-dimensional shortest line between two geometries
• And many, many more…

Stored procedures...

Stored procedures...

CREATE OR REPLACE FUNCTION update_reason_for_document() RETURNS trigger as $$
      DECLARE
        change_reason varchar := '';
      BEGIN
        IF (OLD.name IS DISTINCT FROM NEW.name) THEN
          change_reason := change_reason || 'NAME;';
        END IF;

        IF (OLD.document_type IS DISTINCT FROM NEW.document_type) THEN
          change_reason := change_reason || 'DOCUMENT TYPE;';
        END IF;

        INSERT INTO documents_historical_reasons(document_id, change_reason)
          VALUES (NEW.document_id, change_reason);

        RETURN NEW;
      END;

      $$ LANGUAGE plpgsql;
      CREATE TRIGGER update_history
      BEFORE UPDATE OF document_type, name ON documents
      FOR EACH ROW
      WHEN (OLD.document_type IS DISTINCT FROM NEW.document_type
            OR OLD.name IS DISTINCT FROM NEW.name)
      EXECUTE PROCEDURE update_reason_for_document();
					

But...

Stored procedures – JavaScript

CREATE TABLE ratings (id int, count int, total int, average float);

CREATE FUNCTION add_rating(id int, rating int)
 RETURNS float AS
$$
 var data = plv8.execute("SELECT count, total, average FROM ratings
                          WHERE id = $1", [id])[0];
 var newTotal = data.total + rating;
 var newCount = data.count + 1;
 var newAverage = (newTotal / newCount);
 plv8.execute("UPDATE ratings SET count = $1, total = $2, average = $3
               WHERE id = $4",
 [newCount, newTotal, newAverage, id]);
 return newAverage;
$$
LANGUAGE plv8;
					

Stored procedures – JavaScript

INSERT INTO ratings VALUES (1, 0, 0, 0);
					

SELECT add_rating(1, 5);
 add_rating
------------
 5
 					

SELECT add_rating(1, 4);
 add_rating
------------
 4.5
 				

Thanks!

Questions?

Useful links:

• https://code.google.com/p/plv8js/
• http://www.postgresql.org/docs/current/static/hstore.html
• http://www.postgresql.org/docs/current/static/functions-json.html
• http://www.postgresql.org/docs/9.3/static/textsearch.html
• https://github.com/JackC/surus
• http://postgis.net/