Postgres Table

This class stores result log data in a Postgres table.

It is much better choice for production usage compared to Snowflake table, since OLTP databases are better suited for small transactions and indexed lookups.

Internally this storage relies on brand new psycopg3 package.

Setup

  1. Install SnowKill with additional dependency for Postgres:

pip install snowkill[postgres]

  1. Setup and run a Postgres instance.

  2. Create a Postgres table with the following structure:

CREATE TABLE snowkill_log
(
    query_id TEXT,
    check_result_level INTEGER,
    check_result_name TEXT,
    check_result_description TEXT,
    check_result_time TIMESTAMP WITHOUT TIME ZONE,

    PRIMARY KEY (query_id, check_result_level)
)

Please note the full name of created table. It should be passed to PostgresTableStorage constructor.

Example

from psycopg import connect as postgres_connect
from snowkill import *
from snowkill.storage.postgres_table import PostgresTableStorage


postgres_connection = postgres_connect(getenv("POSTGRES_DSN"))

...

snowkill_storage = PostgresTableStorage(postgres_connection, getenv("POSTGRES_TARGET_TABLE"))
check_results = snowkill_storage.store_and_remove_duplicate(check_results)

Usage notes

  • It is important to import PostgresTableStorage separately, using from snowkill.storage.postgres_table import PostgresTableStorage. This storage class is not automatically exported with snowkill module, since it would trigger import error if psycopg is not installed.

Last updated