Three Ways to Retrieve Row Counts in Redshift Tables and Views

As your data grows in your Amazon Redshift cluster, it’s important to have an accurate count of the number of rows in your tables or views. You might need this information for capacity planning, performance tuning, or simply to satisfy your curiosity. Fortunately, Redshift provides several methods for retrieving this information.

Method 1: Using the COUNT Function

To count the number of rows in a table or view in Redshift, you can use the built-in COUNT function. Here’s an example SQL snippet that you can use:

SELECT COUNT(*) FROM table_name;

Replace “table_name” with the name of the table or view that you want to count. This query will return a single row containing the total number of rows in the table or view.

If you want to track the number of rows over time, you can run this query periodically and store the results in a separate table. Here’s an example SQL snippet that creates a table to store the row counts for a table called “orders”:

CREATE TABLE row_counts (
	timestamp TIMESTAMP,
	row_count BIGINT
);

INSERT INTO row_counts (timestamp, row_count)
SELECT
	SYSDATE,
	COUNT(*)
FROM
	orders;

This code creates a table called “row_counts” with two columns: “timestamp” and “row_count”. The “timestamp” column stores the current date and time, while the “row_count” column stores the current row count for the “orders” table. The INSERT INTO statement runs the COUNT query and inserts the result into the “row_counts” table.

You can then run this code periodically (e.g., daily, hourly) to track changes in the row count over time. Here’s an example SQL snippet that you can use:

INSERT INTO row_counts (timestamp, row_count)
SELECT
	SYSDATE,
	COUNT(*)
FROM
	orders;

This query inserts a new row into the “row_counts” table with the current date and time and the current row count for the “orders” table.

Method 2: Using System Statistics

Redshift automatically collects statistics on your tables and views, including row counts, and makes them available in the STL_QUERY and SVV_TABLE_INFO system tables. Here’s an example SQL snippet that you can use to retrieve the row count for a table or view from the SVV_TABLE_INFO table:

SELECT "rows" FROM SVV_TABLE_INFO WHERE "table"='table_name';

Replace “table_name” with the name of the table or view that you want to count. This query will return a single row containing the total number of rows in the table or view.

One advantage of using system statistics is that they are updated automatically and don’t require you to run any additional queries or scripts to track the row count. However, keep in mind that system statistics may not always be up-to-date or accurate, especially if you have recently loaded data or made other changes to your table.

Method 3: Using Multiple Methods

To ensure the accuracy of your row counts, it’s a good practice to use multiple methods to track the number of rows in your tables or views. For example, you might use the COUNT function to get an exact count of the rows in your table, and also use system statistics to get a more approximate count.

Here’s an example SQL snippet that combines the two methods:

SELECT
	COUNT(*) AS count_exact,
	"rows" AS count_estimate
FROM
	table_name
	JOIN SVV_TABLE_INFO ON "table_name" = "table"
WHERE
	"table" = 'table_name';