Postgresql performance other than basic indexes

Question

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?

you got to figure out, what you are actually looking for. defining indexes blindly is a very bad idea because an index can slow down insertions. som you really got to see, which queries you got and how to work on them. — Hans-Jürgen Schönig
– Hans-Jürgen Schönig, Commented Aug 11, 2015 at 11:01

Hans-Jürgen Schönig · Accepted Answer · 2015-08-11 11:47:43Z

1

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.

answered Aug 11, 2015 at 11:47

Hans-Jürgen Schönig

7616 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql performance other than basic indexes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related