How to leave my bank in postgres always tidy?

Question

How to leave my bank in postgres always tidy?

Navigation

#1 by (1 votes)

1

I'm doing a page search system where I'll always do the following query in the database:

'SELECT title FROM pages ORDER BY title ASC OFFSET' +iniciaL + 'FETCH FIRST 32 ROWS ONLY'

I choose an initial OFFSET and always get the next 32. But I think this sort of order will always cost some processing. I want to know if there is already a command in postgres that will let my table pages always be ordered and as soon as I perform this query: 'SELECT title FROM pages ASC OFFSET' +iniciaL + 'FETCH FIRST 32 ROWS ONLY' I have 100% guarantee that the results are already sorted (if I did not give INSERT is of course).

Is this effective knowing that my insertions only happen once a day, 12:00? or do I leave anyway?

(The page table will have about 5.5 millimeters of rows)

postgresql

asked by anonymous 06.03.2018 / 16:09

1 answer

How to get all the letters of the alphabet in Elixir? Difference between closures and functions

score 1 · Accepted Answer

Knowing that your update only happens once a day I strongly recommend creating a Materialized Views You can save in an orderly way and make the daily refresh after the update

example of the proper documentation:

 CREATE MATERIALIZED VIEW sales_summary AS
  SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date
    ORDER BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);