Every table in pg_memtable gets its own arena, a Dynamic Shared Memory segment. The tricky part is how other backends find it. This post stores the segment’s handle in pg_class.relfilenode, so any backend can attach with one call, and hooks CREATE TABLE and DROP TABLE to create and release the arena.
Category:PostgreSQL
Building a Table Access Method, Part 3: shared memory in Postgres
In Part 2, the TAM callbacks needed for CREATE TABLE … USING memtable were wired up, but there is not yet any storage, so there is no point in making INSERT, DELETE, UPDATE, and SELECT work. pg_memtable stores table data in shared memory, so before writing a single line of pg_memtable-specific code, it’s worth covering …
Continue reading Building a Table Access Method, Part 3: shared memory in Postgres
Building a Table Access Method, Part 1
The standard storage engine of Postgres is called the “heap” because of the storage method. The data is stored in “heap files” in no particular order, just added wherever there’s room and extending the file at the end only when there are no space inside the pages of the file. This storage engine is simple …
Continue reading Building a Table Access Method, Part 1
How to Speed Up Postgres Recovery 16x on IOPS-Limited Storage
As part of Multigres development, we have been testing recovery of Postgres server as part of dealing with graceful switchovers as well as failovers of servers in a variety of different scenarios and with different setups, in particular, with volumes that are I/O limited. We noted that recovery, which should have taken a couple of …
Continue reading How to Speed Up Postgres Recovery 16x on IOPS-Limited Storage
Don’t Put Secrets in Your PostgreSQL Environment Variables
Using the official Postgres Docker image to start a new Postgres server is straightforward. The Docker entrypoint script reads POSTGRES_PASSWORD, sets the superuser password via initdb, and then keeps running. The container starts. Your application connects. Everything looks fine. This is very convenient, but is it a good practice from a security perspective? As you …
Continue reading Don’t Put Secrets in Your PostgreSQL Environment Variables
An InfluxDB Line Protocol API for PostgreSQL
When working at Timescale, we encountered a lot of users who were using InfluxDB and wanted to migrate to TimescaleDB. They had the full setup: using Telegraf for collecting data, often Kafka or similar solution as buffer, and sending it all to a InfluxDB backend. Although InfluxDB is a very good database and very efficient …
Continue reading An InfluxDB Line Protocol API for PostgreSQL
pg_influx: Automatic Table Creation
This is now a pretty decent endpoint for Influx Line Protocol, but there are a few features that are missing for something to be practical for normal usage. One of those is that the application should not lose data just because it does not recognize the metric, tag, or field. Fields and tags that are …
Continue reading pg_influx: Automatic Table Creation
Server Startup and Configuration Options
As discussed in the previous post, it would be nice to be able to automatically start background workers instead of having to do that manually each time you start the server, so it will be covered in this post. As always, the code is available in the timescale/pg_influx repository on GitHub. To automatically start background …
Continue reading Server Startup and Configuration Options
Processing packets in parallel through port reuse
The implementation of the Influx reader this far as been pretty straightforward: a single process that reads data from a single port and insert it into the database. Databases are, however, designed to be able to handle multiple inserts at the same time, so what is preventing us from using multiple processes to ingest data? …
Continue reading Processing packets in parallel through port reuse
Leveraging Prepared Statements to Improve Performance
In the post on prepared statement the statements were prepared each time a row is received, which seems like a waste of CPU. After all, the tables do not change, so it should be perfectly OK to just prepare the statement once, save it away in prepared form, and then reuse it for each row …
Continue reading Leveraging Prepared Statements to Improve Performance