PostgreSQL Network Filter for EnvoyProxy

How do you monitor Postgres? What information can you get out of it, and to what degree does this information help to troubleshoot operational issues? What if you want/need to log all the queries? That may bring heavy trafficked databases down.

At OnGres we’re obsessed with improving PostgreSQL’s observability. So we worked together with Tetrate folks on an Envoy’s Network Filter extension for PostgreSQL, to provide and extend observability of the traffic inout a cluster infrastructure. This extension is public and open source. You can use it anywhere you use Envoy. It allows you to capture automated metrics and to debug network traffic. This talk will be a technical deep-dive into PostgreSQL’s protocol decoding, Envoy proxy filters and will cover all the capabilities of the tool and its usage and deployment in any environment.

Envoy [1] is a high performance C++ distributed proxy designed for single services and applications, as well as a communication bus and “universal data plane” designed for large microservice “service mesh” architectures. Built on the learnings of solutions such as NGINX, HAProxy, hardware load balancers, and cloud load balancers, Envoy runs alongside every application and abstracts the network by providing common features in a platform-agnostic manner. When all service traffic in an infrastructure flows via an Envoy mesh, it becomes easy to visualize problem areas via consistent observability, tune overall performance, and add substrate features in a single place.

Envoy can be used to proxy connections to PostgreSQL instances and in this talk we’ll see how we improve PostgreSQL observability without impacting the performance of the database and without needing to install and/or configure a bunch of things like logs, pgstatstatements, etc, using a Network Filter [2] for PostgreSQL we developed that decodes frontend and backend protocol to get transparently some metrics and metadata about it operation.

Roadmap: * [WIP] SSL termination and monitoring [3] [4] * Integrate Postgres parser to improve dynamic metadata and per-query tracking * Individual (per-query) tracking of query performance * Traffic mirroring for Postgres major upgrade testing and validations

[1] https://www.envoyproxy.io/ [2] https://www.envoyproxy.io/docs/envoy/latest/intro/archoverview/otherprotocols/postgres#arch-overview-postgres [3] https://github.com/envoyproxy/envoy/issues/10942 [4] https://github.com/envoyproxy/envoy/issues/9577

Talk video: https://mirror.as35701.net/video.fosdem.org/2021/D.monitoring/postgresql_filter_envoy.webm

Transcript

1. POSTGRES NETWORK FILTER FOR ENVOY PROXY POSTGRESQL NETWORK FILTER FOR ENVOY PROXY FABRÍZIO DE ROYES MELLO ÁLVARO HERNÁNDEZ
2. POSTGRES NETWORK FILTER FOR ENVOY PROXY ` whoami ` • 25+ years on IT • PostgreSQL Developer at OnGres • PostgreSQL Contributor • Brazilian Community Leader Fabrízio de Royes Mello <fabrizio@ongres.com> @fabriziomello
3. POSTGRES NETWORK FILTER FOR ENVOY PROXY ` whoami ` • Founder & CEO, OnGres • 20+ years Postgres user and DBA • Mostly doing R&D to create new, innovative software on Postgres • Frequent speaker at Postgres, database conferences • Founder and President of the NPO Fundación PostgreSQL • AWS Data Hero Álvaro Hernández <aht@ongres.com> aht.es
4. POSTGRES NETWORK FILTER FOR ENVOY PROXY ENHANCING POSTGRES OBSERVABILITY
5. POSTGRES NETWORK FILTER FOR ENVOY PROXY Postgres monitoring • Not in-core integrated solution. • Postgres provides catalog views with rich monitoring information. • But that means making queries to gather monitoring data. • Eg. Prometheus exporter: dozens/hundreds/... of queries per monitoring cycle. • Postgres monitoring extensions: ◦ may require restart -> downtime. ◦ may require configuration / external binaries-> complexity Can we do better?
6. POSTGRES NETWORK FILTER FOR ENVOY PROXY Postgres wire protocol (FeBe) • Custom (layer 7) TCP protocol. Well documented. • Well structured and defined messages (no “generic message” for many things). • Very stable (current v3 was introduced in 2003 with PG 7.4). • Implemented by countless tools and drivers. • Used also for non-Postgres databases (Yugabyte, CockroachDB, Crate.io, NoisePage, ...).
7. POSTGRES NETWORK FILTER FOR ENVOY PROXY FeBe protocol architecture
8. POSTGRES NETWORK FILTER FOR ENVOY PROXY Idea: proxy and decode the protocol to get metrics!
9. POSTGRES NETWORK FILTER FOR ENVOY PROXY Advantages of metrics via proxying/decoding • s/pull/push/ • Zero impact on the database. 100% transparent. ◦ No performance impact. ◦ No configuration required. No agents/tools to install. • Can be deployed as a sidecar (eg. via injection in K8s). • May significantly increase the volume of metrics obtained. • Opens the door for other added functionality.
10. POSTGRES NETWORK FILTER FOR ENVOY PROXY ENVOY PROXY POSTGRES FILTER
11. POSTGRES NETWORK FILTER FOR ENVOY PROXY Envoy extensibility: simple connection model Application (client) tcp_proxy
12. POSTGRES NETWORK FILTER FOR ENVOY PROXY Envoy extensibility: filter chain Application (client) tcp_proxy PostgreSQL
13. POSTGRES NETWORK FILTER FOR ENVOY PROXY PostgreSQL Filter Architecture: metadata Application (client) tcp_proxy PostgreSQL RBAC Metadata
14. POSTGRES NETWORK FILTER FOR ENVOY PROXY How it all started https://github.com/envoyproxy/envoy/issues/9107
15. POSTGRES NETWORK FILTER FOR ENVOY PROXY An effort developed by a great Community • Contributions from Tetrate, Envoy Maintainers, OnGres and others. • Merged and released with Envoy 1.15. • Led to 10 issues and new functionality being implemented in several other areas. • New features to come in new releases! Help wanted!
16. POSTGRES NETWORK FILTER FOR ENVOY PROXY Postgres filter Timeline November 2019 Issue #9107 created January 2020 First filter POC July 2020 Release 1.15.0 first version of postgres filter October 2020 Release 1.16.0 postgres filter metadata January 2021 Release 1.17.0 start_tls transport socket March 2021 Future Release 1.18.0 postgres SSL termination
17. POSTGRES NETWORK FILTER FOR ENVOY PROXY Metrics currently being exported errors (error, fatal, panic, …) messages (frontend, backend) sessions (encrypted, unencrypted) statements (insert, update, delete, …) transactions (commit, rollback) notices (notice, log, warning, …) Counters (metric / second)
18. POSTGRES NETWORK FILTER FOR ENVOY PROXY DEMO
19. POSTGRES NETWORK FILTER FOR ENVOY PROXY Expected result https://github.com/ongres/envoy-postgres-stats-example
20. POSTGRES NETWORK FILTER FOR ENVOY PROXY COMING TO ENVOY 1.18*: POSTGRES SSL OFFLOADING AND MONITORING
21. POSTGRES NETWORK FILTER FOR ENVOY PROXY Postgres SSL • Doesn’t operate at the TCP level 4, but rather application level (7). • Initial connection is unencrypted, then a request to “upgrade the connection to SSL” is performed. Similar to STARTTLS in SMTPS. • Database connection costs are high. SSL database connection costs are very high. • Use a connection pooler! Like PgBouncer! PgBouncer -> single threaded -> swamped under heavy SSL connection load. • Turning on/off SSL or rotating certificates requires database restart -> downtime.
22. POSTGRES NETWORK FILTER FOR ENVOY PROXY Offload Postgres SSL to Envoy! • Avoids both Postgres and PgBouncer SSL performance problems. • Allows monitoring of encrypted traffic! • Turn on/off, rotate certificates without database impact. • Programmatic management: use Envoy xDS APIs to manage certificates. StartTls infrastructure already released on 1.17. Postgres specific filter implementation coming on 1.18*.
23. POSTGRES NETWORK FILTER FOR ENVOY PROXY Architecture
24. POSTGRES NETWORK FILTER FOR ENVOY PROXY SSL DEMO
25. POSTGRES NETWORK FILTER FOR ENVOY PROXY USE CASES THE FUTURE OF ENVOY’S POSTGRES PLUGIN
26. POSTGRES NETWORK FILTER FOR ENVOY PROXY Envoy’s filter use-case: StackGres.io StackGres Architecture
27. POSTGRES NETWORK FILTER FOR ENVOY PROXY Future plans • Better SQL parsing • Producing per-database statistics • Routing based on Query type • Traffic drain • Opent Telemetry Integration Community: • Envoy slack: envoyproxy.slack.com • PostgreSQL specific channel: #envoy-postgres • PostgreSQL related issues: https://github.com/envoyproxy/envoy/labels/area%2Fpostgres
28. POSTGRES NETWORK FILTER FOR ENVOY PROXY References Original Github Issue: https://github.com/envoyproxy/envoy/issues/9107 First post about Envoy Postgres Filter: https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new- postgres-extension-with-monitoring-support/ Envoy Documentation: https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/o ther_protocols/postgres https://www.envoyproxy.io/docs/envoy/latest/configuration/listener s/network_filters/postgres_proxy_filter
29. POSTGRES NETWORK FILTER FOR ENVOY PROXY Questions?