Solving one of PostgreSQL's biggest weaknesses.

Storing large amounts of data, such as time series data, in a single table is often a challenge when it comes to PostgreSQL. There are other databases out there that can be used for timeseries, however, it does mean giving up many of the features that makes postgres so desirable.

Fortunately, there is another option. TimeScaleDB is a postgres extension that is optimized for time series data. In this video, we provide a brief introduction into TimeScale DB and put it to the test.

This video was sponsored by TimeScaleDB. If you're looking to use a time series database for your own needs, then I highly recommend TimeScale, you can get set up with a free trial using the link below.

Join this channel to get access to perks:

My socials:

My Equipment:

Video Links:

00:00 - Intro
00:56 - Timeseries Data
02:40 - Getting Started
07:33 - HyperTables
12:38 - Continuous Aggregates
16:39 - Results

[dreamsofcode]

To get started with a free trial of TimeScaleDB use the following link!

[stephenreaves]

Also worth pointing out that while p95 time was smaller going to hyper table, the p95 memory went up by a lot

[FunctionGermany]

i gotta respect your dedication to reading and replying to criticism and question in the comments.
when this channel grows more you're not gonna be able to keep up with all comments but if you keep doing this amount of work i think you'll maintain high integrity and trust.
i've seen a bunch of promising tech channels dip their toes too far into the sponsor or hype train pond, releasing content without enough reflection or community consultation (e.g. joshtriedcoding).
i'm looking forward to your future content :)

[yejtroc]

This is great! One point of feedback: instead of writing each parquet file out to disk as a csv, and then COPYing from the csv file, try converting the parquet file to csv and streaming it directly into the STDIN of the COPY command. Forego the reading and writing to disk. I expect this to speed up your load script considerably.

[ilyapunko]

We are using timescale db in prod. But, actually, this solution has a lot of problems yet. You can check issues. But anyway it's pretty simple and affordable variant for timeseries.

[daves.software]

Did you have an index on the started_at column for the regular table?

[vslabs-za]

Interesting, thanks. My big data table is more than a few TB and over 90 billion (no typo) rows. But it's in that "dreaded vendor database", Oracle. It's a 2 node Oracle database cluster on 2017 h/w, processing and analysing over a billion WAN IP traffic flows per day (SQL & PL/SQL code). Would love to try out PostgreSQL though. But then there's my other pursuits (like machine learning on IP traffic for anomaly detection)... and not enough hours in a day. This is a great time for s/w engineers. So many fascinating technologies, products, languages, and concepts to explore.

The Golden Age of Software Technologies. 🙂

[spartan_j]

Thanks for the video. But don't you know about "\timing" in psql?

[gnydick]

These kinds of add-ons are great, but they also come with maintenance and reapair nightmares. Imagine there is an outage of data feeding in, or the db itself suffers an outage. You need a way to be able to reliably and accurately rebuild possibly large amounts of materialized views from arbitrary offsets.

[virennjay]

Subscribed to your channel 5 minutes into this video. Learnt a lot just from setting up the environment for this. Love how easy to follow and replicate the steps were.

[themarksmith]

Excellent video, would love to see more on TimeScaleDB!

[TLAngus]

Looks interesting, but in your performance comparison you could have added an index on started_at to the normal postgres table. I think that would have increased its query speed substantially (albeit increasing storage)

[lhxperimental]

In older versions of Postgres you had to use table inheritance to achieve partioninging. But since last several years, they have declarative partitions although you still have to create the partition tables yourself.

[Stublet]

Thank you for this video. As it happens I was thinking about looking into timescale as well as influx and a few other time time series dbs out there. Postgres is not my favorite db of choice, but I"m a big proponent of using the right tool for the job and timescale checks a lot of my boxes here. Also props for featuring neovim. :)

[Terandium]

So, self hosting for FREE is too much work.. using a docker image is also to much work? So lets use this way more expensive cloud instance that totally isn't sponsoring me...

[charlesm.]

This channel is slowly becoming my favorite place on the internet

[Kanikkl]

Awesome demo! Thank you! This really opens up another technological layer to me. In the past I had no connection to db optimization other than using indexes in mysql. But this is so much more and so useful! This is actually applicable to an use case that has been bugging me for years. Different type of timeseries data that we are currently cashing through a php scheduler in a redis storage. Will share this with the team 🎉

[chudchadanstud]

If I plan on running a db locally I usually just stil to sqlite. It's 3x faster than redis fully optimised and you can ignore the n+1 problem.

[hansiboy]

Lovely video! I would love to see this used in a project using sensor data or another type of real time data

[yassinebouchoucha]

more advanced reportings on that data, Also could you make example using compression and hyperfunctions ?