Run your queries from Timescale Console

As Timescale Cloud is based on PostgreSQL, you can use lots of different tools to connect to your service and interact with your data.

In Timescale Console you can use the following ways to run SQL queries against your database:

• Data mode: a rich experience powered by PopSQL. You can write queries with autocomplete, save them in folders, share them, create charts/dashboards, and much more.

• SQL Assistant in Data mode: write, fix, and organize SQL faster and more accurately.

• SQL editor in Ops mode: a simple SQL editor in Ops mode that lets you run ad-hoc ephemeral queries. This is useful for quick one-off tasks like creating an index on a small table or inspecting pg_stat_statements.

If you prefer the command line to the ops mode SQL editor in Timescale Console, use psql.

You use the data mode in Timescale Console to write queries, visualize data, and share your results.

Expand image

Available features are:

• Real-time collaboration: work with your team directly in the data mode query editor with live presence and multiple cursors.
• Schema browser: understand the structure of your database and see usage data on tables and columns.
• SQL Assistant: write, fix, and organize SQL faster and more accurately using AI.
• Autocomplete: get suggestions as you type your queries.
• Version history: access previous versions of a query from the built-in revision history, or connect to a git repo.
• Charts: visualize data from inside the UI rather than switch to Sheets or Excel.
• Schedules: automatically refresh queries and dashboards to create push alerts.
• Query variables: use Liquid to parameterize your queries or use if statements.
• Cross platform: works from Timescale Console or download the desktop app for macOS, Windows, and Linux.
• Easy connection: to Timescale Cloud, PostgreSQL, Redshift, Snowflake, BigQuery, MySQL, SQL Server, and more.

Now you have used the data mode in Timescale Console, see how to easily do the following:

• Write a query
• Share a query with your teammates
• Create a chart from your data
• Create a dashboard of multiple query results
• Create schedules for your queries

If your Timescale Service runs inside a VPC, do one of the following to enable access for the PopSQL desktop app:

• Use PopSQL's bridge connector
• Use an SSH tunnel
  • When you configure the connection in PopSQL, under Advanced Options, enable Connect over SSH
• Add PopSQL's static IPs (23.20.131.72, 54.211.234.135) to your allowlist

The number of data mode seats you are allocated depends on your Pricing Plan.

There are a few factors to consider:

1. What instance size is your database?
2. How many users are running queries?
3. How computationally intensive are the queries?

If you have a small number of users running performant SQL queries against a service with sufficient resources, then there should be no degradation to performance. However, if you have a large number of users running queries, or if the queries are computationally expensive, best practice is to create a read replica and send analytical queries there.

If you'd like to prevent write operations such as insert or update. Instead of using the tsdbadmin superuser, create a read-only user for your service and use that in the data mode.

SQL Assistant in Timescale Console helps you write, fix, and organize SQL faster and more accurately.

SQL Assistant offers a range of features to improve your SQL workflow, including:

• Real-Time Help: SQL Assistant provides in-context help for writing and understanding SQL. Use it to:

  • Understand functions: need to know how functions like LAG() or ROW_NUMBER() work? SQL Assistant explains it with examples.
  • Interpret complex queries: SQL Assistant breaks down dense queries, giving you a clear view of each part.

• Error resolution: SQL Assistant diagnoses errors as they happen, you can resolve issues without leaving your editor. Features include:

  • Error debugging: if your query fails, SQL Assistant identifies the issue and suggests a fix.
  • Performance tuning: for slow queries, SQL Assistant provides optimization suggestions to improve performance immediately.

• Query organization: to keep your query library organized, and help your team understand the purpose of each query, SQL Assistant automatically adds titles and summaries to your queries.

For best results with SQL Assistant:

• Schema Awareness: SQL Assistant references schema data but may need extra context in complex environments. Specify tables, columns, or joins as needed.
• Business Logic: SQL Assistant does not inherently know specific business terms such as active user. Define these terms clearly to improve results.

Security and privacy is prioritized in Timescale Console. In Data Mode, project members manage SQL Assistant settings under User name > Settings > SQL Assistant.

Expand image

SQL Assistant settings are:

• Opt-In Features: all AI features are off by default. Only members of your Timescale Cloud project can enable them.
• Data Protection: your queries and data remain private and are not used for AI training. SQL Assistant operates with strict security protocols.
• Data Usage: to provide AI support, Timescale may share the query title, description and body, also the database connection type such as PostgreSQL, and the schema.
• Sample data: to give the LLM more context so you have better SQL suggestions, enable sample data sharing in the SQL assistant preferences.
• Telemetry: to improve SQL Assistant, Timescale collects telemetry and usage data, including prompts, responses, and query metadata.

SQL editor is an integrated secure UI that you use to run queries and see the results for an Timescale Cloud service.

Expand image

To enable or disable SQL editor in your service, click Operations > Service management, then update the setting for SQL editor.

• SQL editor in the ops mode: free for anyone with a Timescale Cloud account.
• Data mode: the number of seats you are allocated depends on your Pricing Plan. SQL Assistant is currently free for all users. In the future, limits or paid options may be introduced as we work to build the best experience.
• PopSQL standalone: there is a free plan available to everyone, as well as paid plans. See PopSQL Pricing for full details.