Unfortunately, this is already the umpteenth time that a developer in our company used DBeaver to access our database. We again had a major performance bottleneck the last weekend because someone forgot to close the application before the weekend.

It's ridiculous that only opening this application (he used to access some other database, but it auto connected to this one) can take down a whole system by locking a table with a select query it automatically execute. And never release this.

Not only that, in the past it happened that a developer did a change on a data record on a table and locking it with a commit, taking the whole data backend down. DBeaver won't automatically release the commit after some time so if you forgot this was still locked in the background, you bring everything down. It doesn't even warn the users that the whole table is locked.

Is there a way I can block the use of DBeaver for our database? Can I block specific user agents that wants to connect?

DBJoy is a lightweight, native macOS Postgres client. It’s AI-first, with a built-in agent for working with your data and an MCP server so Claude Code can read (or write) to it. It also has all the features you’d expect from any other database client: diff and migration tools, tunnelling, batch editing, ER diagrams, and more. I’ll keep improving it and will add more database adapters soon.

https://github.com/agustind/dbjoy

Here is my reasoning. It's not because it creates another copy of your database or helps with major-version upgrades.

I think its bigger value is that it gives you a way out.

A Postgres instance might begin as one resource in AWS, Azure, or some cloud. Five years later, your identity system, private network, applications, and operational tooling have grown around it. Moving the database now means moving half the architecture.

Logical replication doesn’t eliminate the hard parts. You still have schema changes, replication slots, lag, IAM, networking, extensions, and application assumptions to deal with.

I wrote up the longer argument here:

https://streambed.dev/blog/logical-replication-is-postgres-most-important-feature/

Would love to hear your favorite feature and the reasoning too.

I’m new to postgreSQL and am working on a college course. When trying to register my server I keep getting a getaddrinfo failed error, and I can’t figure out how to fix it. Not sure what I’m doing wrong here and looking up the error hasn’t helped, hoping someone here can help me with this. Thanks!

My app has been running on a free Postgres tier and it's starting to hit the limits now that real users are on it. Connection limits are getting tight and I don't want to deal with random pauses or throttling anymore.

Most recommendations jump straight to AWS RDS, and that feels like overkill for where I'm at right now. I'd also rather not manage Postgres on a VPS myself if there's a reasonably priced managed option out there.

Looking for something paid with predictable pricing, backups handled, and a setup that doesn't take a whole afternoon. What's working for you on a small production app?

I m a complete beginner and an experience Linux admin interested in db administrator. I hear a lot of words around like purging, indexing , tuning , design best practices etc etc... how do I learn all of this shit? every tutorial on udemy or YouTube either has half or different syllabus.

I am looking for structured learning experience in some order. with projects. to understand better.

I’m looking for a PostgreSQL hosting provider with a free tier that meets two key requirements:

• At least 1GB of free database storage

• Very generous or effectively unlimited API/query limits

Would appreciate any suggestions or experiences.

- pgadmin4 flat out doesn't work on Fedora. It sucks anyway. Sluggish, disorganized web interface.

- Dbeaver gives me an authentication failure. I tried using my Linux username/password and username/password for the database. Neither worked. After closing and opening again, it left me at an infinite loading screen

https://github.com/dbeaver/dbeaver/issues/3361

https://github.com/dbeaver/dbeaver/issues/8105

- DBVisualizer crashes on startup

And yeah afaik that's it.

Like how the fuck does it suck this bad and how are people using this on production server if they can't even open a properly tested GUI? I can't imagine doing all it in the command line is even remotely convenient especially for something large-scale.

RSVP here: https://postgresconf.org/conferences/postgres-webinar-series/tickets

An email will be sent with joining instructions.

Sharding is how most of us scale Postgres. It works, however, it is not without its challenges. What key should we shard on? How big will my shards need to be? How will sharding affect performance? What application-level mods will need to happen? What happens if we need to re-shard the database?

Automating the sharding process seems right. We should be able to define a shard and let the db take care of the underlying segmentation and movement of data.

CockroachDB is wire-compatible with PostgreSQL and automates the sharding process. If you want to scale, you simply spin up a node, point it at the cluster and the database takes care of rebalancing and moving data. You can scale on the fly and even reshard while in production without downtime or loss of data.

In this webinar, we will speak through how CockroachDB delivers this effortless scale, covering: Primary keys in Cockroach - the “key” to sharding RAFT, range partitions and the architecture that delivers scale Automating data placement for latency and resilience Re-shard in production without downtime All while speaking SQL

I have some fairly complex queries in my application and I notice that they're running quite a lot slower with newer versions. Going from version 11 to 12 made this one query four times as slow, and now going from version 14 to version 15 it's again six times slower than the previous version. When will this end? With careful re-writing and index creation, I managed to get Version 14 at a similar performance as Version 11 was. Except that version 11 also runs a lot faster with these same optimizations. So all in all, the newer versions run a lot slower than the older ones, 12 and 15 being the most notable speed bumps. Anyone else has this problem?

NVM about version 15, I forgot to optimize the indexes... My problems still stands for version 12 and onwards

Hi guys, I just installed PostgreSQL on my Windows 10 machine. Now to create a DB file, I went into D:\Program Files\PostgreSQL\15\bin on cmd prompt & typed pysql.exe & hit enter. Now it is saying: Password for user myWindowsAccountUsername but my Windows account uses a PIN to login every time, I entered PIN it didn't work. And Windows account is linked to my outlook.com ID so I tried that password but even that doesn't work.

During installation I purposely set '123' as password but even that fails. Can someone please help? What pswd am I supposed to enter? Or if you can guide how to do on Ubuntu server is also fine. End goal: make a simple db file which later on I manipulate with Python. Thank you.

Earlier I tried installing & creating a DB on my Ubuntu server but this password/user authentication thing is just not entering my head, so decided to try on Windows. I checked out multiple learning resources & just can't figure out how to create a simple DB file & run queries on it using Python. I'm spoiled by SQLite.

Built a tool called MigrationSafe that checks Postgres migration files for risky operations before production.

It catches things like:

• NOT NULL columns without DEFAULT

• DROP COLUMN

• indexes without CONCURRENTLY

• foreign keys without NOT VALID

• and other risky migration patterns

Around 20 checks right now.

No install or setup needed, just run it against your SQL migrations.

https://www.npmjs.com/package/migrationsafe

Would appreciate any feedback, suggestions.....

I made a Postgres GUI recently and I’m looking for a few people to try it!!

Most database GUIs feel like they lack a little flair, for me at least.

It’s early and free - I’m mostly looking for feedback before I build too far in my own vacuum.

A couple things it supports:

- Postgres connections + juggling multiple connections

- Combined views (identified with FK for joining) and save them for later

- A handful of color themes with dark mode ofc

- BYO AI API key to power AI Assistant to help you write queries

- Good security - TLS cert verification, read only mode if you want, credentials saved in OS keychain

- Direct, AWS SSM, and preconnect script connection options

Btw Apple silicon chip only right now!

DM me if you want the download link!

Currently I use pg hosted on Hetzner in Germany. My users are in different global regions and pay latency cost. I run a Shopify app that complains that my LCP is above recommended threshold of 2.5s. I have optimized my queries and calls and was able to optimize it a bit.

My question is, there is no cheap way to have pg global replicas. My app is new and doesn't have enough revenue to justify the cost. I have done some research and the only option I see is migrating to SQLite which can be easily and cheaply replicated. But, with that, I lose pg features like JSONB, ::datetime and the likes. Also, SQLite doesn't support most ALTER commands.

Has anyone solved this?

UPDATE: It was clear that there is no cheap and reliable solution for this. And rightly so. Keeping infrastructure reliable for millions of users takes a lot of cost. I zeroed down my requirements to 'having a reliable zero downtime database' so that my users never face outage. I moved to planetscale ps-5 high availability instance. When the budget allows, i will add replicas in other regions.

This sub is littered with trivial questions about pgAdmin, not being able connect to a server etc. that should be asked at Stack Overflow. Maybe it's time to completely ban them and make rules more similar to /r/programming with links to news, articles and open-ended high quality questions?

I created a database with postgres on a remote server and then I use it on my app. But every once in a while (maybe every 2 days) my database gets deleted and a new super user is being created automatically. I'm unsure why it happens can any one point out what could be the issue here.

I've been tinkering with postgres as an amateur for a few years now. I'm comfortable making big complex relational schemas, in normalised form, etc. What I'm not good at is performance issues - because I've never hit them, only ever having made toy databases. Help me transition from 'it works!' to 'it works fast!'.

I recently hit my first 'scalability' issue, and this is the perfect opportunity to learn! I've managed to make a DB with 2 tables that scales super-badly, like n² bad - with just 1.1k rows of data, a summary view takes 14 seconds to run! I could of course give my pg15 server more than 1 virtual Haswell-era x86 core and 2 GB of RAM, but I doubt that would help this particular problem for long! Help me figure out the 'right' way to have done this :)

So I'm tracking my electricity usage, 1900s-style. I don't have a smart meter, so I just have a simple webpage that I keep open on my phone and I simply note whenever the meter changes when I go past it. The meter only displays to the nearest KWh, so this data is very inaccurate - all I can guarantee is that the meter reading was what it was at the time I took the reading:

metering=# \d electricity.reading
                           Table "electricity.reading"
   Column   |           Type           | Collation | Nullable |      Default      
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | gen_random_uuid()
 reading    | integer                  |           | not null | 
 reading_ts | timestamp with time zone |           | not null | now()
Indexes:
    "reading_pkey" PRIMARY KEY, btree (id)
    "reading_uniq" UNIQUE CONSTRAINT, btree (reading)
    "time_uniq" UNIQUE CONSTRAINT, btree (reading_ts)

Obviously this doesn't immediately produce any particularly interesting data, beyond simple stats like KWh usage in last day, last week, last month, etc. In order to get anything really interesting, we need to summarise lots of data together. At this point, obviously the 'best' thing may be to switch to some dedicated stats tool, or Python, or Matlab or R or Microsoft BASIC, or whatever, but I don't wanna - I want to use postgres, and I hope you do too, on /r/postgresql!

So, let's turn discrete time-based KWh samples into a continuous Watts view containing both usage and the start- and end- timestamps for that period in each record. I'd also like to choose a day-divider (for the later analysis steps) other than midnight; I'm rarely awake at 5AM, so we'll use that, and apply the offset here:

metering=# \d+ electricity.normalised_watt_reading
                              View "electricity.normalised_watt_reading"
      Column      |           Type           | Collation | Nullable | Default | Storage | Description 
------------------+--------------------------+-----------+----------+---------+---------+-------------
 id               | uuid                     |           |          |         | plain   | 
 reading_start_ts | timestamp with time zone |           |          |         | plain   | 
 reading_end_ts   | timestamp with time zone |           |          |         | plain   | 
 watts            | double precision         |           |          |         | plain   | 
View definition:
 SELECT reading.id,
    reading.reading_ts - '05:00:00'::interval hour AS reading_start_ts,
    lead(reading.reading_ts) OVER (ORDER BY reading.reading_ts) - '05:00:00'::interval hour AS reading_end_ts,
    ((lead(reading.reading) OVER (ORDER BY reading.reading) - reading.reading) * 1000)::double precision / (date_part('epoch'::text, lead(reading.reading_ts) OVER (ORDER BY reading.reading) - reading.reading_ts) / 3600::double precision) AS watts
   FROM reading
  WHERE reading.reading_ts > '2022-09-01 00:00:00+01'::timestamp with time zone;

This could be the first problem, as I'm looking forwards and backwards in the results. Still, I can count the rows in the view in ~50ms, so it seems to be handling it pretty well.

Now, let's get silly and start wrapping this data around a 24-hour period in 10-minute increments in another view (while undoing the 5-hour offset we applied in the previous view so the times make sense):

metering=# \d+ electricity.day_summary_all_days   
                         View "electricity.day_summary_all_days"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
  WHERE slot.slot < normalised_watt_reading.reading_end_ts
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

This has to be where things are getting slow - running the above takes 1.7s! There's a lot of 10 minute intervals between August last year and now, and that's getting multiplied by the number of samples in the main table. It works, though! I get really interesting graphs that show a massive spike when I usually shower in the morning on work days, a general elevation in usage through the work day, and increase after work when I turn my TV or desktop PC on! Woo!

Going further, I decided to create even more views, e.g. to summarise only weekends by adding 'AND to_char(slot.slot, 'ID'::text)::integer > 5' to the WHERE condition. I then added a second table to record the set of days I didn't work (I am 100% remote WfH), so I could have views that only include holidays or bank holidays, etc. e.g.:

metering=# \d electricity.special_day
              Table "electricity.special_day"
  Column  | Type | Collation | Nullable |      Default      
----------+------+-----------+----------+-------------------
 id       | uuid |           | not null | gen_random_uuid()
 day      | date |           | not null | 
 day_type | text |           | not null | 
 comment  | text |           |          | 
Indexes:
    "special_day_pkey" PRIMARY KEY, btree (id)
    "special_day_uniq" UNIQUE CONSTRAINT, btree (day)

^ yes, of course I could have normalised day_type, but I don't think a 3rd table is going to solve this DB's problems XD

metering=# \d+ electricity.day_summary_work_days
                         View "electricity.day_summary_work_days"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
     LEFT JOIN special_day ON to_char(slot.slot, 'YYYY-MM-DD'::text)::date = special_day.day
  WHERE slot.slot < normalised_watt_reading.reading_end_ts AND to_char(slot.slot, 'ID'::text)::integer < 6 AND special_day.day_type IS NULL
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

Now this is where things get bad. This view takes 9.5 seconds to run! Clearly the number of 'special days' is multiplying the number of rows being used to summarise data over the 24 hour period, instead of cutting them down.

I won't bother showing here unless you're interested, but I then have a 'super-summary' view that takes all these individual 24-hour summary views and concatenates them into one big table. This takes about 14 seconds to run.

So, what are the obvious things wrong here, and how would you have done things differently? I tried adding explicit indexes to the main readings table, but I it made no difference, potentially because the unique constraints add implicit indexes already? I'm really only interested in postgres, but if you really want, let's also discuss what other tools you'd use that would be more appropriate than pg.

Edit: depesz: https://explain.depesz.com/s/ofXO