3
votes
3answers
194 views

PostgreSQL 9.0 how to reclaim disk space after delete without rebuilding table?

Our PostgreSQL 9.0 Windows production server is running low on space. In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the ...
1
vote
2answers
481 views

Avoiding a sort on an already clustered index for group by

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid: select count(did), pid from T group by pid Here is the query plan: ...
2
votes
2answers
1k views

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
5
votes
1answer
331 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
4
votes
3answers
975 views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...