Now do I move a tablespace on a windows installation of postgres? This thread shows how to do it on unix, not windows. The tablespace was originally created with this command... CREATE TABLESPACE tabspace2 OWNER theowner LOCATION 'e:/pgdata2/fdb'; The command ALTER TABLESPACE allows the name to be edited, but I need to change the file location. Work from the previous thread, I can get the oid from SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tabspace2'; Digging through my postgres installation I've found this folder where the bottom level foldername matches the oid c:\pg_data\14\data\pg_tblspc\57620. The icon for this folder has a little blue arrow in the bottom left corner - indicating (I think) that the folder is a link. I believe that this links to the location on e: that was specified when the table space was created. The trail starts to run cold here, I think windows links can only be dropped or created (not edited), so how can I move the tablespace? It's currently populated with live data, so I'm trying to establish the correct methodology *before* I start fiddling. Any help appreciated.

I have the following table (in PostgreSQL 14.6):

create table waste_trajectory
(
    id               uuid default uuid_generate_v4() not null primary key,
    observation_id   uuid not null,
    tank_id          varchar(30),
    stored_on        timestamp with time zone default now(),
    score            numeric(14, 10)
);

... with this index:

CREATE INDEX IF NOT EXISTS idx_wt_stored_on_desc
    ON waste_trajectory (stored_on desc);

The table contains a large amount of data. If I run the following query:

SELECT
    id,
    observation_id,
    tank_id,
    -- stored_on::text,
    score
    FROM waste_trajectory
ORDER BY stored_on DESC
LIMIT 1;

I get results in a reasonable amount of time:

[2025-05-12 14:02:46] completed in 969 ms

But if I un-comment that stored_on::text line, the query takes over 10 minutes:

[2025-05-12 14:14:39] completed in 11 m 39 s 250 ms

The EXPLAIN ANALYSE for the un-commented version of the query shows:

|Limit  (cost=24469520.63..24469520.75 rows=1 width=1035) (actual time=736266.363..736286.177 rows=1 loops=1)                                                                        |
|  ->  Gather Merge  (cost=24469520.63..46550572.58 rows=184416572 width=1035) (actual time=736211.570..736231.382 rows=1 loops=1)                                                   |
|        Workers Planned: 4                                                                                                                                                          |
|        Workers Launched: 4                                                                                                                                                         |
|        ->  Sort  (cost=24468520.57..24583780.93 rows=46104143 width=1035) (actual time=736147.303..736147.305 rows=1 loops=5)                                                      |
|              Sort Key: ((stored_on)::text) DESC                                                                                                                                    |
|              Sort Method: top-N heapsort  Memory: 31kB                                                                                                                             |
|              Worker 0:  Sort Method: top-N heapsort  Memory: 26kB                                                                                                                  |
|              Worker 1:  Sort Method: top-N heapsort  Memory: 26kB                                                                                                                  |
|              Worker 2:  Sort Method: top-N heapsort  Memory: 28kB                                                                                                                  |
|              Worker 3:  Sort Method: top-N heapsort  Memory: 26kB                                                                                                                  |
|              ->  Parallel Seq Scan on waste_trajectory  (cost=0.00..24237999.86 rows=46104143 width=1035) (actual time=1032.453..712735.895 rows=36882681 loops=5)|
|Planning Time: 0.104 ms                                                                                                                                                             |
|JIT:                                                                                                                                                                                |
|  Functions: 11                                                                                                                                                                     |
|  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                       |
|  Timing: Generation 2.080 ms, Inlining 397.283 ms, Optimization 186.396 ms, Emission 124.416 ms, Total 710.175 ms                                                                  |
|Execution Time: 736286.614 ms

Unfortunately, I don't have the liberty of changing the query, since it's in a component I don't control. Is there a way to handle this strictly using a different index? I tried:

CREATE INDEX IF NOT EXISTS idx_wt_stored_on_desc_text
    ON waste_trajectory (cast(stored_on as text) desc);

... but this returns:

[42P17] ERROR: functions in index expression must be marked IMMUTABLE

Can I improve the performance of this query without resorting to a custom IMMUTABLE function for converting the timestamp to text?

According to SELECT pg_database_size('mydb') my database is about 15TBytes. According to SELECT spcname FROM pg_tablespace I have a total of 5 tablespaces (pg_global, pg_default and 3 others I created). If I use SELECT pg_tablespace_size ('????') on each tablespace I find I have about 25Mbytes in the two system tablespaces, and less than 10TBytes in the other 3 tablespaces. What accounts for the 5TByte discrepancy? Some similar thread suggests that this may be 'toast' space. But this query... SELECT oid::regclass, reltoastrelid::regclass, pg_relation_size(reltoastrelid) AS toast_size FROM pg_class WHERE relkind = 'r' AND reltoastrelid 0 ORDER BY 3 DESC; Indicates no more than a few MBytes of toast The database is mostly used as a data-warehouse, so there's very little update/deletion of data once it's been inserted, but I've tried doing a VACUUM FULL on some of the larger tables, but using this query... SELECT (pg_total_relation_size(c.oid) + pg_indexes_size(c.oid) ) as tablePlusIdx FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' AND relname = 'mytable' before and after the VACUUM FULL indicates a space saving of only a few percent. What else might account for the difference between pg_database_size() and the sum size of all the individual partitions? Is there any data stored outside of the tablespaces? I would prefer NOT to have to try a VACUUM FULL on all the tables, firstly because it doesn't seem to make any difference (not on the tables I've tried anyhow) and because the DB is so large even a single VACUUM FULL can take several hours.

We have a query that intermittently runs very poorly for no apparent reason. We have Postgres 14.13 configured with pg.log_min_duration_statement set to 30000. We have a query that runs as part of our core hosting service that is basically

SELECT * FROM table 
LEFT OUTER JOIN table2 ON table.table2_id = table2_id 
WHERE "table"."url" = ? LIMIT 1;

This runs thousands of times every hour, and is fine, but then occasionally we'll get a log saying it took **minutes**. Here's an example log

pid=864204,user=service_account,db=app_db,app=pg-node,client=XX.XX.XX.XX LOG:  duration: 156414.095 ms  statement:  SELECT * FROM table LEFT OUTER JOIN table2 ON table.table2_id = table2_id WHERE "table"."url" = ? LIMIT 1;

Here's an example log for the auto_explain

pid=2377906,user=service_account,db=app_database,app=pg-node,client=XX.XX.XX.XX LOG: duration: 156414.095 ms plan:
Query Text: SELECT * ...
Limit (cost=5.29..8.33 rows=1 width=741) (actual time=0.153..0.157 rows=1 loops=1
-> Nested Loop Left Join (cost=5.29..8.33 rows=1 width=741) (actual time=0.152..0.154 rows=1 loops=1
-> Bitmap Heap Scan on XXXX (cost=5.01..6.02 rows=1 width=546) (actual time=0.112..0.113 rows=1 loops=1)
Recheck Cond: (XXXXXX)
Filter: ((XXXXX) AND (XXXXX))
 Heap Blocks: exact=1
Bitmap Index Scan on XXXXX (cost=0.00..5.01 rows=1 width=0) (actual time=0.097..0.097 rows=1 loops=1)
Index Cond: (XXXXX)
-> Index Scan using XXXX on table (cost=0.29..2.30 rows=1 width=422) (actual time=0.037..0.037 rows=1 loops=1)
Index Cond: (XXXXX)
Filter: (XXXXX)

Metrics show no extra load on the server (CPU is below 20%, memory is unchanged, there's no spike in other queries). We turned on auto_explain to try and track this down, but the analysis shows that all parts of the query took <1ms The database where this is occurring is a read replica. We've contacted support for the hosting service providing the database. They've suggested that network errors with the connection to the client could be causing the issue. Is this possible? If not, what other explanations could there be?

We have an AWS RDS PostgreSQL (v14.10) instance running on a db.t3.micro instance. Randomly throughout the day (some days more than others), our .NET Core application starts failing with the following exceptions: An exception occurred while iterating over the results of a query for context type 'App.Server.Database.DbContext'. System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. ---> Npgsql.PostgresException (0x80004005): 53300: sorry, too many clients already System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. ---> Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.TimeoutException: Timeout during reading attempt We haven't been able to track down the root cause yet, thinking we have an application side connection leak somewhere, but that hasn't yielded any results. With the t3.micro instance type, our max_connections is set to 81 (using this query select * from pg_settings where name='max_connections';), but we never see the DatabaseConnection metric in RDS monitoring get above 25-30. See the following screenshot of our metrics. We started throwing these errors just after 12:30 (see red arrow on right) Has anyone see this issue before, or is there something else I should be looking into? Can anyone explain why the DatabaseConnections metric shows a count much lower than our max?

We have a table in main DB that is getting logically replicated to another DB called SOR. We need to rename this table, and add a bunch of tables to the publication on the main DB. I tried this procedure 1. disable the subscriber on SOR DB 2. rename the (receiving) table on SOR DB 3. rename the table on main DB, also add the tables to the publication 4. re-enable subscriber on SOR DB 5. refresh the subscription ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION WITH (copy_data=false); I waited for the replication slot on main DB to clear up, and then checked record count, SOR DB lost some data in this time window. Is there a seamless way to rename a table in logical replication without losing data on the subscriber side ? Would (copy_data=true) help ? Thank you ! some more details: 1. we use PG 14 on AWS RDS 2. The reason for this change is we are migrating a parent-child table into a partitioned table 3. The said table (to be renamed) is close to 6 TB, and the DB is our live production database, so we would like to avoid copying this table over from scratch.

I have some large sets of tables that I partition by date. Every day I run a query similar to this to merge the two tables WITH moved_rows AS ( DELETE FROM tableA_20230531 RETURNING * ) INSERT INTO tableA_202305 SELECT * FROM moved_rows The table called tableA_20230531 contains all my data for 1 day - averages about 1Gbyte. The table tableA_202305 contains the data for the month so far, obviously this gets bigger as the month progresses. Sometimes the query falls due to lack of space (**ERROR: 53100: could not extend file.... No space left on device** ). I know how large the two tables are, and I know how much free space I have on the disk. Is there any way of working out (before running) if I have enough space for the query to execute successfully? Somedays it will happily work even if the available freespace is less than the size of the daily table. Otherdays it will fail even with freespace more than twice the size of the daily table. (no other users connected when this is running, whole database on same disk, one index, about 10% of the table size - an additional 100Mbyte per day. Archiving, replication and checkpointing all left to original, as-installed default settings - that's my of saying I don't know!)