PostgreSQL is a popular database often integrating with Kafka. Lenses integrates with PostgreSQL to create a seamless data journey by providing a 360 view to the streaming flow. You can preview or query data present on PostgreSQL tables and assign metadata to surface relevant information faster, all while keeping your data secure and compliant.
Integration summary
transactions*
Data sources integrating with the catalog, are governed by granular permissions subject to namespaces. Like this you can shape the data catalog based on your teams or access constraints.
Access Management & permissions
In the Data Catalog, a user is able to search for terms [1] based on Metadata(Tags, Description and Field Names) [2]. He can see the Name of the Dataset, along with their mathcing fields, their Type and Description if it exists, and if they are protected by a policy [3].
Name
Type
Description
Once we identify, our Dataset of choice, we can drill deeper, and navigate to the Details View. There we can see Data, Schema and Metadata information. We can specify, the number of items, we want to include in our query, view the results in Tree or Table View and see the Schema for each individual field.
Supported Syntax
For PostgreSQL we support a limited subset of the SQL Queries geared towards the use case of secure data exploration. Currently this only includes simple SELECT queries with WHERE clauses and basic sorting.
SELECT
WHERE
If you have any question, please refer to the following list. If you still have more, we are more than welcome to answer any question at our community channel.
How can I view the status of my connection?
Currently, Lenses UI provides no visual indication of a connection health status. However, troubleshooting connection problems should be fairly straightforward and can be done via by inspecting the Lenses logs and searching for ERROR entries related to your connection. More on Logs
Why Lenses is displaying inconsistent record counts?
In order to minimise load on the database, Lenses relies on the Postgres’ Statistics Collector to extract record counts. The freshness of such counts is affected by the volume of the data held in the db and by how routine vacuuming is configured. While Postgres provides various settings to control vacuuming globally, it is also possible to tweak this mechanism on a single table basis. For instance, one can aggressively reduce the autovacuum threshold down to zero by setting the following:
ALTER TABLE public.employee SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 0);
Finally, one can vacuum a table on a one off basis, or on a schedule (by using cron or an equivalent tool) by directly issuing a vacuum command. Please keep in mind that VACUUM creates a substantial amount of I/O traffic, which can cause poor performance for active query sessions.
What versions of PostgreSQL does Lenses support?
We offically support PostgreSQL version ranging from 9.6 to 12.5.
How many PostgreSQL connections can Lenses handle?
A Lenses on a Kubernetes cluster with 4GB of RAM can handle up to 10 PostgresSQL DBs with approximetaly 10K Table and/or Views with good responsiveness.
What PostgreSQL features are not supported currently?
We are not supporting yet, but we plan to in the upcoming release to support JOIN, Aggregations (i.e: GROUP BY, HAVING) and calling Native PostgreSQL functions.
JOIN
Aggregations (i.e: GROUP BY, HAVING)
Native
Does Lenses SQL support write operations for PostgreSQL Tables?
Support for write operations (e.g. inserting/updating records, creating/altering tables, etc) is not planned for the immediate future.
On this page
