title,content,url
"How to Build a Weather Station With Elixir, Nerves, and TimescaleDB","This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Alexander Koutmos, author of the Build a Weather Station with Elixir and Nerves book, joins us to share how he uses Grafana and TimescaleDB to store and visualize weather data collected from IoT sensors.About the teamThe bookBuild a Weather Station with Elixir and Nerveswas a joint effort between Bruce Tate, Frank Hunleth, and me.I have been writing software professionally for almost a decade and have been working primarily with Elixir since 2016. I currently maintain a few Elixir libraries onHexand also runStagira, a software consultancy company.Bruce Tateis a kayaker, programmer, and father of two from Chattanooga, Tennessee. He is the author of more than ten books and has been around Elixir from the beginning. He is the founder ofGroxio, a company that trains Elixir developers.Frank Hunlethis an embedded systems programmer, OSS maintainer, and Nerves core team member. When not in front of a computer, he loves running and spending time with his family.About the projectIn the Pragmatic Bookshelf book,Build a Weather Station with Elixir and Nerves, we take a project-based approach and guide the reader to create a Nerves-powered IoT weather station.For those unfamiliar with the Elixir ecosystem,Nervesis an IoT framework that allows you to build and deploy IoT applications on a wide array of embedded devices. At a high level, Nerves allows you to focus on building your project and takes care of a lot of the boilerplate associated with running Elixir on embedded devices.The goal of the book is to guide the reader through the process of building an end-to-end IoT solution for capturing, persisting, and visualizing weather data.Assembled weather station hooked up to development machine.One of the motivating factors for this book was to create a real-world project where readers could get hands-on experience with hardware without worrying too much about the nitty-gritty of soldering components together. Experimenting with hardware can often feel intimidating and confusing, but with Elixir and Nerves, we feel confident that even beginners get comfortable and productive quickly. As a result, in the book, we leverage a Raspberry Pi Zero W along with a few I2C enabled sensors to capture weather and environmental data. In all, the reader will capture and persist into TimescaleDB the current: altitude, atmospheric pressure, temperature, CO2 levels,TVOClevels, and the ambient light.Once the environmental data is captured on the Nerves device, it is published to a backend REST API and stored in TimescaleDB for later analytics/visualization. Luckily, TimescaleDB is an extension on top of PostgreSQL, allowing Elixir developers to use existing database tooling likeEctoto interface with time-series enabled tables.After the time-series weather data is stored in TimescaleDB, we walk the reader through how to visualize this data using the popular open-source visualization toolGrafana.Using Grafana for visualizing the weather was an easy choice given that Grafana natively supports TimescaleDB and is able to easily plot time-series data stored in TimescaleDB hypertables.✨Editor’s Note:Check outGrafana 101 video seriesandGrafana tutorialsto learn everything from building awesome, interactive visualizations to setting up custom alerts, sharing dashboards with teammates, and solving common issues.The diagram shows all of the various components of the weather station system and how they interact with one another.By the end of the book, readers have a fully-featured IoT application and API backend that can power a live Grafana dashboard in order to plot their TimescaleDB data published from their Nerves weather station.Screenshot of Grafana dashboard, showing various graphs for various weather data📖If you are interested in learning about how to build an end-to-end IoT weather monitoring solution, be sure tocheck out the book and the accompanying code. If you are interested in learning more about Nerves and Elixir,check out the Nerves documentation.Choosing (and using!) TimescaleDBFrom the onset of the book, we knew that we wanted to use a purpose-built time-series database to persist the weather station data. We wanted the project to be as realistic as possible and something that could possibly be expanded for use in the real world.With that goal in mind, TimescaleDB was an obvious choice given that PostgreSQL has become a ubiquitous database and it has great support in the Elixir community. In addition,leveraging TimescaleDB on top of PostgreSQL does not add a tremendous amount of overhead or complexity and allows new users to easily leverage the benefits of a time-series database without having to learn any new query languages or databases. Specifically, all it took for readers to start leveraging TimescaleDB was to run a single SQL command in their database migration:SELECT create_hypertable('weather_conditions', 'timestamp').""It’s this kind of pragmatism and ease of use that makes TimescaleDB a great time-series database for projects both small and large. ""-Alexander KoutmousAll in all, leveraging TimescaleDB as the time-series database for the project worked out great and allowed us to show readers how they can set up a production-ready IoT project in a relatively short amount of time.✨Editor’s Note:To start with TimescaleDB today,sign up for a free 30-day trialorinstall TimescaleDB on your own server.Getting started advice & resourcesAny time we had questions about the inner workings of TimescaleDB, how to set it up, or what the various configuration options are, we turned to the official TimescaleDB docs. Some of the articles that helped us get started included:•Using TimescaleDB via Docker• Understanding some ofthe fundamental TimescaleDB concepts• Getting an overview of some of theTimescaleDB best practicesWe’d like to thank Alex, Bruce, and Frank for sharing their story, as well as for writing a book that makes building full-stack IoT solutions accessible for complete beginners. We congratulate them and the entire Nerves community on their success, and we cannot wait to read the final version of their book that will be released in January 2022 🎊We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescaleHeroes program.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-to-build-a-weather-station-with-elixir-nerves-and-timescaledb/
CloudQuery on Using PostgreSQL for Cloud Assets Visibility,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Ron Eliahu, CTO and co-founder at CloudQuery, joins us to share how they transform data about cloud assets into PostgreSQL tables to give developers visibility into the health of cloud infrastructure. Thanks toTimescaleDB, CloudQuery users manage their data more transparently while maintaining scalability.CloudQueryis an open-source cloud asset inventory powered by SQL. CloudQuery extracts, transforms, and loads cloud assets intonormalizedPostgreSQL tables, enabling developers to assess, audit, and monitor the configurations of their cloud assets.Cloud asset inventory is a key component to solve various challenges in the cloud:Cloud Infrastructure Monitoring, Visibility, and Search: Give developers, SREs, DevOps, and security engineers a streamlined way to gain visibility and perform a wide range of tasks. These tasks include security analytics, fleet management auditing, governance, and cost.Security & Compliance: Turn security and compliance tasks to data problems and solve them with the best tools and practices in DataOps. UseCloudQuery Policiesto codify, version control, and automate security and compliance rules with SQL.Infrastructure as Code (IaC) Drift Detection: CloudQuery leverages its asset inventory to quickly detect drift against IaC (Terraform, more to come) which you can run both in the CI and locally.📖Read CloudQuery´sannouncement blogabout releasing CloudQuery History in Alpha and adding support for TimescaleDB.About the teamWe started CloudQuery a year ago, to solve the cloud asset visibility problem, and quickly gained traction. We are currently a small but mighty team of open-source and cloud security enthusiasts, spread all around the world!A little about myselfRon Eliahu, I am the CTO and co-founder at CloudQuery, I am an engineer at heart, I love building open source projects and working with anything database-related.About the projectQueryable cloud asset inventory is key in solving a lot of core challenges in the cloud such as security, compliance, search, cost, and IaC drift detection. That is why we started CloudQuery and followed a few key decisions:PostgreSQL- The most used database in the world with a huge ecosystem of business intelligence and visualization tools.Open-source- To cover a huge amount of API and cloud providers we decided to make this open-source where everyone can contribute without being blocked by a vendor.Pluggable Architecture with CloudQuery SDK- Writing plugins, extracting configuration data, transforming it, and loading it to PostgreSQL requires a lot of boilerplate code. To scale this and improve the developer’s experience both internally and externally we releasedCloudQuery SDK.Normalized PostgreSQL tables inDataGripcontaining data about cloud assets from Microsoft AzureAnother key observation and requirement that we saw early on for a cloud asset inventory is the ability to not only query the current state but also go back in time. This is super useful for tasks such as forensics, post-mortems, compliance, and more.This feature required us to maintain historical snapshots in PostgreSQL and we started to look out for a solution, which was quite a journey for us.Choosing (and using!) TimescaleDBFirst attempt: PostgreSQL PartitioningWith some good experience ofPostgreSQLunder our belt, the first thing we tried isPostgreSQL partitioningPretty quickly it turned out to be not as easy as expected, hard to maintain and manage, lacking easily creatable retention policies, and bucketing queries. Given our philosophy is to integrate with best-in-class tools and focus our development efforts on our core business use-cases we started looking for an alternative solution.✨Editor’s Note:For more comparisons and benchmarks, seehow TimescaleDB compares to InfluxDB, MongoDB, AWS Timestream, vanilla PostgreSQL, and other time-series database alternativeson various vectors, from performance and ecosystem to query language and beyond.Second attempt: TimescaleDBGiven CloudQuery uses PostgreSQL under the hood, supporting historical snapshots in a scalable way usually involves using partitioning. TimescaleDB´screate_hyperfunctionsfeature allows us to do just that in a simple and effective way giving our users a transparent and automated way to manage their data while still maintaining scalability.Current CloudQuery architecture diagramCloudQuery transforms cloud resources into tables, some of these resources have a complex data structure, and are split into multiple relational tables, some of which are hypertables. In order to retain data integrity, we use foreign key relationships (withON DELETE CASCADE) to the root resource table. With these foreign keys in place, if a reference to a cloud resource is deleted (for instance a S3 bucket), the downstream data is removed.While TimescaleDB hypertablesdosupport using foreign keys that reference a regular PostgreSQL table, hypertable cannot be thesource reference of a foreign keyIn our case, some of our ""root"" reference tables are hypertables which meant that we had to come up with another way to do the cascading deletes to retain data integrity.A common alternative is to create trigger functions that will cause a delete on the relation table if a row is deleted in the parent table, the issue here is that some resources in CloudQuery can have three or more levels of relations and we didn’t want to create many queries to solve this, so we came up with the following functions to easily create the deletion cascade.First, we wanted a trigger function that will delete our relational table. We used trigger arguments to pass the relation table name its foreign key name so we can delete the data in the relation table. Full code is availablehere.CREATE OR REPLACE FUNCTION history.cascade_delete()
					RETURNS trigger
					LANGUAGE 'plpgsql'
					COST 100
					VOLATILE NOT LEAKPROOF
				AS $BODY$
				BEGIN
					BEGIN
						IF (TG_OP = 'DELETE') THEN
							EXECUTE format('DELETE FROM history.%I where %I = %L AND cq_fetch_date = %L', TG_ARGV[0], TG_ARGV[1], OLD.cq_id, OLD.cq_fetch_date);
							RETURN OLD;
						END IF;
						RETURN NULL; -- result is ignored since this is an AFTER trigger
					END;
				END;
				$BODY$;Then, we call the create trigger function on the root table and pass these arguments to the child. Full code is availablehere.CREATE OR REPLACE FUNCTION history.build_trigger(_table_name text, _child_table_name text, _parent_id text)
					RETURNS integer
					LANGUAGE 'plpgsql'
					COST 100
					VOLATILE PARALLEL UNSAFE
				AS $BODY$
				BEGIN
					IF NOT EXISTS ( SELECT 1 FROM pg_trigger WHERE tgname = _child_table_name )  then
					EXECUTE format(
						'CREATE TRIGGER %I BEFORE DELETE ON history.%I FOR EACH ROW EXECUTE PROCEDURE history.cascade_delete(%s, %s)'::text,
						_child_table_name, _table_name, _child_table_name, _parent_id);
					return 0;
					ELSE
						return 1;
					END IF;
				END;
				$BODY$;To sum it all up, we built two generic SQL functions to make sure all our hypertables and relational hypertables data get deleted if the root table has any data removed.Future plansCompliance overtime is a common request so we are working on integrating the results ofCloudQuery Policieswith TimescaleDB so you can monitor and visualize compliance with TimescaleDB and Grafana.Getting started advice & resourcesBefore you jump into implementing your own partition strategy, definitely give TimescaleDB a try. It can save you a lot of development time and make your product more robust.The Timescale documentationis a great place to start.🗞️Subscribe to our newsletter atcloudquery.ioand join ourDiscordto hear about our upcoming and latest features.We’d like to thank Ron and all folks at the CloudQuery team for sharing their story, as well as for their work to transform complex and scattered cloud assets data into structured and easily accessible tables enabling developers to monitor their cloud inventory.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/cloudquery-on-using-postgresql-for-cloud-assets-visibility/
How a Data Scientist Is Building a Time-Series Forecasting Pipeline Using TimescaleDB,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Andrew Engel, chief data scientist atRasgo,explains how he has been using TimescaleDB to build a time-series forecasting side project. He’s working on proofs of concept of machine learning pipelines to pull relevant data— from sports statistics to betting odds and finance pricing data—to make predictions for better decision-making or bets.About the UserI am currently the chief data scientist at Rasgo. Before joining Rasgo, I worked as a data scientist and data science leader for over 10 years across various fields. I received my Ph.D. in Systems and Industrial Engineering with a focus on decision-making under uncertainty and probability modeling from the University of Arizona. After my Ph.D., I was an assistant professor of Applied Mathematics at Towson University, where I began working on data science problems.As my career progressed, I found myself doing less and less technical work in data science and more time spent on team building, interacting with management and customers, and basically talking about data science instead of doing data science. This culminated in 2016 when I took a role withDataRobotto help their prospects and customers use DataRobot’s machine learning platform to implement their data science initiatives successfully.While this was, and still is, fulfilling work, I missed the technical work that originally drew me into data science. DataRobot encouraged us to do this (and leverage their product). In short, I became the DataRobot Marketing team's go-to content writer about sports analytics and betting. As part of this, I would build machine learning models to predict the outcome of sporting events. I regularly wrote blogs on my predictions for Grand Slam tennis, the Super Bowl (and NFL playoffs), the Champions League knockout stage, and the Premier League.Because of this and contacts developed over the years as a data scientist, multiple people have asked me to advise and even build proofs of concept of machine learning pipelines to automate both sports betting predictions and market models (from financial markets to sports betting markets in aid of monetizing these sports predictions).About the ProjectAndrew’setu package on GitLabIn my spare time, I have been building proofs of concept of machine learning pipelines to pull relevant data (this could be sports statistics, historical sports betting odds, or financial market pricing data), use machine learning to make predictions, and share those predictions back to the organization to help them make decisions or bets.Initially, all of this was written in Python, usingAirflowto orchestrate the entire pipeline. I always used PostgreSQL as my database and have been a long-time user for several reasons. While I was a graduate student, I supported myself as a web programmer during the first web boom, and we used Oracle as our database. During this job, I learned SQL and saw the power of enterprise-ready databases. For personal projects, I could afford neither Oracle itself nor the hardware to run it. Evaluating the open-source SQL databases, PostgreSQL was by far the most feature-complete (and similar to what I already knew). This is still true today.But, back to my project. Using PostgreSQL, I would write the original data, the engineered features for modeling, and the final predictions into the database for model monitoring and future model development.In all of these cases, the underlying data is event data and has time-series characteristics. This means that as a data scientist, I was interested in capturing how the data changed over time. For example, when modeling a sports betting market, I was interested in how the betting line or odds would change over time. Was it rising, falling, oscillating, etc.? Capturing this information as machine learning features was key to my models performing well. I typically used a Python library calledtsfreshto build these features.✨Editor's Note:Do you know what time-series data is?The answer is in this blog post.As the amount of data grew, I spent most of the time in the pipeline pulling data from the database, building the features, and pushing the results back to the database.Choosing (and Using!) TimescaleDBAs I was first working on these sorts of problems in my spare time, DataRobot asked me to lead their go-to-market efforts in the sports and entertainment space. In this new role, I began talking with several Formula 1 teams about possibly using DataRobot in their workflows.“During the investigation of TimescaleDB, I realized I could use PostgreSQL’s support of custom extensions built in Python to allow me to call tsfresh functions directly within the database without the expense of moving the data out or pulling it back in”As part of these conversations, I learned aboutKdb+ from KX Systems. This is a high-performance columnar time-series database used by F1 teams to store and process sensor data collected from their cars (and simulations). Financial institutions also use it to store and process market data. More intriguing from my point of view was that Kdb+ included a reimplementation of tsfresh within the database.I understood the power of Kdb+, but its price was well out of my budget for these proofs of concept. Still, I liked the idea of a database that supported time-series data and could process it efficiently.I have been using PostgreSQL for years and including it in the technology stack I used in these proofs of concept. While other databases supported time series, TimescaleDB combined the familiarity of PostgreSQL, making it the clear choice.During the investigation of TimescaleDB, I realized I could use PostgreSQL’s support of custom extensions built in Python to allow me to call tsfresh functions directly within the database without the expense of moving the data out or pulling it back in.In addition, TimescaleDB’s time_bucket() function would allow me to perform my time-series aggregations for arbitrary time intervals (eg., on a daily or weekly basis) as opposed to estimating the time period from a set number of rows. This was a huge improvement over using either tsfresh in Python (where it worked at a set number of rows) or a window function in PostgreSQL.✨Editor's Note:Read our docs to learn how you can best use TimescaleDB’s time_bucket() hyperfunction.I then implemented all of the tsfresh functions as custom extensions in Python and built custom types to pass the necessary information into the function and custom aggregations to perform the aggregations to get the data ready for the time-series feature.As expected, there were significant benefits to moving this processing into the database. In addition to eliminating the time to extract and insert the data, the database handled running these calculations in parallel much more simply than trying to manage them within Python.“By releasing this as open source, I hope to help anyone [...] working on any sort of time-series problem to be able to fully leverage the power of TimescaleDB to manage their time-series data”It was not without its drawbacks, however. First, PL/Python is untrusted in PostgreSQL and limits the types of users that could use these functions. More importantly, each time the function was called, the Python interpreter was started, and the tsfresh library and its dependencies were loaded. This meant that most of the processing time was spent on getting ready to perform the calculations instead of the actual calculations. The speedup was significantly less than expected.At this point, I saw promise in this approach but needed to make it faster. This led me to reimplement the tsfresh functions in C and create custom C extensions for PostgreSQL and TimescaleDB. With these C extensions, I was able to generate a performance improvement between 10-100 times faster than the corresponding performance of the PL/Python version.✨Editor's Note:If you want to extend aggregate functions in PostgreSQL but find it daunting to create and maintain them in C,check out this video where we explain how PGX can help create, test, and deploy aggregates using Rust.Current Deployment & Future PlansWhen I saw this performance, I felt I needed to clean up my code and make it available to the community as open source. This led to two packages:etu, which contains the reimplementation of tsfresh feature calculators in C, andpgetu, which includes the C extension that wraps etu and makes it available as functions in PostgreSQL and TimescaleDB.As an example, these functions can be called in a SELECT statement in TimescaleDB as:SELECT 
  time_bucket(interval '1 months', date) AS month,
  absolute_sum_of_changes(amount, date) AS amount_asoc,
  approximate_entropy(amount, date, 1, 0.1) AS amount_ae,
  fft_aggregated(amount, date, ARRAY['centroid', 'variance', 'skew', 'kurtosis']) as ammount_fft_agg,
  median(amount) as median_amount,
  mode(amount) as mode_amount
FROM transactions
GROUP BY month
ORDER BY month;Here’s another example: this is looking at the power output of a windmill based on wind speed.SELECT TIME_BUCKET(interval '1 hour, time) AS hour,
autocorrelation(windspeed, time, 1) AS windspeed_autocorrelation_1,
autocorrelation(output, time, 1) AS output_autocorrelation_1,
count_above_mean(output) AS output_cam,
count_below_mean(output) AS output_cbm,
energy(windspeed, time) AS windspeed_energy,
fft_coefficient(windspeed, time, ARRAY[1, 1, 2, 2], ARRAY['real', 'imag', 'real', 'imag'] AS windspeed_fft_coeff_json,
number_cwt_peaks(windspeed, time, 5) AS windspeed_number_wavelet_peaks_5,
number_cwt_peaks(output, time, 5) AS output_number_wavelet_peaks_5
FROM sensor_data
GROUP BY hour
ORDER BY hour;This makes it really easy to perform time-series feature engineering directly in the database.By releasing this as open source, I hope to help anyone working as a data scientist on time-series-like problems quickly generate features directly within their database and anyone else working on any sort of time-series problem to be able to fully leverage the power of TimescaleDB to manage their time-series data.As an open-source project, I hope to see people benefit from this in their personal projects and hopefully find enough value to be interested in helping improve both of these libraries going forward.RoadmapThis project is the culmination of about one year of part-time development work. I spent the first third building a version using Python, the following third understanding how to build C extensions, writing proof of concept versions of some of the functions, and testing them to determine if the speedup was worthwhile (it was a 10x to 100x in extreme cases).Regarding feedback, I have just released it and received a small amount of positive press on thePostgreSQL subredditbut limited feedback from others. I have also shared it with a number of the data scientists in my network, and the feedback has been overwhelmingly positive.Currently, etu and pgetu support most of the feature calculators in tsfresh. My next step is to implement the approximately 15 calculations that I have not yet finished. Once that is done, I would like to increase the functions these libraries support.If you need more information about this project or want to discuss it, I can be reached and followed onLinkedInandTwitter.We’d like to thank Andrew for sharing his story on how he is creating proofs of concept of machine learning pipelines for time-series forecasting using TimescaleDB.We’re always keen to feature new community projects and stories on our blog. If you have a project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-a-data-scientist-is-building-a-time-series-forecasting-pipeline-using-timescaledb-and-helping-others-perform-time-series-engineering-directly-in-the-database/
How Conserv Safeguards History: Building an Environmental Monitoring and Preventive Conservation IoT Platform,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Nathan McMinn, CTO and co-founder at Conserv, joins us to share how they’re helping collections care professionals around the world understand their collections’ environments, make decisions to optimize conditions, and better protect historical artifacts - no IT department required.Think for a moment about your favorite museum. All of the objects it houses exist to tell the stories of the people who created them - and of whose lives they were a part - and to help us understand more about the past, how systems work, and more. Without proper care, which starts with the correct environment, those artifacts are doomed to deteriorate and will eventually be lost forever (read more about the factors that cause deterioration).Conservstarted in early 2019 with a mission to bring better preventive care to the world’s collections. We serve anyone charged with the long-term preservation of physical objects, from paintings and sculptures to books, architecture, and more. (You’ll often hear our market segment described as “GLAM”: galleries, libraries, archives, and museums.) At the core, all collections curators need to understand the environment in which their collections live, so they can develop plans to care for those objects in the short, mid, and long-term.While damagecancome in the form of unforeseen and catastrophic events, like fire, theft, vandalism, or flooding, there aremanyissues that proactive monitoring and planning can prevent, such as: mold growth, color fading associated with light exposure, and mechanical damage caused by fluctuating temperature and relative humidity.At Consev, we’ve built a platform to help collections gather the data required to understand and predict long-term risks, get insights into their data, and plan for how to mitigate and improve the preservation environment. Today, we’re the only company with an end-to-end solution - sensors to screen - focused on preventive conservation.  Collections like theAlabama Department of Archives and Historyand various others rely on us to develop a deep understanding of their environments.About the teamWe’re a small team where every member makes a big impact. Currently, we’re at 6 people, and each person plays a key role in our business:Austin Senseman, our co-founder and CEO, often describes his background as “helping people make good decisions with data.” He has extensive experience in analytics and decision support – and a history of using data to guide organizations to their desired outcomes.Nathan McMinn(this is me!), our other co-founder and CTO, comes from the development world. I’ve spent my career leading engineering teams and building products people love, most notably in the enterprise content management sector.Ellen Orr, a museum preparator turned fantastic collections consultant.Cheyenne Mangum, our talented frontend developer.Melissa King, a preventative conservator who recently joined to help us build better relationships with more collections.Bhuwan Bashel, who is joining in a senior engineering role (oh yeah, he’ll get some TimescaleDB experience quickly!).About the projectWe collect several types of data, but the overwhelming majority of the data we collect and store in TimescaleDB is IoT sensor readings and related metrics. Our solution consists of fleets of LoRaWAN sensors taking detailed environmental readings on a schedule, as well as event-driven data (seethis articlefor an overview of LoRaWAN sensors, use cases, and other details).So, what ends up in our database is a mix of things like environmental metrics (e.g., temperature, relative humidity, illuminance, and UV exposure), sensor health data (e.g., battery statistics), and data from events (e.g., leak detection or vibration triggers).We also capture information about our customers’ locations - such as which rooms are being monitored -  and data from human observations - such as building or artifact damage and pest sightings - to give our sensor data more context and some additional “texture.” It’s one thing to collect data from sensors, but when you pair that with human observations, a lot of interesting things can happen.Our UI makes it simple to add a human observation to any data point, collection, or date.See our docsfor more details.For us, it is all about scale and performance.We collect tens of thousands of data points each day, and our users think about their metrics and their trends over years, not days.Also, like anybody else, our users want things to feel fast. So far, we’re getting both from TimescaleDB.With those criteria met, our next challenge is how to use that data to (1) provide actionable insights to our customers, allowing them to ask and answer questions like “what percentage of time is my collection within our defined environmental ranges?” and (2) offer in-depth analysis and predictions, like possible mold and mechanical damage risks.This iswhere we’ve gotten the most value out of TimescaleDB: the combination of a performant, scalable repository for our time-series data, the core SQL features we know and trust, and the built-in time-series functionalityprovided by TimescaleDB let us build new analysis features much faster.Example of our Collections focused analytics dashboard -see our docsfor more details.✨Editor's Note:To learn how we’ve architected TimescaleDB to support joining time-series data with other relational data,check out our data model documentationandfollow our Hello, Timescale! tutorialto try it out for yourself.Using (and choosing) TimescaleDBI first found out about TimescaleDB at anAll Things Open conferencea few years ago; it was good timing, since we were just starting to build out our platform. Our first PoC used ElasticSearch to store readings, which we knew wouldn’t be a permanent solution. We also looked at InfluxDB, and, while Amazon’s Timestream looked promising, it wasn’t released yet.Our database criteria was straightforward; we wanted scale, performance, and the ability to tap into the SQL ecosystem. After evaluating TimescaleDB’s design, we were confident that it would meet our needs in the first two categories, but so would many other technologies.What ultimately won me over was the fact that it’s PostgreSQL.I’ve used it for years in projects of all sizes; it works with everything, and it’s a proven, trustworthy technology – one less thing for me to worry about.✨Editor’s Note:To see how TimescaleDB stacks up to alternatives, read ourInfluxDB vs. TimescaleDBandAmazon Timestream vs. TimescaleDBbenchmark posts (included in-depth performance, scale, and developer experience comparisons) andexplore our at-a-glance comparisons.Current deployment & use casesOur stack is fairly simple, standard stuff for anyone that has a UI → API → database pattern in their application. Our secret sauce is in how well we understand our users and their problems, not our architecture :).Some of our future plans will require us to get more complex, but for now we’re keeping it as simple and reliable as possible:Node.js services running in Docker containers on AWS ECS, with TimescaleDB on the database tierReact.js frontendMobile app built with FlutterIn the near future, I’d like to move over toTimescaleDB’s hosted cloud offering. As we get bigger, that will be something we evaluate.In line with the above, our queries themselves aren’t that clever, nor do they need to be. We make heavy use of PostgreSQL window functions, and the biggest features we benefit from, in terms of developer ergonomics, are TimescaleDB’s built-in time-series capabilities:time_bucket,time_bucket_gapfill,histograms,last observation carried forward, and a handful of others. Almost every API call to get sensor data uses one of those.We haven’t usedcontinuous aggregatesorcompressionyet, but they’re on my to-do list!✨Editor’s Note:In addition to the documentation links above,check out our continuous aggregates tutorialfor step-by-step instructions and best practices, andread our compression engineering blogto learn more about compression, get benchmarks, and more.Getting started advice & resourcesTimescaleDB gives you more time to focus on end-user value, and less time focusing on things like “Can I connect tool x to my store?” or “How am I going to scale this thing?”If you’re considering TimescaleDB or databases in general, and are comfortable with Postgres already, try it. If you want the biggest ecosystem of tools to use with your time-series data, try it.  If you think SQL databases are antiquated and don’t work for these sorts of use cases, try it anyway - you might be surprised.For anybody out there thinking about an IoT project or company, as a technologist, it’s really tempting to focus on everything before data gets to the screen. That’s great, and you have to get those things right, but that’s just table stakes.Anybody can collect data points and put a line graph on a screen. That’s a solved problem.Yourchallenge is to develop all the context around the data, analyze it in that context, and present it to your users in the language and concepts they already know.TimescaleDB can help you do that, by giving you more time to focus on end-user value, and less time focusing on things like “Can I connect tool x to my store?” or “How am I going to scale this thing?”Other than those words of advice, there’s nothing that hasn’t already been covered in-depth by people in the PostgreSQL community that are FAR smarter than I am :).We’d like to give a big thank you to Nathan and the Conserv team for sharing their story and,more importantly, for their commitment to helping collections’ keep history alive. As big museum fans, we’re honored to play a part in the tech stack that powers their intuitive, easy-to-use UI and robust analytics 💛We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@lacey butler), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-conserv-safeguards-history-building-an-environmental-monitoring-and-preventative-analytics-iot-platform/
How Messari Uses Data to Open the Cryptoeconomy to Everyone,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Adam Inoue, Software Engineer at Messari, joins us to share how they bring transparency to the cryptoeconomy, combining tons of data about crypto assets with real-time alerting mechanisms to give investors a holistic view of the market and ensure they never miss an important event.Messariis a data analytics and research company on a mission to organize and contextualize information for crypto professionals. Using Messari, analysts and enterprises can analyze, research, and stay on the cutting edge of the crypto world – all while trusting the integrity of the underlying data.This gives professionals the power to make informed decisions and take timely action. We are uniquely positioned to provide an experience that combines automated data collection (such as our quantitativeasset metricsandcharting tools) withqualitative researchandmarket intelligencefrom a global team of analysts.Our users range from some of the most prominent analysts, investors, and individuals in the crypto industry to top platforms like Coinbase, BitGo, Anchorage, 0x, Chainanalysis, Ledger, Compound, MakerDAO, andmany more.About the teamI have over five years of experience as a back-end developer, in roles where I’ve primarily focused on high-throughput financial systems, financial reporting, and relational databases to support those systems.After some COVID-related career disruptions, I started at Messari as a software engineer this past April (2021). I absolutely love it. The team is small, but growing quickly, and everyone is specialized, highly informed, and at the top of their game. (Speaking of growing quickly,we’re hiring!)We’re still small enough to function mostly as one team. We are split into front-end and back-end development. The core of our back-end is a suite of microservices written in Golang and managed by Kubernetes, and I - along with two other engineers - “own” managing the cluster and associated services. (As an aside, another reason I love Messari: we’re a fully remote team: I’m in Hawaii, and those two colleagues are in New York and London. Culturally, we also minimize meetings, which is great because we’re so distributed,andwe end up with lots of time for deep work.)From a site reliability standpoint, my team is responsible for all of the back-end APIs that serve the live site, ourpublic API, our real-time data ingestion, the ingestion and calculation of asset metrics, and more.So far, I’ve mostly specialized in the ingestion of real-time market data – and that’s where TimescaleDB comes in!About the projectMuch of our website is completely free to use, but we haveProandEnterprisetiers that provide enhanced functionality. For example, our Enterprise version includesIntel, a real-time alerting mechanism that notifies users about important events in the crypto space (e.g., forks, hacks, protocol changes, etc.) as they occur.We collect and calculate a huge catalog ofcrypto-asset metrics, like price, volume, all-time cycle highs and lows, and detailed information about each currency. Handling these metrics uses a relatively low proportion of our compute resources, while real-time trade ingestion is a much more resource-intensive operation.Our crypto price data is currently calculated based on several thousand trades per second (ingested from partners, such asKaikoandGemini), as well as our own on-chain integrations withThe Graph. We also keep exhaustive historical data that goes as far back as the dawn of Bitcoin. (You can read more about the history of Bitcoinhere.)Messari dashboard, with data available atmessari.iofor freeOur data pipelines are the core of the quantitative portion of our product – and are therefore mission-critical.For our site to be visibly alive, the most important metric is our real-time volume-weighted average price (VWAP), although we calculate hundreds of other metrics on an hourly or daily basis. We power our real-time view through WebSocket connections to our back-end, and we keep the latest price data in memory to avoid having to make constant repeated database calls.Everything “historical” - i.e., even as recently as five minutes ago - makes a call to our time-series endpoint.Any cache misses there will hit the database, so it’s critical that the database is highly available.We use the price data to power the quantitative views that we display on our live site, and we also directly serve our data to API users. Much of what we display on our live site is regularly retrieved and cached by a backend-for-frontend GraphQL server, but some of it is also retrieved by HTTP calls or WebSocket connections from one or more Go microservices.The asset view of Messari dashboard, showing various price stats for a specific currency.The accuracy of our data is extremely important because it’s public-facing and used to help our users make decisions. And, just like the rest of the crypto space, we are also scaling quickly, both in terms of our business and the amount of data we ingest.Choosing (and using!) TimescaleDBWe’re wrapping up a complete transition to TimescaleDB fromInfluxDB. It would be reasonable to say that we used InfluxDB until it fell over; we asked it to do a huge amount of ingestion and continuous aggregation, not to mention queries around the clock, to support the myriad requests our users can make.Over time, we pushed it enough that it became less stable, so eventually, it became clear that InfluxDB wasn’t going to scale with us. Thus,Kevin Pyc(who served as the entire back-end “team” until earlier this year) became interested in TimescaleDB as a possible alternative.The pure PostgreSQL interface and impressive performance characteristics sold him on TimescaleDB as a good option for us.From there, the entire tech group convened and agreed to try TimescaleDB. We were aware of its performance claims but needed to test it out for ourselves, for our exact use case. I began by reimplementing our real-time trade ingestion database adapter on TimescaleDB – and on every test, TimescaleDB blew my expectations out of the water.The most significant aspects of our system are INSERT and SELECT performance.INSERTs of real-time trade data are constant, 24/7, and rarely dip below 2,000 rows per second. At peak times, they can exceed 4,500—and, of course, we expect this number to continually increase as the industry continues to grow and we see more and more trades.SELECT performance impacts our APIs’ response time for anything we haven’t cached; we briefly cache many of the queries needed for the live site, but less common queries end up hitting the database.When we tested these with TimescaleDB, both of our SELECT and INSERT performance results flatly outperformed InfluxDB. In testing, even thoughTimescale Cloudis currently only located in us-east-1 and most of our infrastructure is in an us-west region, we saw an average of ~40ms improvement in both types of queries. Plus, we could batch-insert 500 rows of data, instead of 100, with no discernible drop in execution time relative to InfluxDB.These impressive performance benchmarks, combined with the fact that we can use Postgres with foreign key relationships to derive new datasets from our existing ones (which we weren’t able to do with InfluxDB) are key differentiators for TimescaleDB.✨Editor’s Note:For more comparisons and benchmarks, seehow TimescaleDB compares to InfluxDB, MongoDB, AWS Timestream, and other time-series database alternativeson various vectors, from performance and ecosystem to query language and beyond. For tips on optimizing your database insert rate, see our13 ways to improve PostgreSQL insert performanceblog post.We are also really excited about continuous aggregates. We store our data at minute-level granularity, so any granularity of data above one minute is powered by continuous queries that feed a rollup table.In InfluxDB-world, we had a few problems with continuous queries: they tended to lag a few minutes behind real-time ingestion, and, in our experience, continuous queries would occasionally fail to pick up a trade ingested out of order—for instance, one that’s half an hour old—and it wouldn’t be correctly accounted for in our rollup queries.Switching these rollups to TimescaleDB continuous aggregates has been great; they’re never out of date, and we can gracefully refresh the proper time range whenever we receive an out-of-order batch of trades or are back-filling data.At the time of writing, I’m still finalizing our continuous aggregate views—we had to refresh them all the way back to 2010! — but all of the other parts of our implementation are complete and have been stable for some time.✨Editor’s Note:Check out thecontinuous aggregates documentationand followthe step-by-step tutorialto learn how to utilize continuous aggregates for analyzing the NFL dataset.Current deployment & future plansAs I mentioned earlier, all of the core services in our back-end are currently written in Go, and we have some projects on the periphery written in Node or Java. We don't currently need to expose TimescaleDB to any project that isn't written in Go. We useGORMfor most database operations, so we connect to TimescaleDB with agorm.DBobject.We try to use GORM conventions as much as possible; for TimescaleDB-specific operations likemanaging compression policiesor thecreate_hypertablestepwhere no GORM method exists, we write out queries literally.For instance, we initialize our tables usingrepo.PrimaryDB.AutoMigrate(repo.Schema.Model), which is a GORM-specific feature, but we create new hypertables as follows:res := repo.PrimaryDB.Table(tableName).Exec(
		fmt.Sprintf(""SELECT create_hypertable('%s', 'time', chunk_time_interval => INTERVAL '%s');"",
			tableName, getChunkSize(repo.Schema.MinimumInterval)))Currently, our architecture that touches TimescaleDB looks like this:The current architecture diagram.We use Prometheus for a subset of our monitoring, but, for our real-time ingestion engine, we’re in an advantageous position: the system’s performance is obvious just by looking at our logs.Whenever our database upsert backlog is longer than a few thousand rows, we log that with a timestamp to easily see how large the backlog is and how quickly we can catch up.Our backlog tends to be shorter and more stable with TimescaleDB than it was previously – and opur developer experience has improved as well.Speaking for myself, I didn’t understand much about our InfluxDB implementation’s inner workings, but talking it through with my teammates, it seems highly customized and hard to explain from scratch. The hosted TimescaleDB implementation with Timescale Cloud is much easier to understand, particularly because we can easily view the live database dashboard, complete with all our table definitions, chunks, policies, and the like.Looking ahead, we have a lot of projects that we’re excited about! One of the big ones is that, with TimescaleDB, we’ll have a much easier time deriving metrics from multiple existing data sets.In the past, because InfluxDB is NoSQL, linking time-series together to generate new, derived, or aggregated metrics was challenging.Now, we can use simple JOINs in one query to easily return all the data we need to derive a new metric.Many other projects have to remain under wraps for now, but we think TimescaleDB will be a crucial part of our infrastructure for years to come, and we’re excited to scale with it.Getting started advice & resourcesTimescaleDB is complex, and it's important to understand the implementation of hypertables quickly. To best benefit from TimescaleDB’s features, you need to think about how to chunk your hypertables, what retention and compression policies to set, and whether/how to set up continuous aggregates. (Particularly with regard to your hypertable chunk size, because it's hard to change that decision later.)In our case, the “answers” to three of these questions were addressed from our previous InfluxDB setup: compress after 48 hours (the maximum time in the past we expect to ingest a trade); retain everything; and rollup all of our price and volume data into our particular set of intervals (5m, 15m, 30m, 1h, 6h, 1d, and 1w).The most difficult part was understanding how long our chunks should be (i.e., setting ourchunk_time_intervalon each hypertable). We settled on one day, mostly by default, with some particularly small metrics chunked after a year instead.I’m not sure these decisions would be as obvious for other use cases.✨Editor’s Note:We’ve put togethera hypertable best practices guideto help you get started (including tips on how to size your chunks andcheck your chunk size).Explore the roadmap on GitHubfor future plans on compression.In summary, the strongest advantages of TimescaleDB are its performance and pure Postgres interface. Both of these make us comfortable recommending it across a wide range of use cases. Still, the decision shouldn’t be cavalier; we tested Timescale for several weeks before committing to the idea and finishing our implementation.We’d like to thank Adam and all of the folks at Messari for sharing their story, as well as for their effort to lower the barriers to investing in crypto assets by offering a massive amount of crypto-assets metrics and a real-time alerting mechanism.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-messari-uses-data-to-open-the-cryptoeconomy-to-everyone/
How WsprDaemon Combines TimescaleDB and Grafana to Measure and Analyze Radio Transmissions,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Rob Robinett and Gwyn Griffiths, the creators of WsprDaemon, join us to share the work they’re doing to allow amateur radio enthusiasts to analyze transmission data and understand trends, be it their own personal noise levels or much larger space weather patterns.Amateur radio is a hobby for some three million people worldwide (see “What is Amateur Radio?” to learn more) and its technical scope is vast, examples include: designing and building satellites, devising bandwidth-efficient data communications protocols, and creating novel, low noise antennas for use in urban locations.Our project,WsprDaemon, focuses on amateurs who use the (amateur-developed!) open-sourceWeak Signal Propagation Reporter(WSPR): a protocol that uses low-power radio transmissions that probe the earth’s ionosphere to identify radio propagation paths and provide insights onspace weather. On a typical day, 2,500 amateurs may report 2.7 million WSPR “spots” to thewsprnetdatabase, whose webpage interface allows simple queries on data collected in the last two weeks.Image of radio antennae, with mountains in the background. Photo of theNorthern Utah WebSDRantenna (photo courtesy of Clint Turner)Radio signals that end up in the WsprDaemon TimescaleDB database may be received on a wide variety of antennas, from the 94-foot tower-supported multiband array in Northern Utah pictured above, to more modest 3-foot installations that you may see in many suburban or urban locations.About the TeamWe have a small, two-member volunteer core team, and we’re supported by a dozen or so beta testers and radio specialists (we typically have people from six countries on a weekly Zoom meeting). Rob Robinett, based in Berkeley California, is CEO of TV equipment manufacturerMystic Videoand he’s founded a series of Silicon Valley startups. He recently “rediscovered” amateur radio - after an absence of more than 40 years - and he's applying his software expertise to developing systems that measure short wave radio transmission conditions.Gwyn (left) & Rob (right), WsprDaemon's core volunteer team membersGwyn Griffiths, based in Southampton, UK, returned to amateur radio after retiring from a career as anocean technologist, where he worked with sensors and data from ships, undersea moorings, and robotics underwater vehicles. Gwyn focuses on the TimescaleDB components, devises Grafana dashboards to help inspire WsprDaemon users to create their own, and writes our step-by-step guides (check them outhere).About the projectWsprDaemon ingests data from the wsprnet database into TimescaleDB, allowing users to access historical data (remember, the wsprnet database shows online data from the last two weeks, and our project allows users to use older data for more robust analysis) and enabling a rich range of SQL queries.Additionally, TimescaleDB facilitates our Grafana dashboards; seeing a month of “spots” gives users a far deeper understanding about their own data, enables comparisons with other datasets, and provides a platform for further experimentation and creative graphics.Our TimescaleDB application caters to a wide spectrum of radio amateurs, from casual hobbyists to third-party developers:Some hobbyists simply want to see lists of who’s heard their transmissions in the last hour, or whotheyheard, at what strength, and where the transmissions originated.Other users want to display transmission metrics as time-series graphs, while there’s another class of users for whom ability to use aggregate functions, apply defined time buckets, derive statistics, and create heat maps and other visualizations is essential (such as the internationalHam Radio Science Citizen Investigation community).Last, third-party app developers, like theVK7JJlisting,WSPR watch, and other mapping and graphing apps, also access our WSPR data, appreciating the fast query response.The key measurement for a WSPR receiver is the signal-to-noise ratio (SNR): how strong an incoming signal is compared with the background noise. But, there is alsovital metadata, including the location of the receiver and transmitter, the operating radio frequency, and most critically - time. On average, our database takes in about 4,000 “sets” of this data from a given transmitter, typically 400kB, every two minutes.This below shows an example of SNR from three transmitters, in New York State, Italy, and Virginia.Signal-to-noise (SNR) Grafana dashboard exampleThe seven-day time-series data shown in this dashboard example provides rich information for its receiver, station KD2OM in New York State:They consistently hear N2AJX, just 16km distant, whose radio waves will have travelled over the ground, at much the same SNR throughout the day.They hear WA4KFZ in Virginia throughout most days – but with a dramatic variation in SNR. It’s at a minimum in the hours before sunrise (all times are UTC), and with peaks above the local station. This is the ionosphere at work, providing a path with less loss over 1000km than 16km for over-the-ground waves.The time-series view also allows us to see day-to-day variations, such as the shorter period of high SNR on 23rd June compared to prior days.They hear IU0ICC from Italy via the ionosphere from early evening to about 0300 local time each day, with a consistent shape to the rise and fall of SNR.While SNR is the main measurement, our users are also interested in aggregate metadata functions, which provide an overview of daily changes in the ionosphere.Our project allows them to run these more complex queries, and we bring in complementary public domain data, such as the case below where we pull in data from theUS Space Weather Prediction Center.WsprDaemon also runs complex transmission data queries and visualizationsIn this example, the top panel of the Grafana dashboard uses the WsprDaemon dataset to display a simple count of the “spots” in each 10 minute time bucket with, on the second y-axis,  a measure of the planetary geomagnetic disturbance index (kp) from the US Space Weather Prediction Center. In 2020, we’re at the minimum of the11-year sunspot cycle, so our current space weather is generally very quiet, but we’re anticipating disturbances - as well as more WSPR spots - as the sun becomes more active over the next four years.The second panel is a heat map that shows the variation in distance between the receiver in Belgium and the transmitters it’s heard over time.The third panel shows the direction of arrival at the receiver, while the bottom panel helps the user interpret all of this data, showing the local noise level and instances of overload.Editor’s note: see ourGrafana Series Overrideblog post to learn how (and why) to use two y-axes to more accurately plot your data and understand trends.Using TimescaleDBAs evidenced above, a big advantage for our users is our ability to bring disparate datasets together into one database, with one graphical visualisation tool.Our initial need was for a database and visualisation tool for radio noise measurements from a handful of stations. A colleague suggestedInfluxandGrafana, and kindly set up a prototype for us. We were hooked.We sought to expand to cover a larger range of data sets from several thousand sources. The Influx documentation was great, and we had an extended application running quickly. Initially,our query time performance was excellent, but, as we accumulated weeks of data we hit thecardinalityissue.Query times became unacceptably long, and we looked for an alternative time-series database solution.We quickly came across an objectivearticleon how to solve the cardinality problem that led us to adopt TimescaleDB.The biggest factor in choosing TimescaleDB was that it solved our cardinality problem, but there were also “nice to have” features, such as the easy-to-usetool to migrate our data from Influxand the underlying use of PostgreSQL. But, we did miss Influx’s comprehensive single-source documentation.Editor’s Note: Because we think it’s important to remain balanced and let our community members’ voice shine through, we don’t edit mentions of alternative technologies (favorable or unfavorable🙂).Current deployment & future plansOur initial TimescaleDB implementation is on a DigitalOcean Droplet (2 cores, 4GB memory 100GB SSD disk), but we are moving to our own 16 core, 192GB memory Dell server and a back-up (we’re evaluating query performance as our user base grows).As noted above,the way TimescaleDB has solved the issue of cardinality was a big selling point for us, and it’s what makes the WsprDaemon site performant for our users.When we wereusing Influx, a typical query that returned 1,000 results from a table of 12 million records and a cardinality of about 400,000 took 15-25 seconds.Now,running TimescaleDB on the same Digital Ocean Droplet(albeit with 4GB rather than the previous 2GB of memory),those same queries overwhelmingly return results in under 2s*.*as long as the data requested is within the chunk that is in memory. That’s why we’ve recently increased our Dell server memory from 24 to 192GB, to handle one-month chunks, and why it will become our primary machine.We use bash Linux shell scripts with Python to gather the data that populates our database tables. We find that batch upload usingpsycopg2.extras.execute_batchworks well for us, and our users use a variety of methods to access WsprDaemon, including Node.js andpsqlvia its command line interface.Simplified WsprDaemon architecture, showing data routes fromwsprnetand 3rd party interfacesWe already make extensive use of Grafana dashboards, and we expect to expand our capabilities - adding global map panels is just one example. But, even after extensive searching, it’s not always straightforward or obvious how to obtain the best, streamlined end result.For example, creating an animation that shows the global geographic distribution of receivers and transmitters by hour requires us to export data to CSV using psql, import the file intoOctave, generate maps on anazimuthal equidistantprojection, save these map files as PNG, and then import intoImageJto generate an AVI file.Editor’s Note: To learn more about building Grafana visualizations with TimescaleDB, check out ourstep-by-step tutorials,blogs, and “Guide to Grafana” webinar series.Our future path includes collaboration, both with others in the global amateur radio community and more data sources. We continually learn about people who have neat solutions for handling and visualising data, and, by sharing knowledge and experience, we can collectively grow and improve the tools we offer this great community. We’re keen to expand our connections to other third party data sources, such as space weather, to help our users better interpret their results.Getting started advice & resourcesWe’re non-professional database users, so we only feel qualified to speak to others with a similar minimal level of prior familiarity.As you evaluate time-series database options, of course, readindependent comparisonsand the links they provide, but also look carefully at insightful, fair-minded comparisons from TimescaleDB, e.g., onSQL vs Flux. Try to assess the advantages of different approaches foryourapplication, current and future skill sets, and requirements.Parting thoughtsWe believe that data analytics for radio amateurs is in its infancy. We’re detailing our approach and experience with TimescaleDB and Grafana in a paper at the 39th gathering of theDigital Communications Conferencein September 2020 (for the first time, the Conference will be completely virtual, which is likely to enable a larger-than-usual participation from around the world). We’ll feature some nice examples of how self inner joins help pull out features and trends from comparisons, as well as many other capabilities of interest to our users.We’d like to thank Rob & Gwyn for sharing their story, as well as for their work to create open-source, widely distributed queries, graphs, and tools. Their dedication to making transmission data accessible and consumable for the global amateur radio community is yet another testament to how technology and creativity combine to breed amazing solutions.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@lacey butler), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/wsprdaemon-combines-timescaledb-grafana-analyze-radio/
How Ndustrial Is Providing Fast Real-Time Queries and Safely Storing Client Data With 97 % Compression,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Michael Gagliardo, technical lead and software architect at Ndustrial, shares how Ndustrial is helping clients save energy and money by collecting several types of data and offering on-demand analytics quickly and seamlessly using TimescaleDB. And the best part is that they don’t need to worry about keeping vast amounts of raw data—TimescaleDB handles that, too.About the CompanyNdustrialis based out of Raleigh, North Carolina, in the United States. Our company has been around since 2011, and we work exclusively in the industrial sector.We aim to help our customers—who are large industrial energy users—save energy. And by doing so also save money and improve sustainability. A lot of that is collecting data from very disparate devices, such as industrial IoT equipment, utility meters, utility bills, and even external data, and ultimately trying to use and combine that data to give our customers a better view of what we would call production-normalized KPIs.The company’s high-level data architectureThese KPIs are essentially metrics that we try to get down to the point of the single unit that our customers are producing, and that changes based on their manufacturing process, what sector they're in, and what their actual product is. But if we can get it down to that point, we can understand how much energy consumption is required for a single product or unit. And then help them find and even completely automate these opportunities to save energy.We’re talking about customers that might have multiple facilities, even in the hundreds across the US and the world, so our goal in this space is to be able to aggregate all that data into a single view, understand what’s happening, and where you may have a lack of efficiencies in order to find those opportunities and save energy and money.About the TeamThe Ndustrial team building more than data applicationsWe currently have around 18 engineers working at the company, split into two main teams. We have an Integrations team that's more focused on different types of ingestion and where we get that data from (which sometimes gets very specific with our customers). The other team is focused on building our platform's more generalized extensibility aspect and a front end,our energy intensity user interface (UI), called Nsight.I am the technical lead and software architect for the Platform team. We focus on building the functionality to turn the customers’ data into meaningful metrics based on their organization as well as building out features for Nsight, making that easier to consume as a customer.From a background perspective, most of our engineers come from a mix of experiences. Some of them have actually been in the energy world, whereas others come from the B2B SaaS world, such as myself. So it's a nice foundation to build on where you have both perspectives and merge them toward one scalable product.About the ProjectMore than 80 percent of our data is time series across the board. Now the facets of where it comes from could be standard IoT devices just setting up their data points. It could be a time series of line items on a utility bill associated with a statement per day, per month, or per year. But it always comes down to data points associated with a timestamp that we can aggregate together to give a perspective of what’s happening.✨Editor’s Note:Learn more about the features of the best database for time-series data.Then, we have the other side: non-time series, to give a digital perspective of the client’s organization so you can start associating different elements by those groupings or categories to provide even more focused metrics in our UI.The company’s UI, NsightI joined Ndustrial in June 2020, and we already had some applications to store and manage time-series data. This data is very unique, frequent, and needs to be retrieved quickly. But as far as leaning on databases specific to time-series data, that wasn’t really a factor in the beginning. It was more focused on, “How can we store this and at least get to it quickly?”“Compression was a game-changer from our perspective: not having to worry about getting databases on the order of 5, 10, or 15 TB to store this information was a massive factor for us”But then you start considering other aspects: now, I want to aggregate that data in unique ways or across arbitrary windows. That's where the drive to “We need something more powerful, something that can handle this data and not just store it efficiently but query it efficiently” comes in. And along with it came Timescale.Compression was a game-changer from our perspective as a company: not having to worry about getting databases on the order of 5, 10, or 15 TB to store this information was a massive factor for us. But then we want the dashboarding that we have, Nsight, which is our energy intensity platform, to be very dynamic.We don’t always know ahead of time what our customers want to visualize. We’re not trying to hard code a bunch of metrics we can report on quickly. We tried to build a much more dynamic platform, and most of our querying is actually on demand. There is some downsampling in between to make things more efficient, but the queries we are providing in our dashboard and arbitrary metrics are all things our customers are creating, and they happen on demand.Getting data back efficiently without feeling like the UI is slow was a major win for us in using TimescaleDB. Before, we had a lot more pre-aggregation. That was the biggest power I've seen from it, along with not having to deal with having an entire team of database administrators (DBAs) to go through and make sure that's possible [Ndustrial uses Managed Service for TimescaleDB]. It’s also one of the more valuable benefits for our customers, whether or not they realize this is even happening.Choosing (and Using!) TimescaleDBWhen I joined the company, Ndustrial had a proof-of-concept (PoC) running for TimescaleDB, so I think the company as a whole was moving in that direction. I had some experience with TimescaleDB and other time-series databases. This was only three years ago, but even then, Timescale was very prominent in the world of “Hey, we need a time-series database to be stored somewhere.”“For one of our larger customers, we normally store about 64 GB of uncompressed data per day. With compression, we’ve seen, on average, a 97 percent reduction”Other alternatives we researched were InfluxDB and Prometheus for storing this data. But what brought it home for us is that PostgreSQL is a foundation in our company. It is a standard of what we expect from developers and, ultimately, the main reason the PoC started to test out how we could work with time-series data without changing and bringing up new technologies and languages to learn.We knew we would be collecting many times-series points per day—across all the different types of data we’re collecting, above a hundred million points per day. It gets massive, and we want to store and query that efficiently. It’s not just about how we store this and how we ensure we’re not spending mass quantities of money to store it, but also how we get that back out, ask questions, and query data without having to do a lot of work in between to get it into a shape where we can do that efficiently.So the compression aspect of TimescaleDB was something we leaned on immediately. Honestly, that was probably what sold it out of the PoC: when we tested compression with our chunks and started seeing the percentage by which we could reduce the storage size with minor degradation in query performance.✨Editor’s Note:Read how TimescaleDB expands the PostgreSQL functionality with faster queries and reduces storage utilization by 90 percent.For one of our larger customers, which would take a big chunk of the time series, we normally store about 64 GB of uncompressed data per day. With compression, we’ve seen, on average, a 97 percent reduction. So down to less than 2 GB a day. And that goes from a record count of two million records to just a couple hundred thousand, which is pretty significant. It would take a larger and more performant database to hold that type of weight.CREATE FUNCTION public.show_all_chunks_detailed_size()
    RETURNS TABLE(
        hypertable text,
        chunk text,
        time_range tstzrange,
        total_bytes bigint,
        total_size text,
        table_size text,
        index_size text,
        toast_size text,
        compression_savings numeric
     ) AS
$func$
BEGIN
    RETURN QUERY EXECUTE (
      SELECT string_agg(format('
            SELECT
                %L AS hypertable,
                s.chunk_schema || ''.'' || s.chunk_name AS chunk,
                tstzrange(c.range_start, c.range_end) AS time_range,
                s.total_bytes,
                pg_size_pretty(s.total_bytes) AS total_size,
                  pg_size_pretty(s.table_bytes) AS table_size,
                pg_size_pretty(s.index_bytes) AS index_size,
                pg_size_pretty(s.toast_bytes) AS toast_size,
                round(100 * (1 - p.after_compression_total_bytes::numeric / p.before_compression_total_bytes::numeric), 2) AS compression_savings
            FROM
                chunks_detailed_size(%L) s
                LEFT JOIN chunk_compression_stats(%L) p USING (chunk_name)
                LEFT JOIN timescaledb_information.chunks c USING (chunk_name)
            ', tbl, tbl, tbl
        ), ' UNION ALL ')
        FROM (
            SELECT hypertable_schema || '.' || hypertable_name AS tbl
            FROM timescaledb_information.hypertables
            ORDER BY 1
        ) sub
   );
END
$func$ LANGUAGE plpgsql;The query that the Ndustrial team usesto monitor their chunk sizes across hypertables, including the compression stats they are seeingMost companies believe this, but data is critical to us. We keep raw data: we never know how we will go back and reuse it. It's not something we can downsample, throw away, and hope one day we don't need it. So being able to store it and not having to focus too much on complex retention strategies to put things in a much slower, cold storage (versus hot storage) because we're able to compress it and still query it without worrying too much about going over limits is very significant for us.As far as query performance, I don't have an exact metric. Still, being able to make arbitrary queries, arbitrary roll-ups, on-demand unit conversion, and currency conversion at the time of querying and get responses back in the same amount of time that we got from doing pre-aggregates and without the difficulty of changing the questions’ format to roll up this data is a massive benefit.Before TimescaleDB, we were using Cassandra, and a fair amount of mixed data could also have been in a standard PostgreSQL database. But most of our high-frequency IoT data was in Cassandra, primarily for the benefit of being able to use partitioning for fast retrieval. However, it took a lot more effort to get that data out. So as efficient as it could be to store it, we had difficulty on the other side of getting it back out in the way that we wanted it.We also tried InfluxDB but didn’t go with it. It wasn’t just about learning a new language (InfluxQL); storing most of our relational data inside PostgreSQL was a significant factor. The platform we've been working with for the last two years has been a shift in usingGraphQLas our main layer for that service, our tenancy.TimescaleDB provides the time-series metrics, but the rest of our relational graph that builds out an organization are just entities and our PostgreSQL database. So, being able to use time-series metrics as they relate to relational entities, groupings, and various ways of querying those types of data without going outside PostgreSQL, was a significant aspect for us.With InfluxDB, we would deal with more latency: the amount of time it would take to query on one side, query on the other side, and correlate them together. Not to mention that you’re dealing with a new language. With TimescaleDB, it’s just a SQL query.I’ve had experience with InfluxDB, which is one of the reasons we made sure we tested that out as well. And that's probably a direction I would go back to if TimescaleDB didn’t exist. Luckily, we don’t have to. I mean, it's also a powerful database, but for the other factors I just mentioned, TimescaleDB made more sense at the time.Current Deployment & Future PlansFrom a more architectural perspective, I'm focused on ensuring the technologies we bring in aren't going to hinder our developers by having to make them learn something new every time we bring in a new engineer. If there's too much of a learning curve and it's not something they're already used to, you're going to slow down.The Ndustrial data architectureAs a small startup, we must balance the technologies we can benefit from without having 10 developers purely stationed for managing them, which is why we chose Managed Service for TimescaleDB. You don't want to have every developer spend all their time managing it or being aware of it and worrying about what happens if you don’t apply those maintenance updates or backups correctly.“Getting all these updates and zero downtime for deployments and replicas is significant for us because we need to assure our customers that we are as reliable as the data we're storing”Time series is the main portion of the type of data we collect, so it’s a very significant aspect of our platform. We wouldn’t be able to do much without it. Putting that in the hands of the same developers that are also having to focus on integrations, customers, and future work would have been too much of a risk. On the other hand, getting all these updates and zero downtime for deployments and replicas is significant for us because we need to assure our customers that we are as reliable as the data we're storing.As for deployment, on the ingestion side, we useKotlinas our foundation for building out stream-processing pipelines on a technology calledPulsaras our messaging layer. It's very similar toKafkaPTP-style (point-to-point) message queuing. Essentially, all of the ingestion that we do, whether it's IoT data or other types of integrations that we're either pulling from or getting pushed to, flows through these stream processing pipelines, ultimately landing into our TimescaleDB database. So that is our main input into TimescaleDB.Then, on the other side of the house, we have our GraphQL platform. The platform exposes a GraphQL API that can query TimescaleDB for arbitrary queries, metrics, and results based on what the consumer is asking. And then, in our front end, which is our dashboarding, there’s a full-featured single-page app in React.We also use other technologies, such asGrafana, for a more ad hoc view of the data, especially because we also do implementations at facilities for installing meters and devices if the customers don't already have those available. Hooking those up and having a view into our TimescaleDB database of that specific feed of data gives us a lot more visibility and speeds up the installation time without worrying about whether or not it's set up correctly.Our data is very disparate and unpredictable in terms of how it gets sent. Some customers will have devices sending data every second to minutes or every hour. Devices might go down, come back up, and send their last days’ worth of data all at once. We could have other systems sending it at their own regular rates.It's not normalized, so to be able to lean on TimescaleDB hyperfunctions liketime_bucket_ gapfilland bucketing, andlocftype of functions to turn those results into something more meaningful because we can fill the gap or understand what should happen in the case where that data point didn't exist is really powerful for us.✨Editor’s Note:Learn more about hyperfunctions and how they can help you save time and analyze time-series data better.Obviously, compression was a big thing for us in terms of getting that in place in the beginning. And since then, we have used continuous aggregates. We are still at a point where we're building out the process of what that will look like in our platform because this data is unique—I mean, we're storing arbitrary data. We don't even know sometimes what that feed is sending us.We’re still building a more automated process to be able to make smarter decisions about what becomes continuous aggregates, what becomes something we physically want to store, and the intervals at which we want to store it and throw away the rest. And so, I hope to lean on continuous aggregates a bit more in the future.But, ultimately, just being able to query and use TimescaleDB features to roll up and aggregate that data is significant for us. I love following theTimescale experimental repositoryand seeing what people come out with each release. I basically have a list on my computer of things I intend to add to our platform soon. Just to expose those capabilities, too. Because if we can do it, why not turn that into something we can create in our app? And create it as another view, metric, or perspective in the UI. So it's always exciting to see what you guys are coming up with.RoadmapOver the last few years, we've been building this new platform and leaning pretty heavily on it. There are a few pieces left that we're getting to really make it where we want it to be. Part of that is exposing many of these self-made dynamic metrics to our customers to the degree where they can just generate whatever they want that meets the needs of their system based on their use case or personas.We have various individuals using our platform that come from different disciplines within an organization, so being able to cater metrics and analytics to them directly is really powerful, and exposing a platform in a way where they can do that without having to rely on us to do it for them is also very powerful.To achieve this, we're building more of these ad hoc type views, which will lean on the functionality we get from an in-house metrics engine that uses TimescaleDB to query this data. Beyond that, we're entering into more of an automated controls world within our platform.Up to this point, we've primarily been focused on getting this data visible and exposing it to our customers, showing them how they can become more efficient, and working with them more closely to help them get there. But we're moving toward the direction of taking these opportunities and automating the process that allows them to save energy and money or participate in these opportunities.This requires us to analyze this data to predict outcomes that could happen in the near future so that we can prepare customers and get them set up and make them more valuable than others who don't have that information. It’s not really a competition, but at the same time, we want to show clients that they can get value out of their data. So having all of our time-series data in a common place that we can query in a common way is going to really make that successful in the future.Advice & ResourcesMy advice is always to start with the type of data you’re storing. In the beginning, you obviously don't want to break things out too early. You never know what could shift. Wait for that next use case before you start extracting or breaking things out: you never know if you’ll end up doing it, and then you might just have more complexity on your hands for no reason.But as a whole, data is different across the board. It's okay to store it in different places that are meant to store that data. So, having a time-series database versus storing a relational database over here and maybe search indexes over here. That's okay. I mean, is your product going to be piecing those together and really leaning on the type of data I'm storing? What is the size and frequency of this data?And then selecting the right technologies specific to that data or maybe multiple technologies to handle that data. You should do that ahead of time because it gets more difficult the longer you wait to do so because it becomes the process of migrating all of that and doing it without downtime. But ultimately, that's just the best way to start: understand what you're storing and figure it out from there.Regarding resources, I think that theTimescale Docsare great. Especially dealing with other technologies and being a developer, there are many times you’ll go to the documentation for some technology, and you’ll be as confused as you were before you got there. I think Timescale has done a wonderful job with their docs and how they break it out into use cases and some examples.I also lean pretty heavily onCommunity Slack, whether or not I'm asking a question. It's just nice to monitor it and see what other people are asking. It might raise questions or give me ideas about issues we ran into in the past. And so having that as a resource as well, or knowing that if I have an issue, I can ask and get pretty quick and immediate feedback. It's really great.Between the Docs and the Community, it shouldn’t be difficult to get up with what you need to meet your system’s needs.Want more insider tips?Sign up for our newsletterfor more Developer Q&As, technical articles, and tutorials to help you do more with your data. We deliver it straight to your inbox twice a month.We’d like to thank Michael and the folks at Ndustrial for sharing their story on how they speeded up their client-facing dashboards, even for real-time queries, while carefully storing all their customers’ data by making the most of TimescaleDB’s compression powers.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-ndustrial-is-providing-fast-real-time-queries-and-safely-storing-client-data-with-97-compression/
How I Power a (Successful) Crypto Trading Bot With TimescaleDB,"This is an installment of our “Community Member Spotlight” series, where we invite TimescaleDB community members to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Felipe Queis, a senior full-stack engineer for a Brazilian government traffic institution, joins us to share how he uses TimescaleDB to power his crypto trading bot – and how his side project influenced his team’s decision to adopt TimescaleDB for their work.My first experience with crypto wasn’t under very good circumstances: a friend who takes care of several servers at his job was infected with ransomware – and this malware was demanding he pay the ransom amount in a cryptocurrency called Monero (XMR).After this not-so-friendly introduction, I started to study how the technology behind cryptocurrencies works, and I fell in love with it. I was already interested in the stock market, so I joined the familiar (stock market) with the novel (crypto). To test the knowledge I’d learned from my stock market books, I started creating a simpleMoving Average Convergence Divergence (MACD)crossover bot.This worked for a while, but I quickly realized that I should - and could - make the bot a lot better.Now, the project that I started as a hobby has a capital management system, a combination of technical indicators, and sentiment analysis powered by machine learning. Between 10 March 2020 and 10 July 2020, my bot resulted in asuccess rateof 61.5%,profit factorof 1.89, and cumulative gross result of approximately 487% (you can see a copy of all of my trades during this period inthis Google Sheet report).About meI'm 29 years old, and I’ve worked in a traffic governmental institution in São Paulo, Brazil (where I live too) as an senior full-stack developer since 2012.In my day job, my main task at the moment is processing and storing the stream of information from Object Character Recognition (OCR)-equipped speed cameras that capture data from thousands of vehicles as they travel our state highways. Our data stack uses technologies like Java, Node.js, Kafka, and TimescaleDB.(For reference, I started using TimescaleDB for my hobby project, and, after experiencing its performance and scale with my bot, I proposed we use it at my organization. We’ve found that it brings together the best of both worlds: time-series in a SQL databaseandopen source).I started to develop my crypto trading bot in mid- 2017, about six months after my first encounter with the crypto ecosystem – and I’ve continued working on it in my spare time for the last two and a half years.Editor’s Note: Felipe recently hosted aReddit AMA (Ask Me Anything)to share how he’s finally “perfected” his model, plus his experiences and advice for aspiring crypto developers and traders.About the projectI needed a bot that gave me a high-performance, scalable way to calculate technical indicators and process sentiment data in real-time.To do everything I need in terms of my technical indicators calculation, I collectcandlestick chartdata and market depth via an always-up websocket connection that tracks every Bitcoin market on theBinance exchange(~215 in total, 182 being tradeable, at this moment).The machine learning sentiment analysis started as a simple experiment to see if external news affected the market. For example: if a famous person in the crypto ecosystem tweeted that a big exchange was hacked, the price will probably fall and affect the whole market. Likewise, very good news should impact the price in a positive way. I calculated sentiment analysis scores in real-time, as soon as new data was ingested from sources like Twitter, Reddit, RSS feeds, and etc. Then, using these scores, I could determine market conditions at the moment.Now, I combine these two components with a weighted average, 60% technical indicators and 40% sentiment analysis.Felipe's TradingBot dashboard, where he tracks all ongoing trades and resultsQuick breakdown of Felipe’s results and success rates week-over-week (for the period of 10 March 2020 - 10 July 2020)Using TimescaleDBAt the beginning, I tried to save the collected data in simple files, but quickly realized that wasn’t a good way to store and process this data. I started looking for an alternative: a performant database.I went through several databases, and all of them always lacked something I wound up needing to continue my project. I tried MongoDB, InfluxDB, and Druid, but none of them 100% met my needs.Of the databases I tried,InfluxDB was a good option; however, every query that I tried to run was painful, due to their own query language (InfluxQL).As soon as my series started to grow exponentially to higher levels, the server didn't have enough memory to handle them all in real-time. This is because the currentInfluxDB TSMstorage engine requires more and more allocated memory for each series. I have a large number of unique metrics, so the process ran out of available memory quickly.I handle somewhat large amounts of data every day, especially on days with many market movements.On average, I’m ingesting around 20k records/market, or 3.6 million total records, per day (20k*182 markets).This is where TimescaleDB started to shine for me. It gave me fast real-time aggregations, built-in time-series functions, high ingestion rates – and it didn’t require elevated memory usage to do all of this.Editor’s Note:For more about how Flux compares to SQL and deciding which one is right for you,see our blog post exploring the strengths and weaknesses of each.To learn more about how TimescaleDB real-time aggregations work (as well as how they compare to vanilla PostgreSQL), seethis blog post and mini-tutorial.In addition to this raw market data, a common use case for me is to analyze the data in different time frames (e.g., 1min, 5min, 1hr, etc.). I maintain these records in a pre-computed aggregate to increase my query performance and allow me to make faster decisions about whether or not to enter a position.For example, here’s a simple query that I use a lot to follow the performance of my trades on a daily or weekly basis (daily in this case):SELECT time_group, total_trades, positive_trades, 
	negative_trades,
	ROUND(100 * (positive_trades / total_trades), 2) AS success_rate, profit as gross_profit,
    ROUND((profit - (total_trades * 0.15)), 2) AS net_profit
FROM (
	SELECT time_bucket('1 day', buy_at::TIMESTAMP)::DATE AS time_group, COUNT(*) AS total_trades, 
		SUM(CASE WHEN profit >  0 THEN 1 ELSE 0 END)::NUMERIC AS positive_trades, 
		SUM(CASE WHEN profit <= 0 THEN 1 ELSE 0 END)::NUMERIC AS negative_trades,
		ROUND(SUM(profit), 2) AS profit 
	FROM trade
	GROUP BY time_group ORDER BY time_group 
) T ORDER BY time_groupAnd, I often use this function tomeasure market volatility, decomposing the range of a market pair in a period:CREATE OR REPLACE FUNCTION tr(_symbol TEXT, _till INTERVAL)
	RETURNS TABLE(date TIMESTAMP WITHOUT TIME ZONE, result NUMERIC(9,8), percent NUMERIC(9,8)) LANGUAGE plpgsql AS $$ DECLARE BEGIN

RETURN QUERY 
	WITH candlestick AS ( SELECT * FROM candlestick c WHERE c.symbol = _symbol AND c.time > NOW() - _till )
	SELECT d.time, (GREATEST(a, b, c)) :: NUMERIC(9,8) as result, (GREATEST(a, b, c) / d.close) :: NUMERIC(9,8) as percent FROM ( 
		SELECT today.time, today.close, today.high - today.low as a,
      		COALESCE(ABS(today.high - yesterday.close), 0) b,
      		COALESCE(ABS(today.low - yesterday.close), 0) c FROM candlestick today
      	LEFT JOIN LATERAL ( 
			  SELECT yesterday.close FROM candlestick yesterday WHERE yesterday.time < today.time ORDER BY yesterday.time DESC LIMIT 1 
		) yesterday ON TRUE
    WHERE today.time > NOW() - _till) d;
END; $$;

CREATE OR REPLACE FUNCTION atr(_interval INT, _symbol TEXT, _till INTERVAL)
	RETURNS TABLE(date TIMESTAMP WITHOUT TIME ZONE, result NUMERIC(9,8), percent NUMERIC(9,8)) LANGUAGE plpgsql AS $$ DECLARE BEGIN
	
RETURN QUERY
	WITH true_range AS ( SELECT * FROM tr(_symbol, _till) )
	SELECT tr.date, avg.sma result, avg.sma_percent percent FROM true_range tr
	INNER JOIN LATERAL ( SELECT avg(lat.result) sma, avg(lat.percent) sma_percent
		FROM (
			   SELECT * FROM true_range inr
			   WHERE inr.date <= tr.date
			   ORDER BY inr.date DESC
			   LIMIT _interval
			 ) lat
		) avg ON TRUE
  WHERE tr.date > NOW() - _till ORDER BY tr.date;
END; $$;

SELECT * FROM atr(14, 'BNBBTC', '4 HOURS') ORDER BY dateWith TimescaleDB, my query response time is in the milliseconds, even with this huge amount of data.Editor’s Note: To learn more about how TimescaleDB works with cryptocurrency and practice running your own analysis,check out our step-by-step tutorial. We used these instructions toanalyze 4100+ cryptocurrencies, see historical trends, and answer questions.Current Deployment & Future PlansTo develop my bot and all its capabilities, I used Node.js as my main programming language and various libraries:Coteto communicate between all my modules without overengineering,TensorFlowto train and deploy all my machine learning models, andtulindfor technical indicator calculation, as well as various others.I modified some to meet my needs and created some from scratch, including a candlestick recognition pattern, a level calculator for support/resistance, and Fibonacci retracement.Current TradingBot architecture + breakdown of various Node.js librariesToday, I have a total of 55 markets (which are re-evaluated every month, based on trade simulation performance) that trade simultaneously 24/7; when all my strategy conditions are met, a trade is automatically opened. The bot respects my capital management system, which is basically to limit myself to 10 opened positions and only use 10% of the available capital at a given time. To keep track of the results of an open trade, I use dynamicTrailing Stop LossandTrailing Take Profit.The process of re-evaluating a market requires a second instance of my bot that runs in the background and uses my main strategy to simulate trades in all Bitcoin markets. When it detects that a market is doing well, based on the metrics I track, that market enters the main bot instance and starts live trading. The same applies to those that are performing poorly; as soon as the main instance of my bot detects things are going badly, the market is removed from the main instance and the second instance begins tracking it. If it improves, it's added back in.As every developer likely knows all too well, the process of building a software is to always improve it. Right now, I’m trying to improve my capital management system usingKelly Criterion, assuggested by a userin my Reddit post (thanks, btw :)).Getting started advice & resourcesFor my use case, I’ve found TimescaleDB is a powerful and solid choice: it’s fast with reliable ingest rates, efficiently stores and compresses a huge dataset in a way that’s manageable and cost-effective, and gives me real-time aggregation functionality.TheTimescale website,""using TimescaleDB"" core documentation, andthis blog post about about managing and processing huge time-series datasetsis all pretty easy to understand and follow – and the TimescaleDB team is responsive and helpful (and they always show up in community discussions, likemine on Reddit).It’s been easy and straightforward to scale, without adding any new technologies to the stack. And, as an SQL user, TimescaleDB adds very little maintenance overhead, especially compared to learning or maintaining a new database or language.We’d like to thank Felipe for sharing his story, as well as for his work to evangelize the power of time-series data to developers everywhere. His success with this project is an amazing example of how we can use data to fuel real-world decisions – and we congratulate him on his success 🎉.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@lacey butler), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-i-power-a-successful-crypto-trading-bot-with-timescaledb/
"Using IoT Sensors, TimescaleDB, and Grafana to Control the Temperature of the Nuclear Fusion Experiment at the Max Planck Institute","This is an installment of our “Community Member Spotlight” series, where we invite our customers or users to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, David Bailey, an intern at the Wendelstein 7-X fusion reactor experiment at theMax Planck Institute for Plasma Physics, explains how he’s using TimescaleDB and Grafana to monitor the reliability of the reactor’s heating system. Currently working on the designs of the sensor boards that measure the microwaves feeding the reactor, David needs a tight grasp on his systems and data to prevent the experiment from being aborted due to excessive heating—and is successfully achieving it with a little help from TimescaleDB.About the UserI am David Bailey, and I am currently completing an internship at the Wendelstein 7-X fusion reactor experiment, which is operated by the Max Planck Institute for Plasma Physics in Greifswald, Germany. The experiment aims to help us understand, and someday utilize, the power of fusion for further scientific endeavors and power generation. It does this by using a novel way of containing the hot gasses needed for the experiments and tweaking its design to reach longer and longer experiment runs.David BaileyThe end goal is to reach 30 minutes of uninterrupted containment of plasma heated to millions of degrees. To be able to do this, there is a lot of data to collect and process and a number of problems left to solve—one of which I can help with!About the ProjectMy specific task at the internship is to help with the heating system. Because the experiment is not set up for self-sustaining fusion, where the gasses would heat themselves, we constantly have to feed in energy to keep it hot: 10 megawatts, to be precise!We do this with various microwave sources—similar to scaled-up versions of household microwaves—and a big array of mirrors to guide the energy to the reactor vessel.Schematic of the W7-X microwave heating system and mirror assembly. Credit: Torsten Stange, David Bailey; Max Planck Institute GreifswaldThere are dangers in using these highly powerful microwaves: if dust or a fine droplet of water gets in the way of the energy, an arc can form—a lightning bolt of sorts. If the energy is not turned off fast enough, this arc can do a lot of damage, and it also means that we need to abort the experiment to wait for things to cool off again—not good if you want to run everything reliably for long periods of time!Image of a forming microwave arc, taken at the Max Planck IPP Greifswald experiment siteMy task is to design sensor boards to precisely track the amount of energy coming out of the microwave array. The ultimate goal is to detect changes in the power output on the microsecond scale, so the power can be turned off before the arc fully forms. If done right, we can turn the heating back on without a pause and continue the experiment!One of the key aspects of what I am designing is that it needs to be reliable. If it's not sensitive enough, or too sensitive, or if there are issues with the communication with the rest of the system, it can severely impact the rest of the experiment in a negative way.One of the sensors that David is working onThe only way to ensure something is reliable is through data—a lot of it. A problem might present itself only after hundreds of hours or in subtle ways that are only apparent across days of data, but it can still be relevant and important to know about.​Writing a program to handle this amount of data myself would've been an unnecessary effort. It needs a tool that has the necessary functionality in it already, such as statistical operators, compression, etc., and you can get all of this in time-series databases, such as TimescaleDB!To track that the sensors and system are working as expected, I collect and handle several types of data using TimescaleDB:I am recording the general health metrics of the board in question: temperature, voltage levels, etc. These shouldn't change, but the harsh environment close to the reactor might cause issues, which would be very important to know about!Log messages of the boards themselves to understand what the software was doing.And finally, detection events: every time the board sees something suspicious, it sends a measurement series of this event, about 1,000 samples taken over a millisecond. We can use this during the initial development phase to ensure everything is working correctly. I can use a function generator to send a predetermined reference signal and compare that to what the board tells me it saw. If there are discrepancies, those might point to errors in the soft- or hardware and help me fix them.When the system is deployed, we can use these measurements to refine the detection system, to be able to set it as fast as possible without too many false positives, and gather data about how the actual physical events look to the board.Choosing (and Using!) TimescaleDB✨Editor's Note:See how you can get started with Grafana and TimescaleDB with ourdocsorvideos.I started using Timescale to track data formy self-built smart homeas a small playground for me—that's still running! Aside from that, I only use it for the monitoring system mentioned above, though I hope to motivate others at my research institute to use it! It is also my go-to for other projects, should I need a way to store measurements efficiently.The top factors in my decision to use TimescaleDB were, first, the open-source nature of it. Open-source software can be much more beneficial to a wider range of people, mature faster, generally has more flexible features, and isn't so locked into a specific environment. It's also much more approachable to individuals because of no fussy licensing/digital rights management.Then, the documentation. You can read up on a lot of things that TimescaleDB can do, complete with great examples! It doesn't feel like you're alone and have to figure it out all by yourself—there's rich, easy-to-understand documentation available, and if that doesn't have what you need, the community has been very helpful, too.✨Editor's Note:Need help with TimescaleDB? Join ourSlack CommunityorForumand ask away!And lastly,it's still all just SQL! You don't need to learn another database-specific format, but you can start using it immediately if you've worked with any standard database before! That was helpful because I already knew the basics of SQL, giving me a great starting point. Plus, you can use PostgreSQL's rich relational database system to easily store non-time-series data alongside your measurements.Because the Wendelstein is fairly old, database tools didn’t exist when it started. As such, they decided to write their own tool,called ArchiveDB, and never quite brought it to the modern age. It can only store time-series data in a particular format and has no relational or statistical tools aside from minimum/maximum aggregates, no continuous aggregates, and probably no compression.“In the future, I want to motivate my research institute to install a proper multi-node TimescaleDB cluster. It could bring many much-needed modern features to the table, and distributed hypertables and the matching distributed computing of statistics could be incredibly useful”Storing all my measurement data in it would have been possible, but I would have had to write all of the statistical processing myself. Using Timescale was a major time-saver! I tried other tools, but none of them quite fit what I needed. I didn't want to spend too much time on an unfamiliar tool with sparse documentation.First, I tried the company’s standard database, ArchiveDB, but it didn’t offer much more functionality than simply saving a CSV file. I also tried another PostgreSQL extension,Citus. It didn’t lend itself nicely to my use case because the documentation was not as clear and easy, and it seemed much more tailored to a distributed setup from the start, making trying it out on my local machine a bit tricky. I believe I gaveInfluxDBa try, but writing in PostgreSQL felt more natural. Additionally, it was helpful to have the regular relational database from PostgreSQL and the rich set of aggregate functions.I might have used PostgreSQL without TimescaleDB,or perhaps InfluxDB had it not been for Timescale.The Grafana panels observing the sample measurements. A single event is plotted above for inspection using a scatter plot, while the accuracy of the individual measurements is plotted in a time-series belowOne of the things I am verifying right now is the measurements’ consistency. This means running thousands of known reference signals into the boards I am testing to see if they behave as expected. I could automate this using a query that JOINs a regular table containing the reference data with aTimescaleDB hypertablecontaining the measurements.With a bit of clever indexing, I was able to use the corr(x, y) function to check if things lined up. Thanks to the rich data types of PostgreSQL, I can cache metadata such as this correlation in a JSONB field. This speeds up later analysis of the data and allows me to store all sorts of extra values for individual events.The resulting data I then downsampled usingminto find the worst offenders.WITH missing_bursts AS ( 
  SELECT burst_id FROM adc_burst_meta WHERE NOT metadata ? 'correlation' AND $__timeFilter(time)),
raw_correlations AS (
    	SELECT
    	burst_id,
    	corr(value, reference_value) AS ""correlation""
    	FROM adc_burst_data
    	INNER JOIN missing_bursts USING(burst_id)
    	INNER JOIN adc_burst_reference USING(burst_offset)
    	GROUP BY burst_id
)    
update_statement AS (
     UPDATE adc_burst_meta 
    SET metadata = jsonb_set(metadata, '{correlation}', correlation::text::jsonb) FROM raw_correlations WHERE adc_burst_meta.burst_id = raw_correlations.burst_id
)

SELECT $__timeGroup(time, $__interval) AS “time”, 
   min((metadata->>’correlation’)::numeric)
FROM adc_burst_meta 
WHERE $__timeFilter(time) 
GROUP BY “time”That made even small outliers very easy to spot across hundreds of thousands of samples and made analyzing and tagging data a breeze! This insight is incredibly useful when you want to ensure your system works as intended. I could easily find the worst measurements in my sample set, which allowed me to quickly see if, how, and in what way my system was having issues.Current Deployment and Future Plans​Right now, I deploy TimescaleDB on a small, local computer.Running the Docker image has been very useful in getting a fast and easy setup, too!In the future, I want to motivate my research institute to install a proper multi-node TimescaleDB cluster. It could bring many much-needed modern features to the table, and distributed hypertables and the matching distributed computing of statistics could be incredibly useful! My queries could work orders of magnitude faster than my local machine with little extra effort.For the most part, I interact with TimescaleDB through a simple Ruby script, which acts as an adapter between the hardware itself and the database. I also used Jupyter notebooks and theJupyter IRuby Kernelto do more in-depth data analysis.✨Editor's Note:Check out this videoto learn how to wrap TimescaleDB functions for the Ruby ecosystem.“Grafana and Timescale really go hand in hand, and both of them are excellent open-source tools with rich documentation”I do use Grafana extensively! Grafana and Timescale really go hand in hand, and both of them are excellent open-source tools with rich documentation. Both have Docker images and can be set up quickly and easily. It is great to plot the measurements with just a few SQL queries.​Without Grafana, I would have had to write a lot of the plots myself, and correlating different time-series events with each other would have been much harder. Either I would have had to spend more time implementing that myself, or I wouldn't have gotten this level of information from my measurements.​The main benefit of using TimescaleDB is that you get a well-balanced mixture. Being built on top of PostgreSQL and still giving you access to all regular relational database features, you can use TimescaleDB for a much larger variety of tasks.​You can also dive right in, even with minimal SQL knowledge—and if you ever do get stuck, theTimescaleandPostgreSQL documentationare well written and extensive, so you can almost always find a solution!​Lastly, there is no ""too small"" for TimescaleDB either. Being open source, quick to set up, and easy to use while performing well even on a spare laptop or PC, it can be a valuable tool for any sort of data acquisition—even if it feels like a ""small"" task!​I learned a lot about my system already by using TimescaleDB and can be much more confident in how to proceed thanks to it. In a way, it has even changed my developer experience. I have the data acquisition and TimescaleDB running continuously while working on new software features or modifying the hardware. If I were to introduce a bug that could mess up the measurements, I might see those much sooner in the data. I can then react appropriately and quickly while still developing.​I can worry less about ensuring everything works and focus more on adding new features!Advice and ResourcesGive TimescaleDB a try whenever you need to record any kind of time-series data. It's much easier to use than writing your own script or dumping it into a CSV file. The built-in functionality can take care of a lot of number crunching very efficiently, allowing you to benefit from years of finely developed functions. It's well worth the trouble, and there's no such thing as ""too small"" a use case for it!​TimescaleDB has helped me be more confident in developing my hardware, and I want to motivate others in my field to try it out themselves. A lot can be gained from a few days or months of data, even if at first you don't think so—some insights only pop out at larger scales, no matter what you're working on.I am very excited to share my use case of TimescaleDB. Whenever I think of databases, I am drawn to these larger web services and big tech companies—but that doesn't have to be the case! That can also be helpful advice for others looking for the right database for their use case.We’d like to thank David and all of the folks at the Max Planck Institute for Plasma Physics for sharing their story on how they’re monitoring the Wendelstein 7-X fusion reactor’s heating system using TimescaleDB.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/using-iot-sensors-timescaledb-and-grafana-to-control-the-temperature-of-the-nuclear-fusion-experiment-in-the-max-planck-institute/
How Trading Strategy Built a Data Stack for Crypto Quant Trading,"This is an installment of our “Community Member Spotlight” series, where we invite TimescaleDB community members to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Mikko Ohtamaa, CEO at Trading Strategy, joins us to share how they give traders and investors direct access to high-quality trading strategies and real-time control over their assets by integrating market data about thousands of crypto assets, algorithms, profitability simulation, and more into one solution. Thanks to TimescaleDB, they can focus on solving business problems without a need to build the infrastructure layer.Trading Strategyis a protocol for algorithmic trading of crypto-assets in decentralized markets. Cryptocurrency traders and strategy developers can utilize the protocol to easily access and trade on next-generation markets with sophisticated tools that have been traditionally available only for hedge funds.Users don’t need to have a deep understanding of blockchain technology, as the Trading Strategy protocol is designed to make a complex topic easier to understand and approach. This is accomplished by integrating market data feeds for thousands of crypto assets, algorithm development, profitability simulation, trade execution, and smart contract-based treasury management tools into one vertically integrated package.About the teamMy name isMikko Ohtamaa. I am the CEO of Trading Strategy. I have been a software developer for 25 years. For the last decade, I have been CTO of various cryptocurrency and fintech companies. I am also one of the firstEthereum Dappdevelopers and Solidity smart contract auditors.Trading Strategy is a remote-first company with five people. We have offices in London and Gibraltar. We use a combination of remote working tools (Discord, Github, Google Workspace) and more intense get-together sprint weeks to manage the software development.As our work is highly technical, all team members have backgrounds in software development, quantitative finance, or blockchain technologies.About the projectTrading Strategy operates on market data feeds, for which raw data is collected directly from the blockchains. Most market data is heavily time-series, though there are also elements of product catalog features like different trading pairs, tokens, and exchanges.Because of the powerful combination of PostgreSQL and TimescaleDB, we can store the data in a single database,making it simple for software developers to build on top of this and this, in turn, saves us a lot of software development costs.We have two kinds of workloads:historical datasets, that are challenging sizewise, andreal-time data feedsfor algorithms that are challenging latency-wise. TimescaleDB offers vertical scaling as a data lake,  but also offers continuous real-time aggregations for time-series data, making it a good fit for real-time needs.✨Editor’s Note:We’ve put together resources about TimescaleDB’scontinuous aggregatesto help you get started.Data from TimescaleDB is feeding bothon our market information websiteand feeds, but also on the trading algorithms themselves, which make the trading decisions based on the data input. Our applications include OHLCV, or so-called candle charts, market summaries information like daily top trades and liquidity, and risk information for technical trade analysis.Collage of charts from Trading Strategy websiteChoosing (and using!) TimescaleDBPostgreSQL has been the open-source workhorse of databases for the last three decades and offers the most well-known, solid, foundation to build your business on.We chose TimescaleDB over other time-series databases because of its solid PostgreSQL foundation, easy, out-of-the-box functionality, and true open-source nature with an active TimescaleDB community.Moreover, TimescaleDB comes with well-documented code examples on how to use it for stock-market chart data, allowing us to take these examples and build our first MVP based on TimescaleDB example code.✨Editor’s Note:Check out ourthree-step tutorialto learn how to collect, store, and analyze intraday stock data.For example, we heavily utilize the continuous aggregate view feature of TimescaleDB, to upsample our 1-minute candle data to 15 minutes, 1 hour, and daily candles.We can fully offload this work to TimescaleDB, with only a minimal 10-20 lines of SQL code describing how to upsample different columns.Current deployment & future plansBesides TimescaleDB, our other major components in the software development stack are Svelte/SvelteKitweb frontend framework and Python, Pyramid, andSQLAlchemybackend.I invite everyone evaluating TimescaleDB toread our blog post about our software architecture.The current architecture diagramAt the moment, we have trading data from 1000 decentralised exchanges (aka dexes), from three blockchains (Ethereum, Polygon and Binance Smart Chain), featuring 800k trading pairs. For reference,NASDAQhas only 3000 trading pairs, giving a reference point for the massive diversity of blockchain and cryptocurrency markets! Currently, we are fitting everything on one 1 TB database, but we are still early on what kind of data points we collect. We expect the database to grow dozens of terabytes over the next year.Architecture diagram of trading data sourcesTrading Strategy is completing its seed round. So far, the team has been lean. We expect to start growing as a business now, as our business is finding a product-market fit. We are looking to launch user-accessible trading strategies later this year, as soon as we are confident the software stack is well-behaving and related smart contracts are secure.We are at the bleeding edge of blockchain technology. Many of the components we built and many of the problems we solve we do as the first in the world.TimescaleDB allows us to focus on solving these business problems without us needing to build the infrastructure layer ourselves.🗯️If you are interested in crypto trading strategies, please come to ask any questions in ourpublic Discord chat.Advice & resourcesIf you are generally interested in algorithmic trading and machine-based solutions on financial markets, pleaseread our announcement blog postto learn about our vision for decentralised finance and decentralised protocol.To see TimescaleDB in action, youcan explore our public real-time API endpoints, view ourreal-time market data charts, ordownload historical market datasets generatedout from TimescaleDB.Trading Strategy contributes heavily to open source. You canstudy our Trading Strategy Python clientand our100% open source SvelteKit frontend.If you have software development questions or questions about trading strategies, please come to ask any questions inour public Discord chat.We also love coffee brewing tips shared by the TimescaleDB. Due to increased brewing activity, our team is now 150% caffeinated.It matters, and is one of the major reasons I came to Timescale to work in#DevRel.And for the record, I'm totally up for answering any coffee questions you want to throw our way. ☕️😉https://t.co/f74kT9eHWF— Ryan Booz (@ryanbooz)January 19, 2022We’d like to thank Mikko and the entire Trading Strategy team for sharing their story! We applaud your effort to give access to high-quality trading strategies to users around the world.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-trading-strategy-built-a-data-stack-for-crypto-quant-trading/
Automated Mocking Using API Traffic: Speedscale's Story,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Ken Ahrens, co-founder and CEO of Speedscale, joins us to share how they modernize and automate testing practices for cloud infrastructure by providing API traffic data going into and out various microservices. Thanks to TimescaleDB, Speedscale´s UI loads quickly and provides the ability to drill deep down into high-fidelity data.Speedscaleis one of the first commercial technologies utilizingactual API trafficin order to generate tests and mocks. Speedscale helps Kubernetes engineering teams validate how new code will perform under production-like workload conditions.Speedscale service mapSpeedscale provides unparalleled visibility, collects and replays API traffic, introduces chaos, and measures the golden signals of latency, throughput, saturation, and errors before the code is released.Screenshot of Speedscale UISpeedscale Traffic Replayis a modern load, integration, and chaos testing framework -- an alternative to legacy scripting tools that can take days or weeks to run and do not scale well for modern architectures.If your organization provides a SaaS product that is critical for revenue, or your team is responsible for performant infrastructure, Speedscale is for you. Speedscale enables engineering leaders to generate quality automation quickly without the need for manual scripting. Since actual API calls (not scripts) are the primary ingredient, tests and mocks can be built and regenerated quickly to keep pace with the speed of changing business requirements.As microservices become more separated logically, they are highly dependent on each other to deliver the expected functionality. This means performance problems become distributed across multiple services and can be difficult to trace.  Multiple contributing factors affect the state of an application in microservices and Kubernetes environments. A testing harness that closely mirrors the production setup and incoming traffic has become a requirement for highly distributed and containerized environments.By leveraging traffic to automatically generate sophisticated mocks, engineers and testers are granted the ability to isolate and contract/performance test smaller components in the context of a tightly coupled architecture.  This superpower enables rapid testing iterations. Moreover, without the need for scripting, testing can finally move as fast as development.About the foundersSpeedscale’s leadership team comes from companies like New Relic, Observe Inc, Wily Introscope (bought by CA Technologies), and iTKO (bought by CA Technologies).My name isKen Ahrens. I am co-founder and CEO of Speedcale. Much of my career has been focused on helping companies develop and manage complex web applications. I previously ran North America teams for New Relic and CA/Broadcom. Previous startups included Pentaho (acquired by Hitachi), ITKO (acquired by CA/Broadcom), and ILC (acquired by General Dynamics). My first foray into programming started with a brand new language called Java at Georgia Tech and has grown into a lifetime interest.Matthew LeRay, co-founder and CTO at Speedscale, has invested the past 20 years in improving the performance of applications across multiple generations of technology. Previously, he was head of product at Observe, SVP at CA Technologies (acquired by Broadcom), and engineering leader at ILC (acquired by General Dynamics). He is an alumnus of Georgia Tech in both Computer Engineering and Business. His first love is debugging Golang code but he occasionally takes a break to craft hand-carved guitars.Nate Lee, co-founder and VP of Sales & Marketing, has served a variety of roles within the software industry. Most recently, he was in enterprise sales for the digital transformation consultancy Contino (acquired by Cognizant). Prior to Contino, he served as Product Manager at CA Technologies, by way of iTKO where he was a Presales Engineer for 6 years. Before iTKO, he spent time as a support leader at IBM Internet Security Systems, and engineer at ILC (acquired by General Dynamics). He graduated from Georgia Tech with an MBA in Technology, and a BS in Computer Science.  You’ll most likely find him outdoors on 2 wheels when he’s not innovating with his Speedscale buddies.As a small, nimble startup, our normal workweek is comprised mostly of helping customers scope their use cases and deciding where to start with our testing framework (recording traffic, generating traffic replay “scenarios” comprised of tests and mocks of auto-identified dependencies). We are an engineering startup with a heavy emphasis on Kubernetes and Golang. We also are improving the protocol and version support of both our Enterprise Kubernetes version and Desktop version called theSpeedscale CLI.About the projectWe noticed the application of outdated testing practices to modern cloud infrastructure (eg. UI testing, manual testing, API test tools with no mocking). As the number of connections in distributed, containerized applications grew, the need for quality automation increases exponentially. As a result, the popularity ofcanary releases and blue green deploymentshave risen, but we believe this is due to a lack of robust quality automation alternatives.Speedscale uses a transparent proxy to capture API transaction data from which to model robust API tests and realistic mocks of backend dependencies. Our traffic replay framework iscapable of showing engineering teamscomplex headers, authentication tokens, message bodies, and associated metadata (with sensitive data redaction when necessary). In addition, the platform is able toautomatically identify backend dependenciesthat are needed to operate your service, allowing new developers on old services to get up to speed quickly. This data is streamed to AWS S3 and filtered for test and mock scenario creation. These scenarios can then be replayed as part of validation test suites and integrated into CI or GitOps workflows.Our customers need to be able to understand the API traffic going into and out of their various microservices over time. They want to see the sequence of API calls as well as the trend of the overall volume of calls.Data is ingested by our platform into our cloud data warehouse. As new data arrives, we determine the index where that API call can be found and write the index to TimescaleDB. Then we can use the data from TimescaleDB to find the original value. Because the indexes are much smaller than the original data, we are able to calculate aggregates on the fly and plot them in our user interface. The Traffic Viewer graph shows inbound and outbound calls, backend dependencies, and an “infinite scroll” list of traffic. All of these components are powered by TimescaleDB queries.Speedscale Traffic ViewerChoosing (and using!) TimescaleDBWe knew right from the start that we have a time-series problem. There was always new data flowing in, and users wanted to focus on data from certain periods of time, they didn't just want all the data presented to them. We decided to use a time-series database to store the data.We wanted a technology that could run inside Kubernetes, is easy to operate (we are a startup after all) and scale for our needs. We initially implementedElasticsearchand exposed the data throughKibana. It let us quickly prototype the use cases and worked great for lower volumes of data. But it scaled poorly for our use case and we had very little control over the look and feel of the UI. Then we evaluated TimescaleDB, Influx, Prometheus and Graphite.We selected TimescaleDB because we were already using PostgreSQL as part of our technology stack, and also the paper evaluation looked like TimescaleDB would scale well at our load ranges.✨Editor’s Note:For more comparisons and benchmarks, seehow TimescaleDB compares to InfluxDB, MongoDB, AWS Timestream, vanilla PostgreSQL, and other time-series database alternativeson various vectors, from performance and ecosystem to query language and beyond.We useSQiurreLto issue SQL queries like this one that powers the inbound throughput graph.rrPairsQuery := sq.Select(
		fmt.Sprintf(""time_bucket(INTERVAL '%s', time) AS bucket"", durationToSQLInterval(interval)),
		""is_inbound"",
		""COUNT(id)"").
		From(rrPairsTableName).
		GroupBy(""is_inbound"", ""bucket"").
		OrderBy(""bucket"")We deploy TimescaleDB on theKubernetes operatorviaFlux. Our core services are currently written in Golang which we use to connect TimescaleDB with microservices inside Kubernetes as well as AWS Lambda.Currently, our architecture that touches TimescaleDB looks like this:Current Speedscale architecture diagramAfter implementing TimescaleDB, our AWS cloud costs went down about 35% because it is cheaper to run than the AWS OpenSearch we used before. In addition, the query performance improved dramatically, a majority of queries take under 100ms to complete.Advice & resourcesHaving aKubernetes operatorwas a big help for us because it was proof that this was built for our architecture.We’ve made a version of our traffic capture capability available as a free CLI which you can find here:https://github.com/speedscale/speedscale-cliWe’d like to thank Ken and all folks from Speedscale for sharing their story. We applaud your efforts to modernize and automate testing practices for modern cloud infrastructure.🙌We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/automated-mocking-using-api-traffic-speedscales-story/
How NLP Cloud Monitors Their Language AI API,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Julien Salinas, full-stack developer, founder, and chief technology officer (CTO) at NLP Cloud, talks about how his team is building an advanced API to perform natural language processing (NLP) tasks while taking care of the complexity of AI infrastructures for developers.About the CompanyNLP Cloudis an advanced API for text understanding and generation in production. The most recent AI models are easy to use on NLP Cloud (GPT-NeoX 20B, GPT-J, Bart Large, among others). Thanks to the API, you can perform all kinds of natural processing language processing tasks: text summarization, paraphrasing, automatic blog post generation, text classification, intent detection, entity extraction, chatbots, question answering, and much more!Several API clients are available, so you can easily add language AI to your application in Python, Go, JavaScript, Ruby, and PHP. You can also train or fine-tune your own AI model on NLP Cloud or deploy your in-house models.Today, more than 20,000 developers and data scientists use NLP Cloud successfully in production! They love NLP Cloud because they don’t want to deal with MLOps (DevOps for machine learning) by themselves. NLP Cloud takes care of the complex infrastructure challenges related to AI (GPU reliability, redundancy, high availability, scaling, etc.).About the TeamMy name is Julien Salinas, and I’m a full-stack developer, founder, and CTO of NLP Cloud. Our company has a team of six high-level engineers skilled in NLP, DevOps,  and low-level optimization for machine learning.The team works hard to provision state-of-the-art NLP models like GPT-NeoX (equivalent to OpenAI’s GPT-3) and make sure that these models run reliably in production and at an affordable cost.About the ProjectA summarization task performed by the NLP Cloud APIWe realized we needed a time-series database when our users asked us for a pay-as-you-go planfor GPT-J, one of our open-source NLP models. They wanted to be charged based on the number of words they’re generating, the same way OpenAI does with their GPT-3 API. Our users also wanted to monitor their usage through their NLP Cloud dashboard.So, we started implementing TimescaleDB to log the following:The number of API calls per user and API endpointThe number of words sent and generated per user by GPT-J and GPT-NeoXThe number of characters sent and received by our multilingual add-onWe had two main requirements:Writing the data had to be very fast in order not to slow down the APIQuerying the data had to be easy for our admins to quickly inspect the data when needed and easily show the data to our customers on their dashboardChoosing (and Using!) TimescaleDB✨Editor’s Note:Not that you really need them, but here are nine reasons to choose TimescaleDB vs. InfluxDB or AWS Timestream.I found out about Timescale by looking for InfluxDB alternatives. I found the Telegraf, Influx, and Grafana (TIG) stack quite complex, so I was looking for something simpler.“TimescaleDB is a cornerstone of our pay-as-you-go plans”The top factors in my decision for Timescale were the following:Easy data downsampling thanks to continuous aggregatesPostgreSQL ecosystem: no need to learn something new, and we were all already skilled in SQL and PostgreSQL, so it saved us a lot of time and energyWe use TimescaleDB behind ournatural language processing APIto track API usage. Based on that, we can do analytics on our API and charge customers depending on their consumption. TimescaleDB is a cornerstone of our pay-as-you-go plans. Most of our users select such plans.If you want to see how we do it, I detailedhow we use TimescaleDB to track our API analyticsin a previous blog post.“The greatest TimescaleDB feature for us is the ability to automatically downsample data thanks to continuous aggregates”Before using TimescaleDB, we did very naive analytics by simply logging every API call into our main PostgreSQL database. Of course, it had tons of drawbacks. We had always known it would be a temporary solution as long as the volume of API calls remained reasonably low (right after launching the API publicly), and we quickly switched to TimescaleDB as soon as possible.We also evaluated a TIG solution (InfluxDB) but found that the complexity was not worth it. If TimescaleDB did not exist, we would maybe stick to a pure log-based solution backed by Elasticsearch.Current Deployment and Future PlansWe use TimescaleDB as a Docker container automatically deployed by our container orchestrator. Two kinds of applications insert data into TimescaleDB: Go and Python microservices. To visualize the data, we’re using Grafana.The greatest TimescaleDB feature for us is the ability to automatically downsample datathanks to continuous aggregates. We’re writing a lot of data within TimescaleDB, so we can’t afford to keep everything forever, but some high-level data should be kept forever. Before that, we had to develop our own auto-cleaning routines on PostgreSQL: it was highly inefficient, and some of our read queries were lagging. It’s not the case anymore.✨Editor’s Note:Learn how you can proactively manage long-term data storage with downsamplingorread our docs on downsampling.The NLP Cloud API is evolving very fast. We are currently working hard on multi-account capabilities: soon, our customers will be able to invite other persons from their team and manage multiple API tokens.In the future, we also plan to integrate severalnew AI modelsand optimize the speed of our Transformer-based models.Advice and ResourcesWe recommend Timescale to any development team looking for a time-series solution that is both robust and easy to deal with. Understandably, most developers don’t want to spend too much time implementing an analytics solution. We found that TimescaleDB was simple to install and manage for API analytics, and it scales very well.TheTimescaleDB docsare a very good resource. We didn’t use anything else.My advice for programmers trying to implement a scalable database strategy? Don’t mix your business database or online transaction processing (OLTP) with your online analytical processing or analytics database (OLAP).It’s quite hard to efficiently use the same database for both day-to-day business (user registration and login, for example) and data analytics. The first one (OLTP) should be very responsive if you don’t want your user-facing application to lag, so you want to avoid heavy tasks related to data analytics (OLAP), as they are likely to put too much strain on your application.Ideally, you want to handle data analytics in a second database that is optimized for writes (like TimescaleDB) and is perfectly decoupled from your OLTP database. The trick then is to find a way to properly move some data from your OLTP database to your OLAP database. You can do this through asynchronous extract, transform, and load (ELT) batch jobs, for example.We’d like to thank Julien and his team at NLP Cloud for sharing their story and writing a blog post on how the NLP Cloud Team uses TimescaleDB to track their API analytics.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-nlp-cloud-monitors-their-language-ai-api/
Visualizing IoT Data at Scale With Hopara and TimescaleDB,"IntroductionI have been involved in building DBMSs for 50 years. During that time, I have been the main architect behind PostgreSQL (the foundation of Timescale), Vertica, VoltDB, and Paradigm4. Recently, I have been studying user-facing problems, most notably, “How do users derive information from the massive amount of data we are collecting?”Sometimes users know exactly what they are interested in, and a conventional dashboard (such as Tableau or Spotfire) can help them find it. Often, however, the real question behind a query a user wants to run is, “Tell me something interesting?” I.e., “Show me an actionable insight.” To provide these meaningful data insights, a sophisticated visualization system is needed to complement more traditional analytics systems.I have long been a fan of Google Maps, which allows you to go from a picture of the Earth to the plot map on your street in 21 clicks. This is an exemplar of a “detail on demand” system, often called a “pan-zoom” interface. A big advantage of such systems is that no user manual is required since the interface is so intuitive. Unfortunately, Google Maps only works for geographic data. So what to do if you have floor plans, 3D models, scatter plots, or the myriad of other representations that users want to see?Hoparacan be thought of as “Google Maps on steroids.” It will produce pan-zoom displays for any kind of data. It is especially applicable for real-time monitoring applications, often from IoT data collection or asset tracking of sensor-tagged devices.Hopara is almost three years old, headquartered in Boston, and employs 12 people.Figure 1. Example of a Hopara app in the lab space (powered by Timescale)In this post, I will walk you through a Hopara monitoring application powered by TimescaleDB. It shows the benefit of Hopara visualization aided by a traditional analytics dashboard. This application reports vibration issues in sensor-tagged machines, and the real-time vibration data is stored in a Timescale database for effective real-time querying.The Problem: Monitoring (Lots of) Sensor Data in Real TimeLet’s consider a French company that operates 58 factories in Brazil, manufacturing construction materials (think pipes, glue, and nails). This company is referred to as FC in the rest of this post.FC is in the process of installing about 50K sensors from a Brazilian vendor, IBBX. These sensors primarily report vibration, typically at 100 msec intervals. Excessive vibration is often an early warning of machine failure or the need for urgent service. Hence, real-time monitoring is required for 50K time series of vibration data.Unlike some applications that can aggregate data in the network from sensor to server, FC requires that details be available on all sensors so abnormal events can be reported on individual machines.These abnormal events can include the following:Latest reading over a thresholdFive sequential readings over five minutes above a second thresholdOne reading per minute over a third threshold for 10 minutesA reading over a fourth threshold more than once a day for a monthIn addition, FC wants to filter machines by location (e.g., only those plants in São Paulo) and by time (e.g., report only weekend vibrations).A time-series database is the optimal solution for storing the 50K time-series sensor data for a long period of time. Although this database is likely measured in GB, it is easy to imagine much larger applications.In the rest of this blog post, I first talk about the FC reporting architecture and their future requirements. Then, I discuss the requirements for the database, and why Hopara ended up using Timescale.From Data to Insights: Building Actionable VisualizationsFC runs a sophisticated analytics application provided by IBBX. It monitors vibrations and employs machine learning-based predictive analytics to forecast needed repair events. The IBBX dashboard for FC is shown in Figure 2. It shows “the details” using typical dashboard technology.  This is very useful for initiating corrective action when required.Figure 2. FC/IBBX dashboard using time-series sensor data in real timeHowever, this dashboard does not show “the big picture” desired by the FC personnel. Hence, IBBX recently partnered with Hopara to produce a “detail-on-demand” system for the same data. Figure 3 shows FC factories on a map of Brazil color-coded with their facility health.Figure 3. The Big Picture: FC factories on a map of Brazil color-coded with their facility healthNotice that there are two factories with a yellow status. If an FC user wants to “drill into” one of them, the user will see Figure 4. Figures 4 and 5 shows greater detail about one yellow sensor in Figure 3, while Figure 6 shows the time series of sensor readings for the sensor in question over five days. Notice that, in a few clicks, a user can move from an overview to the actual time-series data.FC users appreciate both the analytics dashboard and the Hopara drill-down system. As a result, IBBX and Hopara combined their software into a single system. The takeaway from this example is that there is a need for predictive analytics and insightful visualization. IoT customers should have both kinds of tools in their arsenal.✨Editor's Note:If you want to learn more abouttime-series forecasting, its applications, and popular techniques, check out this blog post.Figure 4. The factory in questionWe now turn our attention to response time. It is accepted pragma that the response time for a command to a visualization system must be less than 500 msec. Since it takes some time to render the screen, the actual time to fetch the data from a storage engine must be less than this number. The next section discusses DBMS performance considerations in more detail.Behind The Scenes: Powering Real-Time Visualizations Using TimescaleFigure 5. More real-time detailsFigure 6. The actual dataAs mentioned previously, these real-time views are powered by TimescaleDB. TimescaleDB is built on top of PostgreSQL and extends it with a series of extremely useful capabilities for this use case, such asautomatic partitioning by time,boosted performance for frequently-run queries, andcontinuous aggregates for real-time aggregations.To guarantee a real-time display, Hopara fetches live data from the database for every user command. Otherwise, stale data will be rendered on the screen. The screenshots above come from a real Hopara application interacting with a database. We note in Figure 2 that the alerting condition is the first one mentioned in a previous section (the latest reading over a threshold).In other words, the display is showing a computation based on the most recent values from the various sensors. Specifically, Hopara uses a database with the schema shown in Figure 7, with a Readings table with all the raw data. This is connected to a Sensor table with the characteristics of each individual sensor.Figure 7. The database schemaThen, the display in Figure 2 requires fetching the most recent reading for each sensor. This can be produced by running the following two-step PostgreSQL query:–— get the latest reading timestamp + sensor_id
SELECT max(timestamp) as timestamp, sensor_id
FROM readings
GROUP BY sensor_id


—— get the reading value
SELECT l.timestamp, l.sensor_id, r.value FROM latest l
INNER JOIN readings r ON r.sensor_id = l.sensor_id AND r.timestamp = l.timestampThis query, while easy to understand, is not efficient. The following representation leverages PostgreSQLdistinct onand Timescaleskip scanto perform the query faster, so it is a preferred alternative.SELECT DISTINCT ON (sensor_id) * 
FROM readings
WHERE timestamp > now() - INTERVAL '24 hours' 
ORDER BY sensor_id, timestamp DESC;Note that condition one can be triggered inadvertently, for example, by a worker brushing against the machine. Hence, some combination of conditions 2-4 is a more robust alerting criterion. Unfortunately, these require complex real-time aggregation, which is not present in PostgreSQL. As a result, Hopara switched to TimescaleDB, which extends PostgreSQL with these capabilities (through its continuous aggregate capabilities).Turn now to Figure 6, which displays five days of data for three sensors. Since each sensor is reporting every 100 msec, there are around 4.5M observations in a five-day window. Obviously, this level of granularity is inappropriate for the display presented. In addition, there is no way to produce the display within the required response time. Hence, Hopara aggregates the raw data into two-minute averages using Timescale’scontinuous aggregates(Figure 5 displays these averages).Timescale’shypertablesautomatically partition big tables, so it’s easier for the query planner to localize time-series data. This greatly accelerates queries such as the one required to produce Figure 5, another reason for the switch from PostgreSQL to TimescaleDB.As a result, Hopara and TimescaleDB are powerful tools for IoT applications of the sort discussed in this blog post.We want to thank Professor Michael Stonebraker and the team at Hopara for sharing their story on how they are providing meaningful visualization solutions for their customers’ sensor data. As PostgreSQL lovers and enthusiasts, we are incredibly grateful and proud to see Professor Stonebraker using TimescaleDB to provide real-time insights to Hopara’s customers.If you want to learn more about Timescale and see how we handle time-series data, events, and analytics,read our Developer Q&As. These are real stories from real developers working in the real world. And if you want to try our cloud solution, sign up for a 30-day free trial. You will have access to all its unique features, from continuous aggregations to advanced analytical functions (and you don’t even need a credit card!).About the AuthorMichael Stonebrakeris a pioneer of database research and technology. He joined the University of California, Berkeley, as an assistant professor in 1971 and taught in the computer science and EECS departments for 29 years.While at Berkeley, he developed prototypes for the INGRES relational data management system, the object-relational DBMS, POSTGRES, and the federated system, Mariposa. He is the founder of three successful Silicon Valley startups whose objective was to commercialize these prototypes.Mike is the author of scores of research papers on database technology, operating systems, and the architecture of system software services. He was awarded the ACM System Software Award in 1992 (for INGRES) and the Turing Award in 2015.He was elected to the National Academy of Engineering and is presently an adjunct professor of computer science at MIT’s Computer Science and AI Laboratory (CSAIL.)The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/visualizing-iot-data-at-scale-with-hopara-and-timescaledb/
How Octave Achieves a High Compression Ratio and Speedy Queries on Historical Data While Revolutionizing the Battery Market,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Nicolas Quintin, head of Data atOctave, shares how the company migrated from AWS Timestream to Timescale in search of a more mature and scalable database and is revolutionizing the battery market by improving the battery systems’ safety and predicting maintenance needs.To do so, the Octave team collects and analyzes millions of data points daily while dramatically saving disk space (their compression ratio is 26.06!) and delivering speedy queries on historical data on client-facing applications. How do they do it? With a little help from Timescale's compression capabilities and continuous aggregates.About the CompanyOctaveis a cleantech company based in Belgium that gives electric vehicle (EVs) batteries a second life. We develop energy storage systems by repurposing usedlithium-ion (Li-ion) batteriesand transforming them into smart, sustainable assets to store the excess wind and solar energy.Batteries from an electric vehicle are typically retired when their usable capacity has decreased to roughly 80 percent. Octave gives these batteries a new life through smart solutions for stationary energy storage, for which demand is rapidly growing. This way, we can save resources and raw materials that would be traditionally used to produce new batteries.The company repurposes batteries from electric vehicles to create these battery cabinetsOctave’s batteries are suitable technologies for small and medium-sized enterprises or industrial sites looking to optimize their energy management or decrease their electricity bill amid the record-high energy prices in Europe.More specifically, Octave’s sustainable energy storage system allows customers to increase their self-consumption and cope with the intermittency of renewable energy sources. It also enables customers to participate actively in the energy markets, becoming more independent from energy suppliers, fossil fuels, and grid operators.About the TeamWe’re currently a team of around 10 people working on everything related to electrical and mechanical design engineering, embedded and software engineering, and business development. We’re growing fast!As the head of data, my role at Octave is to collect data from the edge devices, store them in our databases, develop data pipelines, improve and optimize the battery state algorithms through big data analytics, and present actionable insights in dashboards. Finally, I also ensure that this entire process happens seamlessly.About the ProjectOctave distinguishes itself from traditional battery suppliers by leveraging the plethora of data and measurements from the battery system and building up an extensive history of each battery cell.We handle large streams of battery measurements with clear time-series characteristics: each data point is composed of a timestamp and a value. These data are collected from the battery systems (our IoT edge devices) and sent back to our cloud for further analysis.Among the information we collect, the most basic yet crucial data points are undoubtedly the voltage and temperature measurements from each battery cell in operation.One of Octave’s dashboards made with Grafana and Timescale""We initially used AWS Timestream in the early days of Octave, which at first seemed a natural choice to handle our time-series data since our cloud infrastructure was entirely built in AWS. However, we quickly realized we would need a more widely used, mature, and scalable database solution""The data are streamed back to ourBattery Cloud(our in-house developed cloud platform, hosted on AWS and relying on Timescale databases), where they are crunched and further processed. This allows us the following:Analyze the battery cells’ behavior and degradation based on their history and how they are cycled (the temperature, current levels, and depth of discharge significantly impact the lifetime of batteries!).Improve the safety of the system by immediately detecting anomalies.Implement a data-driven predictive maintenance process. The ultimate goal is to predict when to replace a used battery module, and by doing so, we can extend the lifetime of the entire system. This is a true game-changer for second-life battery systems.✨Editor's Note:Learn what time-series forecasting is, its applications, and its main techniques in this blog post.A diagram of Octave’s Battery CloudChoosing (and Using!) TimescaleDBI have been using PostgreSQL for some time, and we were keen to use tools and stacks we were familiar with for the sake of efficiency. So inevitably, I was immediately interested when I heard about an interesting PostgreSQL extension called TimescaleDB, specifically built to handle time-series data, which we knew we needed since we were dealing with a typical IoT use case.""Timescale has proven to be a key enabler of Octave’s data-driven Battery Cloud technology""We initially used AWS Timestream in the early days of Octave, which at first seemed a natural choice to handle our time-series data since our cloud infrastructure was entirely built in AWS. However, we quickly realized we would need a more widely used, mature, and scalable database solution if we ever wanted to scale our operations and deploy several dozen or hundred second-life battery systems in the field. So, we went looking for alternatives.After some research, Timescale quickly became our preferred option, given itsimpressive compression ratios, lightning-fast queries,unmissable continuous aggregates, friendlycommunityandextensive documentation, and most importantly, its plain PostgreSQL syntax.The cherry on the cake is Timescale's ease of use and user-friendly interface.We went immediately for Timescale because we were looking for a managed service, and Timescale seemed the recommended option and was nicely compatible with our preferred AWS region.✨Editor’s Note:Timescale’s user interface now offers an even friendlier and user-centered experience.The Timescale Team shared their redesign journey and lessons in this blog post.We have found TimescaleDB’s compression ratio to be absolutely phenomenal! We’re currently at a compression ratio of over26, drastically reducing the disk space required to store all our data.A narrow table modelOctave’s compression ratio with TimescaleThe power of continuous aggregates is hard to overstate:they are basically materialized views that are continuously and incrementally refreshedand allow for lightning-fast queries on large historical datasets.I have found thedocumentationto be very clear and abundant. Every feature is very well explained. So it’s very easy toget started with TimescaleDB. And if we have more precise questions specifically related to our use case, we can always rely on our customer success manager or the very reactive Support Team. 🙂✨Editor’s Note:Read how our Support Team raises the bar on hosted database support.Current Deployment & Future PlansMost of our backend software is currently written in Python. We leverage AWS IoT to manage our battery fleet and circulate the data between our edge devices and our battery cloud viaMQTT.We rely on some ETL (extract, transform, load) pipelines interacting with Timescale that extract battery measurements and insert the processed data back into the database. We also use some dashboarding tools, such asGrafanaandStreamlit, as well as API endpoints connected to our Timescale database.It’s no secret that time-series data can grow very fast. We currently store close to one million data points per battery system daily. As we have already sold our first 28 battery cabinets, the size of our database is expected to increase quickly. This is only the start, as we expect to triple or quadruple sales by the end of 2023.So far, we have found Timescale to be powerful, scalable, and pretty well-suited for our application. Timescale has proven to be a key enabler of Octave’s data-driven Battery Cloud technology.We have started to leverage the power of continuous aggregates. For example, continuous aggregates are queried behind the scenes of our customer-facing applications. They enable our clients to quickly and seamlessly inspect and download historical data of their second-life battery systems.SELECT time_bucket(INTERVAL '15 min', time) AS bucket,
    bms.iot_thing,
    bms.iot_device,
    bms.name,
    bms.string,
    bms.module,
    bms.cell,
    avg(bms.value_real) AS value_real,
    last(bms.value_str, time) AS value_str
FROM public.bms
GROUP BY bucket, iot_thing, iot_device, name, string, module, cell;✨Editor’s Note:Here are three reasons you should upgrade to the new version of continuous aggregates.RoadmapWe are very proud and honored to recently have won the competitive call from theEIC Accelerator, led by the European Innovation Council! We’re now working towards industrializing and scaling our battery cloud technology.Advice & ResourcesI believe that getting started with a hands-on example is always a good way to evaluate something. You can try Timescale for free with a demo dataset to get acquainted with the service.So I recommend quickly spinning up a first TimescaleDBinstance and having fun playing around with the service.Also,there is very extensive documentation and tons of examples and tutorialsavailable on the website, which helps you quickly master Timescale! So make sure to have a look at the website.Anything else you'd like to add?Thanks to Timescale for this opportunity to share our story and our mission at Octave!Want to read more developer success stories?Sign up for our newsletterfor more Developer Q&As, technical articles, tips, and tutorials to do more with your data—delivered straight to your inbox twice a month.We’d like to thank Nicolas and all the folks at Octave for sharing how they’re leveraging continuous aggregates and Timescale’s compression powers to handle their millions of battery cell data points daily.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/high-compression-ratio-and-speedy-queries-on-historical-data-while-revolutionizing-the-battery-market/
How Density Manages Large Real Estate Portfolios Using TimescaleDB,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, part of the team atDensity—Shane Steidley, director of software, andBrock Friedrich, software engineer—explain how they are using TimescaleDB to query data from IoT devices to help companies with substantial real estate footprints make data-driven decisions to optimize their real estate usage while reducing carbon emissions.About the CompanyShane:Density builds technology that helps companies understand how people use physical space. Founded in 2014, our platform provides workplace analytics enabled by custom-built sensors which capture the complexities of how people move through and use space without invading privacy. These sensors feed raw data to Density’s analytics platform—a fast, data-rich system that provides comprehensive insights into how spaces are used, allowing companies to easily compare a space's performance against its intended purpose, another space, or portfolio benchmarks.The platform can reveal if employees are choosing focus space or collaborative space, which floors are the most popular (and why), and how one office’s occupancy rate compares to others in a portfolio or against industry benchmarks. Today, our technology is helping inform decisions for the workplaces of some of the largest companies in the world, spanning 32 countries, with more than 1.25 billion square feet under management. Our growing list of customers ranges from Fortune 100 to high-growth tech companies that see the value in understanding the performance of their office spaces to lower operational costs, improve employee experience, and reduce their carbon footprint.Our ultimate mission is to measure and improve humanity’s footprint on the world—optimizing commercial real estate portfolios is just the first step. Thirty-nine percent of CO2  emissions are directly or indirectly linked to real estate construction, so if we can help companies measure and optimize their use of real estate, we can have an outsized impact on the different issues that are impacting climate.A tweet from the Density Team on Earth DayWith Density, customers can understand how their space is used and right-size it. When we started, we needed to sell the problem and then sell our solution. Post-COVID, everybody understands the problem and the data that they could have access to, and how Density can provide value.Brock: Density also provides that value while maintaining the anonymity of each person that walks underneath our sensors.Shane: Privacy is very important to us; it’s one of our core tenets. And because of this, we don’t use cameras—we use other sensor types, namely infrared and radar, that don’t capture any personally identifiable information (PII).About the TeamShane: Density was born out of a web development consultancy. The consultancy founders got tired of walking to their favorite coffee shop during the cold, harsh winter in upstate New York only to find a long line. This unpleasant experience gave them the idea to create an occupancy sensor.As you might expect, given the company’s origin, the Density engineering team started with 4-5 engineers with a heavy web development background. We have since grown our engineering team into a team capable of performing what I call true full-stack development. We have an electrical engineering team that designs our hardware and lays out the boards, and our devices are manufactured in Syracuse, NY, where we also have an R&D lab. Our team includes experts in mechanical design and embedded software, and we now have a backend system with multiple pipelines (whose data typically ends up in TimescaleDB) and multiple React-based frontend applications.Brock: As a team, there are many different daily routines. It’s a mix of running production systems like Shane mentioned, live data pipelines, running historical analysis, or writing new features.Shane:And we’re hiring! If you are interested in joining the Density Team,please check out our jobs page!About the ProjectShane: We use different types of data at different stages. Our entry sensors generate infrared data that is very dense. It’s not feasible to send this dense data to the cloud,  so we have to do all processing and machine learning at the edge (on the device).  The results of this edge processing are +1/-1 counts that can be aggregated in our pipelines and TimescaleDB.Our radar-based sensors generate sparse data, so we do less processing at the edge and more processing in the backend. That data has to go through a pipeline and get transformed before it makes sense to insert it into TimescaleDB and perform any aggregations. TimescaleDB really provides value when it comes to querying the data, allowing customers to slice the data up in multiple dimensions. That is something that just wasn’t easy before we started using TimescaleDB.Brock:Yeah, to tack onto that, in TimescaleDB we store counts of people in spaces over time, and a wealth of derivative metrics off of those people counts, things like how long were people in spaces, not specific people, but how long was what we call dwell time?How long was this space used continuously, or what was its usage compared to similar space types throughout the day?One of the parts I think Shane was trying to highlight is that there's a real dynamism to how those queries can take shape in that they can be sliced and diced and composed in a more or less arbitrary number of ways. And TimescaleDB’s flexibility—it is built on a relational model at the end of the day—and its ability to do the partitioning under the covers and thehypertablesto let us access all of the data back in time very quickly is the magic combination that we were looking for in a time-series database to meet our use case.Choosing (and Using!) TimescaleDBBrock: I found out about Timescale back in a previous life, circa early 2019. I worked for an oil and gas firm and was doing a lot of research into time-series storage and the options available in that space because we were developing a data software solution for supervisory control and data acquisition. Essentially, it boiled down to real-time remote sensing and monitoring for industrial controls.During that research, I happened across TimescaleDB, which was still pretty early on. It was right about the timecontinuous aggregatescame out, which was one of the big selling points for me. So when I came to Density, they were just beginning to evaluate options for time-series databases for our applications. I was able to contribute my previous experience with TimescaleDB to that process. As we evaluated the options, TimescaleDB came out as the clear winner, and the rest is history.✨Editor’s Note:Read our documentationto learn more about continuous aggregates.Shane:As an IoT company, we’ve had sensors since the very beginning. And when we started, a lot of the engineering staff came from a web consultancy, so I don’t think we did realize from the beginning thatwe needed a time-series database or even quite knew what a time-series database was.“I think moving forward, TimescaleDB, at least in my opinion, is just going to be the default time-series database”Shane SteidleyWhat seems obvious to us now wasn’t obvious back in 2017, when we built an entire time-series database using stored procedures and vanilla PostgreSQL. It was pretty cool when we finally brought over TimescaleDB. We were like: “Oh, it just does all this for us! Look, there’s a bucket function, and it’s going to return the right size buckets that you need. And it handles all of the weirdness of time zones and daylight savings time.” And you can ingest all this data, whereas before, because of how we were using PostgreSQL, we would struggle with ingesting the amount of data we needed to ingest.I think moving forward, TimescaleDB, at least in my opinion, is just going to be the default time-series database. I think you're just going to have to have a reason not to use TimescaleDB because it's so simple and fits in with PostgreSQL.Brock:The top factors that led us to TimescaleDB specifically were its tolerance for high insert rates. It's just blown away all of our expectations, even based on benchmarks that we were able to see online at the time. It's built on top of PostgreSQL, as Shane talked about earlier. There's very little in the way of a special TimescaleDB domain-specific language, and it's operationally very familiar to operating vanilla PostgreSQL.Both of those were huge wins, just both operationally and development-wise. We always have the ability to fall back on core PostgreSQL principles or relational data models as we need to, but we also have the capability to dive deeper into TimescaleDB’s specific functionality to meet those big time-series use cases.“I get the most pride in doing plain SQL against TimescaleDB, getting time-series results at scale, and not having to do a bunch of backflips”Brock FriedrichShane:We use TimescaleDB like a time-series database should be used. We use it not just for the continuous aggregates of count data and other metrics that Brock's touched on, but the bucketing, the things that are so complicated if you push them to application code. When handled in TimescaleDB, it just gives you the right data the way that you want it. There are obviously some edge cases, but 99 % of the time, TimescaleDB just does what you want it to do.Brock: What would we use if TimescaleDB didn't exist is one of those topics that I like not to think about because it gives me anxiety. There are plenty of other time-series database options, but none fit the cross-section of requirements that at least Density has in quite the manner that TimescaleDB does. So whenever somebody else asks me that question, like in passing, I just say, “Let's just pretend like that's not possible and go about our business.”I get really proud of querying against TimescaleDB whenever we run really simple queries, like selecting the data from one of our sensors for a day from six years ago, and we run that at scale, and it runs like it would run in a much smaller scale like we only had a few months of data. And that goes back to one of the things I was appreciating earlier, which is that chunk exclusion –– that ability to operate what would be these really large query plans for vanilla PostgreSQL, and trim them down to something that’s predictable and reasonable, and operates at relatively low latencies. So all that’s to say, I get the most pride in doing plain SQL against TimescaleDB, getting time-series results at scale, and not having to do a bunch of backflips./* This query yields space count aggregates at 10-minute resolution for a given set of spaces over a three-day period,
   where each 10-minute bucket is represented in the output, even if that bucket contains no data.The query first
   selects data from a sparse one-minute cagg and unions that data to a set of empty records, generated with the Postgres
   generate_series function, then rolls up the unioned records into 10-minute aggregates.
   The union against the set of empty records ensures that all 10-minute intervals are represented in the final results.
   This step is necessary as the one-minute data is sparse, meaning a given 10-minute interval could contain no data, and
   the time_bucket_gapfill function does not register that a bucket needs to be injected if no records exist within
   an interval.
 */

select und.space_id,
       time_bucket('10m', und.inner_bucket)                                                        as bucket,
       min(und.occupancy_min)                                                                      as occupancy_min,
       max(und.occupancy_max)                                                                      as occupancy_max,
       first(und.first_occupancy, und.inner_bucket) filter (where und.first_occupancy is not null) as first_occupancy,
       last(und.last_occupancy, und.inner_bucket) filter (where und.last_occupancy is not null)    as last_occupancy
from (select c1m.bucket          as inner_bucket,
             c1m.space_id        as space_id,
             c1m.occupancy_min   as occupancy_min,
             c1m.occupancy_max   as occupancy_max,
             c1m.first_occupancy as first_occupancy,
             c1m.last_occupancy  as last_occupancy
      from cav_space_counts_1m c1m
      where c1m.bucket between '2022-05-22 13:00:00+0000' and '2022-05-25 13:00:00+0000'
        and c1m.space_id in (997969122178368367, 997969123637986180)
      union
      select time_bucket_gapfill('10m', generate_series,
                                 '2022-05-22 13:00:00+0000',
                                 '2022-05-25 13:00:00+0000') as inner_bucket,
             space_id,
             null                                            as occupancy_min,
             null                                            as occupancy_max,
             null                                            as first_occupancy,
             null                                            as last_occupancy
      from generate_series('2022-05-22 13:00:00+0000'::timestamptz,
                           '2022-05-25 13:00:00+0000'::timestamptz,
                           '10m')
               join unnest(array [997969122178368367, 997969123637986180]) as space_id on true) as und
group by und.space_id, bucket
order by bucket;✨Editor’s Note:Learn more about chunk exclusionin this blog post about how we fixed long-running now ( ) queries (and made them lightning fast), orread our docs for more info on hypertables and chunks.Current Deployment and Future PlansBrock:For data visualization, we useTableauandGrafana.The primary languages we use that interact with TimescaleDB are Rust and Python. Bonus: big shout out to JetBrains for their database IDE,DataGrip. It is the best on the market by a wide margin.✨Editor’s Note:Slow Grafana performance?Learn how to fix using downsampling in one of our recent blog posts.We find TimescaleDB to be very simple to use, just flat-out, dead simple. Any TimescaleDB-specific semantics, all of the defaults always take you a long way toward meeting whatever use case you're setting out to achieve. The narrative and API documentation online is first class, in my opinion.But maybe the most telling point is that there’s very little shock value whenever you’re discovering new feature value or features within TimescaleDB. And by shock value, I mean whenever I discover something like a continuous aggregate, for example, it operates conceptually almost identically to how vanilla PostgreSQL materialized view operates, but there’s extra pizazz on top that TimescaleDB does to meet the real-time component of the materialized view, refresh in the background, and all that.So I don't really want to undersell whatTimescaleDB is doing under the covers to make the magic happen. But, from an end perspective, coming from a PostgreSQL background, many of the features align conceptually with what I would expect if I was just writing something against the vanilla PostgreSQL.RoadmapShane:When we started, we threw everything at TimescaleDB, and now we’re being more responsible users of TimescaleDB. We’re at that sweet spot within TimescaleDB where it’s found product-market fit within Density.Brock:It’s hard to name my favorite TimescaleDB feature, but continuous aggregates have been a game-changer in a variety of different ways. Early on, whenever we first onboarded to TimescaleDB and deployed, it made development significantly faster, and we could just offload a lot of decision logic and complexity around time zone handling and bucketing (that’s a big one) to TimescaleDB and let it handle that complexity for us.Continuous aggregates come into play in that we were able to roll up multiple resolutions of our sensor account data, our people count data, and make that available in a much more efficient way with little-to-no effort so far as the code that we actually had to write to deliver those efficiencies.Continuous aggregates are becoming less important for us now than they were in our previous usage, just because as Shane was talking about earlier on, we're growing to such a size, and our use cases are becoming so complex that we're wanting to move to more the ETL (extract, transform, load) type of processing out of the database. So we're not monopolizing database resources to do some of those computations and move them upstream of the database into processing pipelines and still take advantage of continuous aggregates but to a lesser degree. We're doing less of the mathematical stuff in the database, but we're still using continuous aggregates to roll up high-resolution data to lower resolutions.Advice & ResourcesBrock:If I had to recommend resources, the first would probably be theTimescale blog. It’s been historically pretty informative over the years about the internals, like what’s happening in TimescaleDB underpinning a specific feature. One that I remember specifically is explaining thevarious compression algorithms in play for different data types within PostgreSQL. Being able to distill that knowledge down to a blog article that I could consume and then assimilate into my mental model of what was going on under the covers of the technology I was using has been helpful repeatedly.The advice that I would give for building a scalable database or a strategy around that is that when designing for an analytic workload specifically, don't direct any read load to the master or in standby notes. Always use a read replica for enough reasons that we probably don't have enough time to talk about here.The primary determinant of how efficient and scalable your solutions are going to be boils down to how many rows your queries have to touch for any given type of query you submit. Touching in that context includes both the scanning of index tuples and heap access for the rows, the end of the indexed contents of the rows, so be smart with your indexes and only index what you need and design towards the end of needing as few indexes as possible, because those are how you reduce that overhead that your database has to work through to fulfill a particular request or query.Shane: So my advice, at least for an IoT company, is to consider your real-time use cases and your historical use cases and consider them separately. What we found out is we treated everything the same. Everything went through the same pipeline, and it was just queries that were different. When you're using TimescaleDB—and really any architecture—it's extremely useful to consider those use cases separately and architect them separately. They have different requirements. If you try to shoehorn all data, in real time, into any time-series database, whether it's something awesome like TimescaleDB or something else, it's going to cause problems and it's not going to scale very well. You can do a lot more by separating historical and real-time use cases.We’d like to thank Shane, Brock, and all the folks at Density for sharing their experience with TimescaleDB in measuring and optimizing real estate usage.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/density-measures-large-real-estate-portfolios-using-timescaledb/
How METER Group Brings a Data-Driven Approach to the Cannabis Production Industry,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Paolo Bergantino, Director of Software for the Horticulture business unit at METER Group, joins us to share how they make data accessible to their customers so that they can maximize their cannabis yield and increase efficiency and consistency between grows.AROYAis the leading cannabis production platform servicing the U.S. market today. AROYA is part ofMETER Group, a scientific instrumentation company with 30+ years of expertise in developing sensors for the agriculture and food industries. We have taken this technical expertise and applied it to the cannabis market, developing a platform that allows growers to grow more efficiently and increase their yields – and to do so consistently and at scale.About the teamMy name isPaolo Bergantino. I have about 15 years of experience developing web applications in various stacks, and I have spent the last four here at METER Group. Currently, I am the Director of Software for the Horticulture business unit, which is in charge of the development and infrastructure of the AROYA software platform. My direct team consists of about ten engineers, 3 QA engineers, and a UI/UX Designer. (We’re also hiring!)About the projectAROYA is built as a React Single-Page App (SPA) that communicates with a Django/DRF back-end. In addition to usingTimescale Cloudfor our database, we use AWS services such as EC2+ELB for our app and workers,ElastiCache for Redis,S3for various tasks,AWS IoT/SQSfor handling packets from our sensors, and some other services here and there.✨Editor’s Note:""Timescale Cloud"" is known as ""Managed Service for TimescaleDB"" as of September 2021.As I previously mentioned, AROYA was born out of our desire to build a system that leveraged our superior sensor technology in an industry that needed such a system. Cannabis worked out great in this respect, as the current legalization movement throughout the U.S. has resulted in a lot of disruption in the space.The more we spoke to growers, the more we were struck by how much mythology there was in growing cannabis and by how little science was being applied by relatively large operations. As a company with deeply scientific roots, we found it to be a perfect match and an area where we could bring some of our knowledge to the forefront.We ultimately believe the only survivors in the space are those who can use data-driven approaches to their cultivation to maximize their yield and increase efficiency and consistency between grows.As part of the AROYA platform, we developed a wireless module (called a “nose”) that could be attached to our sensors. Using Bluetooth Low Energy (BLE) for low power consumption and attaching a solar panel to take advantage of the lights in a grow room, the module can run indefinitely without charging.The AROYA nose in its natural habitat (aroya.ioInstagram)The most critical sensor we attach to this nose is called the TEROS 12, the three-pronged sensor pictured below. It can be installed into any growing medium (like rockwool, coconut coir, soil, or mixes like perlite, pumice, or peat moss) and give insights into the temperature, water content (WC), and electrical conductivity (EC) of the medium. Without getting too into the weeds (pardon the pun), WC and EC, in particular, are crucial in helping growers make informed irrigation decisions that will steer the plants into the right state and ultimately maximize their yield potential.The AROYA nose with a connected TEROS 12 sensor (aroya.ioInstagram)We also have an ATMOS 14 sensor for measuring the climate in the rooms anda whole suite of sensorsfor other use cases.An AROYA repeater with an ATMOS 14 sensor for measuring the climate (aroya.ioInstagram)AROYA’s core competency is collecting this data - e.g., EC, WC, soil temp, air temperature, etc. - and serving it to our clients in real-time (or, at least “real-time” for our purposes, as our typical sampling interval is 3 minutes).Growers typically split their growing rooms into irrigation zones. We encourage them to install statistically significant amounts of sensors into each room and its zones, so that AROYA gives them good and actionable feedback on the state of their room.  For example, there’s a concept in cultivation called ""crop steering"" that basically says that if you stress the plant in just the right way,  you can ""steer"" it into generative or vegetative states at will and drive it to squeeze every last bit of flower. How and when you do this is crucial to doing it properly.Our data allows growers to dial in their irrigation strategy, so they can hit their target ""dryback"" for the plant (this is more or less the difference between the water content at the end of irrigation until the next irrigation event). Optimizing dryback is one of the biggest factors in making crop steering work, and it's basically impossible to do well without good data. (We provide lots of other data that helps growers make decisions, but this is one of the most important ones.)Graph showing electrical conductivity (EC) and water content (WC) data related to a room in AROYA.This can be even more important when multiple cultivars (“strains”) of cannabis are grown in the same room, as the differences between two cultivars regarding their needs and expectations can be pretty dramatic. For those unfamiliar with the field, an example might be that different cultivars ""drink"" water differently, and thus must be irrigated differently to achieve maximum yields. There are also ""stretchy"" cultivars that grow taller faster than ""stocky"" ones, and this also affects how they interact with the environment. AROYA not only helps in terms of sensing, but in documenting and helping understand these differences to improve future runs.The most important thing from collecting all this data is making it accessible to users via graphs and visualizations in an intuitive, reliable, and accurate way, so they can make informed decisions about their cultivation.We also have alerts and other logic that we apply to incoming data. These visualizations and business logic can happen at the sensor level, at the zone level, at the room level, or sometimes even at the facility level.A typical use case with AROYA might be that a user logs in to their dashboard to view sensor data for a room. Initially, they view charts aggregated to the zone level, but they may decide to dig deeper into a particular zone and view the individual sensors that make up that zone. Or, vice versa, they may want to pull out and view data averaged all the way up to the room. So, as we designed our solution, we needed to ensure we could get to (and provide) the data at the right aggregation level quickly.Choosing and using TimescaleDBThe initial solutionDuring the days of our closed alpha and beta of AROYA with early trial accounts (late 2017 through our official launch December 2019), the amount of data coming into the system was not significant. Our nose was still being developed (and hardware development is nice and slow), so we had to make due with some legacy data loggers that METER also produces. These data loggers only sampled every 5 minutes and, at best, reported every 15 minutes. We usedAWS’ RDS Aurora PostgreSQLservice and cobbled together a set of triggers and functions that partitioned our main readings table by each client facility – but no more. Because we have so many sensor models and data types we can collect, I chose to use anarrow data modelfor our main readings table.This overall setup worked well enough at first, but as we progressed from alpha to beta and our customer base grew, it became increasingly clear that it was not a long-term solution for our time series data needs. I could have expanded my self-managed system of triggers and functions and cobbled together additional partitions within a facility, but this did not seem ideal. There had to be a better way!I started looking into specific time-series solutions. I am a bit of a home automation aficionado, and I was already familiar with InfluxDB – butI didn’t wish to split my relational data and readings data or teach my team a new query language.TimescaleDB, being built on top of PostgreSQL, initially drew my attention: it “just worked” in every respect, I could expect it to, and I could use the same tools I was used to for it.At this point, however, I had a few reservations about some non-technical aspects of hosting TimescaleDB that prevented me from going full steam ahead with it.✨Editor’s Note:For more comparisons and benchmarks, seehow TimescaleDB compares to InfluxDB, MongoDB, AWS Timestream, and other time-series database alternativeson various vectors, from performance and ecosystem to query language and beyond.Applying a band-aid and setting a goalBefore this point, if I am perfectly truthful, I did not have any serious requirements or standards about what I considered to be the adequate quality of service for our application. I had a bit of an “I know it when I see it” attitude towards the whole thing.When we had a potential client walk away during a demo due to a particularly slow loading graph, I knew that we had a problem on our hands and that we needed something really solid for the long term.Still, at the time, we also needed something to get us by until we could perform a thorough evaluation of the available solutions and build something around that. At this point, I decided to stand aRedis clusterbetween RDS and our application which stored the last 30 days of sensor data (at all the aggregation levels required) as a Pandas dataframe. Any chart request coming in for data within the first 30 days - which accounted for something like 90% of our requests - would simply hit Redis. Anything longer would cobble together the answer using both Redis and querying the database.Performance for the 90% use case was adequate, but it was getting increasingly dreadful as more and more historical data piled up for anything that hit the database.At this point, I set the goalposts for what our new solution would need to meet:Any chart request, which is an integral part of AROYA, needs to take less than one second for the API to serve.The research and the first solutionWe looked at other databases at this point, InfluxDB was looked at again, we got in a beta of Timestream for AWS and looked at that. We even considered going NoSQL for the whole thing. We ran tests and benchmarks, created matrices of pros and cons, estimated costs, the whole shebang.Nothing compared favorably to what we were able to achieve with TimescaleDB.Ultimately,the feature that really caught our attention wascontinuous aggregatesin TimescaleDB. The way our logic works, more or less, we see the timeframe that the user is requesting and sample our data accordingly. In other words, if a user fetches three months worth of data, we would not send three months worth of raw data to be graphed to the front-end. Instead, we would bucket our data into appropriately sized buckets that would give us the right amount of data we want to display in the interface.Although it would require quite a few views, if we created continuous aggregates for every aggregation level and bucket size we cared about, and then directly queried the right aggregation/bucket combination (depending on the parameters requested), that should do it, right? The answer was a resoundingyes.The performance we were able to achieve using these views shattered the competition.Although I admit we were kind of “cheating” by precalculating the data, the point is that we could easily do it. Not only this, but when we ran load tests on our proposed infrastructure, we were blown away by how much more traffic we could support without any service degradation. We could also eliminate all the complicated infrastructure that our Redis layer required, which was quite a load off (literally and figuratively).Grafana dashboard for the internal team showing app server load average before and after deployment of initial TimescaleDB implementation.The Achilles’ heel of this solution, an astute reader may already notice, is that we were paying for this performance in disk space.I initially brushed this off as fair trade and moved on with my life.We foundTimescaleDB’s compressionto be as good as advertised, which gave us 90%+ space savings in our underlying hypertable,but our sizable collection of uncompressed continuous aggregates grew by the day (keep reading to learn why this is a “but”...).✨Editor’s Note: We’ve put together resources aboutcontinuous aggregatesandcompressionto help you get started.The “final” solutionAROYA has been on an amazing trajectory since launch, and our growth was evident in the months before and after we deployed our initial TimescaleDB implementation. Thousands upon thousands of sensors hitting the field was great for business – but bad for our disk space.Our monitoring told a good story of how long our chart requests were taking, as 95%+ of them were under 1 second, and virtually all were under 2 seconds. Still, within a few months of deployment, we needed to upgrade tiers in Timescale Cloud solely to keep up with our disk usage.✨Editor’s Note:""Timescale Cloud"" is known as ""Managed Service for TimescaleDB"" as of September 2021.We had adequate computing resources for our load, but 1 TB was no longer enough, so we doubled our total instance size to get another 1 TB. While everything was running smoothly, I felt a dark cloud overhead as our continuous aggregates grew and grew in size.The clock was ticking, and before we knew it, we were coming up on 2 TB of readings. So, we had to take action.We had attended a webinar hosted by Timescale and heard someone make a relatively off-hand comment about rolling their own compression for continuous aggregates. This planted a seed that was all we needed to get going.The plan was thus: first, after consulting with Timescale staff, we were alerted we had way too many bucket sizes. We could useTimescaleDB’s time_bucket functionsto do some of this on the fly without affecting performance or keeping as many continuous aggregates. That was an easy win.Next, we split each of our current continuous aggregates into three separate components:First, we kept the original continuous aggregate.Then, we leveraged theTimescaleDB job schedulerto move and compress chunks from the original continuous aggregate into ahypertablefor that specific bucket/aggregation view.Finally, we created a plain old view that UNIONed the two and made it a transparent change for our application.This allowed us to compress everything but the last week of all of our continuous aggregates, and the results were as good as we could have hoped for.The 1.83TB database was compressed into 700 GB.We were able to take our ~1.83 TB database and compress it down to 700 GB. Not only that, about 300 GB of that is log data that’s unrelated to our main reading pipeline.We will be migrating out this data soon, which gives us a vast amount of room to grow. (We think we can even move back the 1TB plan at this point, but have to test to ensure that compute doesn’t become an issue.) The rate of incrementation in disk usage was also massively slowed, which bodes well for this solution in the long term. What’s more, there was virtually no penalty for doing this in terms of performance for any of the metrics we monitor.Our monitoring shows how long sampling of chart requests takes to serve.Ultimately TimescaleDB had wins across the board for my team.Performance was going to be the driving force behind whatever we went with, and TimescaleDB has delivered that in spades.Current deployment & future plansWe currently ingest billions of readings every month using TimescaleDB and couldn’t be happier.Our data ingest and charting capabilities are two of the essential aspects of AROYA’s infrastructure.While the road to get here has been a huge learning experience, our current infrastructure is straightforward and performant, and we’ve been able to rely on it to work as expected and to do the right thing. I am not sure I can pay a bigger compliment than that.The current architecture diagramWe’ve recently gone live with our AROYA Analytics release, which is building upon what we’ve done to deliver deeper insights into the environment and the operations at the facilities using our service. Every step of the way, it’s been straightforward (and performant!) to calculate the metrics we need with our TimescaleDB setup.Getting started advice & resourcesI think it’s worth mentioning that there were many trade-offs and requirements that guided me to where AROYA is today with our use of TimescaleDB. Ultimately, my story is simply the set of decisions that led me to where we are now and people’s mileage may vary depending on their requirements.I am sure that the set of functionality offered means that, with a little bit of creativity, TimescaleDB can work for just about any time-series use case I can think of.The exercise we went through when iterating from our initial non-Timescale solution to Timescale was crucial to get me to be comfortable with that migration. Moving such a critical part of my infrastructure was scary, and it isstillscary.Monitoring everything you can, having redundancies, and being vigilant about any unexpected activity - even if it’s not something that may trigger an error - has helped us stay out of trouble.We have a bigGrafanadashboard on a TV in our office that displays various metrics and multiple times we’ve seen something odd and uncovered an issue that could have festered into something much more if we hadn’t dug into it right away. Finally, diligent load testing of the infrastructure and staging runs of any significant modifications have made our deployments a lot less stressful, since they instill quite a bit of confidence.✨ Editor’s Note:Check outGrafana 101 video seriesandGrafana tutorialsto learn everything from building awesome, interactive visualizations to setting up custom alerts, sharing dashboards with teammates, and solving common issues.I would like to give a big shout-out to Neil Parker, who is my right-hand man in anything relating to AROYA infrastructure and did virtually all of the actual work in getting many of these things set up and running. I would also like to thankMike FreedmanandPriscila Fletcherfrom Timescale, who have given us a great bit of time and information and helped us in our journey with TimescaleDB.We’d like to give a big thank you to Paolo and everyone at AROYA for sharing their story, as well as for their efforts to help transform the cannabis production industry, equipping growers with the data they need to improve their crops, make informed decisions, and beyond.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-meter-group-brings-a-data-driven-approach-to-the-cannabis-production-industry/
How Flowkey Scaled Its AWS Redshift Data Warehouse With Timescale,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Nikola Chochkov, lead data scientist atflowkey, shares how his team migrated from Amazon Redshift to TimescaleDB and is driving rapid growth and experimentation by analyzing the users’ behavioral data using TimescaleDB along with Metabase or Jupyter/Rmarkedown Notebooks.About the CompanyFlowkeyis a leading app for learning to play the piano, with over 10 million registered users in more than 100 countries. The company was launched in 2015 and quickly became one of the global leaders in its category.Here is a video from our founder, Jonas Gößling, explaining how it all started.About the TeamWe are a team of around 40 people, with more than 10 of us working in Data and Engineering.We have a Marketing team (responsible for user acquisition, customer relationship management, collaborations, etc.), a Creative team (building all of our visual content, our design, and advertising), Course and Song teams (creating our in-app learning content—e.g., the courses series and the piano renditions of the songs in our library). We also have Customer Support, Product, Engineering, Data, and Operations teams.Many of us take on more than one role, and for many of us, flowkey has been the first significant career step. Not for me personally, though, but still.🙂About the ProjectThe flowkey appWe are a business that depends heavily on analytics for business decision-making. Our experimentation—a major driver of our growth—is powered by the data analysis of user behavioral data (app usage). This data consists of user events, which we track from our product.“We realized we needed to scale our previous Amazon Redshift data warehouse model into a more suitable solution for categorical, time-series data analysis. We found out about TimescaleDB from being part of the PostgreSQL community”For example, a Learn Session starts at a given timestamp for a user, and we record this event.✨Editor’s Note:Time-series data is a sequence of data points collected over time intervals, allowing us to track changes over time. To learn more, readWhat Is Time-Series Data (With Examples).When we launch a new feature, we typically A/B test it and evaluate its impact based on measuring key performance indicators (KPIs), which are predefined for the test. Every day, we receive millions of incoming events, tracked around our product, in the format:(user_id, event, timestamp, properties)Thepropertiesfield is a schemaless object that depends on the particular event type that we track from the app. For example, a Learn Session event would have asongIdand alearnMode, while a Subscription Offer interaction event would have aproductId, etc.Choosing (and Using!) TimescaleDBWith time, we realized we needed to scale our previous Amazon Redshift data warehouse model into a more suitable solution for categorical, time-series data analysis.We found out about TimescaleDB from being part of the PostgreSQL community, and when we were faced with the problem at hand, it was a natural way forward for us.After doing our research, we realized that TimescaleDB suited our needs perfectly. Here's a list of our arguments:Our data analysts are well-versed in SQL and PostgreSQL.The events’ raw data is available in a PostgreSQL schema alongside all our other business intelligence data.TimescaleDB is an actively developed, open-source solution. It allowed us to deploy it on our self-hosted PostgreSQL data warehouse.A TimescaleDB hypertable model would allow us to accommodate the schemaless JSON structure of our events.select event, platform, time, jsonb_pretty(data) from events_today limit 5;
            event             | platform |          time           |                      jsonb_pretty
------------------------------+----------+-------------------------+---------------------------------------------------------
 SONG_OPEN_UI_ELEMENT_CLICKED | ios      | 2022-11-03 00:00:00.034 | {                                                      +
                              |          |                         |     ""songId"": ""E2kCpqHCwB2xYf7LL"",                     +
                              |          |                         |     ""context"": ""song-cover"",                           +
                              |          |                         |     ""listIndex"": 6,                                    +
                              |          |                         |     ""routeName"": ""Songs"",                              +
                              |          |                         |     ""currentTab"": ""SongsTab""                          +
                              |          |                         | }
 ONBOARDING_QUESTION_VIEWED   | web      | 2022-11-03 00:00:00.145 | {                                                      +
                              |          |                         |     ""context"": ""preferredCategories""                   +
                              |          |                         | }
 SONG_PLAYER_SCROLLED         | ios      | 2022-11-03 00:00:00.157 | {                                                      +
                              |          |                         |     ""level"": 1,                                        +
                              |          |                         |     ""songId"": ""Lui27TDJ4vZBevxzc"",                     +
                              |          |                         |     ""direction"": ""backwards"",                          +
                              |          |                         |     ""songGroupId"": ""vhisAJBkPwvn6tdoq"",                +
                              |          |                         |     ""loopBoundsMs"": null,                              +
                              |          |                         |     ""finalPositionMs"": 0,                              +
                              |          |                         |     ""initialPositionMs"": 24751                         +
                              |          |                         | }
 AB_TEST_VARIANT_ASSIGNED     | ios      | 2022-11-03 00:00:00.249 | {                                                      +
                              |          |                         |     ""variant"": ""CONTROL"",                              +
                              |          |                         |     ""experimentName"": ""player_onboarding_video_09_2022""+
                              |          |                         | }
 ONBOARDING_ANSWER_SUBMITTED  | web      | 2022-11-03 00:00:00.314 | {                                                      +
                              |          |                         |     ""answers"": [                                       +
                              |          |                         |         ""dont-know""                                    +
                              |          |                         |     ],                                                 +
                              |          |                         |     ""context"": ""learningGoals""                         +
                              |          |                         | }TimescaleDB offers a great set of SQL analytical functions.TimescaleDB offers continuous aggregates, which integrate very well with how we do analytics and real-time data monitoring.Data migration and update (e.g., renaming of events or JSON properties) are available.✨Editor’s Note:Faster queries, reduced storage costs, and greater flexibility. Learn more abouthierarchical continuous aggregates.“We use compression, which has cut our disk space usage by 28 percent”Current Deployment & Future PlansOur data warehouse is deployed on self-hosted machines and works well for us. We employ other PostgreSQL extensions that aren't currently supported by the Timescale cloud offering, which was important to us when we launched. These includeMongo FDWandAdjust’siStore extensionfor cohort analysis data storage.✨Editor's Note:We're working on expanding the catalog of PostgreSQL extensions offered in Timescale's cloud offering. Stay tuned!We employ TimescaleDB's awesome data retention (automated through a user action), and thanks to that, our most recent (and more relevant to our analytics) data is available to us on SSD chunks, while historical data is kept on HDDs.Furthermore, we use compression, which has cut our disk space usage by 28 percent. Our data contains JSONB fields, which are difficult to be compressed. We are pretty happy with it, though, so it's a win. 🙂When we do business analytics, we employMetabaseorJupyter/Rmarkdown Notebooksto derive insights. We established a workflow of writing custom continuous aggregates for the duration of experiments, which are then easy to keep and fully deploy or discard, depending on the decision made for the experiment.✨Editor's Note:Learn how toconnect to Timescale from a Jupyter notebookfor better data querying, cleaning, and analysis.This allows us to iterate our experiments quickly and increase the bandwidth of change, which we can successfully bring to the product.RoadmapWe just finished migrating our setup to a more powerful cluster of machines, which allowed us to benefit from the data tiering options mentioned above. Right now, our system is scalable, and we don't expect any major upgrades to this system to come up soon.Advice & ResourcesWe recommend theTimescale documentationas well as theSlack Community.Want more insider tips?Sign up for our newsletterfor more Developer Q&As, technical articles, and tutorials to help you do more with your data. We deliver it straight to your inbox twice a month.We’d like to thank Nikola and all of the folks at flowkey for sharing their story on how they’re improving their online piano lessons by analyzing millions of user events daily using TimescaleDB.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-flowkey-solved-its-time-series-data-problem-by-migrating-from-aws-redshift-to-timescale/
How United Manufacturing Hub Is Introducing Open Source to Manufacturing and Using Time-Series Data for Predictive Maintenance,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, we speak with Jeremy Theocharis, co-founder and CTO ofUnited Manufacturing Hub, about how they are bringing open source to the world of manufacturing by combining information and operational tools and technologies in an open-source Helm chart for Kubernetes.The team uses TimescaleDB to store both relational and time-series data coming fromMQTTandKafkaand then visualizes it usingGrafanaand their own REST API. With the data, they can prevent and predict maintenance issues, analyze and optimize production losses such as changeovers or micro-stops and reduce resource consumption, and much more.About the CompanyWe believe that open source is the future: we live in a world where 100 percent of all supercomputers useLinux, 95 percent of all public cloud providers useKubernetes, and the Mars Helicopter, Ingenuity, usesf-prime.We are confident that open source will also find its place in manufacturing, and we were among the first to use it in this field, making us the experts.Our story goes back to 2016, when we had the pain of integrating and maintaining various costly Industrial IoT solutions (IIoT). Existing vendors focused only on the end result, which resulted in large-scale IIoT projects failing because they did not address the real challenges.After suffering for years, in 2021, we were fed up and decided to do something about it. Since then, all our products and services have been focused on efficiently integrating and operating large-scale IIoT infrastructures.About the TeamWe are a team of 11 people with different backgrounds, from mechanical engineering to business administration to cybersecurity.Me, personally? I am an IT nerd that learned programming at 14 and then studied Mechanical Engineering and Business Administration atRWTH Aachenin Germany. I did my internship first as a technical project manager in theDigital Capability Center Aachen, where I was responsible for the technical integration of various Industrial IoT solutions. I later did another internship at McKinsey & Company in Singapore and decided I needed a solid technical part in my future profession.I started my own business as a system integrator in the Industrial IoT sector, metChristianandAlex, and founded theUMH Systems GmbHtogether in 2021.About the ProjectUnited Manufacturing Hub's architectureTheUnited Manufacturing Hub(UMH) is an open-source Helm chart for Kubernetes. It combines state-of-the-art information and operational tools (IT/OT) and technologies, bringing them into the engineer's hands.I assume most of the readers here will come from traditional IT, so let me explain the world of manufacturing, especially OT,  first, as it will help you understand our usage of TimescaleDB.OT means Operational Technology. OT is the hardware and software to manage, monitor, and control industrial operations like production machines. Due to different requirements, OT has its own ecosystems.OT comes originally from the field of electronics, and this background is still evident today. For example, the computer that controls the production machine is called aPLC (Programmable Logic Controller). It runs some peculiar flavors of Windows and Linux, which are completely hidden from the system's programmer. The programmer of the PLC will use programming languages likeladder logic, which is like drawing electrical schematics.Because OT is an entirely different world, it is pretty hard to integrate it with the traditional IT world. During system integration, we felt all these pains and decided to develop a tool that allows an easy combination between both fields—the United Manufacturing Hub (UMH).With UMH, one can now easily extract data from the shopfloor, from the PLC tovarious IO-link compatible or analog (4-20mA / 0-10V) sensorstobarcodereaderand different manufacturing execution or enterprise resource planning systems. Using aUnified Namespacebased onMQTTandKafka, the data is aggregated and can then be contextualized through tools likeNode-RED.From there on, the processed data is stored automatically in a TimescaleDB running either in the cloud or on-premise. To visualize the data, we useGrafanawith our ownREST APIfor manufacturing specific logics (also calledfactoryinsight) and our own Grafana data source.Choosing (and Using!) TimescaleDBManufacturing data is mainly relational: orders, products, production plans, and shifts are good examples of this. However, due to the growth of analytics, time-series data gets more and more important, e.g., for preventive or predictive maintenance.✨Editor's Note:Want to learn more about time-series forecasting?Check out this blog post.During one of those earlier system integrator projects, I realized that we needed a time-series database and a relational one.Due to the strong marketing, we chose InfluxDB at first. We did not scan vendors; we just started with whatever we knew from home automation. It sounded perfect: a beautiful user interface, continuous queries to process data, etc.We wanted to process raw sensor data, e.g., converting the distance of a light barrier into the machine status (running/not running). We also needed to store shifts, orders, and products and model the data. We did that via InfluxDB as well.The project was a nightmare. To be fair, InfluxDB was not its main driver, but it definitely was in the top five. Modeling relational data into a time-series database is a bad idea. The continuous queries were failing too often without even throwing error messages. The system could not handle the data buffered somewhere in the system and arrived late.“The stability of TimescaleDB allows us to focus on developing our microservices instead of running around fixing breaking API changes”Additionally, Flux as a query language is comparatively new and not as easy to work with as SQL. It quickly reached the point where we had to implement Python scripts to process data because Flux had reached its limits in use cases that would work seamlessly using SQL. So we felt like InfluxDB was putting unnecessary obstacles in our way.We even wrote a blog article aboutwhy we chose TimescaleDB over InfluxDB for the field of Industrial IoT.One of the main factors for us to use TimescaleDB as our database is the reliability and fault tolerance [the ability of a system to continue operating properly in case of failure] it offers to our stack. Since PostgreSQL has been in development for over 25 years, it is already very robust.“The reliability also manifests in the ease of horizontal scaling across multiple servers, which we are very interested in”The stability of TimescaleDB allows us to focus on developing our microservices instead of running around fixing breaking API changes, which newer, less stable databases like InfluxDB have shown to bring forth.The reliability also manifests in the ease of horizontal scaling across multiple servers, which we are very interested in.Being based on SQL was also a factor for us as SQL is the most well-known query language for relational databases—making working with it much easier. Almost any possible problem is already documented and solved somewhere on the Internet.Now, TimescaleDB is used in our stack as our main database to store the data coming in via MQTT/Kafka. We are storing (among others) machine states, product states, orders, worker shifts, and sensor data. Some are relational; some are time-series.If TimescaleDB didn’t exist, we probably would have to employ a PostgreSQL-based relational database system in addition to InfluxDB for time-series data. That would mean a lot of additional effort as we would have to manage two separate databases and the creation of datasets that span the two. This would also make the system more prone to errors as we would have to employ multiple querying languages.Current Deployment & Future PlansAs I mentioned, the United Manufacturing Hub is an open-source Helm chart for Kubernetes, which combines state-of-the-art IT/OT tools and technologies and brings them into the hands of the engineer.This allows us to standardize the IT/OT infrastructure across customers and makes the entire infrastructure easy to integrate and maintain.We typically deploy it on the edge and on-premise usingk3sas light Kubernetes. In the cloud,  we use managed Kubernetes services likeAKS. If the customer is scaling out and okay with using the cloud, we recommend services likeTimescale.We are using TimescaleDB with MQTT, Kafka, and Grafana. We have microservices to subscribe to the messages from the message brokers MQTT and Kafka and insert the data into TimescaleDB, as well as a microservice that reads out data and processes it before sending it to a Grafana plugin, which then allows for visualization.✨Editor’s Note:Learn how you can improve your Grafana performance using downsampling in TimescaleDB.RoadmapWe are currently positioning the United Manufacturing Hub with TimescaleDB as an open-source Historian. To achieve this, we are currently developing a user interface on top of the UMH so that OT engineers can use it and IT can still maintain it.We can recommend our blog articlefor a good comparison between Historians and Open-Source databases.Furthermore, we are developing a Management Console on top of the Helm chart, which makes a lot of the typical operation tasks (monitoring, logging, changing the configuration, etc.) easily accessible for the OT engineer, reducing the workload of maintaining all the edge devices, servers, and so on for the IT person.Advice & ResourcesFor manufacturing, we recommend the previously mentioned blog articles and the official TimescaleDB documentation. For data models and data ingestions from MQTT and Kafka into TimescaleDB, we can also recommend looking at the United Manufacturing Hub source code (or using it directly).One last piece of advice: I can strongly recommend the bookDesigning Data-Intensive Applicationsby Martin Kleppmann. It really helped me understand the fundamental principles in designing large-scale architectures so you can join discussions on the technical level. It explains the fundamental choices behind databases (from log-based approaches over WAL to binary trees) and the problems and solutions for distributed systems.We’d like to thank Jeremy Theocharis and the folks and United Manufacturing Hub for sharing their story on how they are using TimescaleDB to store their data, and why they chose us over other databases.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-united-manufacturing-hub-is-introducing-open-source-to-manufacturing-and-using-time-series-data-for-predictive-maintenance/
How Edeva Uses Continuous Aggregations and IoT to Build Smarter Cities,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, John Eskilsson, software architect at Edeva, shares how his team collects huge amounts of data (mainly) from IoT devices to help build safer, smarter cities and leverages continuous aggregations for lightning-fast dashboards.Founded in 2009 in Linköping,Edevais a Swedish company that creates powerful solutions for smart cities. It offers managed services and complete systems, including hardware and software platforms.As the creators of the dynamic speed bumpActibumpand the smart city platformEdevaLive, the Edeva team works mainly for municipal, regional, and national road administrations, toll stations, environmental agencies, and law enforcement agencies.The team also solves many other problems, from obtaining large amounts of environmental data for decision-making to developing a screening scale to help law enforcement agencies assess vehicle overloading. The latter, for instance, decreased the amount of time needed to control each vehicle, speeding up traffic checks and allowing law enforcement agencies to control more vehicles.About the TeamTheteam at Edevais a small but impactful group of 11 working on everything from creating hardware IoT devices to analyzing time-series data and making it accessible to customers—and, sometimes—the public.As a software architect, I am in charge of building the best possible solution to receive, store, analyze, visualize, and share the customers’ event data. Our team then comes together to create solutions that work and that the customer actually wants.About the ProjectEdeva has created a dynamic speed bump calledActibumpand the smart city platformEdevaLive.The Actibump has been used in Sweden since 2010. Speeding vehicles activate a hatch in the road that lowers a few centimeters, creating an inverted speed bump. Providing good accessibility for public transportation, such as buses and emergency vehicles, the Actibump still ensures a safe speed for pedestrians and other vulnerable road users. It is also an environmentally friendly solution, helping decrease noise and emissions.The Actibump can be combined with the EdevaLive system, delivering valuable remote monitoring services and statistics to Edeva’s customers.Most of the data we collect is based on IoT devices:Traffic flow data: The Actibump measures the speed of oncoming traffic to decide if it needs to activate the speed bump or not. We capture radar data, among others, and send an event to our smart city platform EdevaLive. The data treats the oncoming traffic as a flow rather than a single vehicle to create the smoothest possible traffic flow.Vehicle classification data (weigh-in-motion): Actibump can be configured with weigh-in-motion. This means that the lid of the speed bump is equipped with a very sensitive high sampling scale. The scale records several weight measurements when the vehicle passes over the speed bump. This way, it can detect how many axles a vehicle has and classify the type of vehicle. At the same time, it fires off one event for each axle with the scale fingerprint so we can analyze if the weight measurements are correct.Vehicle classification data (radar): If we want to classify vehicles in places where we do not yet have an Actibump installed, we can introduce a radar that can classify vehicle types. A roadside server controls the radar, gathers its data, and pushes it to EdevaLive.Bike and pedestrian data: We use cameras installed above a pedestrian and cycle path. The camera can detect and count pedestrians and bicycles passing in both directions. We push this data to EdevaLive for analysis.Number plate data:We can use a camera to detect the number plate of a vehicle. This way, we can control devices like gates to open automatically. It can also be used to look up the amount of electric vs. petrol or diesel vehicles passing the camera or determine if a specific vehicle exceeds the cargo weight limit.Gyroscopic data: We offer a gyroscopic sensor that can gather data for acceleration in all different planes. This device generates a lot of data that can be uploaded to EdevaLive in batches or as a stream (if the vehicle has an Internet connection). This data is GPS-tagged and can be used to calculate jerk to provide indications on working conditions to a bus driver, for instance. The data can also be used to calculate the wear and tear of vehicles and many other things.Environmental data: It is important to monitor environmental data in a smart city platform. This is why we use small portable devices that can measure the occurrence of different particle sizes in the air. It can also measure CO2 and other gasses. On top of that, it measures the usual things like temperature, wind speed, etc. All this data is pushed to EdevaLive.Alarm data: Our IoT devices and roadside servers can send alarm information if a sensor or other parts malfunction. All this data comes to EdevaLive in the same way as a regular IoT event, but these events are only used internally so that we can react as quickly as possible if there is a problem.Status data: If the alarm data detects anomalies, the status data just reports on the status of the server or IoT device. The devices run self-checks and report statistical data, like disk utilization, temperature, and load. This is also just for internal use to spot trends or troubleshoot in case any problems arise. For instance, it is incredibly useful to correlate CPU load with the version number of firmware or other software versions.Administrative data: This is where the power of SQL and time-series data really shines. Let’s say we added a new device, and it has a configuration object that is persistent in a regular table in Timescale. This object keeps some metadata, such as the date it was added to the system or the device's display name. This way, we can use a join easily to pick up metadata about the device and, at the same time, get time-series data for the events that are coming in. There is only one database connection to handle and one query to run.Choosing (and Using!) TimescaleDBWe realized we needed a time-series database a few years ago when we started storing our data in MySQL. At the time, we made a move to MongoDB, and it worked well for us but required quite a bit of administration and was harder to onboard other developers.I looked at InfluxDB but never considered it in the end because it was yet another system to learn, and we had learned that lesson with MongoDB.✨Editor’s Note:For more comparisons and benchmarks,see how TimescaleDB compares to InfluxDB, MongoDB, AWS Timestream, vanilla PostgreSQL, and other time-series database alternatives on various vectors, from performance and ecosystem to query language and beyond.Learning from this journey,  I looked for a solution that plugged the gaps the previous systems couldn’t. That is when I found Timescale and discovered that there was a hosted solution.We are a small team that creates software with a big impact, and this means that we don’t really have time to put a lot of effort into tweaking and administering every single tool we use, but we still like to have control.""With Timescale, our developers immediately knew how to use the product because most of them already knew SQL""Also, since TimescaleDB is basically PostgreSQL with time-series functionality on steroids, it is much easier to onboard new developers if needed. With Timescale, our developers immediately knew how to use the product because most of them already knew SQL.Edeva uses TimescaleDB as the main database in our smart city system. Our clients can control their IoT devices (like the Actibump from EdevaLive) and—as part of that system—see the data that has been captured and quickly get an overview of trends and historical data. We offer many graphs that show data in different time spans, like day, week, month, and years. To get this to render really fast, we use continuous aggregations.""Timescale is basically PostgreSQL with time-series functionality on steroids, it is much easier to onboard new developers if needed""✨Editor’s Note:Learn how you can use continuous aggregates for distributed hypertables.Current Deployment and Future PlansOne of the TimescaleDB features that has had the most impact on our work is continuous aggregations. It changed our dashboards from sluggish to lightning fast. If we are building functionality to make data available for customers, we always aggregate it first to speed up the queries and take the load off the database. It used to take minutes to run some long-term data queries. Now, almost all queries for long-term data are subsecond.For example, we always struggled with showing the 85th percentile of speed over time. To get accurate percentile data, you had to calculate it based on the raw data instead of aggregating it. If you had 200 million events in a hypertable and wanted several years’ data for a specific sensor, it could take you a long time to deliver—users don’t want to wait that long.""It changed our dashboards from sluggish to lightning fast""Now that Timescale introducedpercentile_aggandapprox_percentile, we can actually query continuous aggregations andget reasonably accurate percentile valueswithout querying raw data.✨Editor’s Note:Percentile approximations can be more useful for large time-series data sets than averages. Read how their work in this blog post.Note that “vehicles” is a hypertable where actibump_id is the ID of the dynamic speed bump containing several hundred million records.This is how we build the continuous aggregate:CREATE MATERIALIZED VIEW view1
 WITH (timescaledb.continuous) AS
 SELECT actibump_id,
 timescaledb_experimental.time_bucket_ng(INTERVAL '1 month', time, 'UTC') AS bucket,
 percentile_agg(vehicle_speed_initial) AS percentile_agg
FROM vehicles
GROUP BY actibump_id, bucketAnd this is the query that fetches the data for the graph:SELECT TIMESCALEDB_EXPERIMENTAL.TIME_BUCKET_NG(INTERVAL '1 month', bucket) AS date,
actibump_id,
APPROX_PERCENTILE(0.85, ROLLUP(PERCENTILE_AGG)) AS p85,
MAX(signpost_speed_max)
FROM vehicles_summary_1_month
WHERE actibump_id in ('16060022')
AND bucket >= '2021-01-30 23:00:00'
AND bucket <= '2022-04-08 21:59:59'
GROUP BY date, actibump_id
ORDER BY date ASCHere is an example of the graph:At the moment, we use PHP and Yii 2 to deploy TimescaleDB. We connect to TimescaleDB with Qlik Sense for business analytics. In Qlik Sense, you can easily connect to TimescaleDB using the PostgreSQL integration.It is especially convenient to be able to connect to the continuous aggregations for long-term data without overloading the system with too much raw data. We often use Qlik Sense to rapidly prototype graphs that we later add to EdevaLive.Advice and ResourcesThe next step for us is to come up with a good way of reducing the amount of raw data we store in TimescaleDB. We are looking at how we can integrate it with a data lake. Apart from that, we are really excited to start building even more graphs and map applications.If you are planning to store time-series data, Timescale is the way to go. It makes it easy to get started because it is “just” SQL, and at the same time, you get the important features needed to work with time-series data. I recommend you have a look, especially at continuous aggregations.Think about the whole lifecycle when you start. Will your use cases allow you to use features like compression, or do you need to think about how to store long-term data outside of TimescaleDB to make it affordable right from the start? You can always work around things as you go along, but it is good to have a plan for this before you go live.💻If you want to learn more about how Edeva handles time-series data with Actibump and EdevaLive, the team hostsvirtual biweekly webinars, or you can alsorequest a demo.We’d like to thank John and all the folks from Edeva for sharing their story. We are amazed to see how their work truly impacts the way people live and enjoy their city with a little help from time-series data.🙌We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-edeva-uses-continuous-aggregations-and-iot-to-build-smarter-cities/
How Everactive Powers a Dense Sensor Network With Virtually No Power at All,"📬This blog post was originally published in February 2021 and updated in February 2023 to reflect Everactive's data stack and business evolution.This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Carlos Olmos,Dan Wright, andClayton Yochumfrom Everactive join us to share how they’re bringing analytics and real-time device monitoring to scenarios and places never before possible. Learn how they’ve set up their data stack (moving from six to only three instances), their database evaluation criteria, their advice for fellow developers, and more.About the CompanyEveractivecombines battery-free, self-powered sensors and powerful cloud analytics to provide “end-to-end” hyperscale IoT solutions to our customers. Our company is undergoing a huge transformation from focusing only on industrial monitoring services (read more about Industrial IoT) to opening our platform to developers that can leverage our technology to create their own solutions and services.It is not easy to build a platform, less to build an open platform. Flexibility and stability are key factors. We have found so far that with Timescale (and PostgreSQL underneath), we have been able to bend our data models and data serving needs to implement the new use cases for the platform without pain.We design and build our sensors in-house (down to the chip), and they’re ruggedized for harsh settings and can operate indefinitely from low levels of energy harvested from heat or light. This means our customers’ devices can continuously stream asset health data, despite radio interference and physical obstacles—like equipment, ducts, and pipes—common in industrial settings.Since they charge themselves, these sensors stay operational well beyond what’s possible with traditional, battery-powered Industrial IoT devices. We ingest data from thousands of sensorsinto Timescale, then surface it to our customers through dashboards, charts, and automated alerts.Ourinitial productsare designed to monitor steam systems, which are used in various industries and applications, like process manufacturing, chemical processing, and district energy, as well as a range of rotating equipment, such as motors, pumps, fans, and compressors. Currently, we serve large, Fortune 500 manufacturers in many sectors, including Food & Beverage, Consumer Packaged Goods, Chemical Process Industries, Pharmaceuticals, Pulp & Paper, and Facilities Management.We show customers their data through a web-based dashboard, and we also have internal applications to help our in-house domain experts review and label customer data to improve our automated failure detection.About the TeamWe’re a small team of software and data engineers, spanning the Cloud and Data Science teams at Everactive.Between us, we’ve got several decades of experience managing databases, pipelines, APIs, and various other bits of backend infrastructure.About the ProjectOur key differentiator is that our sensors are batteryless: the custom low-power silicon means that they can be put in more places, without requiring servicing for well over a decade.In turn, this means that we can monitor factory devices that were formerly cost-prohibitive to put sensors on, due to the difficulty or cost associated with charging batteries; being able to collect dataeconomicallyfrom more equipment also means that our industrial data streams are more detailed and cover more equipment than our competitors’.Today, customers place our sensors on steam traps and motors, and we capture a range of metrics – from simple ones, like temperature, to more complex ones, like 3D vibrational data. (You can learn more about steam trap systems and the need for batteryless systems inthis overview video.)Everactive’s new generation of sensors in the wild, including a sensor on a hydraulic arm with a shipping container and, below, a sensor on a shipping container clasp closer upWe then use this data to inform our customers about the health of their industrial systems, so they can take action when and where required. “Action” in this sense could mean replacing a steam trap, replacing a bad bearing in a machine, or various other solutions to problems.For example, we’ll automatically alert customers if their monitored equipment has failed or if machines are off when they should be on, so customers can send a crew to fix the failure, or power on the machine remotely.In addition to receiving alerts from us, customers can use our dashboards to check the latest data and current status of their equipment at any time.One of Everactive’s dashboards, tracking the overall vibration levelAs mentioned earlier, our team’s responsible for delivering these intuitive visualizations to our customers and in-house domain experts –  as well as for feeding sensor metrics into our custom analytics to automate failure detection and improve our algorithms.Using (and Choosing!) TimescaleDBBefore TimescaleDB, we stored metadata in PostgreSQL and our sensor data in OpenTSDB. Over time, OpenTSDB became an increasingly slow and brittle system.Our data is very well-suited to traditional relational database models: we collect dozens of metrics in one packet of data, so it makes sense to store those together. Other time-series databases would force us to either bundle metrics into JSON blobs (making it hard to work with in-database) or to store every metric separately (forcing heavy, slow joins for most queries of interest).TimescaleDB was an easy choice because it let us double down on PostgreSQL, which we already loved using for metadata about our packet streams.We looked briefly at competitors like Influx but stopped considering them once it was clear TimescaleDB would exceed our needs.Our evaluation criteria were pretty simple: will it handle our load requirements, and can we understand how to use it? The former was easy to test empirically, and the latter was essentially “free” as TimescaleDB is “just” a PostgreSQL extension.This “just PostgreSQL” concept also lets us carefully manage our schema as code, testing and automating changes through CI/CD pipelines. We usesqitch, but popular alternatives includeFlywayandLiquibase. We like sqitch because it encourages us to write tests for each migration and is lightweight (no JVM).We previously usedAlembic, the migration component of the popularSQLALchemy Python ORM, but as our TimescaleDB database grew to support many clients, it made less sense to tie our schema management to any one of them.We maintain a layer of abstraction within TimescaleDB by separating internal and external schemas.“The capacity of Timescale to support both traditional schemas and time-series data in the same database allowed us to consolidate into one storage solution”Our data is stored as (hyper)tables in internal schemas like “packets” and “metadata,” but we expose them to clients through an “API” schema only containing views, functions, and procedures. This allows us to refactor our data layout while minimizing interruption in downstream systems by maintaining an API contract. This is a well-known pattern in the relational database world—yet another advantage of TimescaleDB being “simply” a PostgreSQL extension.Current Deployment & Future PlansAnother example of an Everactive dashboardWe useTimescaleand love it. We already used PostgreSQL on Amazon RDS and didn’t want to have to manage our own database (OpenTSDB convinced us of that!).It had become normal for OpenTSDB to crashmultiple times per weekfrom users asking for slightly too much data at once.TimescaleDB is clearly much faster than our previous OpenTSDB system. More importantly, nobody has ever crashed it.One not-very-carefully-benchmarked but huge performance increase we’ve seen?We have a frontend view that requires the last data point from all sensors: in OpenTSDB, it required nearly 10minutesto load (due to hard-to-fix tail latencies in HBase), and our first TimescaleDB deployment brought that down to around 7seconds. Further improvements to our schema and access patterns have brought these queries into the sub-second range.""We moved those schemas from Amazon RDS for PostgreSQL to Timescale. The migration was very simple: moving among PostgreSQL schemas was straightforward""We have been able to maintain sub-second responses even with the growth of data volume: that's very good for us. Also, thanks to compression andcontinuous aggregates, we have been keeping our table sizes in check and with great performance.✨Editor’s Note:For more comparisons and benchmarks, seehow Timescale compares to Amazon RDS for PostgreSQL. To learn more and try Timescale yourself, see ourstep-by-step Timescale tutorial.Timescale has been so good for us that it’s triggered a wave of transitions to managed solutions for other parts of our stack.We’ve recently moved our Amazon RDS data into Timescale to further simplify our data infrastructure and make it easier and faster to work with our data.About 20 % of our data is metadata kept in a relational database. We moved those schemas from Amazon RDS for PostgreSQL to Timescale. The migration was very simple: moving among PostgreSQL schemas was straightforward.We chose RDS from the beginning for simplicity. Eventually, once we had Timescale up and running, it became evident that we didn't need two separate PostgreSQL vendors when we were having such good results with Timescale.The capacity of Timescale to support both traditional schemas and time-series data in the same database allowed us to consolidate into one storage solution. The instances multiply because we keep three environments (development, staging, and production) for each database, so we went from six (three RDS plus three Timescale) to only three Timescale instances.As you’ll see in the below diagram, our sensors don’t talk directly to TimescaleDB; they pass packets of measurements to gateways via our proprietary wireless protocol. From there, we useMQTTto send those packets to our cloud.From our cloud data brokers,Kafkaprocesses and routes packets into TimescaleDB (and Timescale), and our TimescaleDB database powers our dashboard and analytics tools. We also added a third component: outbound channels for our platform users.Everactive architecture diagramCompared to Amazon RDS for PostgreSQL, Timescale also consolidates a lot of the costs associated with operating our instance in AWS—we now have a simpler bill that makes it easier to forecast costs.From the operation point of view, dealing with fewer instances and relying more on the Timescale Support Team for infrastructure maintenance has reduced our database maintenance workload significantly. Our security operations also benefited from the migration, again thanks to the consolidation and the transfer of certain responsibilities to Timescale.✨Editor’s Note:Read how our Support Team is raising the baron hosted database support.We’ll continue to innovate on our technology platform and increase Everactive’s product offerings, including improving our sensors’ wireless range, lowering power requirements to increase energy harvesting efficiency, integrating with additional sensors, and shrinking device form factor. These successive chip platform enhancements will allow us to monitor the condition of more and more assets, and we’re also developing a localization feature to identifywhereassets are deployed.Ultimately, Everactive’s mission is to generate new, massive datasets from a wealth of currently un-digitized physical-world assets. Transforming that data into meaningful insights has the potential to fundamentally improve the way that we live our lives—impacting how we manage our workplaces, care for our environment, interact with our communities, and manage our own personal health.Advice & ResourcesIf you’re evaluating your database options, here are two recommendations based on our experiences. First, if you have enough time-series data that a general database won’t cut it (millions of rows), TimescaleDB should be your first choice. It’s easy to try out,the docs are great, and thecommunityis very helpful.Second, don’t underestimate the importance of using solutions that leverage a wide knowledge base shared by many/most backend developers. The increase in team throughput and decrease in onboarding time afforded by TimescaleDB—everyone knows at leastsomeSQL—in contrast to OpenTSDB—an esoteric thing built on HBase—has been a huge advantage. We expected this to some degree, but actually experiencing it firsthand has confirmed its value.Additionally, the use of schema-as-code tools and an internal/external schema separation discussed above have also been cornerstones of our success. We hadn’t been using these tools and patterns at Everactive previously but have since seen them catch on in other projects and teams.Want to read more developer success stories?Sign up for our newsletterfor more Developer Q&As, technical articles, tips, and tutorials to do more with your data—delivered straight to your inbox twice a month.We’d like to thank Carlos, Dan, Clayton, and all of the folks at Team Everactive for sharing their story (special shoutout to Brian, Joe, Carlos, Greg, and Elise for your contributions to this piece!). We applaud your efforts to bring sustainable, cost-effective sensors and easily actionable data to organizations around the world 🙌.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-everactive-powers-a-dense-sensor-network-with-virtually-no-power-at-all/
How to Reduce Query Cost With a Wide Table Layout in TimescaleDB,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Florian Herrengt, co-founder ofNocodelytics,shares how he tracks, records, and monitors millions of user interactions per day to build dazzling analytics dashboards for website building and hosting platformWebflow. And the best part? The team is making queries up to six times less costly by using a wide table layout.About the CompanyA Nocodelytics dashboardNocodelyticsis an analytics product built specifically for the website building and hosting companyWebflow. We are processing millions of events from various websites and turning them into insightful dashboards.We’re a close-knit team of three based in London. We are two co-founders,Sarwechand myself (Florian), and the team recently expanded whenAlexjoined us as a frontend developer. You can find us on theWebflow Marketplace.As our company is growing fast, we found that a quick, reliable database is vital for our company to grow and thrive.About the ProjectOur goal is to build the number one analytics platform for Webflow.Like other analytics tools, we provide users with a tracking script that they add to their Webflow site. However, because of the nature of Webflow’s audience, we have to do things quite differently from other analytics tools—which presents challenges that no other analytics tool faces.First, one of the things that we do that adds complexity is that we automatically track every event a user does. Whether it’s clicking on a button or link, interacting with a form, or viewing a page, we need to be able to track all of this information with minimal impact on accuracy.Adding a new metric to the Nocodelytics dashboardWe also track events tied to the Webflow Content Management System (CMS) and other third-party tools likeJetboost,Wized,Memberstack, andOutseta, which we automatically integrate with and track.So, we tap into the power of the CMS and the Webflow ecosystem to record how users interact. We then output these interactions into valuable insights for our analytics customers. This means we need to be able to record and ingest millions of events into our database per day without it crashing down. Some of our customers will get publicity and see huge spikes in traffic, so we need to be able to handle this too.Second, we provide our customers with a simple-to-use and customizable dashboard. This allows them to create metrics that go deep and answer almost any question (What are the most popular jobs on my site? How many signups came from Google? Which contact button is most effective?).To do this, we’re building a metric creator that is simple to use on the frontend but complex on the backend, with some advanced SQL being done to return the right answers depending on the question asked. So it’s important that we have the right tool to help us with this.Third, when our customers view their dashboard and look at the metrics, even a few seconds’ wait can cause frustration. As our customers can have several metrics on the dashboard at any time—some with fairly complex queries—there’s a lot of pressure on our database to read the data, crunch the numbers, and return the result quickly.On top of that, we also allow our customers to share and embed their dashboard onto a site, which means the number of users viewing the metrics goes up, and the number of read requests can increase at any time.Choosing (and Using!) TimescaleDBFirst, let’s talk about the previous setup we had and what problems this resulted in.Like many other companies,Nocodelyticsstarted with PostgreSQL. In the beginning, it worked. But the size of the database grew very, very fast. Eventually, with millions of rows, our dashboards became sluggish. Queries for customers with a lot of traffic would take several minutes or even time-out.As we needed a solution as quickly as possible, I had three things in mind when looking for an alternative to PostgreSQL:It had to be quick to learn.The change needed to involve a minimal amount of code.The migration path had to be simple.My first choice wasClickHouse, which seems to have better performance than Timescale for our use case—but keep reading as there's more to it.Not everything was great about ClickHouse: It does a lot, which can get confusing, and I’d rather stick with PostgreSQL, which I’ve used for years and know works.Amazon Athenawas another good option. It's serverless and queries compressed data directly from S3 (which Timescale is now offering in private beta too). It did have some weird limitations (e.g., a maximum of 20 concurrent queries, no way to update data, and dynamic partition columns must always be included in queries), which I found out the hard way. At that point, I was worried about the next issue I’d find, and I lost confidence in the product.Finally,InfluxDB. I spent a few hours with it, but it’s too different from what we already had. The migration would take forever.Also, I must stress that I had never heard about those tools before. I either worked on large projects with big money, where we used Redshift/BigQuery or specialized, small-scale projects, where the smallest PostgreSQL instance would be enough.I was about to use ClickHouse before I came across Timescale by chance while browsing databases.It’s just PostgreSQLThere you have it. The best feature of TimescaleDB: it's all PostgreSQL, always has been. All your tools, all the existing libraries, and your code already work with it. I’m using TimescaleDB because it’s the same as PostgreSQL but magically faster.Whatever technology is behind TimescaleDB, it’s truly impressive. Since theWebflow Conf, we have been inserting more than a million rows per day (without optimizations) in our tiny 8 GB memory instance. Sometimes, we have 3 K/IOPS. PostgreSQL would struggle. It’s like pulling an elastic until it snaps—but it never does, and we barely scratched the surface of what it can do. Also, the community is really nice.“I’m using TimescaleDB because it’s the same as PostgreSQL but magically faster""So, in sum, Timescale was a drop-in replacement that solved most of our issues. I installed the extension,created a hypertable, and everything became magically fast.✨Editor’s Note: Want to get started with TimescaleDB?Check out our documentation.But as I was reading the Timescale documentation, I realized it could be faster. A lot faster.Relational vs. Wide Table LayoutWhen you first learn about relational databases, you learn how to normalize your data with multiple tables and foreign key references. That’s a good, flexible way to store your data. However, it can be an issue when dealing with a large amount of data.That’s where the wide table layout becomes useful.Normalized data vs. Wide tableThe idea is to trade storage and schema flexibility for query performance by reducing the number ofJOINs. But this doesn’t stop you from combining both. You can still add foreign keys to a wide table.You will end up using more storage, but you can mitigate it with TimescaleDB’s compression.✨Editor’s Note:Learn how to save space using compression.Show time: Setting Up the SchemaLet’s create the above schema with relationships and insert dummy data:-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS events_id_seq;
CREATE SEQUENCE IF NOT EXISTS countries_id_seq;
CREATE SEQUENCE IF NOT EXISTS browsers_id_seq;
CREATE SEQUENCE IF NOT EXISTS devices_id_seq;
 
-- Table Definition
CREATE TABLE ""public"".""countries"" (
   ""id"" int4 NOT NULL DEFAULT nextval('countries_id_seq'::regclass),
   ""name"" varchar,
   PRIMARY KEY (""id"")
);
 
CREATE TABLE ""public"".""browsers"" (
   ""id"" int4 NOT NULL DEFAULT nextval('browsers_id_seq'::regclass),
   ""name"" varchar,
   PRIMARY KEY (""id"")
);
 
CREATE TABLE ""public"".""devices"" (
   ""id"" int4 NOT NULL DEFAULT nextval('devices_id_seq'::regclass),
   ""name"" varchar,
   PRIMARY KEY (""id"")
);
 
CREATE TABLE ""public"".""events"" (
   ""id"" int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
   ""name"" varchar,
   ""value"" int,
   ""country_id"" int,
   ""browser_id"" int,
 
   ""device_id"" int,
   PRIMARY KEY (""id"")
);
create index events_country_id on events(country_id);
create index events_browser_id on events(browser_id);
create index events_device_id on events(device_id);
create index countries_name on countries(name);
create index browsers_name on browsers(name);
create index devices_name on devices(name);Then create our new wide table:create table events_wide as
   select
       events.id as id,
       events.name as name,
       events.value as value,
       countries.name as country,
       browsers.name as browser,
       devices.name as device
   from events
   join countries on events.country_id = countries.id
   join browsers on events.browser_id = browsers.id
   join devices on events.device_id = devices.id
 
create index events_wide_country on events_wide(country);
create index events_wide_browser on events_wide(browser);
create index events_wide_device on events_wide(device);ResultsNeat. But was it worth it? Well, yes. It would be a lot less interesting to read otherwise. Now that we have our wide table, let’s have a look at the query cost.-- cost=12406.82
explain select devices.name, count(devices.name)
from events
join countries on events.country_id = countries.id
join browsers on events.browser_id = browsers.id
join devices on events.device_id = devices.id
where browsers.name = 'Firefox' and countries.name = 'United Kingdom'
group by devices.name order by count desc;
 
-- cost=2030.21
explain select device, count(device)
from events_wide
where browser = 'Firefox' and country = 'United Kingdom'
group by device order by count desc;This is a significant improvement. The same query is six times less costly. For a dashboard with dozens of metrics, it makes amassivedifference.You can find the full SQLhere.Future PlansTimescale is packed with amazing features we want to start using. Things liketime_bucket_gapfill()orhistogram().I didn't dive into it yet, but theTimescale Toolkitseems to have a lot of valuable functionality, such asapproximate count distinctsorfunction pipelines, which we can’t wait to try out.We also want to see howcontinuous aggregatescould help us relieve some pressure on the database.Our goal is to keep growing and scaling the number of events we store. We will soon leveragetablespacesanddata tiering to save on storage space. We’re keen to further optimize and use TimescaleDB to help as we grow towards handling billions of rows!June 2023 update:We’re now dealing with more than 500 GB of data, and those wide tables just aren’t efficient anymore.So, we’ve gone ahead and separated the table again. We’re executing a count query first, retrieving the ids, then running another query for the labels. Essentially, it’s a two-query process.TimescaleDB is row-based and our wide table is heavy on strings. As a result, we’re hitting I/O limits. This wasn’t a problem before because we’re using a fast SSD and had fewer rows per site, but now with the data volume, it’s a different story.In retrospect, choosing the wide table structure at that time was good. It accelerated our development pace significantly. We centralized all the events, simplifying our queries for quite some time. Plus, it enabled us to compress all our data without effort. Looking back, it was a beneficial strategy for that stage of our project.We’d like to thank Florian and the folks at Nocodelytics for sharing their story on tracking millions of user events while reducing their query cost using TimescaleDB. Stay tuned for an upcoming dev-to-dev conversation between Florian and Timescale’s developer advocate,Chris Englebert, where they will expand on these topics.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-to-use-wide-table-model-to-reduce-query-cost-in-timescaledb/
Processing and Protecting Hundreds of Terabytes of Blockchain Data: Zondax’s Story,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Ezequiel Raynaudo, data team leader at Zondax, explains how the software company consumes and analyzes hundreds of terabytes of blockchain data using TimescaleDB to create complete backend tech solutions.About the CompanyZondaxis a growing international team of more than 30 experienced professionals united by a passion for technology and innovation. Our end-to-end software solutions are widely used by many exchanges, hardware wallets, privacy coins, and decentralized finance (DeFi) protocols. Security is one of the highest priorities in our work, and we aim to provide the safest and most efficient solutions for our clients.Founded in 2018, Zondax has been consideredthe leader in the industry for Ledger apps development, with more than 45 applications built to date and more near production. Since its inception, our team has been building and delivering high-quality backend tech solutions for leading blockchain projects of prominent clients, such as Cosmos, Tezos, Zcash, Filecoin, Polkadot, ICP, Centrifuge, and more.“The database is a critical part of the foundation that supports our software services for leading blockchain projects, financial services, and prominent ecosystems in the blockchain space”We put a lot of emphasis on providing maximum safety and efficiency for the ecosystem. Our services can be categorized into security, data indexing,  integration, and protocol engineering. Each category has designated project leaders that take the initiative in managing the projects with well-experienced engineers.About the TeamMy team (which currently has two people but is looking to grow by the end of the year) manages the blockchain data in various projects and ensures the tools needed for the projects are well-maintained. For example, we pay close attention to the dynamic sets of different blockchains and create advanced mathematical models for discovering insightful information via data analytics.The database is a critical part of the foundation that supports our software services for leading blockchain projects, financial services, and prominent ecosystems in the blockchain space. In conclusion, we take serious steps to ensure the work of processing blockchain data remains effective, and that the quality of the results meets Zondax's high standards.We welcome professionals from different cultures, backgrounds, fields of experience, and mindsets. New ideas are always encouraged, and the diversity within the team has been helping us to identify various potential advancements we can make in leading blockchain projects.At the same time, our efforts in experimenting and searching for creative and efficient solutions led us to pay close attention to the latest technologies and innovations that we immediately incorporate into our work. We never get bored at Zondax!Since the Covid-19 pandemic, many companies and teams have switched to remote work temporarily or permanently, and for Zondax this is familiar ground. We adopted a culture of remote work from the start, which has been rewarding and encouraging as team members from around the globe often spark fun and constructive discussions despite nuanced cultural differences. And in terms of quality of work, the tools and platforms we have been using accommodate the needs for smooth communication and effective collaboration within different teams.About the ProjectZondax provides software services to various customers, including leading projects in the blockchain ecosystem and financial services. We consume and analyze blockchain data in numerous different ways and for multiple purposes:As input for other services and ecosystems provided by Zondax and for other third partiesTo apply data science and get value in the form of insights by using mathematical models for different blockchain dynamic sets of variablesFinancial servicesBlockchain data backupsThe minimal unit of division of a blockchain, a.k.a. a “block” on most networks, contains a timestamp field among several other sub-structures. It allows you to define blocks at a specific point in time throughout the blockchain history. So having a database engine that can leverage that property is a go-to option.Choosing (and Using!) TimescaleDB✨Editor’s Note:Want to know more about optimizing queries on hypertables with thousands of partitions using TimescaleDB?Check out this blog post.Our first encounter with TimescaleDB was througha blog post about database optimizations. We decided to go ahead and install it on our infrastructure since it’s built on top of PostgreSQL, and our main code and queries didn’t require to be updated. After installing the corresponding helm chart on our infrastructure, we decided to try it.The first tests denoted a substantial increase in performance when writing or querying the database, with no optimizations at all and without using hypertables. Those results encouraged us to keep digging into TimescaleDB’s configurations and optimizations, such as usingtimescaledb-tuneand converting critical tables to hypertables.“If Timescale didn’t exist, we would have a problem and might need to wait a couple of weeks to process a few dozens of terabytes rather than waiting only 1-2 days”Long story short, we went from having to wait a couple of weeks to process a few dozens of terabytes to only 1-2 days. Among the top benefits of TimescaleDB, I would highlight having the best possible write and read performance. It is a critical part of our ecosystem because it helps us provide fast and responsive services in real time. Using TimescaleDB also allows our software to stay synced with the blockchain's nodes, which is one of the most significant acknowledged advantages of our software and services. Last but not least, we also use TimescaleDB for blockchain data backups to protect data integrity.Before finding out about TimescaleDB, we first used custom PostgreSQL modifications like indexing strategies and high-availability setups. Also, our team did some benchmarking using NoSQL databases like MongoDB, but with no substantial improvements on the write/read speeds that we needed.If Timescale didn’t exist, we would have a problem and might need to wait a couple of weeks to process a few dozens of terabytes rather than waiting only 1-2 days.We are glad that we chose Timescale and proud of the work that has been expedited and achieved. For example, despite many challenges, we didn't give up on experimenting with new approaches to process a tremendous amount of blockchain data. Instead, we continued exploring new ideas and tools until we eventually started using TimescaleDB, which drastically shortened the time to process data and accelerated our progress in delivering quality results for the projects.Current Deployment & Future Plans✨Editor’s Note:Read how you canadd TimescaleDB to your Kubernetes deployment strategyquickly and easily.We deploy TimescaleDB using a custom helm chart that fits our infrastructure needs. As far as programming languages, we mainly use Golang to interact with TimescaleDB; and Hasura as the main query engine for external users.Advice & Resources✨Editor’s Note:Want to learn how to create a hypertable using TimescaleDB?Check out our documentation on hypertables and chunks.I’d recommend reading the blog postson how to get a working deployment,hypertables, andTimescale vs. vanilla Postgres's performance using the same queries.A wise man (Yoda) once said, ""You must unlearn what you have learned."" It is inevitable to encounter countless challenges when developing a scalable database strategy, but staying curious and willing to explore new solutions with caution can sometimes be rewarding.We’d like to thank Ezequiel and all of the folks at Zondax for sharing their story and efforts in finding a solution to process enormous amounts of data to build backend solutions for blockchain projects.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/processing-and-protecting-hundreds-of-terabytes-of-blockchain-data-zondaxs-story/
How FlightAware Fuels Flight Prediction Models for Global Travelers With TimescaleDB and Grafana,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Caroline Rodewig, Senior Software Engineer and Predict Crew Lead at FlightAware,  joins us to share how they’ve architected a monitoring system that allows them to power real-time flight predictions, analyze prediction performance, and continuously improve their models.FlightAwareis the world's largestflight tracking and data platform; we fuse hundreds of global data sources to produce an accurate, consistent view of flights around the world. We make this data available to users through web and mobile applications, as well as different APIs.Our customers cover a number of different segments, including:Travelers / aviation enthusiastswho use our website and mobile apps to track flights (e.g., using our “where’s my flight?” program).Business aviation providers(such asFixed Base Operatorsoraircraft operators) who use flight-tracking data and custom reporting to support their businesses.Airlinesthat use flight-tracking data or our predictive applications to operate more efficiently.Editor’s Note: for more information about FlightAware’s products (and ways to harness its data infrastructure), check outthis overview. Want to build your own flight tracking receiver and ground station? SeeFlightAware’s PiAware tutorial.About the teamThe Predictive Technologiescrewis responsible for FlightAware's predictive applications, which as a whole are called ""FlightAware Foresight."" At the moment, our small-but-mighty team is made up of only three people: our project managerJames Parkman, software engineer Andrew Brooks, and myself. We each wear many different hats; a day's work can cover anything from Tier 2 customer support to R&D, and everything in between.A former crew member, Diorge Tavares, wrote acool articleabout his experience as a site reliability engineer embedded in the Predict crew. He helped us design infrastructure and led our foray into cloud computing; now that our team is more established, he’s moved back to the FlightAware Systems team full-time.About the projectOur team's chief project is predicting flight arrival times, or ETAs; we predict both landing (EON) and gate arrival (EIN) times. And, ultimately, we need to monitor, visualize, and alarm on thequalityof those predictions. This is where TimescaleDB fits in.Not only should we track how our prediction error changes over the course of each flight, we also need to track how our error changes over months - or years! - to ensure we're continually improving our predictions. Our predictive models can have short bursts of inaccuracy - like failing to anticipate the impact of a huge storm - but they can also drift slowly over time as real-life behaviors change.As an example of the type of data we extract, the below is our ""Worst Flights"" dashboard, which we use for QA. (Looking through outliers is an easy way to spot bugs.) The rightmost column compares our error to third-parties', so we can see how we're doing relative to the rest of the industry.Our Grafana dashboard for tracking ""Worst Flights"" and our prediction quality vs. other data sourcesBut, we also go deep into specific flights, like the below ""Single Flight"" dashboard view.This is useful for debugging, as it gives a detailed picture of how our predictions changed over the course of asingleflight.Our Grafana dashboard for debugging and assessing our prediction quality at the individual flight levelChoosing (and using) TimescaleDBWe tested out several different monitoring setups before settling on TimescaleDB and Grafana. We recently published ablog postdetailing our quest for a monitoring system, which I’ve summarized below.First, we considered usingZabbix; it's widely used at FlightAware, where most software reports into Zabbix in one way or another. However, we quickly realized that Zabbix was not the tool for the job – our Systems crew had serious doubts that Zabbix would be able to handle the load of all the metrics we wanted to track:We make predictions for around 75,000 flights per day; if we only stored two error values per flight (much fewer than we wanted), it would require making 100 inserts per minute.After ruling out Zabbix, I started looking atGrafanaas a visualization and alerting tool, and it seemed to have all the capabilities we needed. For my database backend, I first pickedPrometheus, because it was near the top of Grafana's ""supported databases"" list and its built-in visualization capabilities seemed promising for rapid development.I didn't know much about time-series databases, and, while Prometheus is a good fit for some data, it really didn't fit mine well:No JOINs. My only prior database experience was with PostgreSQL, and it didn't occur to me that some databases just wouldn't support JOINs. While wecouldhave worked around this issue by inserting specific, already-joined error metrics, this would have limited the flexibility and ""query-a-bility"" of the data.Number of labels to store. At the bare minimum, we wanted to store EON and EIN predictions for 600 airports, at least 10 times throughout each flight. This works out to 12,000 different label combinations, each stored as a time series – whichPrometheus is not currently designed to handle.And, that’s when I found TimescaleDB. A number of factors went into our decision to use TimescaleDB, but here are the top four:Excellent performance.This article comparing TimescaleDB vs. PostgreSQL performancereally impressed me. Getting consistent performance, despite the number of rows in the table, was critical to our goal of storing performance data over several years.Institutional knowledge.FlightAware uses PostgreSQL in a vast number of applications, so there was already a lot of institutional knowledge and comfort with SQL.Impressive documentation.I have yet to have an issue or question that wasn't discussed and answered in the docs. Plus, it was trivial to test out – I love one-line docker start-up commands (seeTimescaleDB Docker Installation instructions).Grafana support.I was pretty confident that I wanted to use Grafana to visualize our data and power our dashboards, so this was a potential dealbreaker.We use several Grafana dashboards, like this one, to view detailed performance over time (average error trends over one or more airports)Editor’s Note: To learn more about TimescaleDB and Grafana,see our Grafana tutorials(5 step-by-step guides for building visualizations, using variables, setting up alerts, and more) andGrafana how-to blog posts.To see how to use TimescaleDB to perform time-series forecasting and analysis,check out our time-series forecasting tutorial(includes two forecasting methods, best practices, and sample queries).Current deployment & use casesOur architecture is pretty simple (see diagram below). We run a copy of this setup in several environments: production, production hot-standby, staging, and test. Each environment has its own predictions database, which allows us to compare our predictions in staging to those in production and validate changesbeforethey get released.⭐Pro tip:we periodically sync Grafana configurations from production to each of the other environments, which reduces the manual work involved in updating dashboards across instances.FlightAware Predict team's system architecture, which uses custom Python programs, Docker, Grafana, and TimescaleDBAfter some trial and error, we’ve set up our TimescaleDB schema as follows:(1) Short term (1 week) tables for arrivals, our own predictions, and third-party predictions.The predict-assessor program reads our flight data feed, extracts ETA predictions and arrival times, and inserts them into the database. For scale, the arrivals table typically contains 500k rows, and the predictions tables each contain 5M rows.Each table is chunked: arrivals by arrival time and predictions by the time the prediction was made.We use archiving functions to copy some data into long-term storage, andadrop_chunkspolicyto ensure that rows older than one week are dropped to prevent unlimited table growth.(2) Long term (permanent) table for prediction and prediction-error data.Archiving functions move data to the long term table by joining the short terms tables together. They also ""threshold"" the data to reduce verbosity, by only storing predictions at predetermined intervals; i.e., predictions that were present 1 and 2 hours before arrival are migrated to long-term tables, but intermediate predictions (i.e., at 1.5 hours before arrival) are not kept.Between the join and the threshold, the archiving process reduces the average number rows per flight from25(across 3 short-term tables) to6!We haven’t enabled adrop_chunkspolicy on this table as of now; after ~9 months of running this setup, our database file is pretty manageable at 54GB. If we start having space issues, we'd opt to store fewer predictions per flight rather than lose any year-over-year historical data.Biggest ""Aha!"" momentContinuous aggregates are what well and truly sold me on TimescaleDB. We went from 6.4 seconds to execute a query to 30ms.Yes, milliseconds.I was embarrassingly late to the party when it comes to continuous aggregates. When I first set up our database, every query was fast because the database was small. However, as we added data over time, some queries slowed down significantly.The biggest offender was a query on our KPIs dashboard, visualized in Grafana below. This graph gives us a birds-eye view of error over time. The blue line represents the average error for all airports at a certain time before arrival; the red line shows the number of flights per day. (You can see the huge traffic drop when airlines stopped flights in March, due to the COVID-19 pandemic.)Our KPI dashboard includes various metrics, including our average error rate and total flights per day across all airportsBeforelearning about continuous aggregates, the query to extract this data looked like this:SELECT
  time_bucket('1 day', arr_time) AS ""time"",
  AVG(get_error(prediction_fa, arr_time)) AS on_error,
  count(*) AS on_count
FROM prediction_history
WHERE 
  time_out = '02:00:00' AND 
  arr_time BETWEEN '2020-03-01' AND '2020-09-05'
GROUP BY 1
ORDER BY 1It took 6.4 seconds and aggregated 1.6M rows, from a table of 147M rows.For what the query was doing, this runtime wasn't too bad – the table was chunked byarr_time, which the query planner could take advantage of.I considered adding indexes to make the query faster, but wasn't convinced they would help much and was concerned about the resulting performance penalties for inserts.I also considered creating a materialized view to aggregate the data and writing a cron job to regularly refresh it...but that seemed like a hassle, and after all, I could wait 10 seconds for something to load 🤷‍♀️.Then, I discovered TimescaleDB's continuous aggregations! For the unfamiliar, they basically implement that regularly-refreshing materialized view idea, but in a far smarter way and with a bunch of cool extra features.Here's the view for the continuous aggregate:CREATE VIEW error_by_time_out
WITH (timescaledb.continuous) AS
  SELECT
    time_out,
    time_bucket(INTERVAL '1 hour', arr_time) AS bucket,
    AVG(get_error(prediction_fa, arr_time)) AS avg_error,
    COUNT(*) AS count
  FROM prediction_history
  GROUP BY time_out, bucket;The new data extraction query is a little bit harder to parse, because the error needs to be aggregated across continuous aggregate buckets:SELECT
  time_bucket('1 day', bucket) AS ""time"",
  SUM(avg_error * count) / SUM(count) AS error,
  SUM(count) AS count
FROM error_by_time_out
WHERE 
  time_out = '02:00:00' AND 
  bucket BETWEEN '2020-03-01' AND '2020-09-05'
GROUP BY 1
ORDER BY 1...and I'll let you guess how long it takes....30ms.Yes, milliseconds. We went from 6.4 seconds to execute the query to 30ms.On top of that, unlike in a classic materialized view, the whole view doesn't have to be recalculated every time it needs to be updated -just the parts that have changed.This means refreshes are lightning fast too.Continuous aggregates are what well and truly sold me on TimescaleDB.The amazing developers at Timescale recently made continuous aggregates even better through ""real-time"" aggregates. These will automatically fill in data between the last view refresh and real-time when they're queried, so you always get the most up-to-date data possible. Unfortunately, our database is a few versions behind so we're not using real-time aggregates yet, but I can't wait to upgrade and start using them.Editor’s Note: To learn more about real-time aggregates and how they work, see our“Ensuring up-to-date results with Real-Time Aggregations” blog and mini-tutorial(includes benchmarks, example scenarios, and resources to get started).Getting started advice & resourcesIn addition to the documentation I’ve linked throughout this post, I'd recommend doing what I did: readingthe TimescaleDB docs, spinning up a test database, and going to town.And, after a few months of use, make sure to go back and read the docs again – you'll discover all sorts of new things to try to make your database even faster (looking at youtimescaledb-tune)!Editor’s Note: If you’d like to follow Caroline’s advice and start testing TimescaleDB for yourself,Timescale Cloudis the fastest way to get up and running - 100% free for 30 days, no credit card required. You can see self-managed and other hosted optionshere.To learn more about timescale-tune,see our Configuring TimescaleDB documentation.We’d like to thank Caroline and the FlightAware team for sharing their story, as well as for their work to make accurate, reliable flight data available to travelers, aviation enthusiasts, and operators everywhere. We’re big fans of FlightAware at Team Timescale, and we’re honored to have them as members of our community!We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@lacey butler), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-flightaware-fuels-flight-prediction-models-with-timescaledb-and-grafana/
How Newtrax Is Using TimescaleDB and Hypertables to Save Lives in Mines While Optimizing Profitability,"This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Jean-François Lambert, lead data engineer atNewtrax, shares how using TimescaleDB has helped the IoT leader for underground mines to optimize their clients’ profitability and save lives by using time-series data in hypertables to prevent human and machine collisions.About the CompanyNewtrax believes the future of mining is underground, not only because metals and minerals close to the surface are increasingly rare but because underground mines have a significantly lower environmental footprint. To accelerate the transition to a future where 100 percent of mining is underground, we eliminate the current digital divide between surface and underground mines.To achieve this goal, Newtrax integrates the latest IoT and analytics technologies to monitor and provide real-time insights on underground operations, including people, machines, and the environment. Newtrax customers are the largest producers of metals in the world, and their underground mines rely on our systems to save lives, reduce maintenance costs, and increase productivity. Even an increase of 5 percent in overall equipment effectiveness can translate to millions in profits.We collect data directly from the working face by filling the gaps in existing communications infrastructures with a simple and easy-to-deploy network extension. With that, we enable underground hard rock mines to measure key performance indicators they could not measure in real time to enable short-interval control of operations during the shift.Our headquarters are based in Montreal, Canada, and we have regional offices around the globe, totaling 150 employees, including some of the most experienced engineers and product managers specializing in underground hard rock mining. Our solutions have been deployed to over 100 mine sites around the world.About the TeamThe Newtrax Optimine Mining Data Platform (MDP) is the first AI-powered data aggregation platform enabling the underground hard rock mining industry to connect all IoT devices into a single data repository. Our team consists of software developers, data scientists, application specialists, and mining process experts, ensuring our customers get the exact solutions they require.As the lead data engineer, it is my responsibility to define, enhance and stabilize our data pipeline, from mobile equipment telemetry and worker positioning to PowerBI-driven data warehousing via RabbitMQ, Kafka, Hasura, and of course, TimescaleDB.About the ProjectA diagram of the Newtrax solutions and how they bring together people, machines, and the environment in their mine intelligence operationsOur Mining Data Platform (MDP) platform incorporates proprietary and third-party software and hardware solutions that focus on acquiring, persisting, and analyzing production, safety, and telemetry data at more than one hundred underground mine sites across the world.From the onset, we have had to deal with varying degrees of data bursts at completely random intervals. Our original equipment manufacturer-agnostic hardware and software solutions can acquire mobile equipment telemetry, worker positioning, and environmental monitoring across various open platforms and proprietary backbones. WiFi, Bluetooth, radio-frequency identification (RFID), long-term evolution (LTE), leaky feeder, Controller Area Network (CAN) bus, Modbus—if we can decode it, we can integrate it.“We have saved lives using TimescaleDB”Regardless of skill sets, programming languages, and transportation mechanisms, all of this incoming data eventually makes it to PostgreSQL, which we have been using since version 8.4 (eventually 15, whenever TimescaleDB supports it!). For the past 10 years, we have accumulated considerable experience with this world-class open-source relational database management system.✨Editor’s Note:Stay tuned as we’ll announce support for PostgreSQL 15 very soon, in early 2023.While we are incredibly familiar with the database engine itself and its rich extension ecosystem, partitioning data-heavy tables was never really an option because native support left to be desired, and third-party solutions didn’t meet all of our needs.Choosing (and Using!) TimescaleDBI have been using PostgreSQL since I can remember. I played around with Oracle and MSSQL, but PostgreSQL has always been my go-to database. I joined Newtrax over 12 years ago, and we’ve used it ever since.In 2019, we found out about TimescaleDB (1.2.0 at the time) and started closely following and evaluating the extension, which promised to alleviate many of our growing pains, such as partitioning and data retention policies.Not only did TimescaleDB resolve some of our long-standing issues, but itsdocumentation,blog posts, and community outlets (Slack,GitHub,Stack Overflow) also helped us make sense of this “time-series database” world we were unknowingly part of.“One of our first tech debt reductions came via the use of thetime_bucket_gapfillandlocffunctions, which allowed us to dynamically interpolate mobile equipment telemetry data points that could be temporarily or permanently missing”A tipping point for choosing TimescaleDB was theNYC TLC tutorialwhich we used as a baseline. After comparing with and without TimescaleDB, it became clear that we would stand to gain much from a switch: not only from the partitioning/hypertable perspective but also with regards to the added business intelligence API functionality, background jobs,continuous aggregates, and variouscompressionandretention policies.Beyond that, we only ran basic before/after query analysis, because we know that a time-based query will perform better on a hypertable than a regular table, just by virtue of scanning fewer chunks. Continuous aggregates and hyperfunctions easily saved us months of development time which is equally, if not more important, than performance gains.More importantly, in order for our solutions (such as predictive maintenance and collision avoidance) to provide contextualized and accurate results, we must gather and process hundreds of millions of data points per machine or worker, per week or month, depending on various circumstances. We usehypertablesto handle these large datasets and act upon them. We have saved lives using TimescaleDB.Beyond the native benefits ofusing hypertablesand data compression/retention policies, one of our first tech debt reductions came via the use of thetime_bucket_gapfillandlocffunctions (see an example later), which allowed us to dynamically interpolate mobile equipment telemetry data points that could be temporarily or permanently missing. Since then, we have been acutely following any and all changes to the hyperfunction API.✨Editor’s Note:Read how you can write better queries for time-series analysis using just SQL and hyperfunctions.Current Deployment & Future PlansAs far as deployment is concerned, the mining industry is incredibly conservative and frequently happens to be located in the most remote areas of the world, so we mostly deploy TimescaleDB on-premises throughKubernetes. The main languages we use to communicate with TimescaleDB are C#, Golang, and Python, but we also have an incredible amount of business logic written as pure SQL in triggers, background jobs, and procedures.While all of our microservices are typicallyrestricted to their own data domains, we have enabled cross-database queries and mutations throughHasura. It sits nicely on top of TimescaleDB and allows us to expose our multiple data sources as a unified API, complete with remote relationships, REST/GraphQL API support, authentication, and permission control.Our use of TimescaleDB consists of the following:We greatly appreciatetimescaledb-tuneautomatically tweaking the configuration file based on the amount of memory and number of CPUs available. In fact, Newtrax has contributed to this tool a few times.We deploy thetimescaledb-haimage (which we also contributed to) because it adds multiple extensions other than TimescaleDB (for example,pg_stat_statements,hypopg,  andpostgis), and it comes with useful command-line tools likepgtop.Our developers are then free to use hypertables or plain tables. Still, when it becomes obvious that we’ll want to configure data retention or create continuous aggregates over a given data set,hypertables are a no-brainer.Even if hypertables are not present in a database, we may still use background jobs to automate various operations instead of installing another extension likepg_cron.Hypertables can be exposed as-is through Hasura, but we may also want to provide alternative viewpoints through continuous aggregates or custom views and functions.✨Editor’s Note:The TimescaleDB tuning tool helps make configuring TimescaleDB a bit easier. Read our documentation to learn how.As mentioned earlier, incoming data can be erratic and incomplete. We might be missing some data points or values. Using a combination of the following code will create a functionmine_data_gapfill, which can be tracked with Hasura and enable consumers to retrieve consistent data series based on their own needs. And you could easily useinterpolateinstead oflocfto provide interpolated values instead of the last one received.CREATE TABLE mine_data
(
  serial TEXT,
  timestamp TIMESTAMPTZ,
  values JSONB NOT NULL DEFAULT '{}',
  PRIMARY KEY (serial, timestamp)
);
SELECT CREATE_HYPERTABLE('mine_data', 'timestamp');

INSERT INTO mine_data (serial, timestamp, values) VALUES
('123', '2020-01-01', '{""a"": 1, ""b"": 1, ""c"": 1}'),
('123', '2020-01-02', '{        ""b"": 2, ""c"": 2}'),
('123', '2020-01-03', '{""a"": 3,         ""c"": 3}'),
('123', '2020-01-04', '{""a"": 4, ""b"": 4        }'),
('123', '2020-01-06', '{""a"": 6, ""b"": 6, ""c"": 6}');


CREATE FUNCTION mine_data_gapfill(serial TEXT, start_date TIMESTAMPTZ, end_date TIMESTAMPTZ, time_bucket INTERVAL = '1 DAY', locf_prev INTERVAL = '1 DAY')
RETURNS SETOF mine_data AS $$
  SELECT $1, ts, JSONB_OBJECT_AGG(key_name, gapfilled)
  FROM (
    SELECT
      serial,
      TIME_BUCKET_GAPFILL(time_bucket, MT.timestamp) AS ts,
      jsondata.key AS key_name,
      LOCF(AVG((jsondata.value)::REAL)::REAL, treat_null_as_missing:=TRUE, prev:=(
        SELECT (values->>jsondata.key)::REAL
         FROM mine_data
        WHERE values->>jsondata.key IS NOT NULL AND serial = $1
        AND timestamp < start_date AND timestamp >= start_date - locf_prev
        ORDER BY timestamp DESC LIMIT 1
      )) AS gapfilled
    FROM mine_data MT, JSONB_EACH(MT.values) AS jsondata
    WHERE MT.serial = $1 AND MT.timestamp >= start_date AND MT.timestamp <= end_date
    GROUP BY ts, jsondata.key, serial
    ORDER BY ts ASC, jsondata.key ASC
  ) sourcedata
  GROUP BY ts, serial
  ORDER BY ts ASC;
$$ LANGUAGE SQL STABLE;

SELECT * FROM mine_data_gapfill('123', '2020-01-01', '2020-01-06', '1 DAY');You can find this code snippet on GitHub too.RoadmapWe need to start looking into thetimescaledb-toolkitextension, which is bundled in thetimescaledb-haDocker image. It promises to “ease all things analytics when using TimescaleDB, with a particular focus on developer ergonomics and performance,” which is music to our ears.“Honestly, theTimescaleDocshad most of the resources we needed to make a decision between switching to TimescaleDB or continuing to use plain PostgreSQL (this article in particular)”Another thing on our backlog is to investigate using Hasura’s streaming subscriptions as a Kafka alternative for specific workloads, such as new data being added to a hypertable.Advice & ResourcesHonestly, theTimescaleDocshad most of the resources we needed to make a decision between switching to TimescaleDB or continuing to use plain PostgreSQL (this article in particular). But don’t get too swayed or caught up in all the articles claiming outstanding performance improvements: run your own tests and draw your own conclusions.We started by converting some of our existing fact tables and got improvements ranging from modest to outstanding. It all depends on your use cases, implementations, and expectations. Some of your existing structures may not be compatible right out of the box, and not everything needs to become a hypertable either! Make sure to consider TimescaleDB’s rich API ecosystem in your decision matrix.Sign up for our newsletterfor more Developer Q&As, technical articles, tips, and tutorials to do more with your data—delivered straight to your inbox twice a month.We’d like to thank Jean-François and all of the folks at Newtrax for sharing their story on how they’re bringing digital transformation to a conservative industry—all while saving lives along the way, thanks to TimescaleDB and hypertables.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.The open-source relational database for time-series and analytics.Try Timescale for free",https://www.timescale.com/blog/how-newtrax-is-using-timescaledb-and-hypertables-to-save-lives/
