PostgreSQL

PostgreSQL is an open-source relational database management system. A large number of PostgreSQL functions are available as standard, and documentation for these can be found in the official documentation. However, it is possible to create your own extensions for specific needs. You can check PGXN, the PostgreSQL Extension network. This is a central distribution system for open-source PostgreSQL extensions. In this blog post I’m going to talk about the different PostgreSQL extensions developed by Adjust....

For this month’s PGSQL Phriday blogging challenge, Tomasz Gintowt asks if people rather use partitioning or sharding to solve business problems. Big Data: Partitioning vs Sharding Adjust Here at Adjust we use both. We also have quite a few databases of all sizes. Let’s look at some examples. We leverage four primary database systems, termed as “Backends”, “Shards”, “Bagger” and “Tracker”. The Backend systems function as intermediate storage of data, anything between hours and weeks....

Introduction PostgreSQL has a process-based architecture. A single running PostgreSQL instance contains several types of processes: a single postgres server process, a backend process per each client connection and various maintenance background processes. During the development of an extension it is often necessary to debug a backend process using gdb (or a different debugger). Along with debugging logs it helps to understand how to work with the extension API since it is often not clear and the PostgreSQL documentation might not be enough....

We are happy to announce that Adjust is hosting a PostgreSQL Meetup on May 11th, 2023, in the Berlin office. Please head over to the Meetup website for details. The event starts at 19:00, and we have two speakers: Elena Grahovac from FerretDB, will talk about: “Building an open-source MongoDB-compatible database on top of PostgreSQL” (Slides) Divyendu Singh will talk about “PLV8ify - TypeScript to PostgreSQL Functions” (Slides) Please note that the event has a limit of 30 participants, and you must register using the Meetup website....

Tablespaces are a key component for disk space management in relational database management systems. They serve as containers for storing objects such as tables, indexes, views, sequences and other data structures. Tablespaces allow database administrators to better manage disk space by allowing fine-grained management of the storage space used by the database. In this blog post, we will explore what a tablespace is, how to create it, and how to use it....

On a lovely Saturday afternoon our NetOps team raised an alarm: the disk usage on a specific database server was growing fast, and with the current growth rate we have about 30 hours left until the disk is full. Currently climbing over 80%, which triggered the alarm in the first place. Not good. However nothing serious as of now, but better investigate now and not wait until Monday. Zabbix Graph: Space utilization The System This particular server has about 3....

Introduction In Adjust we are creating new data types for PostgreSQL, in order to make handling of data easier for us, but also sometimes to save some space. Many of them are written using the C language. Here are some of the more important extensions for us: istore: it is an integer based hstore. In istore both keys and values are represented and stored as integers. pg-base36: a base36 extension, which implements a base36 binary-to-text encoding algorithm....

We recently decided to factor out PostgreSQL authentication into dedicated PgBouncers for different teams, and no longer allow direct remote access to the database cluster. Apart from its main use as a connection pooler, we’ve realized that the isolation afforded by a PgBouncer layer simplifies the management of authentication and connection settings of the different client teams, and frees PostgreSQL of these mundane duties. Motivations In the highly distributed and multi-tenant environment that our databases participate in, even seemingly minor issues easily bloat into an unsustainable mess....

In our infrastructure, we mostly use Gentoo for our servers. For anyone who doesn’t know Gentoo: this Linux distribution compiles packages on the server. Upgrades take a bit more time, but on the other hand the installation can be adjusted to specific requirements: need debug symbols on a dev or staging platform? No problem, compile them in. Want to optimize an application? Change the compile parameters and recompile once: done. Like with pre-compiled packages, this approach has ups and downs....

Recently we got alerted by our NetOps team about a replication lag in one PostgreSQL database. That by itself is not a major problem, nevertheless something which deserves investigation. NetOps is using Zabbix, and the replication lag in the database system in question is measured using a plpgsql function provided by an open source template. In the replica, not in the primary. DO LANGUAGE plpgsql $$ DECLARE ver integer; res text; BEGIN SELECT current_setting('server_version_num') INTO ver; IF (ver >= 100000) THEN SELECT * INTO res from ( SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::integer, 0) END ) T; ELSE SELECT * INTO res from ( SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::integer, 0) END ) T; END IF; perform set_config('zbx_tmp....