Expertise
PostgreSQL developer — since 2010
A partial index shrinks a queue table, but only for as long as the planner picks it; I have worked with PostgreSQL since 2010 and I write that caveat as a measurement, not an assumption.
- years
- 16
Me in the PostgreSQL ecosystem
PostgreSQL is less a storage choice for me than a surface to measure. The measurements I have published on index and queue-table behaviour rest on a benchmark and a described environment; instead of “it usually goes like this” I write what I measured and where it broke. The measurements are in the Research section: partial indexes, bloat and autovacuum, and whether the planner switches to a generic plan.
The architecture side of this work connects to the frame on the Software Architecture page: adding a new data store is a decision, and one more system to operate. On the backend side it is an everyday part of schema and query design in the Backend role.
I have worked with MySQL longer; what I say about PostgreSQL comes only from the measurements I made on PostgreSQL.
What is the PostgreSQL Developer role?
PostgreSQL is an open source object-relational database system that extends the SQL language; it is ACID-compliant, uses multi-version concurrency control (MVCC) and is extensible. A PostgreSQL developer is the person who designs the schema, the query and the index around how this system behaves.
What does the PostgreSQL Developer do?
In PostgreSQL I design schemas, queries and indexes; on heavily updated tables such as queue tables I measure index and vacuum behaviour. Where I can, I keep vector search inside PostgreSQL with pgvector instead of moving it to a separate store. Before adding a new data store I look at where PostgreSQL's own tools are enough.
Technologies I pair it with
-
PostgreSQL
The database I measured queue-table and index behaviour on.
-
pgvector
The extension that keeps vector search inside PostgreSQL instead of a separate store.
-
MySQL
The relational store I have worked with longest.
-
Redis
Cache, counters and short-lived locks.
-
RabbitMQ
Asynchronous delivery between services; the exit door of the outbox relay.
-
Elasticsearch
Where relational stops being enough: full-text search and log analysis.
-
Docker
Development environment and deployment unit.
Questions in this area
Frequently asked
7 questions
-
How long have you worked with PostgreSQL?
Since 2010 — 16 years.
-
What does a PostgreSQL developer do?
A PostgreSQL developer designs schemas, queries and indexes, and builds the application around how the database behaves. For me that means measuring index and vacuum behaviour on heavily updated tables such as queue tables, and keeping vector search inside PostgreSQL where it fits.
-
How do you choose between PostgreSQL and MySQL?
MySQL is the relational store I have worked with longest; my queue-table and index measurements, though, I made on PostgreSQL. The measurements I have belong to PostgreSQL, and I do not make the same claim for MySQL. The choice comes from the workload and the team's knowledge, not the brand.
-
Do you use a partial index on a queue table?
I measured it: a partial index shrinks the index on a queue table markedly, but only for as long as the planner picks it. I also measured bloat and autovacuum behaviour, and whether the planner switches to a generic plan, separately. All the findings are [in the Research section](/research/partial-index-queue-table/).
-
Do you need a separate database for vector search?
Not always. pgvector keeps vector search inside PostgreSQL instead of moving it to a separate store. For large embedding collections I look at a distributed vector database such as Milvus.
-
Do you relay the outbox table by polling or with Debezium CDC?
A polling relay tuned with SKIP LOCKED, batching and a partial index is enough for most teams. I move to Debezium where measured latency or database load pinches.
-
What do you check before adding a new data store?
Whether PostgreSQL's own tools already solve the problem. A separate store is one more system to operate, so I measure the limit of what I already have first.
Let us work together
If you have work in this ecosystem, tell me what you are building and we will talk about how to build it.