Overheard @ Office Hours

A selection of FAQs from Timescale Office Hours. This week we discussed inserting data with Python, building indexes, and more.

At Timescale, we run open office hours once or twice a month to give users an opportunity to ask any questions they might have about TimescaleDB. We’ve found these office hours to be a great informal way to 1) discuss use cases and 2) serve as a way for our community to share knowledge.

[Interested in signing up for office hours? Follow this link for more information.]

Here are some tidbits from this past week:

If you’re inserting data with Python, there are a couple things to consider to improve performance.

1. There is significant overhead attached to inserting a single row at a time - use multi-value inserts whenever possible!
2. COPY is an option, but keep in mind that it is single threaded
3. Marshalling Python objects is expensive, and could be the reason that insert performance isn’t as fast as it could be. Consider increasing your application resources if you think this might be the bottleneck.

If you don’t have a good reason to use timestamp without time zone, don’t use it.

The timestamptz type is recommended. Certain functions require the data to be timestamptz, so you might be casting and re-casting data if you choose to use timestamp without time zone.

What’s a good way to figure out which indexes to build?

Take a look at the queries you run and what WHERE clauses you are using. Those columns can usually be combined into good composite indexes. Remember, indexes are more useful if the column on which it is built has more distinct values.

(For more information on using indexes, check out this blog post: How to use composite indexes to speed up time-series queries.)

Should I be using unique constraints?

In TimescaleDB, due to the automated partitioning, constraints are not checked globally.

I added an index. Why isn’t the query planner using it?

Timescale needs to analyze the makeup of the data stored in your tables, so run ANALYZE or VACUUM ANALYZE after adding an index. Keep in mind that the statistics that PostgreSQL builds are not deterministic, so there are cases where PostgreSQL may choose not to use an index given how your data is stored on disk.

Stay tuned for more posts like this one. We hope to see you at an upcoming office hours!