Re: how to securely delete the storage freed when a table is dropped?

On 13 April 2018 at 18:48, Jonathan Morgan wrote: > For a system with information stored in a PostgreSQL 9.5 database, in which > data stored in a table that is deleted must be securely deleted (like shred > does to files), and where the system is persistent even

A limit clause can cause a poor index choice

The attached script builds a 10G test table which demonstrates a problem that we have in production with postgresql 12.3-1.pgdg18.04+1 on ubuntu linux. Indexes: test_orders_o_date_idx btree(o_date) test_orders_customer_id_o_date_idx btree(customer_id, o_date) We query for the most recent orders

Re: A limit clause can cause a poor index choice

On Tue, 19 May 2020 at 21:56, Mohamed Wael Khobalatte wrote: > I believe a second ordering, by id desc, will get your query to use the right > index, and shouldn't be functionally different from what you would expect. Thanks, that works nicely on our production table, even with much larger

Re: A limit clause can cause a poor index choice

On Tue, 19 May 2020 at 22:15, Michael Lewis wrote: > Increase default_statistics_target, at least on that column, and see if you get a much much better plan. I don't know where I got this query from online, but here ya go. I'd be curious how frac_MCV in this changes when

concurrent re-partitioning of declarative partitioned tables

I want to set up a large table on postgresql 12.4, using declarative partitioning to partition by record creation date. I'd like to have recent records in small partitions but old records in a few larger partitions, so I want merges. The merges should be concurrent, in the sense that they lock out

Re: concurrent re-partitioning of declarative partitioned tables

On Wed, 2 Dec 2020 at 15:59, Michael Lewis wrote: > You can not have overlapping partitions that are both attached. > Not directly, no. That's why I'm considering the _partition_channel hack. Why do you want to merge partitions that you are "done with" instead of > just leaving them

Re: concurrent re-partitioning of declarative partitioned tables

On Wed, 2 Dec 2020 at 16:07, David G. Johnston wrote: > On Mon, Nov 30, 2020 at 8:36 AM Nick Cleaton wrote: > >> I want to set up a large table on postgresql 12.4, using declarative >> partitioning to partition by record creation date. I'd like to have recent >> rec

Re: Advice on using materialized views

On Mon, 6 Dec 2021 at 18:48, Phil Endecott wrote: > > - I have a raw data table, indexed on a timestamp column, to which > new data is regularly being added. > > - I have a set of views that produce hourly/daily/monthly summaries > of this data. Querying these views is slow, so I'd like to

Re: Is this a reasonable use for advisory locks?

On Thu, 14 Apr 2022 at 10:47, Steve Baldwin wrote: > Ok, so you want to allow _other_ updates to a customer while this process > is happening? In that case, advisory locks will probably work. The only > consideration is that the 'id' is a bigint. If your customer id maps to > that, great. If not

Re: Gradual migration from integer to bigint?

On Sat, 30 Sept 2023, 23:37 Tom Lane, wrote: > > I think what you're asking for is a scheme whereby some rows in a > table have datatype X in a particular column while other rows in > the very same physical table have datatype Y in the same column. > An alternative for NOT NULL columns would be

Re: Handling glibc v2.28 breaking changes

On Mon, 25 Apr 2022 at 12:45, Laurenz Albe wrote: > > You could consider upgrade in several steps: > > - pg_upgrade to v14 on the current operating system > - use replication, than switchover to move to a current operating system > on a different > machine > - REINDEX CONCURRENTLY all indexes

Re: AW: [Extern] Re: consistent postgresql snapshot

On Thu, 12 May 2022 at 14:48, Tom Lane wrote: > "Zwettler Markus (OIZ)" writes: > > I don't want to do use the normal backup algorithm where pg_start_backup > + pg_stop_backup will fix any fractured block and I am required to have all > archived logfiles, therefore. > > I want to produce an