Re: Rearchitecting for storage

[Jacob Bunk Nielsen]

Matthew Pounsett  writes:
> On Thu, 18 Jul 2019 at 19:53, Rob Sargent  wrote:
>
>  Can you afford to drop and re-create those 6 indices?
>
> Technically, yes. I don't see any reason we'd be prevented from doing that. 
> But, rebuilding them will take a long time. That's a lot of downtime to incur 
> any time we update
> the DB. I'd prefer to avoid it if I can. For scale, the recent 'reindex 
> database' that failed ran for nine days before it ran out of room, and that 
> was in single-user. Trying to do
> that concurrently would take a lot longer, I imagine.

This may be a stupid question, but are you certain they are all used? It
wouldn't be the first time that I've seen someone create indexes and
then never use them. This script can tell you if there are any indexes
that seems largely unused.

https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql

If you can run your application without access to the indexes for a
while you can create them concurrently in the background using "CREATE
INDEX CONCURRENTLY ...".

Best regards,
Jacob

Re: Rearchitecting for storage

[Jacob Bunk Nielsen]

Matthew Pounsett  writes:

> [...] Is there any rule of thumb for making sure one has enough space
> available for the upgrade?

No, because it depends greatly on which version you are upgrading from
and which version you are upgrading to etc.

Perhaps you could carve out a slice of data, e.g. 1 GB and load it into
a test database and try to upgrade that. That would probably give you an
idea.

Also, you mentioned that your database contains historical test data¹,
then I would guess that one of the indexes is related to timestamps? But
maybe you could live with a smaller BRIN index for the timestamps:
https://www.postgresql.org/docs/11/brin-intro.html - that could
potentially save some space, and may not have been something on the
radar when the database was first developed.

Best regards,
Jacob

¹) I think I know which kind of data based on your progress reports on
   a DNS related list I'm subscribed to.

Re: SQL questiom

[Jacob Bunk Nielsen]

haman...@t-online.de writes:

> I am using a query pretty often that looks like
> SELECT <> WHERE <> AND
> <>
>
> Is there a way (with sql or plpgsql)  to convert that into
> SELECT myquery('<>')

I would solve that by creating a view like:

CREATE VIEW some_view_name AS 
  SELECT <> WHERE <>;

See also https://www.postgresql.org/docs/14/sql-createview.html

Then you can query that view with your actual select criterion like:

SELECT * FROM some_view_name WHERE <>;

Best regards,
Jacob