PostgreSQL time series without TimescaleDB: store daily app and extension stats

PostgreSQL time series storage sounds like a job for a specialist database, but most SaaS and extension backends record something much smaller: one row per thing per day. Daily users for each extension, the rating and rating count of each app, the version that was live, the number of reviews. That is a few thousand rows a day even for a busy product, and plain PostgreSQL handles it well if you design the table for the questions you will ask later.

This guide builds that design step by step: a snapshot schema, an idempotent daily write, the queries you will actually run (day-over-day change, 7-day growth, weekly rollups, rating drops), how to handle missing days, when partitioning and BRIN indexes are worth it, and what the upcoming PostgreSQL 19 release changes for this kind of data. Every query below was run against PostgreSQL 17, except the PostgreSQL 19 syntax, which is marked as such.

Key takeaways

• For daily snapshots, a normal table with a primary key on (listing_id, day) is enough for years of data. Partitioning pays off later than most people think.
• Write each day with INSERT ... ON CONFLICT DO UPDATE and COALESCE, so reruns are safe and a failed fetch never overwrites a good value with NULL.
• Store a missing measurement as NULL, never as 0, and fill gaps at query time with generate_series.
• PostgreSQL 19 (release candidate out since October 15, 2026, general availability planned for November 19) adds IGNORE NULLS to lag() and last_value(), which makes carrying the last known value forward a one-liner.
• Reach for TimescaleDB or a column store when you record many points per minute, not one per day.

PostgreSQL time series: is plain Postgres enough?#

Time series workloads differ mainly in their write rate. A metrics agent that sends a value every ten seconds from thousands of servers produces billions of rows a year, and that is where specialized engines earn their keep. A product that records a daily snapshot is a different shape:

Even the last row is modest for PostgreSQL. The PostgreSQL partitioning documentation gives a useful rule of thumb for when a table becomes big enough to split: "the size of the table should exceed the physical memory of the database server." A daily snapshot table rarely gets there for years.

So the real work is not scaling. It is making the table correct: one row per listing per day, safe to rewrite, honest about missing data, and easy to query for change. If you already run MongoDB, the same design works with a different engine, and our guide to MongoDB time series collections for extension stats shows that version.

A schema for daily snapshots#

Two tables are enough: one for the things you track and one for their daily values.

CREATE TABLE listings (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  store      text NOT NULL CHECK (store IN ('chrome', 'edge', 'firefox', 'google_play', 'app_store')),
  store_id   text NOT NULL,
  name       text NOT NULL,
  UNIQUE (store, store_id)
);

CREATE TABLE listing_snapshots (
  listing_id   bigint NOT NULL REFERENCES listings (id),
  day          date   NOT NULL,
  users        integer,
  rating       numeric(3, 2),
  rating_count integer,
  version      text,
  captured_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (listing_id, day)
);

A few choices here matter more than they look:

• day is a date, not a timestamp. A snapshot describes a day. Storing the capture time in a separate captured_at column keeps the key clean and still tells you when the job actually ran. Pick one time zone for "the day" (UTC is the usual choice) and stick to it, because store dashboards disagree: Google Play Console, for example, reports installs and ratings in Pacific Time.
• The primary key is (listing_id, day). It enforces one row per listing per day and doubles as the index for the most common query: one listing over a date range.
• Every measurement column is nullable. If the store did not answer for the rating today, the rating is unknown, not zero. A 0 would show up as a crash in every chart and every alert.
• numeric(3, 2) for ratings. Stores show ratings at one or two decimals. Storing them as numeric avoids float noise such as 4.5299999 that would otherwise trigger "rating changed" alerts.

Write each day idempotently#

Daily jobs fail, retry and occasionally run twice. The write should give the same result no matter how many times it runs, and a partial failure should not erase what an earlier attempt captured:

INSERT INTO listing_snapshots (listing_id, day, users, rating, rating_count, version)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (listing_id, day) DO UPDATE SET
  users        = COALESCE(EXCLUDED.users, listing_snapshots.users),
  rating       = COALESCE(EXCLUDED.rating, listing_snapshots.rating),
  rating_count = COALESCE(EXCLUDED.rating_count, listing_snapshots.rating_count),
  version      = COALESCE(EXCLUDED.version, listing_snapshots.version),
  captured_at  = now();

EXCLUDED is the row you tried to insert. With COALESCE, a rerun that only managed to fetch the rating updates the rating and keeps the user count from the earlier run. In our test, a second write for the same day with users = NULL and a new rating of 4.54 left users at 5450 and updated the rating, which is exactly the behavior you want from a retry.

From Node.js the call is a parameterized query, for example with pg:

await pool.query(UPSERT_SQL, [listingId, day, users ?? null, rating ?? null, ratingCount ?? null, version ?? null]);

Collect every listing for the day first, then write them in one transaction, so a half-finished run never leaves some listings on today and others on yesterday.

Query change, not just values#

Nobody needs to know that an extension has 5,450 users. They need to know it gained 60 since yesterday, 6.45% in a week, and that its rating slipped after version 2.4.0. Window functions do this without any application code.

Day-over-day change and new versions#

SELECT day,
       users,
       users - lag(users) OVER w             AS users_change,
       rating - lag(rating) OVER w           AS rating_change,
       version <> lag(version) OVER w        AS new_version
FROM listing_snapshots
WHERE listing_id = 1
WINDOW w AS (ORDER BY day)
ORDER BY day;

Use <> rather than IS DISTINCT FROM for the version check. IS DISTINCT FROM treats a missing version as a change, so a day where the fetch failed shows up as a new release, which we saw in our own test data before switching. The trade-off is that <> returns NULL next to a gap, so a release that lands the day after a failed capture is not flagged either. The fix for both is to compare with the last known value, which the section on missing days below shows how to do.

7-day growth rate for every listing#

SELECT l.name,
       cur.users                                                AS users_now,
       prev.users                                               AS users_7d_ago,
       round(100.0 * (cur.users - prev.users) / prev.users, 2)  AS growth_pct_7d
FROM listings l
JOIN listing_snapshots cur  ON cur.listing_id  = l.id AND cur.day  = current_date
JOIN listing_snapshots prev ON prev.listing_id = l.id AND prev.day = current_date - 7
ORDER BY growth_pct_7d DESC;

Joining on exact days drops a listing whose snapshot is missing on either day. That is usually what you want in a ranking, and it is a reminder to fill gaps before you compare. Our guide to Chrome extension user growth explains why a compound rate over four or more weeks is steadier than a single week-over-week number.

Rating drops across all listings#

SELECT l.name, s.day, s.rating, s.prev_rating
FROM (
  SELECT listing_id, day, rating,
         lag(rating) OVER (PARTITION BY listing_id ORDER BY day) AS prev_rating
  FROM listing_snapshots
  WHERE day >= current_date - 30
) s
JOIN listings l ON l.id = s.listing_id
WHERE s.rating < s.prev_rating
ORDER BY s.day DESC;

This is the query behind a "rating dropped" alert. Because ratings are stored as numeric(3, 2), the comparison is exact at the two decimals the store shows. Like the version check, it compares adjacent rows, so a drop that spans a missing day (4.50, then NULL, then 4.40) is skipped until you compare with the last known rating instead.

Weekly rollups#

SELECT listing_id,
       date_trunc('week', day)::date            AS week,
       round(avg(users))                        AS avg_users,
       min(rating)                              AS worst_rating,
       max(rating_count) - min(rating_count)    AS new_ratings
FROM listing_snapshots
GROUP BY listing_id, week
ORDER BY listing_id, week;

date_trunc('week', ...) starts weeks on Monday. If you need buckets that start on a specific day, such as the day you launched, date_bin (PostgreSQL 14 and later) bins timestamps into intervals of any length from an origin you choose.

If a dashboard runs the rollup on every page load, store it in a materialized view and refresh it after the daily job:

CREATE MATERIALIZED VIEW weekly_listing_stats AS
SELECT listing_id,
       date_trunc('week', day)::date AS week,
       round(avg(users))             AS avg_users,
       min(rating)                   AS worst_rating
FROM listing_snapshots
GROUP BY listing_id, week;

CREATE UNIQUE INDEX ON weekly_listing_stats (listing_id, week);

REFRESH MATERIALIZED VIEW CONCURRENTLY weekly_listing_stats;

The unique index is required: the REFRESH MATERIALIZED VIEW reference states that CONCURRENTLY "is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows." With it, readers keep seeing the old rows while the refresh runs.

Handle missing days without lying#

Gaps happen: the job did not run, the store timed out, a listing was briefly unavailable. Two rules keep the charts honest.

Fill the calendar with generate_series#

A chart needs a row for every day, including days with no snapshot. Generate the calendar and left join the data:

SELECT d::date AS day, s.users, s.rating
FROM generate_series(date '2026-10-01', date '2026-10-08', interval '1 day') AS d
LEFT JOIN listing_snapshots s ON s.listing_id = 1 AND s.day = d::date
ORDER BY day;

Missing days now appear as NULL rows, so your chart library can draw a gap instead of silently connecting October 4 to October 6.

Carry the last known value forward (PostgreSQL 17 and 18)#

For alerts and growth rates you often want the last known value instead of a gap. Before PostgreSQL 19 the standard trick counts non-null values to build groups, then takes the one value in each group:

WITH days AS (
  SELECT d::date AS day, s.users, s.rating
  FROM generate_series(date '2026-10-01', date '2026-10-08', interval '1 day') AS d
  LEFT JOIN listing_snapshots s ON s.listing_id = 1 AND s.day = d::date
), grouped AS (
  SELECT *,
         count(users)  OVER (ORDER BY day) AS users_grp,
         count(rating) OVER (ORDER BY day) AS rating_grp
  FROM days
)
SELECT day,
       users,
       max(users)  OVER (PARTITION BY users_grp)  AS users_filled,
       max(rating) OVER (PARTITION BY rating_grp) AS rating_filled
FROM grouped
ORDER BY day;

count(users) only increases on days that have a value, so each gap lands in the same group as the last real value before it. In our test data the missing October 3 and October 5 were filled with 5,160 and 5,230, the values from the days before.

The PostgreSQL 19 version: IGNORE NULLS#

PostgreSQL 19 adds the SQL standard null treatment clause. According to the PostgreSQL 19 release notes, window functions can now "ignore NULLs with the IGNORE NULLS / RESPECT NULLS clause", supported for lead(), lag(), first_value(), last_value() and nth_value(). The window function reference places the clause after the function call, so the whole carry-forward query above becomes:

-- PostgreSQL 19 and later
SELECT day,
       users,
       last_value(users) IGNORE NULLS OVER (ORDER BY day) AS users_filled,
       users - lag(users) IGNORE NULLS OVER (ORDER BY day) AS users_change
FROM listing_snapshots
WHERE listing_id = 1
ORDER BY day;

The second column is the more useful one for alerts: the change compared with the last day that had data, instead of NULL after every gap. We could not run this query ourselves because no PostgreSQL 19 server was available to us, so treat it as written from the documentation and test it on the release candidate before you depend on it.

When to partition, and which indexes to add#

Start with the primary key#

The (listing_id, day) primary key serves "one listing over time" directly. Queries that scan one day across all listings, such as "today's rating drops", read a small slice of the table and are fast at these sizes without extra indexes.

BRIN for large, append-only tables#

Daily snapshots are written in date order, so the day column is naturally correlated with where rows sit on disk. That is the case BRIN indexes are built for: the documentation describes BRIN as "designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table." A BRIN index on day is tiny compared with a B-tree and speeds up date range scans across all listings once the table is large:

CREATE INDEX listing_snapshots_day_brin ON listing_snapshots USING brin (day);

At a few hundred thousand rows, measure with EXPLAIN ANALYZE before adding it. It helps most when the table is far larger than memory.

Partition by range for retention#

Partitioning helps when you want to delete old data cheaply or when the table is truly large. Range partitions by year (or month) look like this:

CREATE TABLE listing_snapshots (
  listing_id   bigint NOT NULL,
  day          date   NOT NULL,
  users        integer,
  rating       numeric(3, 2),
  rating_count integer,
  version      text,
  captured_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (listing_id, day)
) PARTITION BY RANGE (day);

CREATE TABLE listing_snapshots_2026 PARTITION OF listing_snapshots
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE listing_snapshots_2027 PARTITION OF listing_snapshots
  FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
CREATE TABLE listing_snapshots_default PARTITION OF listing_snapshots DEFAULT;

The primary key must include the partition key, which (listing_id, day) already does. The default partition catches rows outside every range, so a job that runs on January 1 before anyone created next year's partition still succeeds. Retention is then one statement instead of a huge DELETE:

ALTER TABLE listing_snapshots DETACH PARTITION listing_snapshots_2026;
DROP TABLE listing_snapshots_2026;

The partitioning documentation explains why this matters: dropping or detaching a partition "is far faster than a bulk operation" and these commands "entirely avoid the VACUUM overhead caused by a bulk DELETE."

What PostgreSQL 19 changes for time series tables#

The PostgreSQL 19 RC 1 announcement on October 15, 2026 says RC 2 will follow on November 12 and that general availability is planned for November 19, 2026. Upgrading from an earlier major version still needs pg_upgrade, pg_dump and pg_restore, or logical replication. Items from the release notes that matter for snapshot tables:

• IGNORE NULLS in window functions, covered above. It removes the most awkward query in most metrics code.
• JIT is now off by default. The release notes say JIT costing "has been determined to be unreliable" and that sites "doing many large analytical queries" need to enable it manually. Compare the run time of your heaviest rollups before and after the upgrade.
• REPACK and REPACK (CONCURRENTLY). A new command "reclaims disk space and reorganizes table contents", and the concurrent form works "without blocking reads and writes to the table." Useful after a large cleanup on a table you have not partitioned.
• Parallel autovacuum. Autovacuum can use parallel workers for indexes, controlled by autovacuum_max_parallel_workers and the per-table autovacuum_parallel_workers setting.
• COPY TO with JSON output and partitioned tables. COPY ... TO ... (FORMAT json) exports rows as JSON, and COPY TO now works on a partitioned table directly instead of needing COPY (SELECT ...). Handy for CSV and JSON exports of history.
• LZ4 by default for TOAST compression. The default default_toast_compression changes from pglz to lz4. It affects large values such as stored JSON payloads, not the small numeric columns of a snapshot row.

If you run the same Node.js backend, our Node.js 26 LTS upgrade checklist covers the runtime side of the same season of upgrades.

When to use TimescaleDB or a column store instead#

Plain PostgreSQL stops being the obvious choice when you record events, not snapshots: page views, API calls, per-minute metrics. At that rate you want automatic partitioning, compression and incrementally maintained rollups. TimescaleDB hypertables are "PostgreSQL tables that automatically partition your time-series data by time", so you keep SQL and your existing driver. Column stores such as ClickHouse go further for wide analytical scans over billions of rows. Neither is needed to store a few thousand daily numbers, and both add operational work you can postpone until the write rate demands it.

Let Extenify keep the history for store data#

The schema above is how you would build store tracking yourself: fetch every listing once a day, upsert a snapshot, diff it against yesterday, alert on changes. Extenify does exactly that as a hosted browser extension analytics dashboard. It records users, ratings, rating counts, reviews and versions for your extensions on the Chrome Web Store, Edge Add-ons and Firefox Add-ons, and for apps on Google Play and the App Store, every day, next to the competitors you choose. A failed fetch is treated as unknown rather than zero, so you are not paged for an outage that never happened, and changes arrive in a daily digest by email, Slack, Discord or Telegram. Turning snapshots into readable alerts is its own problem, and our guide to JSON diff in Node.js shows one way to do it. Every plan starts with a 7-day free trial, and the pricing page lists the plans and history limits.

Frequently asked questions#

Can PostgreSQL be used as a time series database?#

Yes. With a date or timestamptz column in the primary key, window functions for change over time and generate_series for gaps, PostgreSQL handles daily and hourly metrics comfortably. Specialized extensions or engines matter when you write many points per second.

Do I need TimescaleDB for time series in PostgreSQL?#

Not for daily snapshots. TimescaleDB adds automatic time partitioning, compression and continuous aggregates, which help with high-frequency data. A table that grows by thousands of rows a day works fine with native PostgreSQL features.

Should I partition a time series table in PostgreSQL?#

Only when you need cheap retention or the table approaches the size of the server's memory, which is the rule of thumb in the PostgreSQL documentation. Start unpartitioned; you can move to range partitions by year or month later.

How do I fill gaps in time series data in PostgreSQL?#

Generate a row per day with generate_series and left join your data. To carry the last known value forward, use the counting trick shown above on PostgreSQL 17 and 18, or last_value(...) IGNORE NULLS OVER (ORDER BY day) on PostgreSQL 19.

When is PostgreSQL 19 released?#

The PostgreSQL 19 release candidate came out on October 15, 2026. A second release candidate is planned for November 12, and general availability for November 19, 2026, unless an issue forces a delay.

Summary#

PostgreSQL time series storage for daily app and extension stats needs no special engine. Use a snapshot table keyed on (listing_id, day), nullable measurement columns, and an ON CONFLICT ... COALESCE upsert so reruns are safe. Query change with lag(), fill the calendar with generate_series, carry values forward with the counting trick today and IGNORE NULLS on PostgreSQL 19, and add BRIN indexes or range partitions only when size or retention calls for them. When PostgreSQL 19 ships on November 19, test your rollups with JIT off before you switch.

Image credits#

• Cover photo: Wild elephant walking through a jungle trail by shirishpoudel07 on the WordPress Photo Directory, CC0.