WRX VB Maintenance Schedule

 

Just for fun, I recreated the warranty maintenance schedule for my new WRX in Postgres.  G-Two has created a python library for interacting with the Subaru Starlink service, which lets you get the odometer (and a few other things) for as long as you have the service (you get it free the first three years).

The car already tells you about upcoming maintenance all on its own, but tracking it myself sounded like a fun, short-term project.  Eventually it will give me a place to store scanned copies of maintenance invoices as well.  My WRX is coming from Van Bortel in Henrietta, but I noticed their maintenance records link to Van Bortel in Victor isn't 100% reliable - they can usually find out what has been done to my car but sometimes it takes a while.

Drop me a note and tell me if this is useful.  If you'd like to collaborate to make it better I'm open to that, too - I'll start a github project.

CREATE TABLE public.maintenance_items (
    id integer NOT NULL,
    description text
);

CREATE TABLE public.maintenance_schedule (
    id integer NOT NULL,
    maintenance_item_id integer NOT NULL,
    months integer,
    miles integer,
    inspect boolean,
    replace boolean,
    perform boolean
);

INSERT INTO public.maintenance_items VALUES (1, 'Engine oil');
INSERT INTO public.maintenance_items VALUES (2, 'Engine oil filter');
INSERT INTO public.maintenance_items VALUES (3, 'Spark plugs');
INSERT INTO public.maintenance_items VALUES (4, 'Drive belts');
INSERT INTO public.maintenance_items VALUES (5, 'Fuel systems, lines, and connectors');
INSERT INTO public.maintenance_items VALUES (6, 'Fuel filter');
INSERT INTO public.maintenance_items VALUES (7, 'Air cleaner element');
INSERT INTO public.maintenance_items VALUES (8, 'Cooling system, hoses, & connections');
INSERT INTO public.maintenance_items VALUES (9, 'Engine coolant');
INSERT INTO public.maintenance_items VALUES (10, 'Clutch operation');
INSERT INTO public.maintenance_items VALUES (11, 'Transmission gear oil');
INSERT INTO public.maintenance_items VALUES (12, 'N/A');
INSERT INTO public.maintenance_items VALUES (13, 'Front & rear differential gear oil');
INSERT INTO public.maintenance_items VALUES (14, 'Brake lines, operation of parking & service brake systems');
INSERT INTO public.maintenance_items VALUES (15, 'Brake fluid / clutch fluid');
INSERT INTO public.maintenance_items VALUES (16, 'Disc brake pads & rotor, front & rear axle boots & joints');
INSERT INTO public.maintenance_items VALUES (17, 'Steering & suspension');
INSERT INTO public.maintenance_items VALUES (18, 'Wheel bearings');
INSERT INTO public.maintenance_items VALUES (19, 'Rotate & inspect tires');
INSERT INTO public.maintenance_items VALUES (20, 'HVAC system A/C filter');


INSERT INTO public.maintenance_schedule VALUES (1, 1, 6, 6000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (132, 19, 6, 6000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (133, 19, 12, 12000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (134, 19, 18, 18000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (135, 19, 24, 24000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (136, 19, 30, 30000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (137, 19, 36, 36000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (138, 19, 42, 42000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (139, 19, 48, 48000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (140, 19, 54, 54000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (141, 19, 60, 60000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (142, 19, 66, 66000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (143, 19, 72, 72000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (144, 19, 78, 78000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (145, 19, 84, 84000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (146, 19, 90, 90000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (147, 19, 96, 96000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (148, 19, 102, 102000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (149, 19, 108, 108000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (150, 19, 132, 132000, false, false, true);
INSERT INTO public.maintenance_schedule VALUES (2, 1, 12, 12000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (3, 1, 18, 18000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (4, 1, 24, 24000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (5, 1, 30, 30000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (6, 1, 36, 36000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (7, 1, 42, 42000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (8, 1, 48, 48000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (9, 1, 54, 54000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (10, 1, 60, 60000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (11, 1, 66, 66000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (12, 1, 72, 72000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (13, 1, 78, 78000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (14, 1, 84, 84000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (15, 1, 90, 90000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (16, 1, 96, 96000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (17, 1, 102, 102000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (18, 1, 108, 108000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (19, 1, 132, 132000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (20, 2, 6, 6000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (21, 2, 12, 12000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (22, 2, 18, 18000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (23, 2, 24, 24000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (24, 2, 30, 30000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (25, 2, 36, 36000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (26, 2, 42, 42000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (27, 2, 48, 48000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (28, 2, 54, 54000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (29, 2, 60, 60000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (30, 2, 66, 66000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (31, 2, 72, 72000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (32, 2, 78, 78000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (33, 2, 84, 84000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (34, 2, 90, 90000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (35, 2, 96, 96000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (36, 2, 102, 102000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (37, 2, 108, 108000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (38, 2, 132, 132000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (39, 3, 60, 60000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (40, 3, 120, 120000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (41, 4, 30, 30000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (42, 4, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (43, 4, 90, 0, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (44, 4, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (45, 5, 30, 30000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (46, 5, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (47, 5, 90, 0, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (48, 5, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (49, 6, 72, 72000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (50, 7, 30, 30000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (51, 7, 60, 60000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (52, 7, 90, 0, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (53, 7, 120, 120000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (54, 8, 30, 30000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (55, 8, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (56, 8, 90, 0, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (57, 8, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (58, 9, 11, 137500, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (59, 10, 12, 12000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (60, 10, 24, 24000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (61, 10, 36, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (62, 10, 48, 48000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (63, 10, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (64, 10, 72, 72000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (65, 10, 84, 84000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (66, 10, 96, 96000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (67, 10, 108, 108000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (68, 10, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (69, 10, 132, 132000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (70, 11, 30, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (71, 11, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (73, 11, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (74, 12, 30, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (76, 12, 96, 96000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (72, 11, 90, 90000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (78, 13, 30, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (79, 13, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (80, 13, 96, 96000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (81, 13, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (75, 12, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (77, 12, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (82, 14, 12, 12000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (83, 14, 24, 24000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (84, 14, 36, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (85, 14, 48, 48000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (86, 14, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (87, 14, 72, 72000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (88, 14, 84, 84000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (89, 14, 96, 96000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (90, 14, 108, 108000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (91, 14, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (92, 14, 132, 132000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (93, 15, 30, 36000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (94, 15, 60, 60000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (95, 15, 96, 96000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (96, 15, 120, 120000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (97, 16, 12, 12000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (98, 16, 24, 24000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (99, 16, 36, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (100, 16, 48, 48000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (101, 16, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (102, 16, 72, 72000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (103, 16, 84, 84000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (104, 16, 96, 96000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (105, 16, 108, 108000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (106, 16, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (107, 16, 132, 132000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (108, 17, 12, 12000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (109, 17, 24, 24000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (110, 17, 36, 36000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (111, 17, 48, 48000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (112, 17, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (113, 17, 72, 72000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (114, 17, 84, 84000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (115, 17, 96, 96000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (116, 17, 108, 108000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (117, 17, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (118, 17, 132, 132000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (119, 18, 60, 60000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (120, 18, 120, 120000, true, false, false);
INSERT INTO public.maintenance_schedule VALUES (121, 20, 12, 12000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (122, 20, 24, 24000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (123, 20, 36, 36000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (124, 20, 48, 48000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (125, 20, 60, 60000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (126, 20, 72, 72000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (127, 20, 84, 84000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (128, 20, 96, 96000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (129, 20, 108, 108000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (130, 20, 120, 120000, false, true, false);
INSERT INTO public.maintenance_schedule VALUES (131, 20, 132, 132000, false, true, false);

Timestream live dashboards in Grafana

 Amazon Timestream is a strange beast.  It is a time series "database" but under the hood it behaves like a bunch of flat files that are partitioned by time and something else.  That something could be the measurement name (this is the default) but it could also be dimension that you specify.  You better specify them carefully, though, because once the table is created you can't ever change them.

Partitioning is automatic.  If you insert data as time, measurement_name, value, then at the beginning all your rows will go into a few (maybe one) partitions.  As your data grows, partitions get larger and eventually new partitions are created.  Distinct measurements can be within a single partition or spread across many partitions.  There is no way to really tell how this works - under the hood, partitions may be split (but they probably aren't) or new partitions might be appended and indexed.  It keeps track of what measurements and time ranges are within each partition using an index.  This mean that certain queries, like asking for data for a specific measurement and time range, are faster - it can figure out what blocks to look in using the index.  But you pay for each gigabyte scanned.  If you ask for every measurement for a short time range, you can't really predict how many partitions will be scanned.

Amazon tells you to trust them - they will make new partitions when needed in order to minimize query costs.  In Grafana, though, you still have to be very careful.  In the case where you have only a few measurements, then a given time range (say the last 15 minutes) might be in a single partition, with lots of measure_names in it.  If you query them one by one, then you might be paying to scan the same data multiple times, and paying $0.01/GB scanned every time.

In my case, measure_name has pretty low cardinality - there are maybe 50 distinct measurements.  What that means is that my partitions are likely by time only.  For queries that get the most recent data - for example the one below - that isn't necessarily a good thing.

SELECT DISTINCT

       measure_name,

       COALESCE(

           FIRST_VALUE(measure_value::double) OVER (

               PARTITION BY measure_name

               ORDER BY time DESC

               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

           ),

           CASE WHEN FIRST_VALUE(measure_value::boolean) OVER (

               PARTITION BY measure_name

               ORDER BY time DESC

               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

           ) THEN 1 ELSE 0 END

       ) AS "value",

       FIRST_VALUE(time) OVER (

           PARTITION BY measure_name

           ORDER BY time DESC

           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

       ) AS "last update"

FROM $__database.$__table

WHERE time >= ago(5m)

ORDER BY measure_name

I thought I could put this query into a table, hide the table, then have 20 individual gauges refer to its data using the table visualization as the datasource for the remaining dials and gauges.  Turns out that's not how Grafana works.  Instead of fetching the data once, it runs this query once per gauge.  The result is that my development (of the dashboards) is costing me $0.20/hr in timestream queries.  Not a huge deal, but still kind of annoying.

What Grafana really needs is the concept of a 'dashboard query' that can be a datasource for multiple visualizations on the dashboard.  I'll be putting this in as a feature request soon.

The point is this: Timestream works very well with Grafana, but construct your dashboards and queries very, very carefully.