# Memory settingsshared_buffers = 16GBwork_mem = 64MBmaintenance_work_mem = 2GBeffective_cache_size = 48GB# Connection settingsmax_connections = 500# Checkpoint settingscheckpoint_segments = 32checkpoint_completion_target = 0.9# WAL settingswal_buffers = 16MBmax_wal_size = 4GB# Logging settingslog_min_duration_statement = 5000 # Log queries taking longer than 5 seconds
postgres=# \d usersTable "public.users"Column | Type | Collation | Nullable | Default--------------+--------------------------------+-----------+----------+---------firstname | character varying(300) | | |lastname | character varying(300) | | |name | character varying(300) | | |createdat | timestamp(6) without time zone | | |emailaddress | character varying(300) | | |lockout | boolean | | |status | numeric(30,0) | | |lockout_date | timestamp(6) without time zone | | |Indexes:"users_at_idx_1" btree (lockout, status, createdat DESC NULLS LAST)
SELECT
*
FROM
users
WHERE
lockout IN (true)
AND status IN (3, 4)
AND createdat IN ('2023-05-18 00:00:00', '2023-06-19 00:00:00');Index Scan using users_at_idx_1 on public.users (cost=0.56..24879.59 rows=7255 width=72) (actual time=0.132..33.704 rows=3686 loops=1)
Output: firstname, lastname, name, createdat, emailaddress, lockout, status, lockout_date
Index Cond: ((users.lockout = true) AND (users.status = ANY ('{3,4}'::numeric[])) AND (users.createdat = ANY ('{"2023-05-18 00:00:00","2023-06-19 00:00:00"}'::timestamp without time zone[])))
Buffers: shared hit=48 read=3653
Planning:
Buffers: shared hit=53
Planning Time: 0.584 ms
Execution Time: 34.102 msA set of GIN-specific operators that can be used in a query depends on the operator class specified at index creation. For JSONB, supported classes are:jsonb_ops — the default operator class, which provides two categories of operators: checking the existence of keys and checking the existence of values based on JSONPath expressions or key-value pairs.jsonb_path_ops — provides only the latter category and offers better performance for these operators.
Create a GIN index on the JSONB column using the jsonb_path_ops operator class. This limits the operator to only @>, but that operator allows equality comparisons on any number of properties located at different levels within the JSONB document in a single WHERE criteria.
CREATE TABLE sample_jsonb (id serial NOT NULL PRIMARY KEY, data jsonb);
INSERT INTO sample_jsonb (data) VALUES
('{"name":"First", "count":12, "orderDate":"2022-03-03T12:14:01", "extra":"some text"}');
INSERT INTO sample_jsonb (data) VALUES
('{"name": "Second", "count": 23, "orderDate": "2022-04-03T15:17:01"}');
CREATE INDEX sample_jsonb_path_ops_idx
ON sample_jsonb USING GIN (data jsonb_path_ops);
-- select all rows where the name property is equal to "First" a
SELECT * FROM sample_jsonb WHERE data @> '{"name":"First"}'::jsonb
-- select all rows where count is greater than 15
SELECT * FROM sample_jsonb WHERE data @@ '$.count > 15'CREATE INDEX gin_data ON books_data using gin(data);
EXPLAIN ANALYZE SELECT * FROM books_data WHERE data @> '{"author" : "Mark Figueroa"}';
Row Level Security (RLS) lets you define access control policies at the row level within tables. This means that you can specify which users can SELECT, INSERT, UPDATE, or DELETE rows based on the actual data within the row.
CREATE TABLE client (client_id PRIMARY KEY,client_name VARCHAR(255) NOT NULL,email VARCHAR(255),registration_date DATE DEFAULT CURRENT_DATE);
ALTER TABLE client ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS client_isolation_policy ON client;
CREATE POLICY client_isolation_policy on client
USING (current_user =CONCAT('appuser_', client_id::text));
create user appuser_1001 WITH ENCRYPTED PASSWORD 'password'; GRANT connect on database sample_db to appuser_1001; GRANT USAGE ON SCHEMA public TO appuser_1001; GRANT select on all tables in schema public to appuser_1001; GRANT INSERT,UPDATE ON all tables in schema public TO appuser_1001;
select * from client c ; client_id|client_name|email |registration_date| ---------+-----------+---------------+-----------------+ 1001|Client 1001|abc@example.com| 2023-10-17| 1002|Client 1002|abc@example.com| 2023-10-17|
select * from client c ;client_id|client_name|email |registration_date|---------+-----------+---------------+-----------------+1001|Client 1001|abc@example.com| 2023-10-17|
ULIDs contain a 48-bit timestamp followed by 80 bits of random data. The timestamp allows ULIDs to be sorted, which works much better with the b-tree data structure databases use for indexing.”
ULIDsSnowflakeComposite keys