timescaledb

The Day I Discovered My TimescaleDB Was Just PostgreSQL in Disguise

For years I thought my Zabbix was using TimescaleDB. A failed upgrade revealed the truth: 391 million records in plain PostgreSQL with no optimization. My queries were 7,500x slower than they should have been. Sometimes the best discoveries come from fixing what's broken.

Bartosz Maciejewski

18 Jun 2025 — 6 min read

Photo by Resource Database / Unsplash

I've been running Zabbix 6.4 on NetBSD with what I thought was TimescaleDB for years. Turns out I was completely wrong. This is the story of how a failed package upgrade revealed that my "optimized" time-series database was nothing more than vanilla PostgreSQL with a dormant extension.

How It All Started

Last Tuesday I was doing routine maintenance on my monitoring server. The NetBSD pkgsrc had a new TimescaleDB version available: postgresql14-timescaledb-2.18.2.tgz. My previous version was 2.16, and since I'm paranoid about keeping everything updated (I even have a separate article about TimescaleDB maintenance procedures), I went ahead with the upgrade.

u-SYS-mon01# su - pgsql
$ psql -X zabbix
psql (14.17)
Type "help" for help.
zabbix=# ALTER EXTENSION timescaledb UPDATE;
ERROR:  catalog version mismatch, expected "2.16.1" seen "2.15.2"
CONTEXT:  PL/pgSQL function inline_code_block line 7 at RAISE

Where the hell did 2.15.2 come from? I never had that version installed. Something was seriously broken here.

The Investigation Begins

After some failed attempts to fix the version mismatch, I got this interesting error:

zabbix=# SELECT default_version, installed_version
FROM pg_available_extensions
WHERE name = 'timescaledb';
FATAL:  "timescaledb" already loaded with a different version
DETAIL:  The new version is "2.16.1", this session is using version "2.18.2".

At this point I decided to take the nuclear option. First, I had to disable TimescaleDB completely:

u-SYS-mon01# /etc/rc.d/pgsql stop
Stopping pgsql.
u-SYS-mon01# vi /usr/pkg/pgsql/data/postgresql.conf

In the postgresql.conf file, I found the line:

shared_preload_libraries = 'timescaledb'

I commented it out:

#shared_preload_libraries = 'timescaledb'

Then restarted PostgreSQL:

u-SYS-mon01# /etc/rc.d/pgsql start
Starting pgsql.

With TimescaleDB disabled, I could finally clean up:

zabbix=# DROP EXTENSION IF EXISTS timescaledb CASCADE;
DROP EXTENSION
zabbix=# CREATE EXTENSION timescaledb VERSION '2.18.2';
CREATE EXTENSION

Then I re-enabled TimescaleDB by uncommenting the shared_preload_libraries line and restarting PostgreSQL again. Extension was back, crisis averted... or so I thought.

The Shocking Discovery

Out of curiosity, I decided to check what TimescaleDB was actually doing for me:

zabbix=# SELECT * FROM timescaledb_information.hypertables;
 hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | tablespaces
-------------------+-----------------+-------+----------------+------------+---------------------+-------------
(0 rows)

Wait. No hypertables? After all these years? I checked my table sizes:

zabbix=# \dt+ history*
                                          List of relations
 Schema |     Name     | Type  |    Owner    | Persistence | Access method |    Size    | Description
--------+--------------+-------+-------------+-------------+---------------+------------+-------------
 public | history      | table | zabbix_user | permanent   | heap          | 5589 MB    |
 public | history_log  | table | zabbix_user | permanent   | heap          | 8192 bytes |
 public | history_str  | table | zabbix_user | permanent   | heap          | 22 MB      |
 public | history_text | table | zabbix_user | permanent   | heap          | 258 MB     |
 public | history_uint | table | zabbix_user | permanent   | heap          | 19 GB      |

Let me check how much data we're talking about here:

zabbix=# SELECT COUNT(*) FROM history;
  count
----------
 97261250

zabbix=# SELECT COUNT(*) FROM history_uint;
   count
-----------
 391324991

Holy crap. 391 million records in history_uint alone. And the total database size?

zabbix=# SELECT pg_database_size('zabbix')/1024/1024/1024 AS size_gb;
 size_gb
---------
      51

51GB of unoptimized PostgreSQL tables pretending to be a time-series database.

Performance Reality Check

I ran a simple query to see how bad things really were:

zabbix=# EXPLAIN ANALYZE
SELECT * FROM history
WHERE clock > extract(epoch from now() - interval '7 days')
LIMIT 1000;

Limit  (cost=0.00..89.56 rows=1000 width=24) (actual time=191220.745..194405.232 rows=1000 loops=1)
   ->  Seq Scan on history  (cost=0.00..2903594.59 rows=32420972 width=24) (actual time=191220.734..194396.320 rows=1000 loops=1)
         Filter: ((clock)::numeric > EXTRACT(epoch FROM (now() - '7 days'::interval)))
         Rows Removed by Filter: 76793221
 Planning Time: 499.768 ms
 Execution Time: 194410.019 ms

Over 3 minutes. For 1000 records. Sequential scan on 76 million rows. You know what's worse? When I checked for indexes:

zabbix=# \di history*clock*
Did not find any relation named "history*clock*".

No indexes on the clock column. None. I've been running this setup for years without the most basic optimization.

The Migration Marathon

I wrote a comprehensive migration script and started it at 19:19 in a tmux session:

-- First, create the missing indexes
CREATE INDEX CONCURRENTLY history_clock_idx ON history (clock);
CREATE INDEX CONCURRENTLY history_uint_clock_idx ON history_uint (clock);
CREATE INDEX CONCURRENTLY history_str_clock_idx ON history_str (clock);
CREATE INDEX CONCURRENTLY history_text_clock_idx ON history_text (clock);
CREATE INDEX CONCURRENTLY history_log_clock_idx ON history_log (clock);
CREATE INDEX CONCURRENTLY trends_clock_idx ON trends (clock);
CREATE INDEX CONCURRENTLY trends_uint_clock_idx ON trends_uint (clock);

-- Convert to hypertables
SELECT create_hypertable('history', 'clock', 
    chunk_time_interval => 86400, 
    if_not_exists => true,
    migrate_data => true);

SELECT create_hypertable('history_uint', 'clock', 
    chunk_time_interval => 86400, 
    if_not_exists => true,
    migrate_data => true);

The output was promising:

psql:/tmp/setup_timescaledb.sql:18: NOTICE:  migrating data to chunks
DETAIL:  Migration might take a while depending on the amount of data.
  create_hypertable
----------------------
 (1,public,history,t)

The script finished at 02:43:56. That's 7 hours and 24 minutes of migration. But look at the results:

hypertable_name | num_chunks | compression_enabled |  size
-----------------+------------+---------------------+--------
 history_log     |          0 | f                   | 24 kB
 history         |         68 | t                   | 11 GB
 history_uint    |         69 | t                   | 39 GB
 history_str     |         67 | t                   | 42 MB
 history_text    |         67 | t                   | 324 MB
 trends          |          4 | t                   | 231 MB
 trends_uint     |          4 | t                   | 924 MB

The tables grew in size (11GB vs 5.5GB for history) because now they're properly chunked. Each chunk represents one day of data.

The Performance Transformation

Remember that 3-minute query? Here's the same query after migration:

zabbix=# EXPLAIN ANALYZE 
SELECT * FROM history 
WHERE clock > (EXTRACT(epoch FROM now()) - 604800)::integer 
LIMIT 1000;

Limit  (cost=0.44..30.56 rows=1000 width=24) (actual time=0.042..20.837 rows=1000 loops=1)
   ->  Custom Scan (ChunkAppend) on history  (cost=0.44..151473.73 rows=5028776 width=24) (actual time=0.031..13.146 rows=1000 loops=1)
         Chunks excluded during startup: 62
         ->  Index Scan using _hyper_1_12_chunk_history_clock_idx on _hyper_1_12_chunk
 Planning Time: 5.737 ms
 Execution Time: 25.638 ms

From 194410ms to 25.6ms. That's not a typo. We're talking about a 7,581x improvement.

The magic here is "Chunks excluded during startup: 62". TimescaleDB knows which chunks contain data for the time range I'm querying, so it completely ignores 62 out of 69 chunks. Plus it's using an index scan instead of sequential scan.

Compression and Ongoing Maintenance

I had some issues with compression policies initially. Zabbix uses Unix timestamps (integers), not PostgreSQL timestamps, so the syntax is different:

-- Wrong way (causes errors)
SELECT add_compression_policy('history', INTERVAL '7 days');

-- Right way for Zabbix
SELECT add_compression_policy('history', compress_after => 604800);  -- 7 days in seconds

Here's what my compression policies look like now:

zabbix=# SELECT * FROM timescaledb_information.jobs WHERE proc_name LIKE '%compress%';
 job_id |     application_name      | schedule_interval | config                      
--------+---------------------------+-------------------+-----------------------------
   1000 | Compression Policy [1000] | 1 day             | {"hypertable_id": 1, "compress_after": 604800}
   1001 | Compression Policy [1001] | 1 day             | {"hypertable_id": 2, "compress_after": 604800}

Lessons Learned the Hard Way

Just because you have shared_preload_libraries = 'timescaledb' doesn't mean your tables are hypertables
Check your assumptions. A simple query to timescaledb_information.hypertables would have saved me years of poor performance
Those warnings during TimescaleDB installation about converting existing tables? Yeah, they're important
Even without TimescaleDB, basic indexes on time columns are crucial. I can't believe I was missing those

The real kicker? I've been writing about TimescaleDB best practices while unknowingly running the worst possible setup. My Zabbix was basically doing the database equivalent of driving a sports car in first gear for years.

Now my monitoring system actually uses the time-series optimizations it was supposed to have all along. Graphs load instantly, housekeeping doesn't lock up the database, and I can actually run analytical queries without going for coffee.

Sometimes the best learning experiences come from our biggest screw-ups. This was definitely one of mine.