Re: Re: posgres-l ?

Still looking into potentially using Yugabyte, Spanner of ever Oracle

Success on the convection of Postgress

(though I've managed to take the database down 3 time unexpectedly due to constraint issues and failing to pre-allocarted the new partitions ... three down times that would have been avoided on Oracle ... postgres is just not worth the price savings if you are running a critical work load that is high concurrency and high volume)

Conversion to partitioning with least amount of downtime on Postgres ... a tail of stress, success and failure
https://www.kylehailey.com/post/postgres-partition-conversion-minimal-downtime

On Mon, Apr 17, 2023 at 11:33 AM Mark Burgess < mark_at_burgess-consulting.com.au> wrote:

> Hi Kyle,
>
> how did you go with getting this fixed?
>
> Cheers,
>
> Mark
> On 18 Mar 2023 at 5:49 PM +1100, kyle Hailey <kylelf_at_gmail.com>, wrote:
>
>
> just hearing about timescaledb
> We are on managed Cloud SQL on GCP so it's not an offering there.
> I'm always stressed about migration operatons from one source to another.
> We will be implementing Kafka soon so that should make it easier to fork
> incoming requests to the old data source and new
>
> On Fri, Mar 17, 2023 at 5:52 AM Mark Burgess <
> mark_at_burgess-consulting.com.au> wrote:
>
>> No worries at all.
>>
>> Do the search queries have a date range filter in them? Also what version
>> of PG are you running?
>>
>> Reading through this I was thinking whether timescaledb (
>> www.timescaledb.com) would be a good fit for this particular table?
>>
>> Regards,
>>
>> Mark
>> On 17 Mar 2023 at 9:23 PM +1100, kyle Hailey <kylelf_at_gmail.com>, wrote:
>>
>>
>> Thanks for reaching out!
>>
>> Wanting to partition a table over this weekend.
>>
>> Have an 8TB table that that represents all customer requests - about 500K
>> new requests an hour, 7K queries a second
>> All running well - concurrent queries is about 20, which makes it about
>> 2.3ms on average per query to hit 7k queries a second.
>> All that works.
>>
>> Problem is searches are taking longer and longer.
>> I want to add indexes and they take 12 hours to add.
>> I'd like it to be much faster. Might have to try out a number of various
>> index approaches before getting the right one,
>> so want to be able to iterate quickly.
>> Purging data is expensive - deletes are a slow operation at scale.
>> Thinking that we should partition.
>> Issue is how to partition with the least amount of down time possible.
>> Also reading about partitions, it sounds like partitions add
>> significantly to query parsing time which sounds troubling.
>> Ideally I'd like to partition by hour as that's amount data is fast to
>> manipulate, but might have to partition by day to keep # of partitions down.
>> But it sounds like we should keep it to under 100 partitions?
>> Wondering if there could be some sort of partition rollup, i.e partition
>> by hour
>> then merge hours into days , into weeks ??
>>
>> Tried adding.a partial index on a sparse field using where clause that
>> included the rare value and a small date range, the last 24 hours .
>> The query on the sparse field and date range takes seconds because there
>> is an index on the date column.
>> The partial index takes 12 hours to build because for some reason it
>> reads the entire table instead of using the index on the date field for
>> the last 24 hours (and does it twice with concurrently option)
>>
>> I have a budget for outside consulting.
>> Let me know what you think.
>>
>> On Fri, Mar 17, 2023 at 1:25 AM Mark Burgess <
>> mark_at_burgess-consulting.com.au> wrote:
>>
>>> Hi Kyle,
>>>
>>> Let me know what you need help on and I’ll do my best to give you some
>>> pointers.
>>>
>>> You have done lots for the Oracle community over the years - would be
>>> great to be able to help you out in some way.
>>>
>>> Cheers,
>>>
>>> Mark
>>> ---------- Forwarded message ----------
>>> *From:* Mark Burgess <mark_at_burgess-consulting.com.au>
>>> *Date:* 17 Mar 2023 at 8:02 AM +1100
>>> *To:* kylelf_at_gmail.com
>>> *Cc:* Oracle List List <oracle-l_at_freelists.org>
>>> *Subject:* Re: posgres-l ?
>>>
>>> Hi Kyle,
>>>
>>> pgsql-performance_at_postgresql.org
>>> <https://mailto:pgsql-performance_at_postgresql.org> is ok for performance
>>> related questions etc.
>>>
>>> I can’t say that I have found anything the equivalent of oracle-l in the
>>> Postgres world.
>>>
>>> Cheers - Mark
>>> On 17 Mar 2023 at 3:10 AM +1100, kyle Hailey <kylelf_at_gmail.com>, wrote:
>>>
>>>
>>> Any good Postgres email list like Oracle-l?
>>> I just inherited a Postgres system and am having a number of questions.
>>>
>>> PS if anyone is up for consulting on Postgres , I might be able to
>>> allocate some budget.
>>>
>>> Kyle
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l