Chainguard Container for timescaledb-compat

A time-series database for high-performance real-time analytics packaged as a Postgres extension

Chainguard Containers are regularly-updated, secure-by-default container images.

Download this Container Image

For those with access, this container image is available on cgr.dev:

docker pull cgr.dev/ORGANIZATION/timescaledb-compat:latest

Be sure to replace the ORGANIZATION placeholder with the name used for your organization's private repository within the Chainguard Registry.

Compatibility Notes

Chainguard's TimescaleDB Compat image is comparably to the official open source TimescaleDB image from Docker Hub. However, this image only contains Apache 2 from TimescaleDB; no proprietary code is included, so some features may not be available. Please see this feature comparison for more information.

Getting started

You can test this image locally with docker:

docker run --rm -it -p 5432:5432 -e POSTGRES_PASSWORD=password cgr.dev/ORGANIZATION/timescaledb-compat:latest

It should return output similar to this:

The files belonging to this database system will be owned by user "nonroot".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
# ...

Note that the only mandatory environment variable needed by the TimescaleDB image is POSTGRES_PASSWORD.

This command will run the image, but no data within the underlying PostgreSQL database will persist after the image stops running. For a persistent database you can mount a volume mapped to the container's data folder:

docker run --rm -v timescaledb_data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=password -it --name postgres-test cgr.dev/ORGANIZATION/timescaledb-compat:latest

From there you can start making queries with tools that interop with PostgreSQL, for example, with the psql CLI tool.

Example: Using PSQL CLI to deploy a basic database using TimescaleDB's extensions

Create a file named hello_world.sql with the following contents in your current working directory

-- Create table 'rides' which will store trip data
DROP TABLE IF EXISTS "rides";
CREATE TABLE "rides"(
    vendor_id TEXT,
    pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    passenger_count NUMERIC,
    trip_distance NUMERIC,
    pickup_longitude  NUMERIC,
    pickup_latitude   NUMERIC,
    rate_code         INTEGER,
    dropoff_longitude NUMERIC,
    dropoff_latitude  NUMERIC,
    payment_type INTEGER,
    fare_amount NUMERIC,
    extra NUMERIC,
    mta_tax NUMERIC,
    tip_amount NUMERIC,
    tolls_amount NUMERIC,
    improvement_surcharge NUMERIC,
    total_amount NUMERIC
);

-- Create hypertable for rides 
-- This allows us to take advantage of timescaledb's space and time partitioning
SELECT create_hypertable('rides', 'pickup_datetime', 'payment_type', 2, create_default_indexes=>FALSE);
-- Create indexes (special look up tables/ pointers) on the following columns to speed up data retrieval
CREATE INDEX ON rides (vendor_id, pickup_datetime desc);
CREATE INDEX ON rides (pickup_datetime desc, vendor_id);
CREATE INDEX ON rides (rate_code, pickup_datetime DESC);
CREATE INDEX ON rides (passenger_count, pickup_datetime desc);

-- Create table 'payment_types' to store description of payment types for easy lookup
CREATE TABLE IF NOT EXISTS "payment_types"(
    payment_type INTEGER,
    description TEXT
);
INSERT INTO payment_types(payment_type, description) VALUES
(1, 'credit card'),
(2, 'cash'),
(3, 'no charge'),
(4, 'dispute'),
(5, 'unknown'),
(6, 'voided trip');

-- Create table 'rates' to store description of rate codes for for easy lookup
CREATE TABLE IF NOT EXISTS "rates"(
    rate_code   INTEGER,
    description TEXT
);
INSERT INTO rates(rate_code, description) VALUES
(1, 'standard rate'),
(2, 'JFK'),
(3, 'Newark'),
(4, 'Nassau or Westchester'),
(5, 'negotiated fare'),
(6, 'group ride');

Then run the following to apply the database schema:

psql -h "${PGHOST:-127.0.0.1}" -U postgres -f hello_world.sql

Make sure to specify the password either via stdin or by manually typing it

You should see output similar to the following:

psql:hello_world.sql:2: NOTICE:  table "rides" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:hello_world.sql:26: WARNING:  column type "timestamp without time zone" used for "pickup_datetime" does not follow best practices
HINT:  Use datatype TIMESTAMPTZ instead.
psql:hello_world.sql:26: WARNING:  column type "timestamp without time zone" used for "dropoff_datetime" does not follow best practices
HINT:  Use datatype TIMESTAMPTZ instead.
 create_hypertable
--------------------
 (1,public,rides,t)
(1 row)

CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
INSERT 0 6
CREATE TABLE
INSERT 0 6

Documentation References

• TimescaleDB Documentation
• PostgreSQL Documentation
• How to deploy TimescaleDB on Kubernetes
• TimescaleDB Apache 2 Edition Compatibility Notice
• TimescaleDB Editions Feature Comparison

What are Chainguard Containers?

Chainguard's free tier of Starter container images are built with Wolfi, our minimal Linux undistro.

All other Chainguard Containers are built with Chainguard OS, Chainguard's minimal Linux operating system designed to produce container images that meet the requirements of a more secure software supply chain.

The main features of Chainguard Containers include:

• Minimal design, without unnecessary software bloat
• Daily builds to ensure container images are up-to-date with available security patches
• High quality build-time SBOMs attesting to the provenance of all artifacts within the image
• Verifiable signatures provided by Sigstore
• Reproducible builds with Cosign and apko (read more about reproducibility)

For cases where you need container images with shells and package managers to build or debug, most Chainguard Containers come paired with a development, or -dev, variant.

In all other cases, including Chainguard Containers tagged as :latest or with a specific version number, the container images include only an open-source application and its runtime dependencies. These minimal container images typically do not contain a shell or package manager.

Although the -dev container image variants have similar security features as their more minimal versions, they include additional software that is typically not necessary in production environments. We recommend using multi-stage builds to copy artifacts from the -dev variant into a more minimal production image.

Need additional packages?

To improve security, Chainguard Containers include only essential dependencies. Need more packages? Chainguard customers can use Custom Assembly to add packages, either through the Console, chainctl, or API.

To use Custom Assembly in the Chainguard Console: navigate to the image you'd like to customize in your Organization's list of images, and click on the Customize image button at the top of the page.

Learn More

Refer to our Chainguard Containers documentation on Chainguard Academy. Chainguard also offers VMs and Libraries — contact us for access.

Trademarks

This software listing is packaged by Chainguard. The trademarks set forth in this offering are owned by their respective companies, and use of them does not imply any affiliation, sponsorship, or endorsement by such companies.