PostgreSQL

18 Jun 2025 05:34

Hi

I am looking for job change having 5.8y of experience in SQL DBA including 2y as postgresql database please let me know if any vacancies!

PostgreSQL

17 Jun 2025 20:59

And did you try with \o /dev/null too? Maybe its just your terminal 😏

PostgreSQL

17 Jun 2025 20:35

Does it happen without explain? Was thinking it might be preallocating a certain amount of memory based on the expected return size while psycopg allocates it more incrementally.

PostgreSQL

17 Jun 2025 18:17

The indexes can be expression indexes as well (without needing to include the expression columns in the index, since they are in its definition), here are a few examples most of which would be called in queries with 'and public and not deleted' - the 'cv' in the name indicates the following columns are used for covering purposes, not sorting: https://pastebin.com/mqMAC0Pk

PostgreSQL

17 Jun 2025 18:05

Is it possible to create pg_cron in any database in postgres aurora...when execute this script create extension pg_cron ...I got this message can only create extension in database postgres

PostgreSQL

17 Jun 2025 11:42

Oracle has index organized tables, there is no exact equivalent for this in Postgres. It could be because IOTs in Oracle also rarely used so there was no real need for developing this feature. There's a cluster command but it will order data only once.

PostgreSQL

17 Jun 2025 08:59

Create a column with an id and ORDER By in your queries

PostgreSQL

17 Jun 2025 08:52

Their mileage may vary...

PostgreSQL

17 Jun 2025 08:44

I mean when u insert a value in a table it is put at the end, i’d like to put it instantly in a specific order by a column value

PostgreSQL

17 Jun 2025 06:37

I take a look thanks

PostgreSQL

16 Jun 2025 21:44

Hi everyone,
I’m currently working with a PostgreSQL database with PostGIS extension, editing spatial layers directly in QGIS via a PostGIS connection.
I was wondering if anyone here uses QGIS this way and has experience managing value relations (not just value maps) directly within the database—specifically through pgAdmin4.
What I’m trying to achieve is a dependent dropdown menu in QGIS: that is, a field whose dropdown options are dynamically filtered based on the selection made in another field. Essentially, I want to replicate the behavior of Excel’s INDIRECT function, but managed entirely on the PostgreSQL side.
Has anyone implemented something like this before? Do you have examples, notes, or suggestions on how to structure such logic directly in PostgreSQL, so it can be used within QGIS?
Thanks in advance!

PostgreSQL

16 Jun 2025 16:17

That also sounds like you're triggering a significant amount of UPDATEs. Maybe reducing tge fill_factor increases your chance to get HOT updates.

PostgreSQL

16 Jun 2025 15:40

The default configuration is only usable with a small-mid database

PostgreSQL

16 Jun 2025 15:38

I am running postgres with default settings. And when the number of records in the tables increases, I have problems inserting new records because I am using materialized path approach for my hierarchy and I need to calculate a new path for all descendants. Where can I find out how to customize postgres to make it run faster, or can someone give me default settings that are suitable for most cases?

PostgreSQL

14 Jun 2025 12:37

You can scale this approach to every leaked password and every hash algorithm with all popular hash params and you'll get massive tables of precomputed hashes.

To prevent that we need to randomize hash, for that we add some random value to password before hashing and store this value openly. We do that only to prevent precomputed tables to work

PostgreSQL

17 Jun 2025 21:01

😊 Everything was > /dev/null

PostgreSQL

17 Jun 2025 20:57

Yes. None of these queries used EXPLAIN.

PostgreSQL

17 Jun 2025 19:06

I ran across an interesting pattern using psql. The test is: query a table (just two columns, an int and a float) with increasing values of LIMIT. It runs each LIMIT value 25 times to reduce noise.

psql has clear "breakpoints" where the time per query increases by 10%+ with an increase in LIMIT by 1. This isn't true of libpq in general (a similar test in python using psycopg has a smooth linear increase in term).

PostgreSQL

17 Jun 2025 18:10

If you have all the necessary values in an index in the right order for a query it can result in an index-only scan that only has to look at the index, which will be ordered - but you still need to specify order-by and define the index appropriately, e.g. order by date, count, name needs the index to be specified date, count, name not count, date, name.

Other, non-ordered items may also be included in the index to make it index-only while covering such queries if desired (but beware, these indexes can get very large and need to be maintained in the background, which may slow inserts, vacuum, use disk, RAM, etc). https://www.postgresql.org/docs/current/indexes-index-only-scans.html

PostgreSQL

17 Jun 2025 12:01

Likely it's all trade-offs. If the main table was index ordered, it's likely much slower to insert / update. For a gain of only speeding up the single indexed term.

PostgreSQL

17 Jun 2025 08:59

For example you can created an IDENTITY column

PostgreSQL

17 Jun 2025 08:55

Cant i organize witha btree file or indexed file?

PostgreSQL

17 Jun 2025 08:51

That's not how DBMS work... I mean, you can insert them that way, and if you never touch the data again, they'll stay that way 🤷

PostgreSQL

17 Jun 2025 08:43

Is there a way to insert records not like an heap but ordered

PostgreSQL

17 Jun 2025 06:16

Kindly Suggest/share me some postgres DBA Support Persons from Hyderabad I need Support postgres database administration.

PostgreSQL

16 Jun 2025 17:45

PostgreSQL Person of the Week interview with: Nisha Moond

https://postgresql.life/post/nisha_moond/

PostgreSQL

16 Jun 2025 15:41

Thanks for the tip for the start looks really like the way to go.

PostgreSQL

16 Jun 2025 15:39

You could try to use pgtune as a starter

PostgreSQL

14 Jun 2025 15:49

Should i hash the password in the api or directly in the dbms?

PostgreSQL

14 Jun 2025 12:35

Because md5 hash of "qwerty" is a well known value