TIL

Lets say you have a table of events, each representing a point in time when an event occurred. Maybe it’s clicks in your applications, or pages that a user visited. You might like to see how long users are spending in your application, or what they are doing in a given session.

So the task at hand is to use SQL to analyze this event data and combine these points in time into groups of events - I will call them sessions for this post.

The first thing you must do is decide how much time you would allow someone to go before another event happens for it to still be considered the same session. Anything less than this duration between events, and the events are connected into the same session; anything more, and we will call it a new session. The value you use here is up to you and your needs - if the actions you are looking at take a lot of time to perform, or maybe someone is spending time reading a lot of content before continuing to the next thing, you might want to make this threshold higher, maybe tens of minutes or even more. On the other hand, if you expect they should be clicking around fairly quickly, you might want to go much shorter, maybe only seconds. For this example I am going to use 5 minutes between events as my threshold.

The next thing you need to decide is, if the session only lasted for one event, how long would you want to say it lasted? For this example, I am going to use 1 minute.

So there are several SQL features we can use to our advantage - this is all PostgreSQL; everything here works at least as far back as version 9.5, possibly earlier.

The first feature is Postgres’ interval type. If you haven’t played with intervals, you are really missing out. It is a native data type that defines a period of time - perfect for our use cases.

The next thing to understand is window functions. I have been writing SQL long enough that these still feel “new”, but they have been around for a long time now! Window functions are great; they let you aggregate against different partitions (groups, or in this case windows) in the same query, which is perfect for our needs where we want to consider each user independently.

Let’s look at some example data to get our bearings:

DROP TABLE IF EXISTS event_log CASCADE;
CREATE TABLE event_log (
	  ts timestamptz
	, name varchar
);

INSERT INTO event_log ( ts, name ) VALUES
  ('2024-07-01T12:00:00.000Z', 'Alice')
, ('2024-07-01T12:01:00.000Z', 'Alice')
, ('2024-07-01T12:02:00.000Z', 'Alice')
, ('2024-07-01T12:03:00.000Z', 'Alice')
, ('2024-07-01T12:04:00.000Z', 'Alice')
, ('2024-07-01T12:05:00.000Z', 'Alice')
, ('2024-07-01T12:12:00.000Z', 'Alice')
, ('2024-07-01T12:14:00.000Z', 'Alice')
, ('2024-07-01T12:17:00.000Z', 'Alice')
, ('2024-07-01T12:21:00.000Z', 'Alice')
, ('2024-07-01T12:26:00.000Z', 'Alice')
, ('2024-07-01T12:30:00.000Z', 'Alice')
, ('2024-07-01T12:00:00.000Z', 'Bob')
, ('2024-07-01T12:02:00.000Z', 'Bob')
, ('2024-07-01T12:05:00.000Z', 'Bob')
, ('2024-07-01T12:09:00.000Z', 'Bob')
, ('2024-07-01T12:14:00.000Z', 'Bob')
, ('2024-07-01T12:20:00.000Z', 'Bob')
, ('2024-07-01T12:25:00.000Z', 'Bob')
, ('2024-07-01T12:35:00.000Z', 'Bob')
, ('2024-07-01T12:44:00.000Z', 'Bob')
, ('2024-07-01T12:48:00.000Z', 'Bob')
, ('2024-07-01T13:05:00.000Z', 'Bob')
;

Here you can see we have two users, Alice and Bob, and they have several events. Of course, in a real world scenario, these names would probably be IDs to a users table, you would have an event type and probably lots of other attributes for the event, but none of that is necessary for this demo. If you have trouble extending this example to a more real world scenario, reach out in the comments or on Twitter.

Here is the final query, we will walk through the different parts of it to see how it works:

with inputs as (
  select
      '5 minutes'::interval threshold -- change this to however long you want your session timeouts to be
    , '1 minute'::interval min_session_duration -- if there is only one event in the session, how long do you want to say it lasted?
)
, prep as (
  select
      ts
    , name
    , row_number() 
      over (partition by name order by ts) row_num
    , coalesce(
      ts - lag(ts) 
        over (partition by name order by ts asc) <= inputs.threshold
      , false) is_connected
  from
    event_log
  cross
    join inputs
)
, session_detail as (
  select
      ts
    , name
    , max(row_num) 
      filter (where NOT is_connected) 
      over (partition by name order by ts asc) session_seq
  from prep
)
, sessions as (
  select
      row_number() 
      over (partition by name order by min(ts), session_seq) session_seq
    , name
    , count(*) event_count
    , min(ts) start_ts
    , max(ts) end_ts
    , greatest(inputs.min_session_duration, max(ts) - min(ts)) session_duration
  from session_detail
  cross
    join inputs
  group by
      session_seq
    , name
    , inputs.min_session_duration
)
select
    session_seq
  , concat_ws('-', name, session_seq) unique_id
  , name
  , event_count
  , start_ts
  , end_ts
  , session_duration
from sessions
;

If you arent comfortable with SQL that might look like a lot, but trust me, its really not bad - and we will walk through all the different parts.

Here is what the query returns (I have removed the date and seconds and milliseconds for the timestamps for brevity, they are all the same for the example):

Lets start with the two pieces of SQL that make this work.

First theres this line:

, coalesce(
    ts - 
    lag(ts) over (partition by name order by ts asc) 
        <= inputs.threshold
    , false) is_connected

Forgive the formatting - there is no great way to format it that will make everyone happy, but hopefully I can walk you through the pieces.

The goal with this line is to get true or false, is the row we are looking at part of the same session as the previous event for the user.

Starting from the inside out - for the current row, we are taking its ts (timestamp), and subtracting the timestamp of the row before it. This is a window function - you know that because of the over () clause. The window is how we decide what “before it” means.

We are using partition by name, meaning that our window will always only consider rows that have the same values for name as our current row - and we are using order by ts asc, meaning “before” will be a row earlier than the current row.

partition by works just like group by, you are defining the individual groupings of columns whose combination of values are considered unique to define your different groups. In our case, we only want to consider rows by the same user.

lag() is just one option, theres its dual lead(), which if you were to order by desc instead would work just the same.

We do our subtraction and get an interval - a duration of time since that previous event. Now we just need to know if that interval is less than our threshold - if so we are still part of the same session, if not then this is the beginning of a new session.

But our lag(ts) ... could return null - if we are looking at the first event for a user, there is no previous value. And so if we take our current row’s timestamp and subtract null, the result will of course be null. And when we do our comparison with the threshold, we will also get null. So we coalesce() our value to false in that case - if it’s the first event for a user it certainly cannot be part of a previous session.

So now lets look at the other important part of this approach:

, max(row_num) 
      filter (where NOT is_connected) 
      over (partition by name order by ts asc) session_seq

This part is a little more complicated, so bear with me:

Earlier in the query we assigned a row number to each row with the row_number() window function, using the same partitions as we previously discussed.

Our goal is to get the highest row number that was the beginning of the current session - so we are only interested in rows where is_connected is false.

The most important thing to understand is that max() as a window function that includes an order by clause will not consider the entire window, but only the window frame up to the current row. I’m going to quote the postgres documentation here: ¹

There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition.

So our max() is only considering rows where is_connected is false, meaning the events that were the beginning of sessions. Then we want to pick the highest row number that came before the current row - so if we are in the second session we want to pick the beginning of the second session.

These are the two most important parts of this solution - how to figure out if the current row is a continuation of a session or the start of a new one, and then how to find the start of the session for the current row, so we know which session we are a part of.

The one other thing I want to call out here is the CTE at the top I call inputs - this is a pattern I use a lot - it pulls the “variables” of a query to the top, and just selects them as literal values - then I can cross join them where I need to in the rest of the query, making it dynamic to those inputs.

Here is a database fiddle if you want to play with this query to understand it better. I really recommend pulling out parts of the CTEs and understanding how each part works: https://dbfiddle.uk/M_iWoTG-

One last thing to mention is that this query is useful if you want to slice and dice your event data in a few different ways to explore it. You might get different results and gain more insights into your data if you tweak the thresholds. However, if you already have these thresholds set in stone, this would be an easy thing to calculate as your data comes in, or in an intermediate table, ingesting the event data on some period and calculating the duration from the previous event and which session they were a part of.

If you know me you’re aware that I love SQL and relational databases. Of all of the databases I have used, PostgreSQL is by far my favorite. SQL is the one technology I have seen in common across every job I have had over the past two decades. I would bet you could say the same - even if you aren’t writing it directly (which would be a shame in my opinion), it is still there, behind the scenes, handling all of your data. But if you’re fortunate enough to work directly with SQL and a database, it’s hard to find a better tool.

But while I have never seen a better database than PostgreSQL (and you can’t beat the price!), that doesn’t mean that there aren’t things that could be improved. My goal with this post is to start a conversation and throw out some ideas that I have gathered over the years of things I yearned for. Best case scenario, someone tells me that these things already exist and I learn something new! Second best, these ideas resonate with others and we find ways to get some of these in future versions. But I would also love any conversation about why any of these are bad ideas or technically infeasible - I’ll learn something then too. And if anyone has ideas about how I can contribute to these things directly, please let me know!

Read more

One thing you’ll want to learn if you use PostgreSQL for any length of time is how to use EXPLAIN. At my job at Vendr, like my previous roles, we are no exception. The good and bad thing is that in many cases you can go pretty far before you start having issues. But that means that not everyone has had the opportunity to learn the dark art of reading explain output. And as great as PG is, it is not as user friendly in this area as other RDBMs like MSSQL. But with this primer, a few free online tools, and a little bit of time, you can quickly learn how to think about the performance of your queries and where to start when optimizing.

Read more

Something I have a need to do often but can be difficult to do at times in SQL is to create a pivot table. As an example imagine wanting to see customers and their revenue by month. It is straightforward to create a normal data set where the dates are the rows and you have a revenue amount for each. Something like this:

But you quickly come up to two obstacles as you try to take it further - you either want to have the months as columns like this:

or you want to see multiple customers, which as a column can be difficult, or even harder is having the months as columns and the customers as rows:

The term for this is pivot table - which is something you may have done many times in Excel or other spreadsheet applications.

But this is difficult in SQL, because SQL requires you to have a static column list. You can’t ask SQL to give you whatever columns are necessary, you must declare them in your query. (SELECT * may seem like an exception to this rule, but in this case the SQL engine still knows what the columns are going to be before the query is executed).

Luckily Postgres gives you a way around this. If you want actual columns you still have to specify them, but the hard part of aggregation into these pivoted columns and rows are made much easier. The key is using the JSON functionality, which allows you to represent complex values in a single cell. It allows you to aggregate the values into what really represents multiple values, and then pull them back apart after the fact.

Here is an example of what this looks like:

WITH columns AS (
  SELECT
       generate_series dte
     , customer_id
  FROM generate_series('2022-01-01'::date, '2022-12-31', '1 month')
  CROSS JOIN
    (
      SELECT DISTINCT
        customer_id
      FROM
        invoice
    ) AS customers
), data AS (
  SELECT
      columns.dte
    , columns.customer_id
    , SUM(COALESCE(invoice.amount, 0)) total
  FROM columns
  LEFT OUTER
    JOIN invoice
    ON DATE_PART('year', invoice_date) = DATE_PART('year', columns.dte)
    AND DATE_PART('month', invoice_date) = DATE_PART('month', columns.dte)
    AND columns.customer_id = invoice.customer_id
  GROUP BY
      columns.dte
    , columns.customer_id
), result AS (
  SELECT
    customer_id
    , JSONB_OBJECT_AGG(TO_CHAR(dte, 'YYYY-MM'), total) pivotData
  FROM
    data
  GROUP BY
    customer_id
)
SELECT
    customer_id
  , (pivotData->>'2022-01') "jan"
  , (pivotData->>'2022-02') "feb"
  , (pivotData->>'2022-03') "mar"
  , (pivotData->>'2022-04') "apr"
  , (pivotData->>'2022-05') "may"
  , (pivotData->>'2022-06') "jun"
  , (pivotData->>'2022-07') "jul"
  , (pivotData->>'2022-08') "aug"
  , (pivotData->>'2022-09') "sep"
  , (pivotData->>'2022-10') "oct"
  , (pivotData->>'2022-11') "nov"
  , (pivotData->>'2022-12') "dec"
FROM
  result
ORDER BY customer_id

Which gives results like we were after above.

If you would like to see how this is done, I have an interactive fiddle you can play with that shows you step by step how each of these parts work:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=39e115cb8afd6e62c0101286ecd08a3f

This example is using PG 15, but this functionality works all the way back to PG 9.5.

This query is also a great example of using generate_series to generate a set of data to join against, so that you can find any holes and represent all the data points you need (months in this case), even if there is no actual data for that point.

In conclusion, the JSON functionality built into todays relational databases are more than just schemaless data stores and complex values in cells. They can also be powerful as intermediate steps to help you manipulate and transform your data in useful ways.

An alternate title for this post might be Processing API Requests with a Queue. We recently had a project where we were expecting a burst of high traffic and heavy load on some API endpoints. We wanted to make sure that we could handle all of the traffic, even if the processing time was affected - dropped requests were not an option. After doing quite a bit of research this post is what we came up with. In the end this strategy worked well for our purposes, but we did identify some ways that we would improve it in the future.

Also this same strategy will work for any long-running api request. Things like reporting for example, where you need to be able to make a request but it may take a very long (and possibly indeterminate) amount of time to complete. Please forgive the tone of the document, it is being adapted from my notes and it isn’t in conversational form. It’s likely that there may be some things that I should expand upon, so if anything needs clarification please feel free to ask in the comments.

Read more