Deciding what data types are best suited to store that kind of information comes down to a few factors, such as requirements on the precision of float-point values, the actual values content (such as text), compressibility, or query speed.

In this installment of the best practices series (see our posts on narrow, medium, and wide table layouts, single or partitioned hypertables, and metadata tables), we’ll have a look at the different options in PostgreSQL and TimescaleDB regarding most of these questions. While unable to answer the requirement question, a few alternatives may be provided (such as integers instead of floating point)—but more on that later.

Before We Start: Compression

Event-like data, such as time series, logs, and similar use cases, are notorious for ever-growing amounts of collected information. Hence, it’ll grow continuously and require disk storage.

But that’s not the only issue with big data. Querying, aggregating, and analyzing are some of the others. Reading this amount of data from disk requires a lot of I/O operations (IOPS; input/output operations per second), which is one of the most limiting factors in cloud environments, and even on-premise systems (due to how storage works in general). While non-volatile memory express (NVMes) transfer protocols and similar technologies can help you optimize for high IOPS, they’re not limitless.

That’s where compression comes in. TimescaleDB’s compression algorithms (and, to some extent, the default PostgreSQL’s) help decrease disk space requirements and IOPS, improving cost, manageability, and query speed.

But let’s get to the actual topic: best practices for data types in TimescaleDB.

Basic Data Types

PostgreSQL (and the SQL standard in general) offers a great set of basic data types, providing a perfect choice for all general use cases. However, you should be discouraged from using them. The PostgreSQL Wiki provides a great list of best practices regarding data types to use or avoid. Anyhow, you don’t immediately have to jump over; we’ll cover most of them here 🥹.

Nullable columns

When looking back at the best practices on table layout, medium and wide table layouts tend to have a few too many nullable columns, being placeholders for potential values.

Due to how PostgreSQL stores nullable values that are NULL, those are almost free. Having hundreds of nullable columns, most being NULL, is not an issue. The same is true for TimescaleDB’s custom compression. Due to storing data in a columnar format, empty row values are almost free when compressed (null bitmap).

Boolean values

A boolean value is a logical data type with one of two possible values, TRUE or FALSE. It is normally used to record decisions or states.

There isn’t much specific to booleans in TimescaleDB. They are a very simple data type but also a great choice. Still, people often use an integer to represent their values as 1 or 0. This may come in handy with narrow or medium table layouts where you want to limit the number of columns. Nothing speaks against either solution!

In terms of compressibility, booleans aren’t heavily optimized but compress fairly well with the standard compression. If you have a series of states, it may be recommended to only store state changes, though, removing duplicates from the dataset.

Floating-point values

Floating-point data types represent real numbers, most often decimal floating points of base ten. They are used to store all kinds of information, such as percentages, measurements like temperature or CPU usage, or statistical values.

There are two groups of floating-point numbers in PostgreSQL, float4 (a.k.a., real), float8 (double precision), and numeric.

Float4 and float8 columns are the recommended data types. TimescaleDB will handle them specifically (during compression) and optimize their use. On the other hand, numeric, as an arbitrary precision-sized data type, isn’t optimized at all. Numeric isn’t recommended.

In general, though, due to the complexity of floating-point numbers, if you know the required precision upfront, you could use the multiply-division trick and store them as integers, which are better optimized. For example, consider we want to store a temperature value (in Kelvin) and only two decimal places, but the value comes in as a float4.

float4 originalValue = 298.151566;
int storedValue = (round(originalValue * 100))::int;
float4 queryValue = storedValue::float4 / 100;

It is a trick often used in data transmission for embedded devices with a low throughput uplink to limit the number of bytes sent.

Integer values

Integer data types represent natural numbers of various sizes (meaning valid number ranges, depending on how many bytes are used to represent them). Integer values are often used for simple values, such as counts of events or similar.

All integer types (int2, SmallInt, int4, Integer, int8, BigInt) are recommended data types. TimescaleDB is heavily optimized to compress the values of those data types. No less than three compression algorithms are working in tandem to get the most out of these data types.

This is why it is advised (if you know the necessary precision of a floating-point value) to store the values as integers (see floating-point values for more information).

What is true for integers is also true for all serial data types (serial2, SmallSerial, serial4, Serial, serial8, BigSerial), as those are magical “aliases” for their integer counterparts, incorporating the automatic creation of sequences to fill in their values on insert.

That said, they use their corresponding integer data types as a column data type. Anyhow, the PostgreSQL best practices advise against using them and recommend using identity columns instead for anything PostgreSQL from version 10 onwards.

Timestamp, time, and date values

Timestamps and time and date data types represent a specific point in time, some with more and some with less explicit information. All these data types have versions with and without timezone information attached (except date).

Before going into details, I’d generally advise against any of the data types without timezones (timestamp without time zone, timestamp, time without time zone, time). Most of them are discouraged by PostgreSQL’s best practices and shouldn’t be used.

It’s a misconception that it would save any storage space, as many believe, and TimescaleDB doesn’t have any optimization for data types without timezones. While it works, it will add a lot of casting overhead which is implicit and, therefore, not immediately visible. That said, just don’t do it 🔥.

With that out of the way, you can use date and time, but you should consider the use case. While date is optimized and compressed using the same compression scheme as integers, time is not. In any case, you shouldn’t use both for the time-dimension column.

To store dates, you should consider using timestamptz with the time portion set to midnight in the necessary timezone (2023-01-01T00:00:00+00) to prevent any casting overhead when querying.

Likewise, you can use timestamptz to store a time value only. In this case, you encode the time portion to a specific date (such as 1970-01-01T15:01:44+04) and cast the final value back into a time value. Alternatively, you can store the value as an integer by encoding the time into the (nano)seconds since midnight or any other encoding you can come up with.

That leaves us with timestamptz (timestamp with time zone). You’ve guessed it: this is the recommended data type for any kind of point-in-time storage.

This data type is highly optimized, used by all internal functionality, and employs the same compression as integers. Said compression is especially effective with timestamps, as they tend to have little difference between two consecutive values and compress extremely well.

Still, be aware that some frameworks, object-relational mappings (ORMs), or tools love their timestamp without time zone and need to be forced to be good citizens.

Text values

Text values are used to store textual values of arbitrary size. Those values can include detailed descriptions, log messages, and metric names or tags. The available data types include text, char(n), and varchar(n).

PostgreSQL’s best practices advise against using char(n), as it will pad values shorter than n to that size and waste storage. It recommends using text instead.

The same is true with varchar(n) with a length limit. Consider using varchar (without length limit) or text.

From a TimescaleDB-specific perspective, there isn’t much to say except you may want to deduplicate long values using a separate table holding the actual value and a reference (such as a checksum on the content) and storing the reference in the hypertable.

TimescaleDB doesn’t offer any specific optimization to handle this type of data. It will, however, apply dictionary compression (lz4-based) to those text fields.

Byte array (bytea) values

Byte arrays (in PostgreSQL represented by the data type bytea) store arbitrary large sequences of bytes, which may represent anything, from encoded machine state to binary data packets.

When looking at customer/user use cases, it is a very uncommon data type, as most data is decoded before being stored in the database. Therefore, TimescaleDB doesn’t optimize anything about this data type. Compression, however, should use the lz4-based compression.

If you have recurring, large bytea values, you can store them outside the actual hypertable and apply deduplication, as explained for text columns.

Complex and Extension Data Types

Compared to basic data types, complex data types commonly encode multiple values into a single column. This may include custom composite types.

Structural types (JSON, JSONB, XML)

Structural data types encode complete objects or sets of information, often lossy in terms of data types of the actual values. PostgreSQL supports three structural data types, JSON, JSONB (a binary representation of JSON), and XML.

Values often contain complex state information from machines or sensors. They are also common with narrow table layouts to compensate for the different values that need to be stored.

To get it out of the way, if you want to store JSON-like data, don’t use JSON—use JSONB! It’s better regarding storage space, query speed, and anything you can think of.

The only disadvantage of JSONB is that you lose the original order of properties since JSONB will decompose the object for better efficiency due to the way it is stored internally. Anyhow, not sure I can come up with a great reason for why the order should matter, and I hope you agree 😉.

That said, JSONB is a valid choice when storing complex data. The amount of stored data stored should be kept under close observation, though. If you have recurring, large amounts of data inside the JSONB objects, it may be advisable to extract that meta information into a separate, vanilla PostgreSQL table and join them in at query time. An approach is shown in the text values section.

For the XML, I don’t have enough data to give any recommendations. Actually, I cannot remember anyone ever asking anything about it. Due to my scarce experience, I wouldn't advise using it. I guess it may behave similarly to JSONB, but that’s reading clouds.

For all the above data types, TimescaleDB won’t apply any specific optimizations, and compression will likely use the dictionary algorithm, which still yields impressive results (been there, done that).

There are a few things to be wary of when using those data types. Remember that the whole object needs to be read, parsed, and the requested path’s value extracted. That happens for every single row of data being queried.

One thing I learned using JSONB is that indexes on expressions and GIN indexes are your friends if you need to make selection decisions based on data inside the object (such as tags). Ensure that anything you need to make that decision is in an index to read and (potentially) decompress the object.

The second element to remember is that extracting values from those objects yields text values that need to be cast into the required data type. Even if the value is stored as a number inside the JSONB object, extracting it returns the text representation, which then needs to be cast back into an integer or floating-point value, adding a lot of casting overhead to the process.

Universally Unique Identifiers: UUID

Using UUID to store unique identifiers is common in the PostgreSQL world. They are used for various reasons, including simplifying the generation of highly unlikely and colliding values on a different system than the database for “security” reasons (meaning removing potentially guessable series of numbers) and others.

A UUID represents a (semi)random 128-bit number in the format specified in RFC 4122 (ISO/IEC 9834-8:2005), which looks similar to a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11.

TimescaleDB doesn’t optimize this data type at all. It will compress it using the dictionary compression algorithm. However, there are other elements to keep in mind when using UUID. While they offer many advantages and simplify distributed systems, they also create some locality issues, specifically with BTree indexes. You will find a great read on this topic in the Cybertec blog.

My recommendation would be to think twice about using UUIDs.

Tree-like structures: Ltree

While ltree is an uncommon data type when storing event-like data, it can be a great fit for deduplicating recurring, larger values. Ltree represents a certain (sub)path in a tree, illustrating a specific device in an overarching topology. Their values resemble a canonical, dot-separated path (building1.floor3.room10.device12).

Values of data type ltree aren’t handled by TimescaleDB specifically. However, due to their size, this isn’t a big deal.

As mentioned above, ltree values are perfect for deduplicating recurring data, such as meta information, which are unlikely or slowly changing. Combined with a hash/checksum, they can be used as a reference to look them up in an external table.

Key-value pairs: hstore

hstore provides the capability to store key-value pairs of data, similar to what a non-nested JSON(B) object would offer.

From my experience, only a few people use hstore, and most prefer JSONB. One reason may be that hstore only offers text values. That said, there is no experience in compression gains or speed implications. I guess a lot of what was noted on JSONB objects would hold for hstore.

PostGIS data: Geometries and geographies

Last but not least, PostGIS’ data types are like geometry and geography. PostGIS data types are used to store special information of any kind. Typical use cases are GPS positions, street information, or positional changes over time.

TimescaleDB works perfectly fine with PostGIS data types but doesn’t optimize them. Compression ratios, while not perfect, are still decent.

Not a lot to mention here: they work, and they work great.

What Is the Recommendation Now?

Recommending data types is hard since many decisions depend on requirements outside the scope of this blog post. However, I hope this read provides insight into which data types to avoid, which ones are fine, and how you may optimize okay data types.

To summarize, here’s a small table showing different aspects and categorizing them into four states: great 🟢, okay, but be wary of something 🟠, or avoid at any cost 🔴, as well as unknown (feel free to provide experience/feedback) ⚪️.

Conclusion

If you want to know more about the compression algorithms used, see the blog post one of my colleagues wrote, Time-Series Compression Algorithms, Explained.

Also, if you have any feedback or experience with any of the above data types or the non-mentioned ones, I’d be happy to hear from you on Twitter or our Community Slack (@Chris Engelbert)!

Finally, I’m sorry for the wall of text you’ve had to read to get here, but there’s a lot of information to share and many different data types (and those aren’t even all of them). I hope you enjoyed the read and learned something new along the way.

If you want to test Timescale right now, the easiest and fastest way to get started is to sign up for our 30-day Timescale free trial. To try self-managed TimescaleDB, see the documentation for further information.

Timescale hypertables work like regular PostgreSQL tables but offer optimized performance and user experience for time-series data. With hypertables, data is stored in chunks, which work similarly to PostgreSQL’s partitioned tables but support multiple dimensions and other features. While we discussed the table layout in the Narrow, Medium, or Wide Table Layout best practices article, this time, we addressed whether you should use a single table to store all data versus using multiple tables, as well as their respective pros and cons.

Timescale is built upon a relational database model, which means it supports numerous data modeling choices or ways in which data can be organized and laid out. Understanding the database design choices early on is crucial to find the best combination.

I started using Timescale long before joining the company, initially using it to store IoT metrics at my own startups. We went through a few different iterations of designs, and migrations between those were everything but fun. Due to that personal experience, one of my biggest goals is to prevent others from suffering through the same.

Time-Series Data Modeling: Using our Relational Database Experience

As mentioned, Timescale uses a relational data model at its core. Being built on PostgreSQL, we understand we have many ways to store data, including in partitioned tables or just separate ones. A common pattern in the relational world is to divide data into separate tables based on their content, also often referred to as domain or entity. That means that data belonging to a set of A’s is stored in a different table than a set of B’s, representing different domains.

That leaves us questioning how the concepts of time-series data and relational domains fit together. Unfortunately, there is no easy answer. Our primary options are “squeezing” all data into a single table, which could have hundreds of columns (basically defining our domain around the idea of “it’s all just metrics”), or splitting data into multiple tables with fewer columns. The latter choice may slice tables in many ways, such as by metric type (temperature is different from humidity, stock symbol A is different from symbol B), customer, data type, and others, or combinations of the previous.

Both possibilities have their own set of advantages and disadvantages, which can be split into four commonly seen topics:

1. Ease of use
2. Multi-tenancy / Privacy-related requirements (General Data Protection Regulation or GDPR / California Consumer Privacy Act or CCPA / others)
3. Schema migration or upgrading / Future-proofness
4. Tooling support

I did not choose the above order by accident: the sequential importance of these questions may influence your options further down the line.

Pros and Cons

As said before, both design choices have pros and cons, and it’s vital to understand them before making a final data modeling decision. Given the previous set of topics, let’s start by answering a few questions.

First and foremost, how important is the ease of use, meaning, are you and your team up to the challenging tasks that need to be solved down the road? Potential “complications” could involve generation patterns for table names or ensuring that similar tables are all upgraded to the same schema level.

Next up, are you required to provide a harder level of multi-tenancy, such as storing different customers not just by segregating them using a customer ID but are required to store them in different tables, schemas, or even databases? Is your company bound by regulations (e.g., GDPR or CCPA) where users and customers may have the right to be forgotten? With time-series data being (normally) append-only, removing parts of the data (this specific user’s data) may be tricky.

Then we have the question of whether you expect the data schema to change frequently. A large discussion around a future-proof design for hypertables can be found in the Narrow, Medium, or Wide Table Model best practices write-up. However, the higher the number of tables, the more they need to be upgraded or migrated in the future, adding additional complexity.

Finally, how important is support by additional tools and frameworks, such as ORM (Object Relational Mapping) solutions? While I personally don’t think ORM frameworks are a great fit for time-series data (especially when using aggregations), a lot of folks out there make extensive use of them, so talking about them has its merits.

Anyhow, now that we answered those questions, let’s dig into the design choices in greater detail.

Single Table Designs

Storing all data into a single table may initially feel irresponsible from a relational database point of view. Depending on how I slice my domain model, though, it could be a perfectly valid option. The design choice makes sense if I consider all stored data (metrics, events, IoT data, stock prices, etc.) as a single domain, namely time series.

Single tables make a few things super simple. First of all, and probably obvious to most, is querying. Everything is in the same table, and the queries select certain columns and add additional filters or where clauses to select the data. That is as basic as it can get with SQL. That said, querying data is super simple, not just easy.

Upgrading the table’s schema is equally simple. Adding or removing columns implies a single command, and all data is upgraded at the same time. If you have multiple similar tables, you may end up in a situation where some tables are upgraded while others are simply forgotten—no real migration window is needed.

Single tables can easily support multiple different values, either through multiple columns (wide table layout), a JSONB column that supports a wide range of data values (narrow table layout), or through columns based on a value’s potential data type (medium table layout). Those three options have pros and cons, though.

tsdb=> \d
      Column    |           Type           | Collation | Nullable |      Default
—---------------+--------------------------+-----------+----------+-------------------
 created        | timestamp with time zone |           | not null | now()
 point_id       | uuid                     |           | not null | gen_random_uuid()
 device_id      | uuid                     |           | not null |
 temp           | double precision         |           |          |
 hum            | double precision         |           |          |
 co2            | integer                  |           |          |
 wind_speed     | integer                  |           |          |
 wind_direction | integer                  |           |          |

|                  created | point_id | device_id | temp |  hum | co2 |
 wind_speed | wind_direction |
| 2022-01-01 00:00:00.0+00 |      123 |        10 | 24.7 | 57.1 | 271 |
       NULL |           NULL |

Last but not least, single tables play very nicely with tools like ORM frameworks. It is easy to connect a specific set of ORM entities to the hypertable, representing either a full record or the result of an aggregation (which may need a native query instead of an automatically generated one).

But as with everything in this world, this choice has a large downside. Since time-series data is designed around the idea of being append-only (meaning that mutating existing records only happens occasionally), it is hard to delete data. Deleting data based on specific requirements is even harder, such as a user or customer asking to have all their data deleted.

In that situation, we’d have to crawl our way through potentially years of data, removing records from all over the place. That’s not only a burden on the WAL (Write-Ahead Log) to keep track of the changes, but it also creates loads and loads of I/O, reading and writing.

The same is true if we try to store collected and calculated sets of data in the same table. With many systems often having to backfill data (for example, from devices that lost their internet connection for a while and were collecting data locally), calculated values may have to be recalculated. That means that the already stored data must be invalidated (which may mean deleted) and reinserted.

Finally, if your company provides different tiers of data retention, good luck implementing this on a single table. It is the previous two issues, but on a constant, more than ugly, basis.

Multiple Table Designs

Now that we know about the pros and cons of single table design, what are the differences when we aim for multiple table designs instead?

While querying is still simple, querying multiple sets of data simultaneously may be slightly more complicated, involving JOINs and UNIONs to merge data from the different tables. Requesting multiple sets of data at the same time is often done for efficiency reasons, requiring fewer database round trips and minimizing response time. Apart from that, there isn’t a massive difference in ease of use, except for table names, but we’ll come back to that in a second.

One of the major benefits of having multiple tables, especially when sliced by the customer, user, or whatever meaningful multi-tenancy divider for your use case, is the option to quickly react to GDPR- or CCPA-related requests to destroy and remove any customer-related data. In this case, it is as easy as finding all the client’s tables and dropping them. Removing them from backups is a different story, though. 😌

The same is true with calculated and collected data. Separating those tables makes it much easier to throw away and recalculate all or parts of the data when late information arrives.

Also similar is the previously mentioned data retention. Many companies storing huge amounts of data on behalf of their customers provide different data retention policies based on how much the customer is willing to pay. Having tables sliced by customers makes it easy to set customer-specific retention policies and even change them when a customer upgrades to a higher tier. If this is something you need, the multi-table design is it.

However, just as with single tables, multiples have drawbacks too.

Besides the already slightly more complicated elements around querying, which are not necessarily a disadvantage, having many tables requires planning a table name schema. The more dimensions we bring into the game (by customer, metric type, etc.), the more complicated our naming schema needs to be. That said, we may end up with table names such as <<customer_name>>__<<metric_type>>. While this doesn’t sound too bad, it can get ugly fast. We’ve all been there before. 😅

tsdb=> \dt *.cust_*
                    List of relations
 Schema |            Name            | Type  |   Owner
--------+----------------------------+-------+-----------
 public | cust_mycompany_co2         | table | tsdbadmin
 public | cust_mycompany_humidity    | table | tsdbadmin
 public | cust_mycompany_temperature | table | tsdbadmin
 public | cust_timescale_co2         | table | tsdbadmin
 public | cust_timescale_humidity    | table | tsdbadmin
 public | cust_timescale_temperature | table | tsdbadmin
(6 rows)

Tools, such as an ORM framework, may make things even more complicated. Many of those tools are not designed to support arbitrary, runtime-generated table names, making it very complicated to integrate those with this concept. Using different PostgreSQL database schemas per customer and lowering the number of dimensions may help.

There is one more complexity: upgrading and migrating tables. Due to the multiple table design, we may end up with many similar tables segregated by the additional dimensions chosen. When upgrading the table schema, we need to ensure that all those tables eventually end up in a consistent state.

However, many automatic schema migration tools do not easily support that kind of multi-table migration. This forces us to fall back on writing migration scripts, trying to find all tables matching a specific naming schema, and ensuring that all are upgraded the same way. If we miss a table, we’ll figure it out eventually, but probably when it’s too late.

The TL;DR

Now that you’ve laid it all out and answered these questions, you can look at the requirements and see where your use case fits.

Some hard requirements may make your choice obvious, while some “nice-to-have” elements may still influence the final decision and hint at what may become a harder requirement in the near or far future.

While the single table design is very easy to get started with, it may be a hard sell if you need to comply with regulations. However, the multiple table design is certainly more complicated to manage and use correctly.

So What's the Best One for Me?

There’s never a one-size-fits-all answer, or as consultants love to put it: it depends.

Unlike the design choices around the table layout, it’s too complex to make a real recommendation. You can only try to follow the suggested process of answering the questions above and looking at the answers to see which points represent hard requirements, which could become hard requirements, and which are simply nice to have. That way, you’ll likely find the answer to match your use case.

Also, remember that you may have different use cases with diverging requirements, so one use case may end up being perfectly fine running as a single table design, while the other one(s) may need multiple tables.

Plus, you have the chance to mix and match the benefits of both solutions. It was kind of hinted at in the text already, but it is possible to use a simplified multiple table design (for example, per metric type) and separate the customer dimension into a PostgreSQL database schema, with each schema representing one customer.

Similarly, it is possible to use the schema to separate customers and store all metrics/events/data for that specific customer in a single hypertable. There are plenty of options, only limited by your imagination.

Whatever you end up with, try to be as future-proof as possible. Try to imagine what the future will hold, and if you’re unsure whether a somewhat harder requirement will become a must-have, it may be worth considering it as a hard requirement now just to be on the safe side.

If you want to start designing your hypertable database schema as soon as possible, ensuring you get the best performance and user experience for your time-series data while achieving incredible compression ratios, check out Timescale.

If you’re looking to test it locally or run it on-prem, then we’ve got you covered: have a look at our documentation.

Learn more

• Best Practices for (Time-)Series Metadata Tables
• Choosing Your Database Schema: Wide vs. Narrow PostgreSQL Tables