How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)?

My app records data from a data feed. Another process endlessly queries to get the latest freshest entry. Older data may appear on occasion from secondary sources. So the most recently inserted rows are usually, but not necessarily, the freshest data.

I am using this kind of SQL where when_ is a TIMESTAMP WITH TIME ZONE column:

FROM my_table_ 

This code works (if no NULL values in data!). But with a possible couple million rows, and a query every 10 seconds, I'm concerned about performance.

Without any index on when_ column, does this statement require a full scan of all rows?

Does adding an index change the performance? Will Postgres automatically scan the index to locate the most recent row, or must I do something to make an index scan happen?

With an index on when_ column, should I change this SQL to use some other approach/strategy of query?

Is there some other way to gather freshly inserted rows? I'm using UUID rather than SERIAL types for my primary key, and may federate data amongst multiple database instances, so that rules out checking for ever-increasing integer numbers.

  • SELECT *? Do you really need all columns from the row? Or just a small subset? The table definition would be relevant to see width of the row and constraints and types of involved columns. – Erwin Brandstetter Jul 28 '15 at 22:34
  • @ErwinBrandstetter The table has a few TIMESTAMP WITH TIME ZONE fields, a couple UUID, and a couple of TEXT fields with 1K to 20K of HTML, CSV, or XML text. I don’t think the other columns are relevant to my question asking how to get the latest row based on a single timestamp column. Primary key is a UUID. There might be a triple-column UNIQUE constraint but not involving the column in question. My real SQL code selects a few of these columns. I am using SELECT * here to keep things simple. – Basil Bourque Jul 28 '15 at 23:23
  • I was thinking of a partial, covering index. But if your real SQL code selects a few of these columns. (being 1k - 20k in size) that option is out. A basic table definition is practically always relevant for performance questions like this. Either way, the additional info from your comment should be in the question. – Erwin Brandstetter Jul 29 '15 at 0:30
  • @ErwinBrandstetter I am purposely trying to not get specific; I'm not trying to solve a specific performance problem. I thought a general question about how to find latest record in general would be educational for me and many others. – Basil Bourque Jul 29 '15 at 0:37
  • The devil is always in the details with performance optimization. I do have general advice in the answer I am working on, too, but the best answer depends on the complete picture. – Erwin Brandstetter Jul 29 '15 at 0:44

Basic answers

Since you select a couple of big columns (info in comment) an index-only scan is probably not a viable option.

This code works (if no NULL values in data!)

Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well:

SELECT <some big columns>
FROM   my_table_ 

Without any index on when_ column, does this statement require a full scan of all rows?

Yes. Without index, there is no other option left.

With an index on when_ column, should I change this SQL to use some other approach/strategy of query?

Basically, this is the perfect query. There are more options in combination with advanced indexing:

Advanced technique

You have a constant influx of rows with later when_. Assuming that the latest _when constantly increases and never (or rarely) decreases (latest rows deleted / updated), you can use a very small partial index.

Basic implementation:

  1. Run your query once to retrieve the latest when_, deduct a safe margin (to be safe against losing the latest rows) and build an IMMUTABLE function around it. Basically a "fake global constant":

    CREATE OR REPLACE FUNCTION f_latest_when()
    $$SELECT timestamptz '2015-07-25 01:00+02'$$;
  2. Create a partial index excluding all older rows (minus a safe margin if necessary):

    CREATE INDEX my_table_when_idx ON my_table_ (when_ DESC NULLS LAST)
    WHERE when_ > f_latest_when();

    With a millions of rows the difference in size can be dramatic.

  3. Use the function in all your related queries. Include the same WHERE condition (redundantly if necessary) to convince the query planner the index is applicable). For the simple query:

    SELECT <some big columns>
    FROM   my_table_ 
    WHERE when_ > f_latest_when()
    LIMIT  1;

The size of the index grows with new (later) entries. Recreate the function with a later timestamp and REINDEX at opportune moments with no or little concurrent access. Only reindex after a relevant number of rows has been added. A couple of thousand entries won't typically matter much. We are doing this to cut off millions.
The beauty of it: queries don't have to change at all.

Advanced implementation with function to update the partial index automatically in this related answer on SO:

Closely related, with more general advice:

  • Since we are hard-coding a date-time string in this approach, why create the function f_latest_when? Why not use that hard-coded string directly in the index definition? – Basil Bourque Jul 29 '15 at 2:41
  • @BasilBourque: Because you can use f_latest_when() in queries and the index definition alike and don't have to change either after changing f_latest_when(). (But you must reindex.) – Erwin Brandstetter Jul 29 '15 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.