PostgreSQL

15 October 2025 14:20

Message comes after the BIND step

PostgreSQL

15 October 2025 14:20

Anybody seen something like this before?

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp2450094.0.fileset/i2of16.p0.0", size 14909440
STATEMENT:  -- +----------------------------

PostgreSQL

27 September 2025 19:08

The documentation is actually quite good. If you like reading, you can start there: https://www.postgresql.org/docs/current/index.html
If you prefer videos, there are many available, look for some on topics you are interested in.

PostgreSQL

24 September 2025 12:18

If you need an automatic timeout then https://dba.stackexchange.com/a/164450/99326 may help, however I would suggest looking at what is actually happening and maybe use a connection pooler like pgbouncer to protect the DB connection pool.

PostgreSQL

24 September 2025 11:17

Anybody can get to knw why connection leakage occurs?

PostgreSQL

22 September 2025 17:49

Yes, the surrogate key might be more sensible size-wise than a generated text version of the box, and doesn't involve any app changes - I considered one in the original design but removed it because it served no purpose then, when we had no replication (removing it helped some tables size-wise).

(Of course we have one for the cache server as it was a better foreign key for the associated areas, plus you get a consistent order.)

PostgreSQL

22 September 2025 16:22

Yes, if I did what I proposed it'd be because it might be an interesting project and seems the 'ideal' solution (and perhaps useful to others). Either a generated column or manually replicating the [rarely-changed] cache areas seem the simplest solutions. 😼

A point is insufficient because the nearest cache may not be the most suitable, either due to connectivity to the user (see the South America example) or due to varying cache capacities (some have limited monthly transfer; we want to avoid using them if the latency benefit is small but they would incur overages).

Caches may also be disabled and in that case we might want traffic to fall back to a larger cache defined by a large area with a lower (well, technically higher value) priority level.

Polygons from PostGIS might work, assuming https://postgis.net/docs/ST_Geometry_EQ.html acts as an equality operator for index purposes, which seems to be the case based on the description. It was not used initially as box seemed sufficient and we do not have PostGIS installed for any other reason.

PostgreSQL

22 September 2025 13:33

To demand from self is a really tough task, I know.

PostgreSQL

21 September 2025 21:40

Perhaps maybe consider bunny.net as they provide connectivity and actually started as providers of other art archives :P https://bunny.net/pricing/cdn/#calculator

PostgreSQL

21 September 2025 21:37

e.g. west coast South America might actually be better served by California and Dallas than Sao Paulo due to the dearth of interconnections in the center of the continent - they get their connections via submarine cable instead.

PostgreSQL

21 September 2025 21:33

Yes, I was considering that, it seems the most obvious solution aside from a surrogate key (related to the ::text cast mentioned in the first thread - actually could probably just define it as an expression index, but not sure that would fit the logical replication requirements).

PostgreSQL

21 September 2025 21:31

I'm a bit clunky and never used logical replication (i am a big fan of normal log shipping + replication slot), but can't you add a GENERATED AS column, cast the box to something that has equality (e.g. TEXT) and use that as the key? It is not elegant at all, but i assume it would stay the same at least between main pg versions

PostgreSQL

21 September 2025 21:27

Do you actually run inkbunny, or are you running a mirror of inkbunny?

PostgreSQL

21 September 2025 20:23

You must have a primary key on a table.
If the data isn't suitable as keys — use surrogate keys.

(Ask you db architect for a proper relational decomposition of your database. 3rd nornal form is a bare minimum to begin with.)

PostgreSQL

19 September 2025 19:02

Immediate Joiner – Power BI Developer | 5+ Years ExperienceH

ello, I am Mohammad Asgar, an experienced Power BI Developer / Data Analyst with over 5 years of expertise in:
Power BI (Advanced Dashboards, Drill-Down, Drill-Through, Storytelling)
SQL, DAX & ETL
Data Modeling
Delivering KPI-driven reports across Telecom, Retail & Manufacturing domains

Delivered enterprise-level dashboards improving decision-making by 10–20%
Proven success in handling large-scale datasets & cloud migrations
Immediate Joiner (within 3–4 days) | Open to Relocate PAN India

LinkedIn: https://www.linkedin.com/in/md-asgar-95425162

9902232976 | asgariitb@gmail.com

PostgreSQL

15 October 2025 14:20

The query is really just that comment, but inside a SAVEPOINT apparently

PostgreSQL

04 October 2025 14:53

Hi,
We are looking for a Hyderabad based PostgreSQL expert/trainer who can provide offline training in Hyderabad.

If interested, please DM me for details

PostgreSQL

27 September 2025 16:26

PostgreSQL lecture are available ?

PostgreSQL

24 September 2025 11:18

whether killing connections is the right solution if occurs than 30 min

PostgreSQL

22 September 2025 17:56

Thanks for all your suggestions, it's a better problem to have more ways to do it than none. 😸

PostgreSQL

22 September 2025 17:06

OK, understood. Then point and radius, probably?

Well, postgis also would work, even have better extendability — byt installing it.

PS Surrogate box pk is also always an option.

PostgreSQL

22 September 2025 13:36

Text representation of a box and functional indexes on text::box seems much more sraightforward.


Well, let's begin with: why do you use box anyway?
Why not a point and not a postgis?

PostgreSQL

21 September 2025 21:46

I chose this method because we just need a best guess and we can IP geolocate, most are "reasonable" choices if not always optimal. And it fits nicely into PostgreSQL's box matching.

Perhaps I can 'just' create a hash index for box with something like create operator family 'box_hash_ops' using hash and create operator class for data type 'box' using 'box_hash_ops', which only has one required and two optional support functions (basically need to compute a stable hash for 32 and ideally 64-bit).

PostgreSQL

21 September 2025 21:38

You'd need to either have all of your servers traceroute to your user, or keep a table of subnets and AS peerings and "guess" the route, or have your client do it client side (which sounds like the most sensible way honestly)

PostgreSQL

21 September 2025 21:35

We don't know how many hops it is to an arbitrary viewer, and calculating the latency would be interesting... I do manually estimate latency when setting the areas using mtr.

PostgreSQL

21 September 2025 21:32

But that also makes me think: is that part of an algorithm to decide from where to serve media? I would move it to a hop based or latency based one, unless there's legal reasons (e.g. geofencing) to do so

PostgreSQL

21 September 2025 21:29

I really run Inkbunny. And we have always run on PostgreSQL, which is something I have been very thankful for on numerous occasions, despite occasional setbacks. 😼

PostgreSQL

21 September 2025 20:33

I run a furry art archive; I am the DB architect. Well, since the founder handed it to me a decade ago. We don't really have funding for specialists, just $10/day for hosting. 😼

I guess that works, and thank you for suggesting it, just annoyed because the data is suitable, it just lacks the equality opclass and so I'm wondering if it is definable via an extension etc. (Perhaps others ran into this? Though I figure box isn't hugely popular. Ah, more from a decade ago... and the type is ~40 years old, the original Postgres.)

PostgreSQL

21 September 2025 20:05

I just ran into the issue that box doesn't have an equality operator, so can't be updated in a logical replication setup (it breaks replication until you skip the transaction if you do update a row on the primary).

We use them (logically) for catchment areas for a list of cache servers. Is there any good way around this other than removing the table from the subscription. Can we define our own box equality opclass that is equality of its components rather than equality of areas, say? (Because a box in Africa is not the same sized box in America.)

(Just to make it more difficult, although perhaps not for this purpose, multiple caches can cover the same areas, usually at different priorities; the primary key if any is a composite of cache ID and box. Different areas for the same cache may also have the same priority, although we could potentially forbid that and run an index on the composite of cache ID and priority - not sure that works though, and it's a dubious solution.)

PostgreSQL

18 September 2025 12:27

The postgres has good docs... And I would suggest to read them through carefully, at least once, to everyone willing to be a postgres DBA or postgres user...

But that wouldn't be sufficient to be a good DBA. Some experience with less responsibility seems to be required.
(As for MS SQL, but there there are more training courses to begin with).

Another problem is tgat Aurora isn't postgres. While Amazon RDS for Postgres is mostly a closed fork of Postgres, with some rare glitches — the Amazon Aurora definitely isn't.
If you'd be lucky — the AWS team would do most of the work for you.
If you'd not — not a much you could do...