CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Mikhail Chuprynski
Mikhail Chuprynski

Reputation: 2493

Postgresql performance other than basic indexes

I have plenty (10 blns per day) records is DB with the following structure:

class CreateRecords < ActiveRecord::Migration
  def change
    create_table :records, id: false do |t|

      t.column :client_ip, :inet
      t.integer :client_port

      t.column  :destination_ip, :inet
      t.integer :destination_port

      t.datetime :session_start
      t.datetime :session_end

      t.integer :bytes_sent
      t.integer :bytes_received

      t.string  :url
      t.string  :domain

    end
    add_index :records, :client_ip
    add_index :records, :destination_ip
    add_index :records, :domain
  end
end

I have basic indexes here, but I would like to go further and increase performance. Particularly I am interested in very basic queries (no joins or something) like this:

Record.where(client_ip: ip)
Record.where('domain like ?', "%#{domain}%")

What kind of solution should I implement next? Any parallel processes, special indexes, postgresql config other than default one?

Is there any native way in Rails to use Postgres partitioning?

Upvotes: 0

Views: 50

Answers (1)

Hans-J&#252;rgen Sch&#246;nig
Hans-J&#252;rgen Sch&#246;nig

Reputation: 741

for the LIKE part you can try a trigram index:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_whatever ON tab USING gist(domain gist_trgm_ops);

it should speed ip regular expressions and LIKE. PostgreSQL contrib packages are needed to do that.

Upvotes: 1

Related Questions