Introduction

This article provides helpful tips for writing queries in PostgreSQL. Given that all customers share the same database instance in FreshTrack Cloud, it's crucial to write efficient code. Inefficient code can cause a performance impact on all customers by using shared resources.

Writing PL/pgSQL is one of the essential skills required for a FreshTrack Cloud HelpDesk Technician at every level. This article outlines specific guidelines and tips specifically for FreshTrack Cloud Database.

These guidelines are mostly important when writing queries for the reporting software (Bold Report) due to the way parameters are handled. Bold reports pass UUID parameters as strings, and date parameters as date without timezone info in the BROWSER local timezone.

Guidelines Highlights

Never add indexes. If you feel like you need a new index, ask in the Team Support Chat first.
Avoid any type conversions on table columns in the where clause (for example at time zone fts_sys_time_zone() or ::date or ::uuid). ALWAYS convert the parameters.
Avoid LIKE operator as much as you can.
When writing queries, ALWAYS keep in mind of the indexes that your query will use
If you need to search case insensitive, better to add an index on UPPER(search_column).
When writing a report, always return ONLY the columns used in the report. In cloud environments, one key metric to determine the billing is the amount of "data transferred" between the database and your reporting app. Everything adds up.

Date Conversion

To convert a date to the customer's main timezone we can use the following conversion:

select <your_date_column_name> at time zone fts_sys_time_zone() as sampled_on

Dates Filtering/Ordering

Postgres stores date in UTC. PostgreSQL assumes your local time zone for any type containing only date or time.
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client. [Ref.]

Since even a single customer might operate in different geographic areas, in which case we need to do conversion anyway, all databases' default settings are GMT+0 timezone.

Every Cloud Instance instance has a global setting which is TIMEZONE that Identifies the "main" timezone used for the customer assor the specific customers. This means that every time we retrieve/compare a date in the database we need to first convert it to the MAIN customer timezone.

This conversion (<at time zone fts_sys_time_zone()) leads to terrible performances if used in the wrong spot.

Let's take this example on the form_sample table, which contains a column sampled_on we need to filter on. Assuming you are writing this query for your report, your reporting platform would pass the parameter without the timezone information

select sampled_on at time zone fts_sys_time_zone() as sampled_on_local_time from form_sample fs where sampled_on at time zone fts_sys_time_zone() = @FromDate

A much better way to approach the problem is CONVERTING your report parameter into the same timezone you have on the database which is always UTC. For example, if you select 13/07/1983 on your report interface the report engine passes only the date 13/07/1983 without a timezone. If you are not in a UTC timezone (take Perth as an example), and you use this date parameter to query the database directly, you would be gathering wrong information because the midnight of 13/07/1983 in UTC is equivalent to 8 AM Perth time. So what you need to do is to convert the date parameter 13/07/1983 into a Date with timezone IN UTC that mimic the midnight in Perth.

Your correct query is:

and fs.sampled_on > (select @from::timestamp - (select utc_offset from pg_timezone_names where name = fts_sys_time_zone()) ) -- date_trunc('day', @from::timestamp, fts_sys_time_zone())

and fs.sampled_on < (select @to::timestamp - (select utc_offset from pg_timezone_names where name = fts_sys_time_zone()) )

UUID Filtering

When filtering UUIDs, avoid converting them before comparison. This seems something that shouldn't need to be specified right? But it is, and it becomes important after understanding how the reporting platform (Bold Rerport) works.

UUID parameters are passed as strings. In the case of multi-select parameters, are passed as an array of strings.

As an example, let's take a report that displays selected employee's names. The first time, living in your utopistic world where things just work you would write something like this:

select name from employee where id = @EmployeeId

Then you shortly realize that the programmer's life sucks. The SQL engine throws an error about data incompatibility TEXT vs UUID.

So you might think that this next following statement is just what you need, it works, it does what you need:

select name from employee where id::text = @EmployeeId

Yeah but.....

This query above converts the ID into TEXT, then it compares it with your parameter. This conversion leads to exponentially bad performances proportionally to the number of records you have in the database.

The solution?

Instead of converting the column, convert the parameter to the corresponding type of the column you want to filter on.

select name from employee where id = @EmployeeId::uuid

In this case, we have only one conversion, the parameter and we make GREAT use of the index on the column id. Even in the absence of an index on your search column, this will perform incredibly better.

Multiple Parameters - Select where id in (list of id)

In the case your parameters contains a list of employee strings, the above statement doesn't work. You need a IN statement.

select name from employee where id::text IN (@EmployeeId)

this works! Again... but terrible performances.

employee_idIN (@EmployeeId)