Performance Optimizations in PostgreSQL for Web Application

Performance Optimizations in PostgreSQL for Web Application

PGConf 17

820e01609a9f8f0fc0d5167680625b93?s=128

Harisankar P S

March 03, 2017
Tweet

Transcript

  1. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Using Database to pull your application’s weight
  2. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    { “name” => "Harisankar P S", “email” => ”mailme@hsps.in”, “twitter” => "coderhs", “facebook" => "coderhs", “github” => “coderhs”, “linkedin” => “coderhs”, } What I do: I write Ruby code for a living
  3. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    One thing you notice about me is. I love Stickers!!
  4. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    So if you have stickers give them to me!! If you want stickers meet me after my talk.
  5. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    I work at Ruby on Rails dev shop https://redpanthers.co
  6. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    We are from KOCHI, Kerala
  7. PGConf India 2017 So let me tell a story

  8. PGConf India 2017 of a novice developer

  9. PGConf India 2017 who wrote the first line of code

  10. PGConf India 2017 for a web app that was meant

    to process a
  11. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Couple of 1000 rows of data, less than 10 users
  12. PGConf India 2017 but grew so BIG that it was

    process GB’s of data every hour.
  13. PGConf India 2017 Performance Optimisation in Postgres for Web Application

  14. PGConf India 2017 This talk is about all the things

    that I learned Which helped me Scale the application without having to spend a fortune in Hardware
  15. PGConf India 2017 So what are the tools that I

    work with
  16. PGConf India 2017 In my universe these are my tools

    called
  17. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Really awesome when they work together
  18. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Ruby is Captain America
  19. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Rails is Iron man
  20. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    PostgreSQL is the hulk
  21. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    The Hulk can smash, anything
  22. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    But we make him carry our suitcase
  23. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    This talk is about how we can offload couple of the jobs done by Rails to Database. You have a HULK then don’t feel scared to USE it.
  24. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Today we are going to talk about • Query Planner • Indexing • Attribute Preloading • Materialised Views • Generating JSON • Synchronous Commit
  25. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Query Planner
  26. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Database is a General Purpose Software
  27. PGConf India 2017 A database is not build for a

    single use case or industry.
  28. PGConf India 2017 Then how does it handle all the

    scenarios?
  29. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Truth is, it doesn’t!
  30. PGConf India 2017 DB doesn’t know what all scenarios its

    put under, its upto us to nudge and optimise it.
  31. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • SQL syntax is all about how the results should be • What you want in your result - SELECT id, name • Or some information about data like - SELECT average(price), max(price), min(price) Where is the decision on how the data should be fetch made.
  32. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Well thats what Query planner is all about.
  33. PGConf India 2017 Its the Brain of your DB

  34. PGConf India 2017 We need to understand how the system

    work before we can improve its performance
  35. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • A query plan is created by the DB before the query you gave is executed. • Plan is the cost of running the query. The DB chooses the one with the least cost. • Query Plan assumes the plan it has is the ideal one
  36. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    So we need to see what the query planner see Active Record has .explain method to help us there
  37. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Asset.where(asset_id: 1).explain User.where(id: 1).explain
  38. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    So we check the query plan find where we are slowing down and then fix them and make the plan choose the faster method.
  39. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    I have done all these in production =), so you don’t to feel scared to run this.
  40. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Sounds Simple Doesn't it =)
  41. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    So lets see how we can do that.
  42. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Tip: We can make the query plan display in JSON, YML & XML formats as well EXPLAIN (format YAML) select * from users
  43. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Indexing
  44. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Okay..Lets not do that.
  45. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Indexes are a special lookup table that the database search engine can use to speed up data retrieval.
  46. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    An Index is like a pointer to a particular row of a table. Where all the fields in the table are ordered.
  47. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    But you know something?
  48. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Databases are smart
  49. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Even if you have indexes if it find the sequential search to be cost less then it would go for that one.
  50. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Example: Lets say you have a column with a 10,000 rows, but the the content is either short, medium, long. The database don’t use index as it finds sequential is faster
  51. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    We should Index • Index Primary key • Index Foreign key • Index all columns you would be passing into where clause • Index the keys used to Join tables • Index the date column (if you are going to call it frequent, like rankings of a particular date) • Add partial index to scopes
  52. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Do not Index • Do not index tables with a lot of read, write • Do not index tables you know that will remain small, all through out its life time • Do not index columns where you will be manipulating lot of its values.
  53. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Attribute Preloading
  54. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    A good use of Postgres Array
  55. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Rails Way
  56. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    select * from tasks select * from tags inner join tasks_tags on tags.id = tasks_tags.tag_id where tasks_tags.task_id in (1,2,3,..) tasks = Task.find(:all, :include => :tags) 2 Queries Object for each tasks Rails Code
  57. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Fast Postgres Arrays: tasks = Task.find(:all, :select => "*, array(select tags.name from tags inner join tasks_tags on (tags.id = tasks_tags.tag_id) where tasks_tasks.task_id=tasks.id) as tag_names") 1 SQL query Rails doesn't have to create objects >3x faster
  58. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Materialised View
  59. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Database views? Database views are like the view in our rails. A rails view(an html page) shows data from multiple model in a single page Similarly we can show data from multiple table as a single table using the concept called views Why would we do that? Because it makes life easier
  60. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Instead of doing Every time you want the managers SELECT id, name, email FROM companies where role=‘manager’
  61. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    CREATE VIEW company_managers AS SELECT id, name, email FROM companies WHERE role='manager'; You can create a view And simple do SELECT * FROM company_managers;
  62. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Note: • A schema of view lives in memory of a DB • The result is not stored in memory • Its is actually running our query to get the results • They are called pseudo tables
  63. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Materialised views are the next evolution of Database views. We store the result as well in a table • This was first introduced by Oracle • But now found in PostgreSQL, MicrosoftSQL, IBM DB2, etc. • MySQL doesn’t have it you can create it using open source extensions.
  64. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    How can we use it in Ruby? Thanks to ActiveRecord its easy to access such pseudo tables
  65. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Create a migration to record the Materialised view We need a bit of SQL here class CreateAllTimesSalesMatView < ActiveRecord::Migration def up execute <<-SQL CREATE MATERIALIZED VIEW all_time_sales_mat_view AS SELECT sum(amount) as total_sale, DATE_TRUNC('day', invoice_adte) as date_of_sale FROM sales GROUP BY DATE_TRUNC('day', invoice_adte) SQL end def down execute("DROP MATERIALIZED VIEW IF EXISTS all_time_sales_view") end end
  66. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Create Active Record model I place these views at the location app/models/views class AllTimeSalesMatView < ActiveRecord::Base self.table_name = 'all_time_sales_mat_view' def readonly? true end def self.refresh ActiveRecord::Base.connection.execute('REFRESH MATERIALIZED VIEW CONCURRENTLY all_time_sales_mat_view') end end
  67. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Now we can do AllTimeSalesMatView.select(:name) AllTimeSalesMatView.where(email: 'hsps@redpanthers.co')
  68. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    First, Last and Find • They don’t work in your view as they operate on your tables primary key and a view doesn’t have it • If you want to use it then you need to one of the fields in your table as primary key class Model < ActiveRecord::Base self.primary_key = :id end
  69. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Benchmark • I created a table with 1 million random sales and random dates in a year. (Dates where bookmarked as well)
  70. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Take Away • Faster to fetch data. • Capture commonly used joins & filters. • Push data intensive processing from Ruby to Database. • Allow fast and live filtering of complex associations or calculation .fields. • We can index various fields in the table.
  71. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Pain Points • We will be using more RAM and Storage • Requires Postgres 9.3 for MatView • Requires Postgres 9.4 to refresh concurrently • Can’t have Live data • You can fix this by creating your own table and 
 updating it with the latest information
  72. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    JSON generation in DB
  73. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • Websites with simple HTML and plain javascript based AJAX is coming to an end • Its the era of new modern day JS frameworks • JSON is the glue that binds the fronted and our backend • So its natural to find more and more DB supporting the generation and storage of JSON.
  74. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    To convert a single row to JSON select row_to_json(users) from users where id = 1 we use row_to_json() method in SQL
  75. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    {“id":1,"email":"hsps@redpanthers.co", "encrypted_password":"iwillbecrazytodisplaythat", "reset_password_token":null,"reset_password_sent_at":null, "remember_created_at":"2016-11-06T08:39:47.983222", "sign_in_count": 11,"current_sign_in_at":"2016-11-18T11:47:01.946542", "last_sign_in_at":"2016-11-16T20:46:31.110257", "current_sign_in_ip":"::1","last_sign_in_ip":"::1", "created_at":"2016-11-06T08:38:46.193417", "updated_at":"2016-11-18T11:47:01.956152", "first_name":"Super","last_name":"Admin","role":3}
  76. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    But for more practical use we write queries like select row_to_json(results) from ( select id, email from users ) as results {"id":1,"email":"hsps@redpanthers.co"}
  77. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    A more complex one select row_to_json(result) from ( select id, email, ( select array_to_json(array_agg(row_to_json(user_projects))) from ( select id, name from projects where user_id=users.id order by created_at asc ) user_projects ) as projects from users where id = 1 ) result
  78. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    { “id":1,"email":"hsps@redpanthers.co", "project":["id": 3, "name": “CSnipp"] } We did data preloading as well, instead of having the need to run another query separate from the first one. We got the data about projects as well.
  79. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    json_build_object • Added in PostgreSQL 9.4 to make JSON creation a bit more simpler select json_build_object('foo',1,'bar',2); {"foo": 1, "bar": 2}
  80. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    So where is Ruby?
  81. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • For simple JSON creation you can use a gem called Surus • https://github.com/jackc/surus
  82. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Which lets you write code like User.find_json 1 User.find_json 1, columns: [:id, :name, :email] Post.find_json 1, include: :author User.find_json(user.id, include: {posts: {columns: [:id, :subject]}}) User.all_json User.where(admin: true).all_json User.all_json(columns: [:id, :name, :email], include: {posts: {columns: [:id, :subject]}}) Post.all_json(include: [:forum, :post])
  83. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    But for more complicated queries you might still end up writing SQL
  84. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    But Like me if you want to keep as much stuff as possible in Ruby then. Create a materialised view for your complicated query And then use the gem to generate JSON =)
  85. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Benchmarks • In our case we saw request to a (.json) url which used to take 2 seconds, coming down to <= 200ms • Some benchmarks I found online mentions
  86. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • Simple query • More complicate Query • Source: https://github.com/JackC/json_api_bench
  87. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Synchronous Commit
  88. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • PostgreSQL sacrifices speed for durability and reliability • PostgreSQL is known for its slow writes and faster readers • It has slow writes as it waits for confirmation that what we inserted has been recorded to the Hard Disk. • You can disable this confirmation check to speed up your inserts if you are inserting a lot of rows every second
  89. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    User.transaction do User.synchronous_commit false @user.save end Surus Gem Provides
  90. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • Only issue now, is incase your DB crash it can’t recover the lost data not saved to Hard Disk • It won’t corrupt the data, but you might loose some rows of your data • Not to be used in cases when you want data integrity to be 100% • Use it where you don’t mind loosing some information or where you can rebuild it from outside your DB. Like logs, or raw information.
  91. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Postgres Config that we use # How much memory we have to cache the database, RAM_FOR_DATABASE * 3/4 effective_cache_size = <%= ram_for_database.to_i * 3/4 %>MB # Shared memory to hold data in RAM, RAM_FOR_DATABASE/4 shared_buffers = <%= ram_for_database.to_i / 3 %>MB # Work memory for queries (RAM_FOR_DATABASE/max_connections) ROUND DOWN 2^x work_mem = <%= 2**(Math.log(ram_for_database.to_i / expected_max_active_connections.to_i)/Math.log(2)).floor %>MB # Memory for vacuum, autovacuum, index creation, RAM/16 ROUND DOWN 2^x maintenance_work_mem = <%= 2**(Math.log(ram_for_database.to_i / 16)/Math.log(2)).floor %>MB # To ensure that we don't lose data, always fsync after commit synchronous_commit = on
  92. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    # Size of WAL on disk, recommended setting: 16 checkpoint_segments = 16 # WAL memory buffer wal_buffers = 8MB # Ensure autovacuum is always turned on autovacuum = on # Set the number of concurrent disk I/O operations that PostgreSQL # expects can be executed simultaneously. effective_io_concurrency = 4
  93. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Summarize
  94. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    • Index data so that we don’t end up scanning the whole DB • Use arrays for data preloading • Simplify the way you fetch data from the DB using views • Move complicated JSON generation to the Databases • Disable synchronous commit when you feel like it won’t cause a problem
  95. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Conclusions • Know your tech stack • We should have control over all our moving parts • Try to bring about the best with your tech stack before you start throwing more money at it • SQL has been around for 40 years and its planning to say for a while longer =) • There is no golden rule. What worked for me might not work for your specific use case.
  96. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    I blogged about this in detail. • http://blog.redpanthers.co/materialized-views- caching-database-query/ • http://blog.redpanthers.co/create-json-response- using-postgresql-instead-rails/ • http://blog.redpanthers.co/different-types-index- postgresql/ • http://blog.redpanthers.co/optimising-postgresql- database-query-using-indexes/
  97. PGConf India 2017 Performance Optimisation in Postgres for Web Application

    Thank You. Harisankar P S, Chief Solution Artchitect/CEO, Red Panthers I blog about PostgreSQL and Ruby on Rails at http://blog.redpanthers.co