Table Inheritance

Örnek

Şöyle yaparız

CREATE TABLE customers (
  name TEXT,
  age INT,
  address TEXT
);
 
CREATE TABLE office_customers(
  office_address TEXT
) INHERITS (customer)

Biraz veri ekleyelim

INSERT INTO customers VALUES('ravi','32','32, head street');
INSERT INTO customers VALUES('michael','35','56, gotham street');

INSERT INTO office_customers VALUES('bane','28','56, circadia street','92 homebush');

ve select yapalım. Ata tabloya erişmek için ONLY kullanılır.

SELECT * FROM ONLY customer WHERE age > 20 ;

Çıktı olarak ravi ve michael satırlarını alırız. Tüm tablo hiyerarşisine erişmek için sadece SELECT yaparız

SELECT * FROM customer WHERE age > 20 ;

Çıktı olarak ravii michael ve bane satırlarını alırız.

UNIQUE Constraint

Giriş

Unique kısıtı create table esnasında veya daha sonra alter table ile eklenebilir.

Örnek

Şöyle yaparız

ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE ("col1", "col2");

Unique Sütun Null Değer Almamalı

Açıklaması şöyle. Çünkü Null unknown yani bilinmeyen değeri temsil eder. İki tane unknown ise birbiriyle karşılaştırılamaz.

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

Örnek

Elimizde şöyle bir constraint olsun. Yani tüm sütunları kısıta dahil edelim.

ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE
("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8");

Aynı değere sahip iki tane satırı ekleyebildiğimizi görürüz

INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") 
VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);
INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") 
VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);

JDBC PostgreSQL Connection String

Giriş

Açıklaması şöyle. Yani host, port, database parametreleri tanımlanabilir.. Eğer tanımlanmazsa her birinin varsayılan değeri var. Örneğin database tanımlanmazsa bağlanırken kullanılan username kabul edilir.

... this takes one of the following forms:

- jdbc:postgresql:database

- jdbc:postgresql:/

- jdbc:postgresql://host/database

- jdbc:postgresql://host/

- jdbc:postgresql://host:port/database

- jdbc:postgresql://host:port/

The parameters have the following meanings:

host = The host name of the server. Defaults to localhost . To specify an IPv6 address your must enclose the host parameter with square brackets, for example: jdbc:postgresql://[::1]:5740/accounting

port = The port number the server is listening on. Defaults to the PostgreSQL® standard port number (5432).

database = The database name. The default is to connect to a database with the same name as the user name used to connect to the server.

JDBC ile bağlanmak için gereken parametreler Postgres jdbc documentation sayfasında.

Veri Tabanı İsmi Belirtilmezse

Yani jdbc:postgresql://host:port/ şeklide belirtirsek varsayılan veri tabanı ismi kullanıcı ismi ile aynı. Açıklaması şöyle

... the dbname parameter in the connection string defaults to the value of the user parameter.

currentSchema

Schema yazısına taşıdım. Kısaca birden fazla schema'yı görebilsin diye şöyle yaparız

jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public

reWriteBatchedInserts

Açıklaması şöyle

Asking PostgreSQL to Rewrite batched inserts

Hibernate will send a multiple insert statements to RDBMS at once, in order to insert data, and this will be done in the same transaction which is great; however if we are using PostgreSQL we could go a little further in our optimizations by asking him to rewrite those inserts to a single multi-value insert statement; this way if we have a 100 insert statements, it will rewrites them to a single multi-value statement.

Ayrı ayrı olan bir çok INSERT cümlesini tek bir INSERT + çoklu VALUES haline getirir. Yani şöyle olur

// before Rewrite batched inserts
INSERT INTO container( ...) VALUES (...);
INSERT INTO container( ...) VALUES (...);
....
INSERT INTO container( ...) VALUES (...);
 
 
// After PostgreSQL to Rewrite batched inserts
INSERT INTO container( ...) VALUES (...),(...) ..., (...);

Örnek

Şöyle yaparız

jdbc:postgresql://localhost:5432/mastership?reWriteBatchedInserts=true

Prepared Statement Ayarları

3 tane ayar yapılabilir. Açıklaması şöyle

prepareThreshold, default: 5

preparedStatementCacheQueries, default: 256

preparedStatementCacheSizeMiB, default: 5

prepareThreshold

Açıklaması şöyle 5 prepared statement çalıştırıldıktan sonra server-side hale geliyor.

When a prepared statement is defined and executed in Java, it doesn't create a server-side prepared statement. Instead, it waits for 4 client-side executions before a server-side prepared statement is created. 

preparedStatementCacheQueries

Açıklaması şöyle. Prepared statement kapatılsa bile sunucuda yaşamaya devam ediyor.

A prepared statement is automatically cached. It's logical a prepared statement is kept available as long as the prepared statement is not closed. It's less obvious that the prepared statement is cached even if it's closed. The reason for the cache is to keep a potential server-side prepared statement in existence.

preparedStatementCacheSizeMiB

Açıklaması şöyle

The prepared statement cache described with preparedStatementCacheQueries also has a limit in size, which is 5 MB. Please mind this is the client-side prepared statement itself, not the data.

What I don't know for the cache's number of queries and size is how that relates to open/used prepared statements, in other words: is the number the total (open+closed) number or the number of closed prepared statements, and does this limit open cursors.

user + password Ayarları

Örnek

Şöyle yaparız

jdbc:postgresql://myserver:5432/test?user=postgres&password=<postgresql-password>

ssl Ayarları

Açıklaması şöyle. Yani SSL kullanılmasını etkinleştirir. 

Connect using SSL. The server must have been compiled with SSL support. This property does not need a value associated with it. The mere presence of it specifies an SSL connection. However, for compatibility with future versions, the value “true” is preferred.

Şöyle yaparız

jdbc:postgresql://postgres.example.com/test_db?user=bob&password=secret&ssl=true

SSL çeşitli modlarda çalışabilir. Modlar sslmode=XXX şeklinde belirtilir. Modlar şöyle

disable

allow : Non-validating

prefer : Non-validating

require : Non-validating

verify-ca : Validating

verify-full : Validating

verify-full Modu

Açıklaması şöyle. Yani varsayılan davranış  verify-full 

As of 42.2.5 ssl=true implies verify-full as per the release notes. If you wish to get the old behaviour use sslmode=require

Bu modda şöyle bir hata alabiliriz

org.postgresql.util.PSQLException: Could not open SSL root certificate file
  blahblah/.postgresql/root.crt.

veya

org.postgresql.util.PSQLException: Could not open SSL root certificate file 
  C:\Users\user\AppData\Roaming\postgresql\root.crt.

Örnek

Şöyle yaparız

jdbc:postgresql://myamazondomain.rds.amazonaws.com:5432/db_name?
sslmode=verify-full&
sslfactory=org.postgresql.ssl.SingleCertValidatingFactory&
sslfactoryarg=classpath:cert/rds-ca-cert_name.p12

Açıklaması şöyle

We need to use SingleCertValidatingFactory class to specify certificate file on classpath (or from file system, environment variables etc). This class has argument sslfactoryarg where we can add path to certificate file.

require modu

Açıklaması şöyle

require, allow and prefer all default to a non-validating SSL factory and do not check the validity of the certificate or the host name.

Failover and Load Balance

load Balancing Seçeneği

Açıklaması şöyle. Yani loadBalanceHosts=true yapmak lazım

In default mode (disabled) hosts are connected in the given order. If enabled, hosts are chosen randomly from the set of suitable candidates.

Örnek

Şöyle yaparız

jdbc:postgresql://host1:5432,host2:5432,host3:5432/mydb?loadBalanceHosts=true

Örnek - master

Şöyle yaparız

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master

Örnek - slave

Şöyle yaparız

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true

PgBouncer - Lightweight Connection Pooling

Giriş

Açıklaması şöyle. 

A lightweight connection pooler for PostgreSQL that can help manage connections and improve performance, but lacks advanced features like replication and failover support. PGBouncer is a good option if you need a simpler solution focused primarily on connection pooling.

Açıklaması şöyle

PostgreSQL was built to handle a small number of long-lasting connections.

That’s why it has a relatively small default limit of connections and it’s not uncommon for people to get the error “connection limit exceeded for non-superusers”.

...

The answer is using a connection pool. Sometimes the framework you’re using offers that (like SQLAlchemy), other times you might need a specialized tool.

But if you’re using multiple machines to serve your API (as you probably are), you will run into the previous problem again since your framework pooler will only be responsible for managing the instance it’s running on.

When you have multiple servers, a good solution is using PGBouncer.

This amazing tool will allow you to connect multiple servers to it instead of connecting directly to Postgres.

It will queue incoming connections when the concurrent amount is greater than the one specified by you.

Mimarisel Özellikler

Şöyle. C ile geliştirilmiş çok kaynak tüketmeyen bir uygulama

• Primary purpose: Connection pooling only
• Architecture: Single-threaded, event-driven
• Memory per connection: ~2 KB
• Configuration complexity: Simple (one config file)
• Codebase size: ~15k lines of C
• Protocol support: PostgreSQL wire protocol

Kullanım Şekli

Açıklaması şöyle

A common production pattern is Patroni managing PostgreSQL replication and failover, PgBouncer providing connection pooling and HAProxy routing traffic to the correct server. This approach gives you best-in-class tooling for each concern but requires more components to configure and maintain. Pgpool-II combines these functions, trading some performance for operational simplicity.

PgBouncer has two modes

1. Session Pooling - 1 server conn per client

Açıklaması şöyle. 

Session mode keeps a connection assigned to a client for its entire session. Connection only goes back to the pool when the client disconnects.

This preserves:

• Prepared statements
• Session variables
• Temporary tables

But it also means fewer connections available for reuse. Defeats the whole purpose of pooling if you need tons of concurrent sessions.

Bir başka açıklama şöyle

A server connection is assigned to a client for the entire duration of the client session. When the client disconnects, the connection returns to the pool. This is the safest mode and supports all PostgreSQL features including prepared statements, temporary tables and session-level settings.

2. Transaction Pooling - server conn per tx

Açıklama şöyle. Bu mod kısa işler için bir sürü transaction açan web uygulamalarına daha uygun. 

A server connection is assigned only for the duration of a transaction. Between transactions, the connection goes back to the pool for other clients to use. This is much more efficient for web applications where each request is a short transaction. The downside is that session-level features like prepared statements, SET commands and LISTEN/NOTIFY don't work reliably because different transactions may run on different server connections.

Açıklaması şöyle. Yani sadece 25 tane bağlantı ile 1000 kişiye hizmet verebilmek mümkün

A pool of 25 backend connections can serve 1000+ clients because each client only holds a connection for the few milliseconds it takes to run a transaction. With Pgpool-II's session-based approach, you need roughly one child process per concurrent client, which uses more resources.

Kaynak kullanımı şöyle. 

• Query overhead per statement: < 100 microseconds
• Max clients on 2 GB RAM: 10,000+
• Memory at 500 clients: ~10–20 MB
• Connection storm (1000 simultaneous): Handles with minimal resource spike
• CPU usage under load: Minimal (event-driven)

Prepared statement kullanamamayı irdeleyen bir açıklama şöyle. Bu durumda client side prepared statement kullanmak gerekiyor.

In transaction mode, PgBouncer assigns a connection to a client only for the duration of a transaction. After COMMIT or ROLLBACK, the connection goes back to the pool.

Sounds efficient, right? Reuse connections aggressively. Less overhead.

The problem: Our application used prepared statements, session-level settings, and temporary tables.

Every time PgBouncer reassigned a connection, we lost:

• Prepared statements (had to re-prepare)
• Session variables (had to re-set)
• Temporary tables (gone)

So every request was paying a hidden tax:

3. Statement pooling: server conn per query 

Açıklama şöyle

A server connection is assigned for a single statement. After the statement completes, the connection is released. This is the most aggressive mode but only works if your application never uses multi-statement transactions.

PgBouncer Neden Lazım

Belki SpringBoot + Hikari bizim için "connecton pooling" yapıyor. Ancak merkezi veri tabanımıza bağlanan 100 tane micro service olsun. Her birisi 2 tane bağlantı açsa yine de 200 tane eder. Bu durumda Hikari yerine PgBouncer kullanmak çok daha iyi.

Şeklen şöyle

Eğer PgBouncer önüne de HAProxy yani bir Load Balancer koyarsak şeklen şöyle

pgbouncer komutu

Başlatmak için şöyle yaparız

pgbouncer -d pgbouncer.ini

reload için şöyle yaparız

pgbouncer -d -R pgbouncer.ini

Açıklaması şöyle

PgBouncer supports online reload (RELOAD command or SIGHUP) for most settings without dropping connections.

Log dosyaları şöyle

tail -f pgbouncer.log

Monitoring

Açıklaması şöyle

For monitoring, PgBouncer exposes its stats through a virtual pgbouncer database where you run SHOW commands (SHOW STATS, SHOW POOLS, SHOW CLIENTS)

pgbouncer.ini Dosyası

Açıklaması şöyle

PgBouncer configuration involves a single INI file. You specify the databases, set the pool mode, define connection limits and point it at an auth file. That's it. The defaults are sensible and most deployments only need to adjust a handful of settings.

The key settings you'll touch are pool_mode (session, transaction or statement), max_client_conn (maximum client connections allowed), default_pool_size (server connections per user/database pair) and reserve_pool_size (extra connections for burst traffic). That covers most deployments.

1. databases Başlığı

[databases] başlığı altında arka taraftaki veri tabanı için bağlantılar belirtilir.

Örnek

Şöyle yaparız

* = host=localhost port=5432

Örnek

Şöyle yaparız

# Configuration details:
[databases]
writer = host=<primary_db_host> port=5432 dbname=postgres
reader = host=<secondary_db_host> port=5432 dbname=postgres

Örnek

Şöyle yaparız

[databases]
mydatabase = host=127.0.0.1 port=5432 dbname=mydatabase

2. pgbouncer başlığı

[pgbouncer] başlığı altında PgBouncer sunucusu için ayarlar belirtilir

admin_users Alanı

Açıklaması şöyle

This is the database user who will have permission to make PgBouncer settings in the database.

Örnek

Şöyle yaparız

;;;
;;; Users allowed into database 'pgbouncer'
;;;

;; comma-separated list of users who are allowed to change settings
;admin_users = user2, someadmin, otheradmin
admin_users = my_db_user
...

auth_file Alanı

Açıklaması şöyle

The auth_file setting in the pgbouncer configuration file specifies the path to a file that contains a list of users and their passwords for authentication. When a client attempts to connect to the pgbouncer, it checks the user’s credentials against the list of users in the userlist.txt file.

"username" "password"

This file contains a single user named username with a password of password. You can add additional users to the file by adding additional lines with the format “username” “password”.

It is important to note that the userlist.txt file should be secured with appropriate permissions, as it contains sensitive information. By default, pgbouncer expects the userlist.txt file to be owned by the same user as the pgbouncer process and only readable by that user. As per your environment, you can modify the ownership and permissions of the file.

Moreover, it’s advisable to refrain from using a plain text file for storing user credentials. In production environments, it is recommended to use a more secure authentication method, such as SSL or GSSAPI, instead of plain text passwords.

Örnek

Şöyle yaparız

[databases]
mydatabase = host=127.0.0.1 port=5432 dbname=mydatabase

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

auth_type Alanı

Açıklaması şöyle

In the “Authentication settings” section, set auth_type = md5 to authenticate users by a password. The file with the database login and password will be located at /etc/pgbouncer/userlist.txt

listen_port Alanı

Örnek

Şöyle yaparız. Burada pgbouncer 6432 portunu dinliyor

$ cat /etc/pgbouncer/pgbouncer.ini
 
[databases]
world = host=127.0.0.1 port=5432 dbname=world
 
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admindb

Örnek

Şöyle yaparız. Burada pgbouncer 6432 portunu dinliyor

[databases]
* = host = free-tier.gcp-us-central1.cockroachlabs.cloud  dbname = artem-freetier-2924.defaultdb user = artem port = 26257

 [pgbouncer]
 listen_port = 6432
 listen_addr = *
 auth_type = trust
 auth_file = userlist.txt
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 admin_users = artem
 ignore_startup_parameters=extra_float_digits,options

# free tier provided cert
server_tls_sslmode = verify-full
server_tls_ca_file = /Users/artem/.postgresql/root.crt

max_client_conn Alanı

Kaç tane bağlantı olabileceğini belirtir

Örnek

Şöyle yaparız

;;;
;;; Connection limits
;;;

;; Total number of clients that can connect
max_client_conn = 5000
...

pool_mode Alanı

Örnek

Şöyle yaparız

[databases]
mydb = host=db-primary port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling is best for most web apps
pool_mode = transaction

# Max connections to actual Postgres
server_pool_size = 25

# Max client connections PgBouncer will accept
max_client_conn = 1000

# Idle connection timeout
server_idle_timeout = 600

Örnek

Şöyle yaparız

;;;
;;; Pooler personality questions
;;;

;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes
pool_mode = transaction
...

userlist.txt Dosyası

Authentication için gerekir. Açıklaması şöyle

It takes the form of "username" and "password" separated by a space on each new line.

Örnek

Şöyle yaparız

$ cat /etc/pgbouncer/userlist.txt
"admindb" "root123"

 Açıklaması şöyle

So, in this case, I have installed PgBouncer in the same database node, listening in all IP addresses, and it connects to a PostgreSQL database called “world”. I am also managing the allowed users in the userlist.txt file with a plain-text password that can be encrypted if needed.

PostgreSQL 13 ve Öncesine Kadar

Açıklaması şöyle

If you are using PostgreSQL versions up to 13 inclusively, then the default password_encryption method is md5.

Place the username in double quotes and the md5 password hash (in one line):

Örnek

Şöyle yaparız

"my_db_user" "md5badc318d987f61146c6ad8e15d84a111"

md5 hesaplamak için şöyle yaparız

echo "md5"$(echo -n 'YourdbpasswordYourdbusername' | md5sum | awk ' { print $1 } ')

PostgreSQL 14 ve Sonrası

Açıklaması şöyle

If you are using PostgreSQL versions starting from 14, then the default password_encryption method is scram-sha-256.

Örnek

Şöyle yaparız

"my_db_user" "SCRAM-SHA-256$4096:lLN4+i05+kpeffD4s3rRiw==$Oq62iUGamAaF5cpB+agWV4u3xfc5cZCRtvMhmA+Zm3E=:hHkCesEi0p0wLWk1uUEeTtJTYLXHKDLdy2te3VAOe8s="

scram-sha-256 hesaplamak için şöyle yaparız

psql -h  -p  -Atq -U postgres -d postgres 
  -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

Comparison Operators - BETWEEN

Giriş

Comparison Operators şöyle

< less than

> greater than

<= less than or equal to

>= greater than or equal to

= equal

<> or != not equal

BETWEEN

Örnek

Şöyle yaparız

select * from test a
inner join (
  select distinct order_id from test
  where status_update = 'Delivered'
) b on a.order_id = b.order_id
where status_date between '2020-01-01' and '2020-04-25'