Re: Need help to make space on my database
On Mon, 2024-04-29 at 15:45 +0200, Cocam' server wrote: > I need help to make space on my database. I have tables that are several GB > in size. > I used to use the VACUUM FULL VERBOSE command; but now, this command is too > greedy in > free space to be used and I'm looking for a way to make free space (given > back to the OS) If you don't have enough disk space for a VACUUM (FULL), your only option is pg_dump / DROP DATABASE / CREATE DATABASE / restore. Yours, Laurenz Albe
Re: Assistance needed for the query execution in non-public schema
On Tue, 2024-04-16 at 16:30 +0530, Sasmit Utkarsh wrote:
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'),
> coalesce(MAX(id), 1)) from mqa_flfo_cstr;
> ERROR: permission denied for sequence mqa_flfo_cstr_id_seq
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'),
> coalesce(MAX(id), 1)) from mqa_ffp_nval;
> ERROR: permission denied for sequence mqa_ffp_nval_id_seq
>
> msshctd=> \z mqa_flfo_cstr_id_seq
> Access privileges
> Schema | Name | Type | Access privileges |
> Column privileges | Policies
> -+--+--+-+---+--
> shc_tty | mqa_flfo_cstr_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|
> |
> | | | shc_uadmin=rU/pgddb_admin |
> |
> (1 row)
>
> msshctd=> \z mqa_ffp_nval_id_seq
> Access privileges
> Schema | Name | Type | Access privileges |
> Column privileges | Policies
> -+-+--+-+---+--
> shc_tty | mqa_ffp_nval_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|
> |
> | | | shc_uadmin=rU/pgddb_admin |
> |
> (1 row)
>
> Above query executes fine when I try to execute as user "pgddb_admin"(Super
> User).
> but when I switch to shc_uadmin, I see a permission error.
That's because the user is lacking the UPDATE privilege ("U" is USAGE).
> GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
> ERROR: unrecognized privilege type "alter" SQL state: 42601
There is no ALTER privilege. Try
GRANT UPDATE ON SEQUENCE mqa_flfo_cstr_id_seq, mqa_ffp_nval_id_seq TO
shc_uadmin;
Yours,
Laurenz Albe
Re: subquery plan rows = 1, but it's merge joined instead of index lookup
On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote: > Is there some complexity limit after which the planner starts acting dumb? Yes, "join_collapse_limit" and "from_collapse_limit". You can try increasing them. Yours, Laurenz Albe
Re: mystery with postgresql.auto.conf
On Wed, 2024-04-10 at 10:26 -0400, Tom Lane wrote: > Ian Lawrence Barwick writes: > > 2024年4月10日(水) 21:40 Matthias Apitz : > > > Why it does not give an error because the shared lib isn't there? > > > ALTER SYSTEM is a way of modifying the PostgreSQL configuration file > > via SQL; just as when you modify it manually, changes are not applied > > until you actually reload the configuration. > > See: https://www.postgresql.org/docs/current/sql-altersystem.html > > Even if you had issued a reload, you would not have noticed the > faulty setting without looking into the postmaster's log for > warning messages. The system wouldn't get in your face about it > until you did a postmaster restart. An alternative to looking at the log file is to SELECT * FROM pg_file_settings WHERE error IS NOT NULL; after you reload. Yours, Laurenz Albe
Re: Regarding: Replication of TRUNCATE commands is not working
On Tue, 2024-04-09 at 14:50 +0530, Arvind Raghuwanshi wrote: > I have tried to run the TRUNCATE command and found out that it's not getting > replicated using logical replication for pgsql. > I have also checked the schema change using pg_dump command but the schema > change also not getting detected for TRUNCATE command. > > However on pgsql logical replication doc page[1] , it's mentioned that > Replication of TRUNCATE commands is supported. > > [1] PostgreSQL: Documentation: 16: 31.6. Restrictions > > Any idea how we can solve this? What PostgreSQL version are you using? The feature was introduced in v11. How exactly is the publication defined? Perhaps TRUNCATE is excluded. Yours, Laurenz Albe
Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options
On Tue, 2024-04-09 at 15:49 +0530, Rajan Pandey wrote: > I was reading > https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION > and found that it mentions that Immutable Functions and Operators can > be pushed down using `extensions` option for foreign server. > > But it does not mention about TYPE. In the shippable.c/lookup_shippable() > function, I found that type is also pushed down. The comment only says that data types may be shippable, but not that they are actually shipped. Can you think of a case where a data type would be shipped to a foreign server? I wrote a foreign data wrapper, and I cannot think of such a case. Perhaps the function comment should be adjusted by removing the parenthesis or changing it to "(operator/function/...)". > Does this require updating the docs? Can I raise a PR to do so? Thank you! :) You would send a patch against the "master" branch to the pgsql-docs list for that. Yours, Laurenz Albe
Re: Not able to purge partition
On Tue, 2024-04-02 at 01:41 +0530, sud wrote: > On Thu, Mar 21, 2024 at 6:18 PM Laurenz Albe wrote: > > > [create some partitions, then drop a partition of the referenced table] > > > > > > SQL Error [P0001]: ERROR: cannot drop table > > > schema1.test_part_drop_parent_p2024_02_01 because other objects depend on > > > it > > > CONTEXT: SQL statement "DROP TABLE > > > schema1.test_part_drop_parent_p2024_02_01" > > However, out of curiosity, does this default foreign key setup i.e. foreign > keys > between the table (but not between the partitions) also make the data load > into > the child partitions slower ( as it must be then looking and validating the > presence > of the keys across all the partitions of the parent table)? The query checking the foreign key consistency should be somewhat more expensive, since it has to undergo partition pruning to figure out which partition to query. Yours, Laurenz Albe
Re: Timestamp conversion Error in dynamic sql script
On Tue, 2024-04-02 at 11:08 +0530, sud wrote: > Not able to figure out why it's giving below error during timezone conversion > while defining the partitions even though I used the typecast? > > [...] > DECLARE > start_date TIMESTAMP := '2022-01-01'; > [...] > TO_CHAR(start_date + i, '_MM_DD') > > *** > SQL Error [42883]: ERROR: operator does not exist: timestamp without time > zone + integer > Hint: No operator matches the given name and argument types. You might need > to add explicit type casts. As the error states, you cannot add an integer to a timestamp. What is the supposed meaning of that addition? Yours, Laurenz Albe
Re: pg_rewind after promote
On Thu, 2024-03-28 at 17:17 +0100, Emond Papegaaij wrote: > Op do 28 mrt 2024 om 16:21 schreef Laurenz Albe : > > On Thu, 2024-03-28 at 15:52 +0100, Emond Papegaaij wrote: > > > pg_rewind: source and target cluster are on the same timeline pg_rewind: > > > no rewind required > > > > > > If we ignore the response from pg_rewind, streaming will break on the > > > node that reported > > > no rewind was required. On the new primary, we do observe the database > > > moving from timeline > > > 21 to 22, but it seems this takes some time to materialize to be > > > observable by pg_rewind. > > > > This must be the problem addressed by commit 009746 [1]. > > > > A temporary workaround could be to explicitly trigger a checkpoint right > > after > > promotion. > > Would this be as simple as sending a CHECKPOINT to the new primary just after > promoting? > This would work fine for us until we've migrated to v16. Yes, that would be the idea. Yours, Laurenz Albe
Re: pg_rewind after promote
On Thu, 2024-03-28 at 15:52 +0100, Emond Papegaaij wrote: > * we detach the primary database backend, forcing a failover > * pgpool selects a new primary database and promotes it > * the other 2 nodes (the old primary and the other standby) are rewound >and streaming is resumed from the new primary > * the node that needed to be taken out of the cluster (the old primary) >is shutdown and rebooted > > This works fine most of the time, but sometimes we see this message on one of > the nodes: > pg_rewind: source and target cluster are on the same timeline pg_rewind: no > rewind required > This message seems timing related, as the first node might report that, > while the second reports something like: > pg_rewind: servers diverged at WAL location 5/F28AB1A8 on timeline 21 > pg_rewind: rewinding from last common checkpoint at 5/F27FCA98 on timeline 21 > pg_rewind: Done! > > If we ignore the response from pg_rewind, streaming will break on the node > that reported > no rewind was required. On the new primary, we do observe the database moving > from timeline > 21 to 22, but it seems this takes some time to materialize to be observable > by pg_rewind. > > 1. Is my observation about the starting of a new timeline correct? > 2. If yes, is there anything we can do during to block promotion process > until the new >timeline has fully materialized, either by waiting or preferably forcing > the new >timeline to be started? This must be the problem addressed by commit 009746 [1]. You'd have to upgrade to PostgreSQL v16, which would be a good idea anyway, given that you are running v12. A temporary workaround could be to explicitly trigger a checkpoint right after promotion. Yours, Laurenz Albe [1]. https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=009746825090ec7194321a3db4b298d6571e
Re: Is this a buggy behavior?
On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote: > > The reason it doesn't give you a warning is that by the time it would > > be in a position to, it's forgotten that you explicitly said NULL. > > How can that be forgotten? This information ends up in the data > catalogue eventually! It *is* stored in the catalog. But if you add a primary key, that is tantamount to saying ALTER TABLE tab ALTER col SET NOT NULL; So it overrides the definition you made before. Or would you want the above statement to cause an error just because somebody had defined the column nullable before? Perhaps you'd want that, but you are one of the few who do. You'll have to get used to the way it is. Yours, Laurenz Albe
Re: Not able to purge partition
On Mon, 2024-03-25 at 02:54 +0530, veem v wrote: > Can you please suggest some docs which shows the way we should > do the partition maintenance (if not using pg_partman)? man cron > Actually , I am not able to visualize the steps here. Do you mean > to say , we will just create the partition tables without any foreign > key constraints first. Then create parent table future partitions first > (say 10 days partitions) and then child table future 10 days partitions > manually and during that define the foreign key constraints , indexes > etc. Here , how to exactly find the exact matching parent partitions > so as to create the foreign keys one to one I don't see what's so hard about it. When the time is there to create a new partition, create a partition for both the parent and the child table and define a foreign key between them. Yours, Laurenz Albe
Re: Not able to purge partition
On Sun, 2024-03-24 at 00:37 +0530, veem v wrote: > > Instead, use foreign keys between the partitions. > > I am struggling to understand how to maintain those partitions then? > As because we were planning to use pg_partman for creating and dropping > partitions automatically without much hassle. I understand. But do you want to pursue a way that is not working well, just because the tool you chose cannot handle it? Yours, Laurenz Albe
Re: Not able to purge partition
On Sat, 2024-03-23 at 22:41 +0530, veem v wrote: > 1)As we see having foreign key defined is making the detach partition run > for minutes(in our case 5-10minutes for 60 million rows partition), so > how to make the parent table partition detach and drop work fast in such > a scenario while maintaining the foreign key intact? I told you: don't do it. Instead, use foreign keys between the partitions. Yours, Laurenz Albe
Re: uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen
On Fri, 2024-03-22 at 16:07 +0530, Vijaykumar Jain wrote: > On Fri, 22 Mar 2024 at 15:39, Laurenz Albe wrote: > > On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote: > > > We are unable to take the backup of our database. While taking backup we > > > are getting the same error. > > > > > > psql: error: connection to server at "localhost" (::1), port 5014 failed: > > > FATAL: pg_attribute catalog is missing 1 attribute(s) for relation OID > > > 2662 > > > > Then you got severe data corruption. This is the index > > "pg_class_oid_index", > > and corrupted metadata make recovery difficult. > > > > If I were you, I would seek professional help. > > But first, stop working with this database immediately. > > Stop the server and take a backup of all the files in the data > > directory. > > Do we have an option that op has a replica running and the bug has not > propagated to the replica No. Feel free to check on the standby, it should have the same problem. Yours, Laurenz Albe
Re: pg_locks-exclusivelock for select queries
On Fri, 2024-03-22 at 12:43 +0530, arun chirappurath wrote: > I am running below query on a database. why is it creating a exclusive lock > on a virtualxid? > I am running some SELECT queries and its creating an ExclusiveLock in > virtualxid? is this normal? > > SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age > FROM pg_stat_activity > WHERE state <> 'idle' > --AND query NOT LIKE '% FROM pg_stat_activity %' > ORDER BY age; > > |locktype > |database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid > |mode |granted|fastpath|waitstart| > |--||||-|--|-|---|-||--|--|---|---||-| > |relation |58,007 |12,073 | | | | | | > | |5/165 |21,912|AccessShareLock|true |true | > | > |virtualxid| | | | |5/165 | | | > | |5/165 |21,912|ExclusiveLock |true |true | > | That's normal. Every transaction has an exclusive lock on its own transaction ID. Yours, Laurenz Albe
Re: uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen
On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote: > We are unable to take the backup of our database. While taking backup we are > getting the same error. > > psql: error: connection to server at "localhost" (::1), port 5014 failed: > FATAL: pg_attribute catalog is missing 1 attribute(s) for relation OID 2662 Then you got severe data corruption. This is the index "pg_class_oid_index", and corrupted metadata make recovery difficult. If I were you, I would seek professional help. But first, stop working with this database immediately. Stop the server and take a backup of all the files in the data directory. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen
On Fri, 2024-03-22 at 10:56 +0530, Daulat wrote: > We recently started seeing an error “ERROR: uncommitted xmin 3100586 > from before xid cutoff 10339367 needs to be frozen” on our user tables. > I’m unable to do ‘vacuum’, ‘vacuum freeze’ or ‘vacuum full’ on Postgres 14.4 > running on a windows environment. > > Error: > > first come this ERROR: uncommitted xmin 3100586 from before xid cutoff > 10339367 needs to be frozen > CONTEXT: while scanning block 1403 offset 8 of relation > "pg_catalog.pg_attribute" Update to 14.latest; perhaps that data corruption was caused by a bug that is already fixed. Upgrading won't get rid of the error though (I think). The seasy way is to dump the database and restore it to a new database. Yours, Laurenz Albe
Re: Not able to purge partition
On Thu, 2024-03-21 at 22:50 +0530, veem v wrote: > So when you mentioned "to create the foreign keys *not* between the > partitioned table but between the individual partitions" , can that > be done using the same "partman.create_parent" procedure and automated > cron job schedule or has to be done any other way manually ? I don't know the capabilities of partmen, but I would be surprised if it could automatically create foreign keys on the partitions. Yours, Laurenz Albe
Re: Not able to purge partition
On Thu, 2024-03-21 at 11:07 +0530, veem v wrote: > CREATE TABLE schema1.test_part_drop_parent > ( > c1 varchar(36) NOT NULL , > c2_part_date timestamp with time zone NOT NULL , > c3 numeric(12,0) NOT NULL , > CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date) > ) PARTITION BY RANGE (c2_part_date); > > CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 ASC); > > > CREATE TABLE schema1.test_part_drop_child > ( > C1_child varchar(36) NOT NULL , > C1 varchar(36) NOT NULL , > c2_part_date timestamp with time zone NOT NULL , > C3 numeric(12,0) NOT NULL , > CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date), > CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) REFERENCES > schema1.test_part_drop_parent(c1,c2_part_date) > ) PARTITION BY RANGE (c2_part_date); > > CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1, > c2_part_date); > > CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( > c1_child, c2_part_date); > > [create some partitions, then drop a partition of the referenced table] > > SQL Error [P0001]: ERROR: cannot drop table > schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it > CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01" That's normal. If you create a foreign key constraint to a partitioned table, you can no longer drop a partition of the referenced table. What you *can* do is detach the partition and then drop it, but detatching will be slow because PostgreSQL has to check for referencing rows. The best solution is to create the foreign key *not* between the partitioned tables, but between the individual table partitions. That should be easy if you have the same partition boundaries for both. Then you can simply drop a partition from both tables at the same time. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Single-User Mode oid assignment
On Sat, 2024-03-16 at 14:14 +0300, PetSerAl wrote: > I have following script, which initialize two clusters: > > echo CREATE ROLE new_user WITH LOGIN;>init_cluster.sql > echo CREATE DATABASE new_database WITH OWNER = new_user;>>init_cluster.sql > initdb -D case1 -U postgres > pg_ctl start -D case1 -w > psql "host=localhost dbname=postgres user=postgres" pg_ctl stop -D case1 -m smart -w > initdb -D case2 -U postgres > postgres --single -D case2 postgres > In case1 I have following oid assigned: > new_user: 16384 > new_database: 16385 > > In case2: > new_user: 15062 > new_database: 15063 > And pgAdmin does not show new_database in the server tree. > Documentation says following: > > OIDs assigned during normal database operation are constrained to be 16384 > > or higher. This ensures that the range 1—16383 is free for OIDs > > assigned automatically by genbki.pl or during initdb. > > Is such difference in oid assignment in Single-User Mode expected? Yes; see the comment in GetNewObjectId(): /* * Check for wraparound of the OID counter. We *must* not return 0 * (InvalidOid), and in normal operation we mustn't return anything below * FirstNormalObjectId since that range is reserved for initdb (see * IsCatalogRelationOid()). Note we are relying on unsigned comparison. * * During initdb, we start the OID generator at FirstGenbkiObjectId, so we * only wrap if before that point when in bootstrap or standalone mode. * The first time through this routine after normal postmaster start, the * counter will be forced up to FirstNormalObjectId. This mechanism * leaves the OIDs between FirstGenbkiObjectId and FirstNormalObjectId * available for automatic assignment during initdb, while ensuring they * will never conflict with user-assigned OIDs. */16384 Object IDs are forced to be 16384 or above "after normal postmaster start". Yours, Laurenz Albe
Re: Seeing high query planning time on Azure Postgres Single Server version 11.
On Fri, 2024-03-15 at 00:31 +0530, hassan rafi wrote: > We have migrated to postgres version 16.1, but still due to very high update > activity on our DB, we are seeing elevated response times, though now the > planning time is less. > > catalog-v2=> explain (analyze, verbose, settings, buffers) SELECT > products_inventory_delta.upc FROM products_inventory_delta WHERE > products_inventory_delta.modality = 'pickup' AND > products_inventory_delta.store_id = '70600372' ORDER BY upc DESC LIMIT 51 > OFFSET 3; > > QUERY PLAN > > -- > Limit (cost=1450.68..1450.73 rows=1 width=14) (actual > time=5049.115..5049.116 rows=0 loops=1) > Output: upc > Buffers: shared hit=33359 read=6590 dirtied=9379 > -> Index Only Scan Backward using products_inventory_delta_pkey on > public.products_inventory_delta (cost=0.57..1450.68 rows=28606 width=14) > (actual time=1.056..5047.472 rows=28299 loops=1) > Output: upc > Index Cond: ((products_inventory_delta.store_id = '70600372'::text) > AND (products_inventory_delta.modality = 'pickup'::modality)) > Heap Fetches: 16840 > Buffers: shared hit=33359 read=6590 dirtied=9379 > Settings: effective_cache_size = '192GB', jit = 'off', random_page_cost = > '2', work_mem = '2097151kB' > Query Identifier: 220372279818787780 > Planning Time: 0.062 ms > Execution Time: 5049.131 ms Your problem are probably the "Heap Fetches: 16840". If you VACUUM the table, the performance should improve. The best solution is to make sure that autovacuum processes that table more often: ALTER TABLE public.products_inventory_delta SET (autovacuum_vacuum_scale_factor = 0.01); Yours, Laurenz Albe
Re: Is it possible to keep track of SELECTs?
On Tue, 2024-03-12 at 16:34 +0100, Dominique Devienne wrote: > PostgreSQL tables and indexes are ultimately files. > And there are ways to map them to file names, I've seen in the past. > So isn't it possible, provided the filesystem tracks last access time, to > infer when a table was accessed the last time? Sure, but that won't necessarily tell you when the table was last used. It could be autovacuum or autoanalyze that last accessed your table. Also, if the data happen to be in shared buffers, the file won't be read. Using the PostgreSQL statistics is a much better method. Yours, Laurenz Albe
Re: alter table xxx alter column yyy set (n_distinct=nnnn );
On Mon, 2024-03-11 at 14:26 +, Tefft, Michael J wrote: > I tried setting > > alter table ctrg.xxx alter column p_id set (n_distinct_inherited=-0.033 ); > alter table ctrg.xxx alter column pi_id set (n_distinct_inherited=-0.045 ); > alter table ctrg.xxx alter column i_id set (n_distinct_inherited=-0.0002 ); > > I then ran > analyze xxx; > but saw no change in pg_stats.n_distinct for xxx. Well, it works on my PostgreSQL v16. Yours, Laurenz Albe
Re: alter table xxx alter column yyy set (n_distinct=nnnn );
On Mon, 2024-03-11 at 12:36 +, Tefft, Michael J wrote: > I have been struggling to set n_distinct on a few table columns, and confirm > that my changes have actually been accepted. > > I have a 400-million row table with 81 partitions. PostgreSQL version is > 14.11. > Column p_id has 13 million distinct values but pg_stats says n_distinct is > only 82k. > Column pi_id has 18 million distinct values but pg_stats says n_distinct is > only 29k. > Column i_id has 70k distinct values but pg_stats says n_distinct is only 1800. > > I tried: > alter table xxx alter column p_id set (n_distinct=-0.033 ); > alter table xxx alter column pi_id set (n_distinct=-0.045 ); > alter table xxx alter column i_id set (n_distinct=-0.0002 ); > > All ran without error, but pg_stats shows no changes. I tried: > > alter table xxx alter column p_id set (n_distinct=13118955 ); > alter table xxx alter column pi_id set (n_distinct=18059179 ); > alter table xxx alter column i_id set (n_distinct=69911 ); > > Again, all ran without error, but pg_stats shows no changes. > > I saw somewhere (but can’t locate the reference today) that setting > n_distinct takes effect after the next ANALYZE. I tried ANALYZE xxx and > VACUUM ANALYZE xxx but the settings never appeared to take effect. > > I tried increasing “statistics” on the columns to 1000 and running ANALYZE; > this produced somewhat-higher n_distinct values but still far from accurate > and my manually-set values still did not appear. > > How can I get these values to take effect? If it is a partitioned table, set "n_distinct_inherited" on the column, then run ANALYZE. Yours, Laurenz Albe
Re: could not open file "global/pg_filenode.map": Operation not permitted
On Mon, 2024-03-11 at 11:11 +0100, Nick Renders wrote: > We have several other machines with a similar setup, but this is the only > server that has this issue. > > [...] Cluster A is actually a backup from another Postgres server that is > restored on a > daily basis via Barman. This means that we login remotely from the Barman > server over SSH, > stop cluster A's service (port 165), clear the Data folder, restore the > latest back into > the Data folder, and start up the service again. > Cluster B's Data and service (port 164) remain untouched during all this > time. This is > the cluster that experiences the intermittent "operation not permitted" issue. > > Over the past 2 weeks, I have suspended our restore script and the issue did > not occur. > I have just performed another restore on cluster A and now cluster B is > throwing errors in the log again. > > Any idea why this is happening? It does not occur with every restore, but it > seems to be related anyway. I don't know Barman, but with that incomplete description anybody will have problems determining the cause. For example, how are A and B connected? Yours, Laurenz Albe
Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
On Sat, 2024-03-09 at 23:08 +0400, Ilya Basin wrote: > I have a list of bigint keys and I need to retrieve rows by these keys. > Normally, I would split this list into pages of size 900 and perform > several `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary > ORM we use can only produce this SQL: > `SELECT ... WHERE (key=$1 or key=$2 or ...)`. > Surprisingly, PostgreSQL planner treats these two SQLs differently: > > Is it possible to configure PostgreSQL 12.16 to treat the second query as the > first? No, that is currently not possible. Yours, Laurenz Albe
Re: Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy
On Tue, 2024-03-05 at 11:37 +, M Tarkeshwar Rao wrote: > We want to use libpq library APIs only. Is there any support of keepalive at > client side? Yes, there is. Just add "keepalives_idle=60" to the connection string. See https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-KEEPALIVES-IDLE Yours, Laurenz Albe
Re: When manual analyze is needed
On Mon, 2024-03-04 at 10:16 +0530, veem v wrote: > So the partitioned table stats is nothing but the rolledover stats of all the > partitions. > As you mentioned, autoanalyze only works for child partitions but not the > partition tables, > so does it mean we have to schedule some jobs manually(through some > scheduletr like pg_cron) > to analyze these partitioned tables at certain time intervals to keep those > up to date for > partitioned tables? Something like that, yes. > And won't that scan all the child partitions again to have the stats > aggregated/rolled > over from all the child partitions level to the table level? Yes. > Additionally if a query was working fine but suddenly takes a suboptimal plan > because > of missing stats , do we have any hash value column on any performance view > associated > with the queryid which we can refer to see past vs current plans difference > and identify > such issues quickly and fix it? Not that I know of. Yours, Laurenz Albe
Re: Guarantees/Semantics of pg_stats
On Sat, 2024-03-02 at 22:29 +, Baziotis, Stefanos wrote: > Can I maybe get accurate information if the column has an index? In other > words, > are there any type of indexes through which I can get the number of distinct > values or the values themselves, without needing to scan the column? No. Yours, Laurenz Albe
Re: When manual analyze is needed
On Mon, 2024-03-04 at 01:33 +0530, veem v wrote: > We see in one of the RDS postgres instances, from pg_stat_user_tables , > the auto vacuum and auto analyze happening on the tables without our > manual intervention. That's exactly the idea behind autovacuum. > So is auto vacuum analyze is sufficient to make sure optimal stats > and unbloated table structure in place Yes, it is sufficient. If you have a busy database, you may have to tune autovacuum to keep up. The only things that require manual ANALYZE are 1. partitioned tables (autoanalyze will collect statistics on the partitions, but not the partitioned table itself) 2. after you create an index on an expression (otherwise you have to wait until autoanalyze runs to get statistics on the indexed expression) Yours, Laurenz Albe
Re: Orphan files filling root partition after crash
On Mon, 2024-03-04 at 00:04 +0100, Peter J. Holzer wrote: > On 2024-02-28 20:30:35 +0100, Dimitrios Apostolou wrote: > > On Wed, 28 Feb 2024, Laurenz Albe wrote: > > > On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote: > > > > So what is the moral of the story? How to guard against this? > > > > > > Monitor disk usage ... > > > > It happened *fast*. And it was quite a big suprise coming > > from "just" a disk-full situation. > > Been there. > > To prevent this in the future I wrote a small script to monitor disk > space (on multiple hosts and multiple file systems) every few seconds > and invoke another script (which just terminates all database > connections - a bit drastic but effective) if free space runs low: > https://github.com/hjp/platzangst And I maintain that "temp_file_limit" is the best solution. Yours, Laurenz Albe
Re: Content for talk on Postgres Type System at PostgresConf
On Fri, 2024-03-01 at 08:14 -0800, Adrian Klaver wrote: > [pointers to the documentation] I know. I was not searching for help with PostgreSQL, I was trying to point out the surprising behavior of "character" as a suggestion for the talk. Yours, Laurenz Albe
Re: Guarantees/Semantics of pg_stats
On Sat, 2024-03-02 at 07:41 +, Baziotis, Stefanos wrote: > I'm interested in learning more about the guarantees/semantics of pg_stats. > For example, is there a guarantee that the n_distinct and most_common_vals > fields will take into account any values appearing more than M times or > maybe with frequence more than f? In what cases will n_distinct and > most_common_vals will miss some values? Table Statistics are not exact. They are collected from a random sample of the data, so they are never guaranteed to be exact. Their purpose is to estimate the result row count and cost of execution plan steps. You can never use them as proof. Yours, Laurenz Albe
Re: Content for talk on Postgres Type System at PostgresConf
On Thu, 2024-02-29 at 13:38 -0800, Guyren Howe wrote:
> what are the misconceptions, or where might I find them for myself?
In addition to what was already said:
> My current understanding:
> * character is fixed-length, blank-padded. Not sure when you’d
>want that, but it seems clear. Is the name just confusing?
I find the semantics confusing:
test=> SELECT 'a'::character(10);
bpchar
a
(1 row)
Ok, it is 10 characters long.
test=> SELECT length('a'::character(10));
length
1
(1 row)
Or is it?
test=> SELECT 'a'::character(10) || 'b'::character(10);
?column?
══
ab
(1 row)
And why is the result not 20 characters long, with spaces between "a" and "b"?
Best avoid "character".
> * timestamptz is just converted to a timestamp in UTC. Folks might
>imagine that it stores the time zone but it doesn’t.
Yes, and I find that lots of people are confused by that.
You could talk about the interaction with the "timezone" parameter, and
that it is not so much a timestamp with time zone, but an "absolute timestamp",
and in combination with "timestamp" a great way to let the database handle
the difficult task of time zone conversion for you.
Yours,
Laurenz Albe
Re: Voluntary Product Assessment For pgAdmin 8.3
On Thu, 2024-02-29 at 07:31 -0500, Anthony Codjoe - IQ-C wrote: > Your product, pgAdmin 8.3, is a candidate technology for use within the > U.S. General Services Administration (GSA) enterprise environment. > Technologies under review by GSA’s Office of the Chief Technology Officer > (OCTO) > must be accompanied by a completed Voluntary Product Assessment Template > (VPAT). This is a mailing list for PostgreSQL, not for pgAdmin. However, both are open source software. Please refer to your organization's rules for the correct procedure concerning open source software. There is no entity that would fill in your forms. Yours, Laurenz Albe
Re: Non-Stored Generated Columns
On Thu, 2024-02-29 at 12:07 +0100, Dominique Devienne wrote: > But I'm sure indexes on columns "not used at all in a statement" are > eliminated early and easily/cheaply, > w/o even getting into more complex consideration like statistics and co. > Aren't they? You may want a "SELECT count(*) FROM tab" to consider an index-only scan on a small index, right? I'm not saying that it is a large overhead, but if you actually have dozens of indexes, it can make processing slower. Yours, Laurenz Albe
Re: Non-Stored Generated Columns
On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote: > On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe > wrote: > > Honestly, I'm not sure why supporting the non-stored variant of generated > columns is so controversial... > > > I am sure there are some use cases for "virtual" generated columns, and > > I don't think that a patch that tries to implement them will be rejected > > out of hand. It's just that I don't think it is a very important feature. > > Fair enough. And a reaction I expected when I first posted. > The outright rejection of it ever being useful, that's what surprised me. I don't think anybody rejected the idea unconditionally. But there is always a trade-off between the maintenance burden and the usefulness of a feature. All that was said is that the usefulness is low. > > You could use conditional indexes, but then you have to make sure that > > the optimizer knows it can use these indexes. > > I'm not following. Are you saying the planner is not good at that on its own? > I need to do something from my end??? I wasn't sure, but now I tested: a conditional index can also be used by a cascading delete or update. So that's not a worry. > > The sum of the sizes of these indexes shouldn't exceed the size of an > > unconditional index by much, but they would still be more expensive: > > each statement would have to look through all the indexes to decide > > which ones it can use and which ones not. > > Something I maybe didn't make clear. The XArc virtual columns are never > accessed. Yes, they are. The query planner considers all indexes. Yours, Laurenz Albe
Re: Content for talk on Postgres Type System at PostgresConf
On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote: > I am to talk about Postgres’s type system at PGConf: > > https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system > > I picked the issue because I think it’s poorly understood, greatly > under-discussed, and an excellent way to empower postgres users. > > What would you want to see in such a talk? It might be good to explain how "timestamp with time zone" works. That's often confusing for beginners, because it is different from other databases and arguably deviates from the SQL standard. Perhaps it would be good to warn people about using data types like "character", "time with time zone" and "money". Yours, Laurenz Albe
Re: Non-Stored Generated Columns
On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote: > On Wed, Feb 28, 2024 at 8:11 PM Tom Lane wrote: > > Dominique Devienne writes: > > > Views can have foreign-keys? > > > > Surely you'd put the FK on the underlying table. > > Again, the FKs are on the *generated* columns. So > > > > Generated view columns be indexed? > > > > [...[ it's hard to see much use-case here > > The use-case is Polymorphic Foreign Key (aka PFKs). > > For NULL'able PFKs, you have two read-write concrete columns, the ID > and the CODE (aka TYPE) of the relation. > Then you have 1 generated column (that I call XArc) per possible CODE/TYPE > value (for that specific relation), which is either ID or NULL, depending > on the CODE. > And the "regular" FK is on that generated (and ideally Non-Stored, the > whole point of this thread), since it points to a single table now. > And since these FKs are CASCADE'ing, you want them INDEX'ed of course. I concede that that is a borderline use case. But you may as well have the foreign key columns as actual columns (or as stored generated columns). If that's a handful or so, it shouldn't be a big problem. If you have hundred types (hundred referenced tables), you'd end up with hundreds of indexes, and that already looks like a very bad idea (both DML and query planning time will be affected). Polymorphic Foreign Keys are nigh impossible to model well in SQL, and I doubt that non-stored generated columns will solve that. > For NOT NULL PFKs, that more of a PITA [...] See? > So has Ron says. If SQLite and Oracle has them, that's not an accident. Oracle has a lot of things that are not enviable... I am sure there are some use cases for "virtual" generated columns, and I don't think that a patch that tries to implement them will be rejected out of hand. It's just that I don't think it is a very important feature. > PS: Since we are on this subject, I'd like to take an opportunity to > ask a question I've had for a long time, but didn't have time to research. > For a given PFK, all its XArc columns are mutually-exclusive (by design, > see above). So their indexes are full of NULLs. > Are the NULLs stored in these indexes, consuming extra space? In our use > case, we don't care about NULLs. > So should be use an expression index to explicitly not index the NULLs? > The sum of indexes for a given PFK should in theory be the same as a > single index, whatever the cardinality of the PFK, "logically". Yes, NULLs are stored in indexes, just like everything else. You could use conditional indexes, but then you have to make sure that the optimizer knows it can use these indexes. The sum of the sizes of these indexes shouldn't exceed the size of an unconditional index by much, but they would still be more expensive: each statement would have to look through all the indexes to decide which ones it can use and which ones not. Yours, Laurenz Albe
Re: Orphan files filling root partition after crash
On Wed, 2024-02-28 at 20:30 +0100, Dimitrios Apostolou wrote: > Lucky you. It should have been "relfilenode" rather than "oid", > > and some catalog tables don't have their files listed in the catalog, > > because they are needed *before* the database can access tables. > > I actually double checked that the filenames don't appear anywhere in > SELECT * FROM pg_class > and that the files were multi-GB in size including all the > 1GB-pieces. But luck was definitely a part of the equation, I didn't know > that the files might be accessed before tables (at db startup?) or that > "relfilenode" would be more appropriate. Why is that, where can I read > more? I see that many (but not all) rows in pg_class have oid=relfilenode > but for many rows relfilenode=0 which is meaningless as filename. If you are curious about such things, start reading the source. The object ID is immutable, and initially the filenode is the save, but it changes whenever the table is rewritten (TRUNCATE, ALTER TABLE, VACUUM (FULL), ...). Yours, Laurenz Albe
Re: Non-Stored Generated Columns
On Wed, 2024-02-28 at 16:09 +0100, Dominique Devienne wrote: > From https://www.postgresql.org/docs/16/ddl-generated-columns.html: > > PostgreSQL currently implements only stored generated columns > > We use generated columns extensively. > And we have foreign-keys attached to those generated columns. > The fact they are always Stored thus wastes space in our case. > Any chance PostgreSQL might gain actual virtual / non-stored generated > columns soon? Ever? > > For reference, both Oracle and SQLite have virtual / non-stored columns. > And both support FKs and indexes on those too. > Would be great to have feature parity on this particular point, eventually. Isn't almost all of that functionality covered by a view? Yours, Laurenz Albe
Re: Orphan files filling root partition after crash
On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote: > yesterday I was doing: > > ALTER TABLE x ADD UNIQUE ... , ADD PRIMARY KEY ...; > > The table is almost a billion rows long but lies in its own TABLESPACE > that has plenty of space. But apparently the ALTER TABLE command is > writing a lot to the default tablespace (not the temp_tablespace, that is > already moved to a different partition). > > That quickly filled up the 50GB free space in my root partition: > > > 20:18:04.222 UTC [94144] PANIC: could not write to file > "pg_wal/xlogtemp.94144": No space left on device > [...] > 20:19:11.578 UTC [94140] LOG: WAL writer process (PID 94144) was terminated > by signal 6: Aborted > 20:19:11.578 UTC [94140] LOG: terminating any other active server processes > > > After postgresql crashed and restarted, the disk space in the root > partition was still not freed! I believe this is because of "orphaned > files" as discussed in mailing list thread [1]. > > [1] > https://www.postgresql.org/message-id/CAN-RpxDBA7HbTsJPq4t4VznmRFJkssP2SNEMuG%3DoNJ%2B%3DsxLQew%40mail.gmail.com > > I ended up doing some risky actions to remediate the problem: Find the > filenames that have no identically named "oid" in pg_class, and delete > (move to backup) the biggest ones while the database is stopped. > Fortunately the database started up fine after that! Lucky you. It should have been "relfilenode" rather than "oid", and some catalog tables don't have their files listed in the catalog, because they are needed *before* the database can access tables. > So what is the moral of the story? How to guard against this? Monitor disk usage ... The root of the problem is that you created the index in the default tablespace. You should have ALTER TABLE x ADD UNIQUE ... USING INDEX TABLESPACE bigtblspc; > Needless to say, I would have hoped the database cleaned-up after itself > even after an uncontrolled crash, or that it provided some tools for the > job. (I tried VACUUM FULL on the table, but the orphaned files did not go > away). That is not so simple... Also, it would slow down crash recovery. But I agree that it would be nice to have a tool that reports or cleans up orphaned files. Yours, Laurenz Albe
Re: Orphan table files at data/base/
On Wed, 2024-02-28 at 15:44 +0200, Riivo Kolka wrote: > I was an unfortunate sequence of commands (all in single transaction) > > DROP TABLE huge; > CREATE TABLE huge AS... (huge PostGIS ST_Union operation); > CREATE INDEX ON huge USING GIST (geometry); > > by a well-meaning user, that caused a crash+recovery: > > server process (PID 92411) was terminated by signal 9: Killed > terminating any other active server processes > all server processes terminated; reinitializing > database system was not properly shut down; automatic recovery in progress > > And that left behind 280GB of files (of TABLE huge): > data/base/16384/2403959 > ... > data/base/16384/2403959.282 > > > SELECT pg_filenode_relation(0,2403959); > -- returns NULL > > may I do > sudo rm data/base/2403959* > ? If you *know* these files belong to the table created with CREATE TABLE huge AS SELECT ... then you can do that. If you are not 100% certain, go the safe way and use dump/restore to a new database. Then DROP DATABASE on the old database, and all orphaned files will be gone. Yours, Laurenz Albe
Re: could not open file "global/pg_filenode.map": Operation not permitted
On Mon, 2024-02-26 at 15:14 +0100, Nick Renders wrote: > We have a Postgres server that intermittently logs the following: > > 2024-02-26 10:29:41.580 CET [63962] FATAL: could not open file > "global/pg_filenode.map": Operation not permitted > 2024-02-26 10:30:11.147 CET [90610] LOG: could not open file > "postmaster.pid": Operation not permitted; continuing anyway > > This has happened 3 times over the last 2 weeks now, without any indication > what caused it. > The privileges of those 2 files are all in order. > When this happens, the server is no longer accessible, and we need to restart > the service (pg_ctl restart). > Once restarted, Popstgres runs fine again for a couple of days. > > We are running PostgreSQL 16.2 on macOS 14.3.1. Perhaps that is some kind of virus checker or something else that locks files. Yours, Laurenz Albe
Re: Creating table and indexes for new application
On Fri, 2024-02-23 at 02:05 +0530, yudhi s wrote: > postgres version 15+ database. And it would be ~400million transactions/rows > per day in the > main transaction table and almost double in the multiple child tables and > some child tables > will hold lesser records too. > > We are considering all of these tables for partitioning by the same > transaction_date column > and it would be daily partitions. We have some questions, > > 1)While creating these tables and related indexes, do we need to be careful > of defining any > other storage parameters like tablespaces etc Or its fine to make those > table/indexes > aligned to the default tablespace only? and are there any constraints on > tablespace size , > as we will have 100's GB of data going to be stored in each of the daily > partitions? There are no constraints on the size of a tablespace other than the limits of the underlying file system. Use the default tablespace. > 2)Should we be creating composite indexes on each foreign key for table2 and > table3, because > any update or delete on parent is going to take lock on all child tables? Every foreign key needs its own index. A composite index is only appropriate if the foreign key spans multiple columns. > 3)We were thinking of simple Btree indexes to be created on the columns based > on the search > criteria of the queries. but the indexes doc i see in postgres having > INCLUDE keywords also > available in them. So I'm struggling to understand a bit, how it's adding > value to the read > query performance if those additional columns are not added explicitly to > the index but are > part of the INCLUDE clause? Will it give some benefit in regards to the > index storage space? > or should we always keep all the columns in the index definition itself > other than some > exception scenario? Struggling to understand the real benefit of the > INCLUDE clause. The benefits of putting a column into INCLUDE is twofold: 1) The column only has to be stored in the leaf pages, since it is not used for searching. That makes the intermediate index entries smaller, which causes a wider fan-out of the index, which in turn makes the tree shallower and hence faster to search. 2) It is an implicit documentation that the column is not to be used for searching. Yours, Laurenz Albe
Re: Postgresql assistance needed
On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote: > Is there any configuration/query that can be checked to verify if > "shc_uadmin" has the correct path set? The SQL statement "SHOW search_path" would return the current setting. But look at the error message first. Yours, Laurenz Albe
Re: Postgresql assistance needed
On Thu, 2024-02-22 at 17:18 +0530, Sasmit Utkarsh wrote:
> Perhaps, the issue I wanted to highlight here is that I get the same entry
> working
> when I switch user to "pgddb_admin" and not when change user with same
> privileges
> as PGUSER "shc_uadmin" I get the message in the error log like
> "<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa]"
> even though i see the next val from db. Please see below when logged in to
> the db
> and snippet of the code lines where it was throwing the error.
>
> [shc_user@cucmtpccu1 ~]$ psql "host= cucmpsgsu0.postgres.database.azure.com
> port=5432 dbname=mshcd user=shc_uadmin password=abc123 sslmode=require
> options=--search_path=shc,public"
> psql (16.1, server 15.4)
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> compression: off)
> Type "help" for help.
> mshcd=> SELECT nextval('unassigned_pool_fa');
> nextval
> -
> -1811939322
> (1 row)
>
>
> code snippet:
> 3555 } else {
> 3556 LOG_WARN("No File_address is found with
> status=free");
> 3557 //If no free address is found, get the next
> value available address from the sequence
> 3558 PQclear(res);
> 3559 res = PQexec(conn, "SELECT
> nextval('unassigned_pool_fa');");
> 3560 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
> 3561 LOG_ERROR("SELECT nextval Failed");
> 3562 LOG_DEBUG("ROLLBACK TRANSACTION");
> 3563 res = PQexec(conn,"ROLLBACK
> TRANSACTION");
> 3564 PQclear(res);
> 3565 return 0;
> 3566 }
To debug that, get the actual error message using PQerrorMessage().
That should tell you what is going on.
Perhaps the sequence is not on your "search_path", and you should
qualify the name with the schema.
Yours,
Laurenz Albe
Re: Postgresql assistance needed
On Thu, 2024-02-22 at 16:13 +0530, Sasmit Utkarsh wrote: > On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe wrote: > > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote: > > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address > > > 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0 > > > READ of size 4096 at 0xf337ba80 thread T0 > > > #0 0xf795fcdc in __interceptor_memcpy > > > (/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc) > > > #1 0xf78c34bb in pqPutnchar > > > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb) > > > #2 0xf78be05e in PQsendQueryGuts > > > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e) > > > #3 0xf78c05a2 in PQexecPrepared > > > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2) > > > > Perhaps you forgot to terminate a string with '\0'. > > Sorry but are you talking about the export variables in the result? Whatever you fed to PQexecPrepared. Yours, Laurenz Albe
Re: Postgresql assistance needed
On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote: > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address > 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0 > READ of size 4096 at 0xf337ba80 thread T0 > #0 0xf795fcdc in __interceptor_memcpy > (/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc) > #1 0xf78c34bb in pqPutnchar > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb) > #2 0xf78be05e in PQsendQueryGuts > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e) > #3 0xf78c05a2 in PQexecPrepared > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2) Perhaps you forgot to terminate a string with '\0'. Yours, Laurenz Albe
Re: Identifying optimizer usage of indexed expressions
On Mon, 2024-02-19 at 23:08 +, Tim Palmer wrote: > I can identify indexes that are used directly during query execution using > pg_stat_user_indexes. However, it is possible that the optimizer might be > using the statistics on the indexed expressions to guide query planning. > If that is the case then I might remove an index which appears to be unused, > only to then find that this results in the optimizer choosing poorer plans > as it no longer has the additional statistics on the indexed expressions. > > How can I identify indexes which are used by the optimizer when planning? I don't think you can. > If there is nothing available inside the database, I was thinking I would > have to parse all of the queries executed on the database, extract the > expressions, work out which columns of which tables are referenced by > those expressions, and then check whether those expressions are indexed. Well, in an ideal world, you should know which indexes you created for which query. But then indexes might be used in ways you didn't intend. > Or (since that seems like a lot of work), I could remove all indexes which > are reported as unused by pg_stat_user_indexes, but for each and every one > of them create extended statistics on the relevant expression(s) using > CREATE STATISTICS. That might result in a lot of unnecessary expression > statistics - are there any significant costs associated with that? The > docs say "providing benefits similar to an expression index without the > overhead of index maintenance" [1], and don't mention any downside. The small downside is that ANALYZE will have more work, the big downside is that query planning on tables with advanced statistics takes slightly longer. That may or may not be a problem for you, but if you could pay the price of the extra indexes, you will certainly be able to pay the price of advanced statistics. You could also consider the option to drop indexes and see if somebody complains, or watch out for changes in pg_stat_statements. Yours, Laurenz Albe
Re: Users and object privileges maintenance
On Sun, 2024-02-18 at 11:12 +0100, Dominique Devienne wrote: > On Sat, Feb 17, 2024 at 10:50 PM Lok P wrote: > > We were having past experience in Oracle and are newly getting moved to > > postgres database. [...] > > So I just wanted to understand if these grants and privileges for > > objects/users are given and maintained in a similar way in postgres database > > Yes they are. Having done that move from Oracle to PostgreSQL, here are a few > pointers. > > The one big difference between Oracle and PostgreSQL is that any user can see > all catalogs (dictionaries), > and they can know about any objects in the DB, even when they don't > themselves have access to them. > There's no DBA_, USER_, ALL_ views over the catalogs, to hide metadata of > objects you don't have access to. > Beside that, the access controls are basically the same, ROLE and > GRANT/REVOKE based. Three more additions concerning the fundamental differences (without claiming completeness): 1. Schemas and users are not tied together, they are orthoginal concepts. Just like operating system users and directories (and indeed all other databases). 2. In PostgreSQL, there is the important concept of ownership, which is not tied to the schema. The owner is the user who created the object. 3. Different from Oracle, functions are executable by PUBLIC by default, and run with the privileges of the invoker. > As parting thoughts, let me add that I enjoy PostgreSQL more than Oracle. And > libpq way more than OCI. That goes without saying. I have never seen an API as terrible as OCI. As an aside, IBM has re-implemented the OCI API for DB2. I am sure that led to serial quitting and mental illness among IBM's developers. Yours, Laurenz Albe
Re: RowLock and multiple transactions
On Wed, 2024-02-14 at 23:52 +0100, Hannes Erven wrote: > when "SELECT .. WHERE .. FOR NO KEY UPDATE" is used synchronize access, > and the transaction holding the lock completes, how does PostgreSQL > decide /which one/ of multiple waiting transactions will the lock be > granted to next? > > In my testing (on Ubuntu 16.1-1.pgdg20.04+1, 64bit) with a real-world > application (that acquires multiple locks on a number of relations) it > seems that it is always the transaction that attempted to lock _last_ ... ? > I thought that would most probably be random, or if it was not, the > order would have been explained in the docs? Transactions queue behind a lock, and they get the lock in a "first come, first served" order. Yours, Laurenz Albe
Re: FOR UPDATE SKIP LOCKED and get locked row/avoid updating other row(s)
On Tue, 2024-02-13 at 11:17 +, Wiwwo Staff wrote: > I am implementing a queue using PostgreSQL. > I am of course using "FOR UPDATE SKIP LOCKED". > > Is there any way I can tell PostgreSQL to only "operate" on the locked row, > and/or a way to reference it? > > Some explanations of what I mean: > * I have a table with N rows > * I lock row X with a PG Function, in transaction 1 > * I do something in my code, using transaction 2 > * I update the status of row X with a PG Function, in transaction 1 > In last step, I update the row X status, passing my function the ID of this > row X. > But, nothing stops me from updating row Y in the queue table, for whatever > reason. > > My question again: any way to force a "Just update the row X you locked > before, and nothing else/more?" I don't think there is a way to enforce that. Your application code has to do the right thing. Yours, Laurenz Albe
Re: How to do faster DML
On Sun, 2024-02-11 at 13:25 +0530, veem v wrote: > On Sun, 2024-02-11 at 01:25 +0100, Peter J. Holzer wrote: > > On 2024-02-06 11:25:05 +0530, veem v wrote: > > > With respect to the storage part:- In Oracle database we were supposed to > > > keep > > > the frequently accessed column first and less frequently accessed columns > > > towards last in the table while creating table DDL. This used to help the > > > query > > > performance while accessing those columns as it's a row store database. > > > Are > > > there any such performance implications exists in postgres? And there the > > > data > > > types like varchar used to only occupy the space which the real data/value > > > contains. > > > > > > But here in postgres, it seems a bit different as it has significant > > > overhead in storage, as these seem like being fixed length data types and > > > will > > > occupy the space no matter what the read data holds. > > > > Yes. Numbers in Oracle are variable length, so most Oracle tablesbetween > > wouldn't contain many fixed length columns. In PostgreSQL must numeric > > types are fixed length, so you'll have quite a lot of them. > > So it means , say in other databases like (oracle database), we were careless > choosing the data length , say for example Varchar2(4000), if the real data > which > is inserted into the table holds a varchar string of length 20 bytes then > Oracle > trimmed it to occupy the 20 bytes length only in the storage. but in postgre > here > we need to be cautious and define the length as what the data attribute can > max > contains , because that amount of fixed space is allocated to every value > which > is inserted into the table for that attribute/data element. Similarly for > Number/Numeric data type. Please correct if my understanding is wrong. Your understanding is wrong. Oracle and PostgreSQL are not too different about storing values. The data type that Oracle calls "number", and that is called "numeric" in PostgreSQL and in the SQL standard, is stored a variable length data type in both Oracle and PostgreSQL. It is just that Oracle has very few data types (I saw them gush about adding "boolean" as a great new feature in version 23). So, for example, there are no integer data types in Oracle, and you have to store them as a variable length data type. PostgreSQL has integer data types, which are fixed length (2, 4 or 8 bytes) and provide much more efficient storage for integers. "character varying" is also pretty similar in Oracle and PostgreSQL, except that Oracle calls it "varchar2". The only fixed-length character data type is "character", but that is always a bad choice, in Oracle as in PostgreSQL. About your initial question: in PostgreSQL there is also a certain performance gain if you store frequently used columns first, since the database has to skip fewer columns to get to the data. If the previous columns are fixed length data types like integers, that is cheaper, because we don't have to look at the data to know how long they are. Another thing to consider is padding. Each fixed-width data type has certain alignment requirements (imposed by CPU processing) that you can find in "pg_type.typalign". This can lead to wasted space in the form of "padding bytes". For example, if a "bigint" follows a ASCII single-character "text" or "varchar" value (which occupies two bytes), there will be six padding bytes between them to align the "bigint" at a storage address that is a multiple of eight. But although both of these considerations (skipping over previous columns and padding) are relevant for performance, they are often a micro-optimization that you won't be able to measure, and you shouldn't lose too much sleep over them. > > > So there's a bit of a tradeoff between minimizing alignment overhead and > > arranging columns for fastest access. Precisely. Yours, Laurenz Albe
Re: Multiple connections over VPN password fail error
On Fri, 2024-02-09 at 15:04 +0530, Sanjay Minni wrote: > for the second user/role over vpn > even with md5 I get the error: > P3Error PostgreSQL password authentication failed for user "" > am only able to connect if method is "trust". Why is this so > > # IPv4 external connections thru VPN > #TYPE DATABASE USER ADDRESS METHOD > host all all scram-sha-256 (or "md5" - second > user is only able to connect if this is "trust" Please tell us the exact client software in use on the failing client. Also, what is the error message 1) on the client side 2) in the PostgreSQL server log Yours, Laurenz Albe
Re: archive command doesnt work
On Thu, 2024-02-08 at 21:28 +0100, Alpaslan AKDAĞ wrote: > Now I am confused. In Primary pg_is_in_recovery() should be false and in > standby node should be true. Am I wrong? Right. I guess I got confused which is your primary and which is your standby. That's normal in the context of switchover :^/ Yours, Laurenz Albe
Re: archive command doesnt work
On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote: > Thank you for your answer. > > I have checked the pg_is_in_recovery() and here are the results > from primary and hot stand by server. > > Primary (old standby ): > select pg_is_in_recovery(); > pg_is_in_recovery > --- > f > (1 row) > > hot standby(old primary): > select pg_is_in_recovery(); > pg_is_in_recovery > --- > t > (1 row) > and there is also standby.signal file in standby server. > So it seems that there is nothing wrong. > > recovery_min_apply_delay = '2d' are set in warm standby servers. > Before the switchover we had the same settings but we did not have this > problem. It is becoming increasingly obvious that you never actually performed a switchover: it seems that you didn't promote the standby. Either use pg_ctl promote -D /path/to/datadir on the command line or SELECT pg_promote(); in SQL. Yours, Laurenz Albe
Re: archive command doesnt work
On Wed, 2024-02-07 at 12:07 +0100, Alpaslan AKDAĞ wrote: > We have a primary, a hot standby and 2 warm standby servers with 2 days delay. > > After switchover since 01.02.2024 hot standby server does not archive wal > files. I couldn't find the problem. > > related postgresql.conf lines: > archive_mode = on > archive_command = 'cp %p /archive/archived_wal/%f && cp %p > /opt/postgres/backup/archived_wal/%f' > > and in log files there are lines like below but no error or failure lines: > > postgresql-2024-02-05_00.log:2024-02-05 06:25:50.272 CET [979081] LOG: > restartpoint complete: wrote 477 buffers (0.0%); 0 WAL file(s) added, 0 > removed, 4 recycled; write=47.946 s, sync=0.005 s, total=47.964 s; sync > files=85, longest=0.001 s, average=0.001 s; distance=65484 kB, estimate=413488 > kB > postgresql-2024-02-05_00.log-2024-02-05 06:25:50.272 CET [979081] LOG: > recovery restart point at 25C/74083E58 > postgresql-2024-02-05_00.log-2024-02-05 06:25:50.272 CET [979081] > DETAIL: Last completed transaction was at log time 2024-02-05 06:25:50.223799 > +01. That looks like the standby wasn't promoted and is still in recovery, so it won't generate WAL. Restartpoints are only written on servers in recovery mode. Moreover, a promote request would generate a log entry. Another option would be that the promote request is further up in the log, and the server takes a long time to replay all the changes (if the "2 days delay" you mention are set in "recovery_min_apply_delay"). What do you get for SELECT pg_is_in_recovery(); Yours, Laurenz Albe
Re: Question on partitioning
On Tue, 2024-02-06 at 00:26 +0530, veem v wrote: > On Mon, 5 Feb 2024 at 17:52, Laurenz Albe wrote: > > On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > > > In postgresql, Is it possible to partition an existing nonpartitioned > > > table having data > > > already residing in it and indexes and constraints defined in it, without > > > the need of > > > manually moving the data around, to make it faster? > > > > There is no way to do that. > > Which means for any such operation we need to create a new partition table > with that > structure and load that with the data from the existing partition table. You could use logical replication to keep the downtime short. > Additionally I see a lot of other restrictions like > 1)When creating indexes on the partition table "concurrently" keywords are > not allowed. That is no problem. You create the index on ONLY the partitioned table, then you create indexes CONCURRENTLY on the partitions, then you attach these indexes as partitions to the index you created in the beginning. As soon as the last index is attached, the index on the partitioned table becomes valid. > 2)While creating foreign key , it does not allow a "not valid" clause if the > table > is partitioned. Create the foreign key on the partitions; the effect is the same. > 3) While creating indexes on this table or running any ALTER command, the > SELECT >queries running from other processes run longer. Does it take any lock > while >doing DDL on the base table in postgres? I am not sure what the "base table" is. Most DDL always locks the table in ACCESS EXCLUSIVE mode; that is not specific to partitioned tables. Yours, Laurenz Albe
Re: Question on partitioning
On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > In postgresql, Is it possible to partition an existing nonpartitioned table > having data > already residing in it and indexes and constraints defined in it, without the > need of > manually moving the data around, to make it faster? Similarly merging > multiple partitions > to one partition or splitting a single partition into multiple partitions? There is no way to do that. Yours, Laurenz Albe
Re: Query running longer
On Fri, 2024-02-02 at 10:14 +0530, veem v wrote: > On Fri, 2 Feb 2024 at 02:43, Laurenz Albe wrote: > > On Fri, 2024-02-02 at 02:27 +0530, veem v wrote: > > > We have the below query which is running for ~45 seconds on postgres > > > aurora reader instance. > > > I have captured the explain analyze. Want to understand, where exactly > > > the resources are > > > getting spent and if we can be able to optimize it further. > > > > Aurora <> PostgreSQL, but here is what I can see: > > > > - The index scan on SCHEMA1."TAB2" has to check 2 million extra > > rows because "work_mem" is too small. Almost the complete time > > is spent there. > > > > - You may be getting a bad plan, because the statistics on > > SCHEMA1.TAB4 are either out of date or not detailed enough, > > which makes PostgreSQL underestimate the result size. > > As you mentioned below, So wondering how you got to know, if this is the step > where majority of the DB resources get spent. And as total time the query ran > was ~45 seconds, and out of that how much time it spent in this step, how can > i get that? And to fix this one line with regards to TAB2, should we increase > "work_mem" here? > > - The index scan on SCHEMA1."TAB2" has to check 2 million extra > rows because "work_mem" is too small. Almost the complete time > is spent there. > > -> Parallel Bitmap Heap Scan on SCHEMA1.""TAB2"" TAB2 > (cost=84860.50..13040301.00 rows=1175611 width=80) (actual > time=713.054..26942.082 rows=956249 loops=5)" > " Output: TAB2.TAB2_time, TAB2.PRI, TAB2.AID" > Recheck Cond: (TAB2.MID = 'X'::numeric) > Rows Removed by Index Recheck: 2137395 > Filter: ((TAB2.TAB2_time >= '2024-01-01 00:00:00+00'::timestamp with time > zone) AND (TAB2.TAB2_time <= '2024-01-31 00:00:00+00'::timestamp with time > zone)) > Heap Blocks: exact=5300 lossy=782577 > Buffers: shared hit=1651569 read=2245157 > I/O Timings: shared/local read=29063.286 The bitmap index scan builds a bitmap, the size of which is limited by "work_mem". If that is big enough, the bitmap will contain a bit for each table row, if not, it only contains a bit per table row for some table blocks (the "exact" ones), then it degrades to a bit per block (whe "lossy" ones). For lossy blocks, *all* rows in the block have to be rechecked, which is overhead. Given that only a small part of the time (26942.082 - 713.054) * 5 is spent doing I/O (29063.286), I guess that the rest is spent processing table rows. > Another point you mentioned as below , for this , should we run vacuum > analyze on the table TAB4? > > - You may be getting a bad plan, because the statistics on > SCHEMA1.TAB4 are either out of date or not detailed enough, > which makes PostgreSQL underestimate the result size. > > -> Parallel Bitmap Heap Scan on SCHEMA1.TAB4 TAB4 (cost=26.39..7042.63 > rows=1049 width=37) (actual time=23.650..201.606 rows=27613 loops=5) > -> Bitmap Index Scan on TAB4_idx1 (cost=0.00..25.95 rows=1784 > width=0) (actual time=23.938..23.938 rows=138067 loops=1) > Index Cond: ((TAB4.TAB4_code)::text = 'XX'::text) > Buffers: shared hit=72 An ANALYZE might be enough. If not, you can try to collect more detailed statistics for the column: ALTER TABLE SCHEMA1.TAB4 ALTER TAB4_code SET STATISTICS 1000; ANALYZE SCHEMA1.TAB4; Yours, Laurenz Albe
Re: Query running longer
On Fri, 2024-02-02 at 02:27 +0530, veem v wrote: > We have the below query which is running for ~45 seconds on postgres aurora > reader instance. > I have captured the explain analyze. Want to understand, where exactly the > resources are > getting spent and if we can be able to optimize it further. Aurora <> PostgreSQL, but here is what I can see: - The index scan on SCHEMA1."TAB2" has to check 2 million extra rows because "work_mem" is too small. Almost the complete time is spent there. - You may be getting a bad plan, because the statistics on SCHEMA1.TAB4 are either out of date or not detailed enough, which makes PostgreSQL underestimate the result size. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Postgresql BUG / Help Needed
On Thu, 2024-02-01 at 00:48 -0500, Johnathan Tiamoh wrote: > I am having the following issue below. Please, I need help to fix it. > > ERROR: could not access status of transaction 756525298 > Could not open file "pg_xact/02C8": No such file or directory. That is data corruption, and you should restore your backup. Yours, Laurenz Albe
Re: Best practices for data buffer cache setting/tuning (v15)
On Mon, 2024-01-29 at 14:39 -0500, David Gauthier wrote: > Is there a document which makes recommendations on sizing data buffer cache, > tuning options which evict old/unused data in mem, and cache fragmentation > avoidance for a v15.3 DB ? Start with "shared_buffers" sized as a quarter of the available RAM and define enough huge pages on the Linux kernel to fit shared buffers. There are no tuning options for evicting buffers, and there cannot be any fragmentation. Yours, Laurenz Albe
Re:
On Thu, 2024-01-25 at 12:18 -0500, Ron Johnson wrote: > PG 14.10 (and 9.6.24, which we're migrating off of). > > EXPLAIN SELECT works inside a FOR loop, but only the first line of the EXPLAIN > output is stored. What's the magic sauce for seeing the whole EXPLAIN output? DECLARE _v_explain jsonb; BEGIN EXECUTE 'EXPLAIN (FORMAT JSON) select ...' INTO _v_explain; RAISE NOTICE '%', _v_explain; END; Yours, Laurenz Albe
Re: Postgres Database Service Interruption
On Tue, 2024-01-16 at 21:59 +0530, Bablu Kumar Nayak wrote: > I am writing to inform you that our PostgreSQL database service is currently > down. > We are experiencing an unexpected interruption, and we are seeking your > expertise > to help us resolve this issue promptly. > We would greatly appreciate your immediate attention to this matter. If there > are > specific steps we should follow or additional information you require, please > let > us know as soon as possible. > Your assistance in resolving this issue is crucial, and we are confident in > your > expertise to help us bring the PostgreSQL database back online. > > Here are some details about the current situation: > > 2) Starting the server > /apps/postgresdb/pgsql/bin/pg_ctl start -D /apps/postgresdb/pgsql/data > waiting for server to start2024-01-15 11:15:08.010 GMT [] LOG: listening > on IPv4 address "0.0.0.0", port > LOG: listening on IPv6 address "::", port > LOG: listening on Unix socket "/tmp/.s.PGSQL." > LOG: database system was interrupted while in recovery at 2024-01-15 > 10:51:44 GMT > HINT: This probably means that some data is corrupted and you will have to > use the last backup for recovery. > FATAL: the database system is starting up > LOG: database system was not properly shut down; automatic recovery in > progress > LOG: redo starts at 0/ > FATAL: could not access status of transaction > DETAIL: Could not read from file "pg_xact/0001" at offset 204800: Success. > CONTEXT: WAL redo at 0/7A845458 for Transaction/COMMIT: 2023-12-30 > 23:26:16.017062+00 > LOG: startup process (PID 2731458) exited with exit code 1 > LOG: aborting startup due to startup process failure > LOG: database system is shut down > stopped waiting > pg_ctl: could not start server > Examine the log output. You are suffering from data corruption. What did you do to get into this state? The canonical solution is to restore your backup. If you have no backup, and the data are important for you (which is a contradiction in terms), you should hire a consultant with extensive PostgreSQL knowledge in the hope that he or she can salvage some of your data. Yours, Laurenz Albe
Re: Nested-Internal Functions
On Tue, 2024-01-16 at 13:15 +0100, Rossana Ocampos wrote: > I have a query about creating nested functions in PostgreSQL. There are no "nested functions" in PostgreSQL. You'd need to rewrite that to use a proper stand-alone function. Yours, Laurenz Albe
Re: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'
On Wed, 2024-01-10 at 22:51 +, Keaney, Will wrote: > However, the standby is unable to authenticate to the primary to begin > recovery during startup. > It logs an error, "FATAL: could not connect to the primary server: > fe_sendauth: no password supplied". > > pg_hba.conf on the primary: > hostall,replication replprimary-database-server > scram-sha-256 > hostall,replication replstandby-database-server > scram-sha-256 > > myrecovery.conf on the standby: > primary_conninfo = 'host=primary-database-server port=5432 user=repl > application_name=standby-server-name' > recovery_target_timeline = 'latest' > primary_slot_name = 'standby_replication_slot' > > .pgpass on the standby: > # hostname:port:database:username:password > *:*:replication:repl:repl_user_password make sure that .pgpass is in the home directory of the PostgreSQL user and has restrictive permissions (0600). Try using it with a manual connection attempt via "psql". Yours, Laurenz Albe
Re: ERROR: invalid byte sequence for encoding UTF8: 0x00
On Tue, 2024-01-09 at 17:48 +0530, Kiran K V wrote: > I have a table with bytea column and trying to load the data using copy > command. > But Copy command is failing with > "ERROR: invalid byte sequence for encoding UTF8: 0x00. > Why postgresql is failing to load when data contains 0x00. > How to resolve this error ? any workaround to load the data with 0x00 values ? This is not about loading data into a "bytea", but into a "text", "varchar" or "char" column. Check again. Yours, Laurenz Albe
Re: what do you do after pg_walreset ?
On Thu, 2023-12-21 at 18:53 -0500, Johnathan Tiamoh wrote: > Please I wish to find out what you do after running pg_walreset. You mean pg_resetwal. > I made a mistake and deleted my data directory with all its content. > I then restore with 1 day old backups, after that i could start > unless i did pg_walreset. Which means that the backup was broken. > 1, I wish to find out what to do after i have started postgresql ? pg_dumpall the cluster and restore the result to a new cluster to get rod of the data corruption introduced by pg_resetwal. > 2, How do I apply the wall files after the reset ? You cannot do that. > 3, How can I determine the amount of data lost ? Well, everything from after the "backup". Yours, Laurenz Albe
Re: how can I fix my accent issues?
On Tue, 2023-12-12 at 15:44 -0500, Igniris Valdivia Baez wrote: > this is the result I got, now I have to figure it out how to solve it, Since you already have a replacement character in the database, the software that stores the data in the database must be responsible. PostgreSQL doesn't convert characters to replacement characters. Yours, Laurenz Albe
Re: how can I fix my accent issues?
On Mon, 2023-12-11 at 13:54 -0500, Igniris Valdivia Baez wrote: > El sáb, 9 dic 2023 a las 1:01, Laurenz Albe () > escribió: > > > > On Fri, 2023-12-08 at 23:58 -0500, Igniris Valdivia Baez wrote: > > > hello, I have an ETL process collecting data from a postgresql > > > database and xls files and inserting in a postgresql database that > > > process occurs great in a local DB in postgres 14 with UTF8 > > > codification and Spanish_Cuba.1952 collation but when I execute that > > > process in dev which is in postgres 15 and UTF8 with collation > > > en_US.utf8 the words with accents and ñ looks like an interrogation > > > symbol, what can I do to fix this? > > > > If the data you are sending are encoded in WINDOWS-1252 (I assume that > > "1952" is just a typo), you should set the client encoding to WIN1252, > > so that PostgreSQL knows how to convert the data correctly. > > > > You can do that in several ways; the simplest might be to set the > > environment variable PGCLIENTENCODING to WIN1252. > > hello to all, thanks for your answers i've changed the encoding using this: > ALTER DATABASE testdb > SET client_encoding = WIN1252; > > now when we try to select data from a table we get this error: > > ERROR: character with byte sequence 0xe2 0x80 0x8b in encoding "UTF8" > has no equivalent in encoding "WIN1252" SQL state: 22P05ERROR: > character with byte sequence 0xe2 0x80 0x8b in encoding "UTF8" has no > equivalent in encoding "WIN1252" SQL state: 22P05 So that was not the correct encoding. Unfortunately your problem description lacks the precision required to give a certain answer. You'll have to figure out what encoding the application data have and how the client encoding is set in the case where the non-ASCII characters look right and when the don't. You should also investigate what bytes are actually stored in the database in both cases. Yours, Laurenz Albe
Re: how can I fix my accent issues?
On Fri, 2023-12-08 at 23:58 -0500, Igniris Valdivia Baez wrote: > hello, I have an ETL process collecting data from a postgresql > database and xls files and inserting in a postgresql database that > process occurs great in a local DB in postgres 14 with UTF8 > codification and Spanish_Cuba.1952 collation but when I execute that > process in dev which is in postgres 15 and UTF8 with collation > en_US.utf8 the words with accents and ñ looks like an interrogation > symbol, what can I do to fix this? If the data you are sending are encoded in WINDOWS-1252 (I assume that "1952" is just a typo), you should set the client encoding to WIN1252, so that PostgreSQL knows how to convert the data correctly. You can do that in several ways; the simplest might be to set the environment variable PGCLIENTENCODING to WIN1252. Yours, Laurenz Albe
Re: Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix
On Thu, 2023-12-07 at 16:38 -0600, Noel Jones wrote: > We have been utilizing partitioned tables with indexes. We've recently had an > issue > where the parent table's index (id, date) became invalid (indisvalid=FALSE, > indisready=FALSE in pg_index). For reference the parent table is partitioned > on a > date field within the table. Indexes don't become invalid just so. I wonder what happened. > We attempted to fix the issue by doing the following: > > ALTER TABLE table_parent DETACH PARTITION table_badpartition; > DROP INDEX brokenchildindex; > CREATE INDEX newchildindex on table_badpartition using btree (id, date); > ALTER TABLE table_parent ATTACH PARTITION table_badpartition > FOR VALUES FROM (date) TO (date+1); > > This did not fix the issue so we attempted an alternate fix: > > begin; > set role readwrite; > ALTER TABLE table_parent DETACH PARTITION table_badpartition; > ALTER TABLE table_badpartition RENAME TO table_badpartition_detached; > CREATE TABLE table_badpartition PARTITION OF table_parent > FOR VALUES FROM (date) TO (date+1); > ALTER TABLE table_badpartitioneplica identity full; > INSERT INTO table_badpartition (id, date, ...) > SELECT id, date, ... from table_badpartition_detached; > commit; > > This new table was created with the correct columns, the accurate data, and > the > correct indices however the parent index is still listed with indisvalid = > FALSE > and indisready = FALSE. You forgot to ALTER INDEX ... ATTACH PARTITION to turn the invalid index on the partitioned table into a valid index. Yours, Laurenz Albe
Re: vacuum visibility relevance
On Mon, 2023-12-04 at 02:08 +, senor wrote: > Simplified Scenario: > 40+ gig table with 20+ indexes receiving log event type records in an append > only fashion. Log tables typically receive tons of INSERTs. Over twenty indexes are too many. > One of the columns is a sequence ID. > PG version 11.4 > > > If I start a vacuum on this table when sequence ID is 1 million, and the > table continues > to accumulate new events, when vacuum completes, should I be thinking about > the state of > the table and indexes as being vacuumed up to 1 million? Or is vacuum also > operating on > records inserted after vacuum started? VACUUM processes the whole table, but it determines an "xmin horizon" when it starts, that is, it determines which tuples are safe to remove. Rows added after VACUUM started won't qualify, so they won't be removed. > Is there any reason to think about this differently when the vacuum is manual > as opposed to auto? No, the behavior is the same. > I attempted to deal with the append only issue corrected in PG13 by setting > autovacuum_freeze_max_age low enough to trigger vacuum but that has been such > a wild card > I abandoned it. I'm now trying to find a formula to trigger a manual vacuum. > There are > stats gathering processes pulling from the most recently added data all the > time so my > priority is to keep performance high for those. Secondly, there's a ton of > memory allotted > so running vacuum freeze before table pages age out of memory seems like a > good idea. You can monitor the "n_tup_ins" column in "pg_stat_user_tables" and run VACUUM whebever it has increased enough. But the simple solution is to upgrade. Any version older than v13 is due for an upgrade soon. > I am having trouble with autovacuum keeping up and I suspect there's a > communication > problem with the stats collector but have put off looking into that because > updating > to PG15 was "right around the corner". Meanwhile, I have had to run > multiple-thread > scripts to keep up vacuum. Manual vacuums always work and updates stats > correctly but > autovacuums hang and occupies workers doing nothing. Autovacuum deliberately runs slower than a manual VACUUM. If you are running a version older than v12, the default value for "autovacuum_vacuum_cost_delay" is 20ms, which makes autovacuum really slow. You should change that parameter to 2ms, and if that is still too slow, use ALTER TABLE to set it to 0 on the affected table. Then autovacuum will run as fast as manual VACUUM. In addition, see that "maintenance_work_mem" is set high to make autovacuum fast. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Feature request for INITCAP() function
On Thu, 2023-11-30 at 21:08 -0500, Jeff Gerbracht wrote: > It would be great if there was a way to set exceptions to the 'word' > delimiter list used in > the INITCAP() function. For example, I have hyphenated words like > blue-green or > possessives and contractions like don't and cat's tail > These become Blue-Green, Don'T and Cat'S Tail. > > Being able to pass in a set of delimiter exceptions like hyphen and single > quote would make > this function much more useful for our specific use cases. That would pretty quickly drift into locale-dependent rules. Such a function would be even more special-case than the current "initcap()", which is already pretty specific to certain languages. I don't know if there is enough value for the general public in it. But it should be easy to implement it yourself, for example in PL/pgSQL. Why don't you try that? Yours, Laurenz Albe
Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.
On Mon, 2023-11-27 at 11:50 +, Sri Mrudula Attili wrote: > ERROR: could not access status of transaction 16087052 > DETAIL: Could not read from file "pg_subtrans/00F5" at offset 122880: > Success. > STATEMENT: SELECT distinct That's data corruption. Time to restore your backup. Investigate how you got there. Did you run "pg_resetwal"? Did you restore a database from a file system backup? Did you remove a "backup_label" file? Is your hardware broken? Yours, Laurenz Albe
Re: replication primary writting infinite number of WAL files
On Fri, 2023-11-24 at 16:59 +0100, Les wrote: > > > Laurenz Albe (2023. nov. 24., P, 16:00): > > On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > > > Under normal circumstances, the number of write operations is relatively > > > low, with an > > > average of 4-5 MB/sec total write speed on the disk associated with the > > > data directory. > > > Yesterday, the primary server suddenly started writing to the pg_wal > > > directory at a > > > crazy pace, 1.5GB/sec, but sometimes it went up to over 3GB/sec. > > > [...] > > > Upon further analysis of the database, we found that we did not see any > > > mass data > > > changes in any of the tables. The only exception is a sequence value that > > > was moved > > > millions of steps within a single minute. > > > > That looks like some application went crazy and inserted millions of rows, > > but the > > inserts were rolled back. But it is hard to be certain with the clues > > given. > > Writing of WAL files continued after we shut down all clients, and restarted > the primary PostgreSQL server. > > How can the primary server generate more and more WAL files (writes) after > all clients have > been shut down and the server was restarted? My only bet was the autovacuum. > But I ruled > that out, because removing a replication slot has no effect on the autovacuum > (am I wrong?). It must have been autovacuum. Removing a replication slot has an influence, since then autovacuum can do more work. If the problem stopped when you dropped the replication slot, it could be a coincidence. > Now you are saying that this looks like a huge rollback. It could have been many small rollbacks. > Does rolling back changes require even more data to be written to the WAL > after server > restart? No. My assumption would be that something generated lots of INSERTs that were all rolled back. That creates WAL, even though you see no change in the table data. > Does removing a replication slot lessen the amount of data needed to be > written for > a rollback (or for anything else)? No: the WAL is generated by whatever precedes the ROLLBACK, and the ROLLBACK does not create a lot of WAL. > It is a fact that the primary stopped writing at 1.5GB/sec the moment we > removed the slot. I have no explanation for that, except a coincidence. Replication slots don't generate WAL. Yours, Laurenz Albe
Re: replication primary writting infinite number of WAL files
On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > Under normal circumstances, the number of write operations is relatively low, > with an > average of 4-5 MB/sec total write speed on the disk associated with the data > directory. > Yesterday, the primary server suddenly started writing to the pg_wal > directory at a > crazy pace, 1.5GB/sec, but sometimes it went up to over 3GB/sec. > [...] > Upon further analysis of the database, we found that we did not see any mass > data > changes in any of the tables. The only exception is a sequence value that was > moved > millions of steps within a single minute. That looks like some application went crazy and inserted millions of rows, but the inserts were rolled back. But it is hard to be certain with the clues given. Yours, Laurenz Albe
Re: General support on postgres replication
On Thu, 2023-11-23 at 09:32 +, Vijaykumar Patil wrote: > LOG: entering standby mode > LOG: consistent recovery state reached at 1/27000100 > LOG: database system is ready to accept read-only connections > LOG: started streaming WAL from primary at 1/2800 on timeline 36 > LOG: recovery stopping before commit of transaction 25628, time 2023-11-22 > 04:33:31.454379-05 > LOG: redo done at 1/2800BBB0 system usage: CPU: user: 0.00 s, system: 0.00 > s, elapsed: 43.73 s > FATAL: terminating walreceiver process due to administrator command > LOG: selected new timeline ID: 37 > LOG: archive recovery complete When you built your standby, you accidentally set (or copied via pg_basebackup) an option "recovery_target_*", so recovery stopped at that point, and the standby server was promoted. Don't set any of these parameters on the standby server. This is certainly one of the major problems introduced by commit 2dedf4d9a8: If you ever recovered a database, you may end up having recovery parameters set in your configuration file. You don't notice them until you build a standby server, which will then get into trouble. Yours, Laurenz Albe
Re: Configuration knobs & dials to speed up query optimization
On Wed, 2023-11-22 at 11:13 -0500, Ron Johnson wrote: > Pg 9.6.24, which will change by April, but not now. > > We've got some huge (2200 line long) queries that are many UNIONs of > complicated > queries hitting inheritance-partitioned tables. They can't be refactored > immediately, > and maybe not at all (complicated applications hitting normalized databases > make for > complicated queries). > > BIND (and EXPLAIN, when I extract them from the log file and run them myself) > takes > upwards of 25 seconds. It's from JDBC connections, if that matters. > > Is there any way for me to speed that up? > > The Linux system has 128GB RAM, 92% of it being "cached", according to top(1). > > I've read https://www.postgresql.org/docs/9.6/runtime-config-query.html but > can't > go mucking around with big sticks on a very busy system with lots of > concurrent users. Well, the system cannot support a lot of concurrent users if queries take 25 seconds to plan... > Here are the only non-default config values which I can think of that are > relevant > to the question at hand: > shared_buffers = 16GB > work_mem = 300MB > maintenance_work_mem = 12GB > effective_cache_size = 96GB > default_statistics_target = 200 The only parameter that should affect query planning time is the "default_statistics_target". The more, the longer. Other relevant parameters would be "join_collapse_limit" and "from_collapse_limit". But without knowing your query, we can say nothing. Yours, Laurenz Albe
Re: General support on postgres replication
On Wed, 2023-11-22 at 08:57 +, Vijaykumar Patil wrote: > I have created streaming replication with two nodes . > > One is primary and 2nd one is standby but after doing any DML or DDL > operation on > primary Walreceiver process is terminated on standby and standby database is > opening in read write mode. > > Below are the error details . > > 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: recovery > stopping before commit of transaction 25627, time 2023-11-22 > 03:39:58.013764-05 > 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: redo done at > 1/260059E0 system usage: CPU: user: 0.02 s, system: 0.04 s, elapsed: 5197.90 s > 2023-11-22 03:39:58 EST [1276846]: user=,db=,app=,client=FATAL: terminating > walreceiver process due to administrator command > ERROR: [037]: archive-get command requires option: pg1-path > HINT: does this stanza exist? It looks like you have a bad "restore_command" configured. It would be intereseting to see the log lines before the ones you are showing. Yours, Laurenz Albe
Re: strange behavior of pg_hba.conf file
On Wed, 2023-11-22 at 23:33 +0530, Atul Kumar wrote: > Please can you share any command for due diligence whether ip is resolved to > ipv6 ?. Not a lot of diligence is due to figure out that you can use ping localhost Yours, Laurenz Albe
Re: pg_restore enhancements
On Wed, 2023-11-22 at 16:55 +, Efrain J. Berdecia wrote: > Thanks, the issue we've run into, which I guess could be really a setup > issue, with running a COPY command while executing pg_restore, > is that if we are restoring a large table (bigger than 500GB) our WAL > directory can grow to be very large. You can avoidwriting WAL if you set "wal_level = minimal", restart PostgreSQL and restore the dump with the --single-transaction option. Yours, Laurenz Albe
Re: Feature request: pg_get_tabledef(text)
On Wed, 2023-11-22 at 16:41 +0100, Hans Schou wrote: > Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful with > a pg_get_tabledef() to get a full description of how a table is defined. This has been requested before: https://www.postgresql.org/message-id/flat/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com One of the problems is what should be included. Indexes? Policies? Constraints? Another problem is that while a function or view definition is a single SQL statement, a table definition could consist of more than a single statement, depending on the answer to the previous question. No unsurmountable questions, but someone would have to come up with a clear design and implement it. Yours, Laurenz Albe
Re: Connection fails on one system in a address range allowed to connect
On Tue, 2023-11-21 at 23:27 +, Johnson, Bruce E - (bjohnson) wrote:
> DBI
> connect('dbname=webdata;host=dhbpostgres.pharmacy.arizona.edu;port=5432','trav',...)
> failed: FATAL: password authentication failed for user "trav"
> FATAL: no pg_hba.conf entry for host "150.135.124.50", user "trav", database
> "webdata", no encryption at ./pg_test.pl line 8.
>
> The pg_hba.conf on the server includes this which should encompass all
> systems in this VLAN
>
> # external 'OldMTM' site range
> hostssl all all 150.135.124.0/25 password
>
> Anywhere else that I should look for a cause?
"no encryption" does not match a "hostssl" entry.
Either add "sslmode=require" to the connection string, or use a "hostnossl"
entry.
Yours,
Laurenz Albe
Re: Prepared statements versus stored procedures
On Sun, 2023-11-19 at 17:30 +, Simon Connah wrote: > I was reading about prepared statements and how they allow the server to > plan the query in advance so that if you execute that query multiple times > it gets sped up as the database has already done the planning work. > > My question is this. If I make a stored procedure doesn't the database > already pre-plan and optimise the query because it has access to the whole > query? Or could I create a stored procedure and then turn it into a prepared > statement for more speed? I was also thinking a stored procedure would help > as it requires less network round trips as the query is already on the server. Statements in functions and procedures don't get planned until the function or procedure is called for the first time. These plans don't get cached unless the procedural language you are using has special support for that. Currently, only functions and procedures written in PL/pgSQL cache execution plans of static SQL statements. And you are right, that is usually a good thing. Yours, Laurenz Albe
Re: pg_basebackup
On Mon, 2023-11-20 at 07:30 +0100, Matthias Apitz wrote:
> We're facing in a customer installation (PostgreSQL 13.1 on Linux) the
> following problem for the first time and not reproducible:
13.1? Your immediate reaction should be "update to the latest minor release".
> ${BINDIR}/pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}
>
> The resulting stdout/stderr of the script:
>
> 16.11.2023-20:20:02: pg_basebackup the cluster to
> /Backup/postgres/sisis-20231116-1 ...
> pg_basebackup: could not receive data from WAL stream: server closed the
> connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> pg_basebackup: child process exited with error 1
>
> pg-error.log:
>
> 2023-11-16 20:34:13.538 CET [6250] LOG: terminating walsender process due to
> replication timeout
>
> Why the PostgreSQL server says something about "replication", we do
> pg_basebackup?
Because "pg_basebackup" uses a replication connection.
> Some more information:
>
> - wal_sender_timeout has default value (60s)
Increase "wal_sender_timeout", perhaps to 0 (which means "infinite").
Yours,
Laurenz Albe
Re: db size difference on primary and standby
On Thu, 2023-11-16 at 14:49 +0800, Eric Wong wrote:
> We discovered a disk usage difference between our primary and standby node.
>
> After much investigation, the disk usage difference is caused by some
> extra files within the database directory 16432. The extra files are
> only found on the standby node, not present on the primary.
>
> In the data directory ("15/data/base/16432"), the extra files do not
> seem to belong to any table. Normally, the files correspond to the
> column "relfilenode" in the "pg_class" system table; however, when we
> compare the entries in that column with the file names, around 600 of
> the files do not have a corresponding entry in the "pg_class" table.
> The same applies to the files in the "san" tablespace
> ("pg_tblspc/san/PG_15_202209061/16432").
>
> Has anyone seen this behavior before? Also, we also want to know if
> it is safe to remove all those extra files that do not have an entry
> in the "pg_class" table?
If the files only exist on the standby, you can safely remove them.
Be careful, however: if you make a mistake, you'll corrupt your database.
Orphaned files like that can be left behind after a database crash.
Did you experience crashes on the standby?
Yours,
Laurenz Albe
Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT
On Wed, 2023-11-15 at 10:57 +0100, Dimitrios Apostolou wrote: > SELECT [DISTINCT] ... EXCEPT ... > > In this query I get the same results regardless of including DISTINCT or > not. But I get different query plans, I get an extra HashAggregate node > in the case of SELECT DISTINCT. Any idea why? The DISTINCT is superfluous, because EXCEPT already removes duplicate rows. However, the planner does not invest extra processing cycles to detect that you wrote a superfluous DISTINCT, and it does not remove it. As a consequence, you end up with a pointless extra execution plan node that does not achieve anything except slowing down the query. Remove the DISTINCT. Yours, Laurenz Albe
Re: Interpreting MessageQueueSend state
On Mon, 2023-11-13 at 17:06 -0800, Benjamin Leis wrote: > I'm looking at a pg_stat_activity dump where there are a group of active > threads stuck for a long time in MessageQueueSend > 7:52.394089+00 | IPC | MessageQueueSend | active | > | 27406510 | -5892847938856697008 | SELECT > Does this mean they are blocked on clients reading data or does it > represent something blocking on the server side? That's something on the server: a parallel worker process is waiting to send data to the parallel leader process. Look for the "leader_pid" column in the "pg_stat_activity" line above and see what that process is doing. I wouldn't worry about a parellel worker in that state, as long as there is no problem with the parallel leader. Yours, Laurenz Albe
Re: Weirdness (bug?) with aggregates and subqueries
On Wed, 2023-11-08 at 16:36 -0500, Tom Lane wrote: > Laurenz Albe writes: > > I found this in a blog > > (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/): > > CREATE TABLE aa (a INT); > > INSERT INTO aa VALUES (1), (2), (3); > > CREATE TABLE xx (x INT); > > INSERT INTO xx VALUES (10), (20), (30); > > > SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; > > >sum > > ═ > > 6 > > (1 row) > > > Huh? Shouldn't that return three rows, just like > > No. The aggregate function is semantically of the closest query level > that contributes a Var to its argument, so it's evaluated at the "FROM > aa" level, causing that level to become an aggregated query that > returns just one row. Then it acts like an outer reference as far > as the sub-select is concerned. This is documented at the end of > Section 4.2.7 in our manual, > > https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES > > Thank the SQL spec for that weirdness. Thanks for the explanation. Seems like another instance of the standard committee smoking the wrong stuff. Yours, Laurenz Albe
Weirdness (bug?) with aggregates and subqueries
I found this in a blog (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/): CREATE TABLE aa (a INT); INSERT INTO aa VALUES (1), (2), (3); CREATE TABLE xx (x INT); INSERT INTO xx VALUES (10), (20), (30); SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; sum ═ 6 (1 row) Huh? Shouldn't that return three rows, just like SELECT (SELECT sum(14) FROM xx LIMIT 1) FROM aa; sum ═ 42 42 42 (3 rows) Looking at the plan of the weird query, the aggregate seems to be in the wrong place: EXPLAIN (COSTS OFF) SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; QUERY PLAN ══ Aggregate -> Seq Scan on aa SubPlan 1 -> Limit -> Seq Scan on xx (5 rows) And this gives an error: SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa; ERROR: column "aa.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa; ^ I think that the optimizer is going astray here... But perhaps I am missing something obvious. Yours, Laurenz Albe
Re: Maximum xid increasing
On Wed, 2023-11-08 at 09:09 -0500, Craig Milhiser wrote: > Our max xid are over 500m and climbing. Auto vacuuming and manual vacuuming > are returning > > autovacuuming to prevent wraparound. > WARNING: oldest xmin is far in the past > HINT: Close open transactions soon to avoid wraparound problems. > You might also need to commit or roll back old prepared transactions, or drop > stale replication slots. > > The autovacuum previously kicked in at 200m and reduced the maximum xid. > Now there is no reduction. > > We have a writer (master) node, 3 readers (slaves) and a hot-standby. We have > an ETL vendor hooked > up to replicate. All of the replications are working. There is current data > in each. We have checked > our replication slots. Everything is in use and working. > > From transactions, we have terminated every writer node PID since before this > climb has started. > Almost every client has been restarted. > > We are handling 100+ insert/updates per second. Most transactions complete in > ~2 seconds. Though some > may take 1 minute. There are no long running idle in transaction activities. > > We have been scouring the internet reading many helpful pages and trying > their advice but we cannot > find a problem. > > We have restored a daily snapshot in another cluster. After vacuuming the max > xid dropped to 50m. > No client was connected. Implying that a restart should fix the issue. > > I am searching for any hints. We have not found a smoking gun to focus on. > This is a 24x7 operation > and would rather not have to restart the server. > > We are on v13.8, AWS RDS. > > Is there a way to find which row or table or lock or some open transaction is > preventing the vacuuming > from lowering xid? Any hints on where to look? If that is not a bug introduced by Amazon, and you are certain that there are no long-running transactions and stale replication slots, there are two more possibilities: 1. a prepared transaction (check pg_prepared_xacts) 2. a long-running query on a standby, and "hot_standby_feedback = on" Yours, Laurenz Albe
Re: Postgres Out Of Memory Crash
On Thu, 2023-11-02 at 09:12 +, Avi Weinberg wrote: > I'm using Patroni Postgres installation and noticed that twice already > postgres > crashed due to out of memory. I'm using logical replication with around 30-40 > active subscribers on this machine. The machine has 128GB but only 32GB is > allocated > to Postgres. How can I know what is actually causing the out of memory issue? > Is it caused by not optimal postgres configuration or something else? You should look into the PostgreSQL log. That should show a message like LOG: server process (PID 16024) was terminated by signal 9: Killed DETAIL: Failed process was running: SELECT ... It is not certain, but often that statement is the one that used up all that memory. At least it is a starting point for your investigation. Yours, Laurenz Albe
Re: pg_dump/pg_restore --jobs practical limit?
On Wed, 2023-11-01 at 13:09 -0500, Ron wrote: > I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg > 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed > by LVM, and are all on ESX blades. nproc count on some is 16 and on others > is 32. > > Does anyone have experience as to the point of diminishing returns? > > IOW, can I crank them processes up to --jobs=30, will I see no gain -- or > even degradation -- after, for example, --jobs=24? > > This would be for both pg_dump and pg_restore (which would be run on the > RHEL 8 VM). Test, test, test. Theoretical considerations are pretty worthless, and it is easy to measure that. Yours, Laurenz Albe
Re: xmax not zero?
On Tue, 2023-10-31 at 08:23 +0100, Luca Ferrari wrote: > What puzzled me was that such xmax was non-zero for a > while (days). Inspecting pg_class.reloptions I found that accidentally > (?) disabled autovacuum on such table, my fault! > I should have checked pg_stat_user_tables at first! VACUUM won't modify "xmax", nor will it remove the row. Yours, Laurenz Albe
Re: Help with a good mental model for estimating PostgreSQL throughput
On Mon, 2023-10-30 at 08:59 -0700, David Ventimiglia wrote: > On Mon, Oct 30, 2023 at 8:46 AM Laurenz Albe wrote: > > On Mon, 2023-10-30 at 08:05 -0700, David Ventimiglia wrote: > > > Can someone help me develop a good mental model for estimating PostgreSQL > > > throughput? > > > Here's what I mean. Suppose I have: > > > * 1000 connections > > > * typical query execution time of 1ms > > > * but additional network latency of 100ms > > > What if at all would be an estimate of the number of operations that can > > > be performed > > > within 1 second? My initial guess would be ~1, but then perhaps I'm > > > overlooking > > > something. I expect a more reliable figure would be obtained through > > > testing, but > > > I'm looking for an a priori back-of-the-envelope estimate. Thanks! > > > > It depends on the number of cores, if the workload is CPU bound. > > If the workload is disk bound, look for the number of I/O requests a > > typical query > > needs, and how many of them you can perform per second. > > > > The network latency might well be a killer. > > > > Use pgBouncer with transaction mode pooling. > > Thanks! Let's say there are 10 cores, the workload is not CPU bound, and > there is a > connection pooler like pgBouncer in place. Would the number of operations > more likely be: > > * 1000 ms / total ms per operation * number of cores = 1000 ms / 101 ms * 10 > = ~100 > * 1000 ms / total ms per operation * number of connections = 1000 ms / 101 > ms * 1000 = ~1 > * something else > * impossible to determine without more information If the workload is not CPU bound, it is probably disk bound, and you have to look at the number if I/O requests. If you look at the CPU, the second calculation should be more to the point. However, if one request by the customer results in 10 database requests, the request will already take 2 seconds due to the network latency, even though it causes next to no load on the database. Yours, Laurenz Albe
Re: Help with a good mental model for estimating PostgreSQL throughput
On Mon, 2023-10-30 at 08:05 -0700, David Ventimiglia wrote: > Can someone help me develop a good mental model for estimating PostgreSQL > throughput? > Here's what I mean. Suppose I have: > * 1000 connections > * typical query execution time of 1ms > * but additional network latency of 100ms > What if at all would be an estimate of the number of operations that can be > performed > within 1 second? My initial guess would be ~1, but then perhaps I'm > overlooking > something. I expect a more reliable figure would be obtained through > testing, but > I'm looking for an a priori back-of-the-envelope estimate. Thanks! It depends on the number of cores, if the workload is CPU bound. If the workload is disk bound, look for the number of I/O requests a typical query needs, and how many of them you can perform per second. The network latency might well be a killer. Use pgBouncer with transaction mode pooling. Yours, Laurenz Albe
