Re: Partitioning options
On Thu, Feb 8, 2024 at 10:25 PM Justin wrote: > Hi Sud, > > Would not look at HASH partitioning as it is very expensive to add or > subtract the number of partitions. > > Would probably look at a nested partitioning using customer ID using > range or list of IDs then by transaction date, Its easy to add > partitions and balance the partitions segments. > I'll not do that because, then, when getting rid of obsolete data, you must delete a huge number of records, and vacuum each partition. if partitioning by date, you will ease greatly the cleaning, by just getting rid of obsolete partitions which is quite speedy.( no delete, no vacuum, no index updates, ...) Marc > Keep in mind that SELECT queries being used on the partition must use the > partitioning KEY in the WHERE clause of the query or performance will > suffer. > > Suggest doing a query analysis before deploying partition to confirm the > queries WHERE clauses matched the planned partition rule. I suggest that > 80% of the queries of the executed queries must match the partition rule if > not don't deploy partitioning or change all the queries in the > application to match the partition rule > > > On Thu, Feb 8, 2024 at 3:51 PM Greg Sabino Mullane > wrote: > >> Out of curiosity, As OP mentioned that there will be Joins and also >>> filters on column Customer_id column , so why don't you think that >>> subpartition by customer_id will be a good option? I understand List >>> subpartition may not be an option considering the new customer_ids gets >>> added slowly in the future(and default list may not be allowed) and also OP >>> mentioned, there is skewed distribution of data for customer_id column. >>> However what is the problem if OP will opt for HASH subpartition on >>> customer_id in this situation? >>> >> >> It doesn't really gain you much, given you would be hashing it, the >> customers are unevenly distributed, and OP talked about filtering on the >> customer_id column. A hash partition would just be a lot more work and >> complexity for us humans and for Postgres. Partitioning for the sake of >> partitioning is not a good thing. Yes, smaller tables are better, but they >> have to be smaller targeted tables. >> >> sud wrote: >> >> 130GB of storage space as we verified using the "pg_relation_size" >>> function, for a sample data set. >> >> >> You might also want to closely examine your schema. At that scale, every >> byte saved per row can add up. >> >> Cheers, >> Greg >> >>
Re: Multiple connections over VPN password fail error
On Fri, 2024-02-09 at 15:04 +0530, Sanjay Minni wrote: > for the second user/role over vpn > even with md5 I get the error: > P3Error PostgreSQL password authentication failed for user "" > am only able to connect if method is "trust". Why is this so > > # IPv4 external connections thru VPN > #TYPE DATABASE USER ADDRESS METHOD > host all all scram-sha-256 (or "md5" - second > user is only able to connect if this is "trust" Please tell us the exact client software in use on the failing client. Also, what is the error message 1) on the client side 2) in the PostgreSQL server log Yours, Laurenz Albe
Re: Multiple connections over VPN password fail error
Hi, for the second user/role over vpn even with md5 I get the error: P3Error PostgreSQL password authentication failed for user "" am only able to connect if method is "trust". Why is this so # IPv4 external connections thru VPN #TYPE DATABASE USER ADDRESS METHOD host all all scram-sha-256 (or "md5" - second user is only able to connect if this is "trust" regards Sanjay On Fri, Feb 9, 2024 at 2:44 PM Sanjay Minni wrote: > so why do I get a password error when i try to connect 2 users over VPN > from the same machine to the same host with the following settings in > pg_dba.conf - how to find the issue > ( user1:user1pwd@ & user2:user2pwd@ ) > > # IPv4 external connections thru VPN > #TYPE DATABASE USER ADDRESS METHOD > host all all scram-sha-256 > and whats the best option keeping security in mind > > regards > Sanjay > > > On Fri, Feb 9, 2024 at 1:26 PM Daniel Gustafsson wrote: > >> > On 9 Feb 2024, at 08:41, Sanjay Minni wrote: >> >> > while trying to make multiple connects with different role names to a >> single database over VPN i faced a password error issue when trying to >> connect a send user >> > It seems I had to change this line in pg_hba.conf and it worked: >> > >> >`# IPv4 external connections thru VPN >> > #TYPE DATABASE USER ADDRESS METHOD >> > host all all trust `<=(from the >> earlier scram-sha-256) >> > >> > is this the way and is this correct from a security point of view ? >> >> While correctness and security always needs to be evaluated from the >> specific >> needs of an installation, the odds are pretty good that "No" is the >> correct >> answer here. To quote the documentation on the "trust" setting: >> >> "Allow the connection unconditionally. This method allows anyone >> that >> can connect to the PostgreSQL database server to login as any >> PostgreSQL user they wish, without the need for a password or any >> other >> authentication." >> >> I would recommend immediately reverting back to the scram-sha-256 setting >> and >> figuring out why you were unable to login. >> >> -- >> Daniel Gustafsson >> >>
Re: Multiple connections over VPN password fail error
so why do I get a password error when i try to connect 2 users over VPN from the same machine to the same host with the following settings in pg_dba.conf - how to find the issue ( user1:user1pwd@ & user2:user2pwd@ ) # IPv4 external connections thru VPN #TYPE DATABASE USER ADDRESS METHOD host all all scram-sha-256 and whats the best option keeping security in mind regards Sanjay On Fri, Feb 9, 2024 at 1:26 PM Daniel Gustafsson wrote: > > On 9 Feb 2024, at 08:41, Sanjay Minni wrote: > > > while trying to make multiple connects with different role names to a > single database over VPN i faced a password error issue when trying to > connect a send user > > It seems I had to change this line in pg_hba.conf and it worked: > > > >`# IPv4 external connections thru VPN > > #TYPE DATABASE USER ADDRESS METHOD > > host all all trust `<=(from the earlier > scram-sha-256) > > > > is this the way and is this correct from a security point of view ? > > While correctness and security always needs to be evaluated from the > specific > needs of an installation, the odds are pretty good that "No" is the correct > answer here. To quote the documentation on the "trust" setting: > > "Allow the connection unconditionally. This method allows anyone > that > can connect to the PostgreSQL database server to login as any > PostgreSQL user they wish, without the need for a password or any > other > authentication." > > I would recommend immediately reverting back to the scram-sha-256 setting > and > figuring out why you were unable to login. > > -- > Daniel Gustafsson > >
