Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE observations (
   id         serial
 , station_id int
 , created_at timestamp
 , foo        text
);

INSERT INTO observations (station_id, created_at, foo)
SELECT ceil(random() * 10)::int    AS station_id -- few stations
     , '2014-01-01 0:0'::timestamp 
       + interval '1s' * g         AS created_at -- ascending (probably like real life)
     , 'aöguihaophgaduigha' || g   AS foo        -- random ballast
FROM   generate_series (1, 100000) g;              -- 100k rows

CREATE INDEX observations_special_idx ON observations(station_id, created_at DESC, id);

-- Or with separare stations table:
CREATE TABLE stations (station_id int PRIMARY KEY);
INSERT INTO stations
SELECT generate_series (1, 10);

CREATE TABLE



INSERT 0 100000



CREATE INDEX



CREATE TABLE



INSERT 0 10


-- Demos with just the latest 3 observations per station
-- With table stations
SELECT s.station_id, o.created_at, o.id
FROM   stations s
CROSS  JOIN LATERAL (
   SELECT o.id, o.created_at
   FROM   observations o
   WHERE  o.station_id = s.station_id
   ORDER  BY o.created_at DESC
   LIMIT  3  -- exaple value
   ) o
ORDER  BY s.station_id, o.created_at DESC;


station_id
created_at
id


1
2014-01-02 03:46:32
99992

1
2014-01-02 03:46:27
99987

1
2014-01-02 03:46:12
99972

2
2014-01-02 03:46:38
99998

2
2014-01-02 03:46:14
99974

2
2014-01-02 03:46:09
99969

3
2014-01-02 03:46:39
99999

3
2014-01-02 03:46:34
99994

3
2014-01-02 03:46:33
99993

4
2014-01-02 03:46:35
99995

4
2014-01-02 03:46:05
99965

4
2014-01-02 03:46:02
99962

5
2014-01-02 03:46:15
99975

5
2014-01-02 03:46:06
99966

5
2014-01-02 03:46:00
99960

6
2014-01-02 03:46:25
99985

6
2014-01-02 03:46:21
99981

6
2014-01-02 03:46:20
99980

7
2014-01-02 03:46:36
99996

7
2014-01-02 03:46:19
99979

7
2014-01-02 03:46:10
99970

8
2014-01-02 03:46:37
99997

8
2014-01-02 03:46:28
99988

8
2014-01-02 03:46:26
99986

9
2014-01-02 03:46:23
99983

9
2014-01-02 03:46:22
99982

9
2014-01-02 03:46:07
99967

10
2014-01-02 03:46:40
100000

10
2014-01-02 03:46:30
99990

10
2014-01-02 03:46:18
99978



SELECT 30


-- Without table stations:
WITH RECURSIVE stations AS (
   (
   SELECT station_id
   FROM   observations
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT o.station_id
           FROM   observations o
           WHERE  o.station_id > s.station_id
           ORDER  BY o.station_id
           LIMIT  1)
   FROM   stations s
   WHERE  s.station_id IS NOT NULL
   )
SELECT s.station_id, o.created_at, o.id
FROM   stations s
CROSS  JOIN LATERAL (
   SELECT o.id, o.created_at
   FROM   observations o
   WHERE  o.station_id = s.station_id
   ORDER  BY o.created_at DESC
   LIMIT  3  -- example value
   ) o
WHERE  s.station_id IS NOT NULL
ORDER  BY s.station_id, o.created_at DESC;


station_id
created_at
id


1
2014-01-02 03:46:32
99992

1
2014-01-02 03:46:27
99987

1
2014-01-02 03:46:12
99972

2
2014-01-02 03:46:38
99998

2
2014-01-02 03:46:14
99974

2
2014-01-02 03:46:09
99969

3
2014-01-02 03:46:39
99999

3
2014-01-02 03:46:34
99994

3
2014-01-02 03:46:33
99993

4
2014-01-02 03:46:35
99995

4
2014-01-02 03:46:05
99965

4
2014-01-02 03:46:02
99962

5
2014-01-02 03:46:15
99975

5
2014-01-02 03:46:06
99966

5
2014-01-02 03:46:00
99960

6
2014-01-02 03:46:25
99985

6
2014-01-02 03:46:21
99981

6
2014-01-02 03:46:20
99980

7
2014-01-02 03:46:36
99996

7
2014-01-02 03:46:19
99979

7
2014-01-02 03:46:10
99970

8
2014-01-02 03:46:37
99997

8
2014-01-02 03:46:28
99988

8
2014-01-02 03:46:26
99986

9
2014-01-02 03:46:23
99983

9
2014-01-02 03:46:22
99982

9
2014-01-02 03:46:07
99967

10
2014-01-02 03:46:40
100000

10
2014-01-02 03:46:30
99990

10
2014-01-02 03:46:18
99978



SELECT 30

station_id	created_at	id
1	2014-01-02 03:46:32	99992
1	2014-01-02 03:46:27	99987
1	2014-01-02 03:46:12	99972
2	2014-01-02 03:46:38	99998
2	2014-01-02 03:46:14	99974
2	2014-01-02 03:46:09	99969
3	2014-01-02 03:46:39	99999
3	2014-01-02 03:46:34	99994
3	2014-01-02 03:46:33	99993
4	2014-01-02 03:46:35	99995
4	2014-01-02 03:46:05	99965
4	2014-01-02 03:46:02	99962
5	2014-01-02 03:46:15	99975
5	2014-01-02 03:46:06	99966
5	2014-01-02 03:46:00	99960
6	2014-01-02 03:46:25	99985
6	2014-01-02 03:46:21	99981
6	2014-01-02 03:46:20	99980
7	2014-01-02 03:46:36	99996
7	2014-01-02 03:46:19	99979
7	2014-01-02 03:46:10	99970
8	2014-01-02 03:46:37	99997
8	2014-01-02 03:46:28	99988
8	2014-01-02 03:46:26	99986
9	2014-01-02 03:46:23	99983
9	2014-01-02 03:46:22	99982
9	2014-01-02 03:46:07	99967
10	2014-01-02 03:46:40	100000
10	2014-01-02 03:46:30	99990
10	2014-01-02 03:46:18	99978