PL/Python

PL/Python
Name	PL/Python
Paradigm	Procedural, imperative, functional
Developer	PostgreSQL Global Development Group
First appeared	1998
Typing	Dynamic
Influenced by	Python
License	PostgreSQL License

PL/Python PL/Python is a procedural language extension for the PostgreSQL relational database system that enables writing stored procedures and functions using the Python programming language. It is used alongside PostgreSQL core components and extensions to provide programmable logic inside databases managed by institutions such as the University of California, Berkeley, Massachusetts Institute of Technology, Google, Facebook, Amazon (company), and Red Hat. Developers integrate PL/Python with systems like Django, NumPy, Pandas, TensorFlow and connect to services from Oracle Corporation, Microsoft, IBM, SAP, and VMware.

Overview

PL/Python allows database users to implement business rules, data transformations, and computational tasks as server-side functions executed within the PostgreSQL process. Administrators and developers working with PostgreSQL, PostGIS, TimescaleDB, Citus (company), Greenplum, and CockroachDB-inspired architectures often place PL/Python functions alongside SQL, PL/pgSQL, PL/Perl, PL/Tcl, and extensions such as pgBouncer, pgAdmin, pglogical and bucardo for replication. Deployments in environments run by Netflix, Airbnb, Shopify, Stripe, and Uber use PL/Python or similar strategies for complex data processing and analytics.

History and Development

PL/Python originated as part of community efforts to extend PostgreSQL with high-level languages, drawing on earlier procedural language integrations from projects at University of California, Berkeley and contributions by developers associated with The PostgreSQL Global Development Group, Bruce Momjian, Tom Lane, Magnus Hagander, and contributors linked to Red Hat and EnterpriseDB. Over time, PL/Python evolved through collaboration across organizations such as Mozilla, Intel, HP, Canonical (company), SUSE, Amazon Web Services, Google Cloud Platform, and individual contributors who also worked on Python Software Foundation projects. Key milestones tie to PostgreSQL releases, contributions coordinated at conferences like PGCon, PostgresOpen, FOSDEM, PyCon, and SIGMOD.

Language Features and Integration

PL/Python embeds a CPython interpreter within the PostgreSQL backend allowing access to Python's standard library and third-party modules compiled for the server platform. Integrations frequently reference technologies and libraries such as NumPy, SciPy, Pandas, Matplotlib, scikit-learn, TensorFlow, PyTorch, asyncio, Celery, Redis, RabbitMQ, and Apache Kafka for event-driven workloads used by organizations like LinkedIn, Twitter, Dropbox, Spotify, and Pinterest. PL/Python supports argument and return types compatible with SQL, JSON, XML, PostGIS, and array types used in systems from Esri and Mapbox. The extension interoperates with authentication and authorization systems provided by Kerberos, LDAP, OAuth 2.0, and SAML when deployed in enterprise contexts such as NASA, US Department of Energy, European Space Agency, and National Institutes of Health research databases.

Usage and Examples

Common use cases include data validation routines employed by teams at Walmart, Target Corporation, Home Depot, IKEA, and Tesco, ETL pipelines used by SAP, Oracle Corporation customers, and machine learning feature computation integrated for companies like Microsoft Azure, Google Cloud Platform, and IBM Watson. Sample patterns surface in documentation and talks at PyCon, PGCon, OSCON, and Strata Data Conference. Developers use PL/Python functions to call libraries such as NumPy for numerical tasks, Pandas for frame operations, and scikit-learn for model scoring, while coordinating with orchestration tools like Kubernetes, Docker, Ansible, Terraform, and Jenkins in CI/CD pipelines used by GitHub and GitLab.

Security and Trust Levels

PL/Python includes trust levels distinguishing untrusted and trusted variants, a model mirrored in other procedural languages used in systems by Oracle Corporation and Microsoft. Security considerations reference threat actors studied by groups such as CERT Coordination Center, National Institute of Standards and Technology, ENISA, and operational practices from NSA and GCHQ guidance. Deployments enforce policies using SELinux, AppArmor, seccomp, and container isolation recommended by Cloud Native Computing Foundation projects, and auditing frameworks used by Splunk, Elastic (company), Snyk, and Tenable.

Performance and Limitations

Performance of PL/Python depends on interaction between the Python interpreter and PostgreSQL process, I/O patterns, and extension overheads; organizations measure impact using benchmarks from TPC, SPEC, pgbench, and telemetry systems developed by New Relic, Datadog, Prometheus, and Grafana. Limitations include single-process interpreter constraints, GIL-related behavior in CPython that echo concerns addressed by Guido van Rossum and contributors to the Python Software Foundation, and resource management issues handled by database teams at Facebook, Google, and Amazon. Alternative strategies include using foreign data wrappers like postgres_fdw, job queues with pg_cron, or offloading compute to analytics engines such as Apache Spark, Presto, Hive, Dremio, and ClickHouse used by Cloudera, Hortonworks, and Databricks.

Administration and Configuration

Installing and configuring PL/Python requires compiling against a supported Python runtime and linking to PostgreSQL server binaries; packaging and distribution are maintained by vendors including Debian, Ubuntu, Fedora, CentOS, OpenSUSE, Homebrew, MacPorts, Microsoft and EnterpriseDB. Administrators integrate monitoring with platforms like Nagios, Zabbix, Prometheus, and change management systems used by Atlassian and ServiceNow. Backup and recovery practices follow guidance from PostgreSQL Global Development Group, with disaster-recovery patterns implemented by infrastructure teams at Goldman Sachs, JP Morgan Chase, Morgan Stanley, and Barclays.

Category:PostgreSQL extensions