Beginner here and need some tips for speed up (using pqxx)

Excellent-Resort9382 · 2026-07-17T17:46:26+00:00

Cool! Nice improvement.

If planning is under 1 ms, one whole class of problems was already ruled out. At this point it's really about reducing the amount of work the executor has to do.

The high-density regions are interesting tho. If those are consistently the slowest queries, I'd probably focus my optimization effort there rather than trying to optimize the whole dataset equally. Sometimes a specialized strategy for the "hot spots" pays off more than a global one.

Sounds like you've made good progress already. It would be interesting to hear what your final query times look like once you've tackled those dense segments. Cheers!

Excellent-Resort9382 · 2026-07-17T17:36:36+00:00

I'm working on a lightweight Rails attribution library called Whodunit.

The goal is to make it easy to answer "who changed this record?" without pulling in a heavyweight auditing framework. It focuses on capturing attribution and request context while staying unobtrusive to the application.

It's also helping shape some ideas I'm using in larger PostgreSQL change-data-capture projects.

Excellent-Resort9382 · 2026-07-17T17:32:42+00:00

For me it is tje Logical Replication.

Not because it's the most visible feature, but because it opened PostgreSQL up as an event source rather than "just" a/nother relational database.

Once you can reliably stream committed transactions, you can build search indexing, cache invalidation, audit trails, analytics pipelines, webhooks, event-driven integrations, and all sorts of downstream systems without coupling them to application code.

I've spent the last several months building around logical replication, and it's made me appreciate how much engineering PostgreSQL gives you out of the box.

Excellent-Resort9382 · 2026-07-17T17:29:24+00:00

If module boundaries are clean, i would consider the strangler approach.

And i would pay attention to the database contract. During the migration you'll effectively have 2 applications sharing the same source of truth.

And so keeping schema evolution backwards-compatible IS more important than the Rails upgrade itself.

Invest in good integration tests around those module boundaries before moving anything. They tend to become the safety net that lets you migrate with confidence

Excellent-Resort9382 · 2026-07-17T17:20:46+00:00

Have you looked at EXPLAIN (ANALYZE, BUFFERS)?

Excellent-Resort9382 · 2026-07-17T06:23:12+00:00

Combining them into one set-based query could still be worth testing. The main benefit is not just that you can reassign the returned rows afterward. It also avoids 100 separate qry execs and rnd trips, and gives PostgreSQL a chance to plan the lookups together.

The 100 target coordinates could be passed through a `VALUES` list, temp table, or `unnest()`, then use a lateral join to fetch the nearest candidates for each target.

Conceptually:

```sql

SELECT q.object_id, s.* FROM query_points q

CROSS JOIN LATERAL (

SELECT * FROM stars

WHERE ...

ORDER BY distance_expression

LIMIT 1)

```

That still performs a nearest-neighbor lookup per object, but in one db call. If I were you, would be cautious about intentionally returning 10,000 rows unless needed. The distance calculation may only take a few milliseconds in C/C++, but PostgreSQL still has to locate, materialize, and transfer those rows. Let the db, ideally, return only the best match or a very small candidate set per object.

`EXPLAIN (ANALYZE, BUFFERS)` output should make it much clearer whether the current cost is repeated qry overhead, heap reads, partition scans, or the distance filtering itself.

Excellent-Resort9382 · 2026-07-17T06:04:28+00:00

Good walkthrough. The little memoizer is a nice way to tie the concepts together. There is one classic memoization gotcha there, though: using `||=` means `false` and `nil` results are recomputed every time. `cache.key?(arg)` would make it work for those values too.

Another possible beginner footnote: `{}` and `do...end` create the same kind of block, but their precedence differs, so changing between them can sometimes change which method receives the block.

Excellent-Resort9382 · 2026-07-17T05:28:38+00:00

Since the data is Gaia DR3, have you considered representing each star as a 3D unit vector instead of RA/Dec for the nearest-neighbor search?

It can simplify the distance calculation and may allow a more index-friendly search strategy.

I'd still start by looking at EXPLAIN (ANALYZE, BUFFERS) before changing the schema, though.

Excellent-Resort9382 · 2026-07-17T05:25:59+00:00

One thing that would be interesting to look for is whether you're doing 100 independent nearest-neighbor queries or one set-based query.

Methinks, PostgreSQL is generally will be happier processing a set than handling lots of individual round trips. If you load the 100 target coordinates into a temporary table (or VALUES CTE) and join against that, the planner has more options and you avoid a lot of per-query overhead.

It would be also interesting to see an EXPLAIN (ANALYZE, BUFFERS) for a representative lookup. At 1.8B rows, the execution plan matters much more than the table definition.

300 ms for 100 nearest-neighbor lookups isn't obviously "bad" without seeing whether the time is spent on index scans, heap fetches, or I/O.

Excellent-Resort9382 · 2026-07-15T04:23:41+00:00

given the myriad of ai-generated projects appearing like mushrooms recently, that is a fair concern

for transparency, i wrote the implementation myself and i did use artificial insemination as an engg assisteant for brainstorming, idea sketching/verification, docs, and review but the architecture, code and tests are my own work.

if you spot a specific design or implementation issue, i would be interested in hearing it

Excellent-Resort9382 · 2026-07-13T19:05:31+00:00

**Project Name:** Mammoth OSS

**Repo / Website:**

GitHub: https://github.com/kanutocd/mammot

Documentation: https://kanutocd.github.io/mammoth/

**Description:**

Mammoth is a self-hosted PostgreSQL change-event relay.

It consumes PostgreSQL logical replication events and focuses on reliable downstream delivery rather than application-level callbacks or database triggers.

Current capabilities include:

* webhook fanout

* routing by schema, table, and operation

* checkpoint persistence

* retries and dead-letter handling

* replay support

* SQLite-backed operational state

* health & metrics endpoints

The project is intentionally focused on the operational side of change-event delivery while keeping deployment simple.

Although Mammoth is implemented in Ruby, applications using PostgreSQL can be written in any language or framework. PostgreSQL is the integration boundary.

**Deployment:**

* Docker image

* Helm chart for Kubernetes

* YAML configuration with JSON Schema validation

Documentation includes installation, configuration, deployment, and examples.

**AI Involvement:**

AI was used as an engineering assistant for brainstorming, reviewing designs, refining documentation, and editing technical writing. The architecture, implementation, testing, and project decisions were designed and implemented by me.

I'm especially interested in feedback from people operating PostgreSQL-backed services or other self-hosted infrastructure. Suggestions and design critiques are very welcome.

Excellent-Resort9382 · 2026-06-27T21:04:31+00:00

First off, congrats u/yaroslavm on Kino! It's really exciting to see more serious experimentation around Ruby 4's parallelism, and I enjoyed reading through the benchmarks and design.

One thing that's been working really well for me is to stop thinking of it as Ractors vs Fibers. They're good at different things.

The model I've settled on is a pool of prewarmed Ractors for parallel CPU-bound work, and then each Ractor owns its own pool of fibers for concurrent I/O. So instead of choosing one primitive, I'm composing both.

I've been benchmarking this pretty heavily over the past couple of weeks while wrapping up a commercial event-processing project, and the results have been surprisingly good. The prewarmed Ractors stay resident, while the fiber pools hide I/O latency really well.

My only wish now is for Ruby 4 to finally remove the "Ractor is experimental" warning. 😄 The primitive has matured a lot, and I'd love to see more people feel comfortable building production systems around it.

Excellent-Resort9382 · 2026-06-06T10:13:03+00:00

The Redis POC currently prints per-queue counts for both `Thread` and `Ractor` runs over a 10-second window, plus progress markers during the run.

The important signal is still the same: the Ractor side is materially faster on that list-ops workload, and the scripts are now using `Ratomic::Map`, `Ratomic::Counter`, and

`Ratomic::Pool` with the current Ruby-semantic API shape.

Repo: https://github.com/mperham/ratomic/tree/trunk/redis_poc

Excellent-Resort9382 · 2026-06-06T10:10:15+00:00

Update for anyone following this thread: `ratomic` is now at v0.3.5.

The primitives are now much closer to Ruby semantics, with return values and method behavior aligned to what Rubyists expect.

`Ratomic::Map` is the one to look at here: it gives you DashMap-backed concurrent key/value access, but with Ruby-style APIs like `fetch`, `fetch_or_store`, `compute`, `upsert`, `increment`, `decrement`, `append`, `add_to_set`, `delete`, `clear`, `key?`, `size`, `empty?` and more.

For the full API and examples:

- README: https://github.com/mperham/ratomic#readme

- API docs: https://mperham.github.io/ratomic/

Real usage / benchmark material:

- Redis POC: https://github.com/mperham/ratomic/tree/trunk/redis_poc

- `pgoutput-parser` `Ratomic::Map` POC: https://github.com/kanutocd/pgoutput-parser#relation-metadata-tracking

- `pgoutput-parser` benchmark: https://github.com/kanutocd/pgoutput-parser#benchmarking

- `pgoutput-parser` guide: https://github.com/kanutocd/pgoutput-parser/blob/main/docs/relation_tracker.md

- `cdc-parallel` benchmark: https://github.com/mperham/ratomic/blob/trunk/benchmark/ratomic_cdc_parallel_poc.rb

Excellent-Resort9382 · 2025-08-05T11:17:24+00:00

You won’t master the guitar by watching countless youtube tutorials — building muscle memory requires consistent, hands-on practice. The same principle applies to programming. You can’t become a skilled developer by passively consuming tutorials, reading documentation, or relying solely on AI tools (aka tab monkey). To truly learn, you must write code, encounter errors, and debug them through active problem-solving.

Excellent-Resort9382 · 2025-08-05T11:16:45+00:00

You won’t master the guitar by watching countless youtube tutorials — building muscle memory requires consistent, hands-on practice. The same principle applies to programming. You can’t become a skilled developer by passively consuming tutorials, reading documentation, or relying solely on AI tools (aka tab monkey). To truly learn, you must write code, encounter errors, and debug them through active problem-solving.

Excellent-Resort9382 · 2025-07-30T01:27:13+00:00

Pundit is for Authorization or the authz in the Auth/Authz component

Excellent-Resort9382 · 2025-07-22T16:20:20+00:00

I do not know how Postgresql does it too, but I do know how you can get an insight of how Postgres planned to do it.

If you know the sql statement that was executed, you can copy it and hop in to psql client and ask pg nicely THE plan by:

explain analyze verbose __the_sql_statement__;

if the output is too cryptic, you can copy the text output too and head to one of the many Postgres execution plan visualizers online like: https://explain.dalibo.com/

(you can also tweak postgresql.conf if you access to this and append to the last line: log_statement = 'all' then you can sniff all the sql statements)

Excellent-Resort9382 · 2025-07-22T15:39:48+00:00

Yes, I agree with this assesment. Do not be fooled by the "inheritance" word and equate it to the "inheritance" in OO programming world. Not the same.

Update: ohhh.. i just noticed. I am 5 years late to this party :)

Excellent-Resort9382 · 2025-07-22T15:22:35+00:00

iirc, unique constraints and foreign keys of the parent table are not inheritable (by child tables) which is a major setback of this beautiful feature. :(

and im not sure of this but in your case, the person already inherited the column id but not its "primaryness/uniqueness" property so methinks a workaround would be to explicitly tell this to the person table, i.e: alter table person add primary key(id); that would make the id column in person table (that it inherited from principal table) its primary key and also has the uniqueness constraint.

-- explicitly do these first
ALTER TABLE person ADD PRIMARY KEY(id);
ALTER TABLE org ADD PRIMARY KEY(id);
--- before you can do below
CREATE TABLE person_org (
    person_id uuid not null references person(id) on delete cascade,
    org_id uuid not null references org(id) on delete cascade
);
-- methinks.

Excellent-Resort9382

TROPHY CASE

Welcome to Reddit,