Lessons about PostgreSQL (page 4)

The links, tools and tips our engineers and designers share with each other, one “today I learned” at a time.

lessons shared: 83
people sharing: 13
database started in: 2017

Link offline
compose.com · offline
Faster Performance with Unlogged Tables in PostgreSQL:
Rebeca SaraiMay 5, 2020
stackoverflow.com
When using psycopg2's ThreadedConnectionPool, an additional sempahore is needed to avoid "connection pool exhausted" errors:
Flávio JuvenalApril 13, 2020
postgresqltutorial.com
PostgreSQL uses the search path to resolve object names. When we refer to a table using just its name, PostgreSQL searches fo...
PostgreSQL uses the search path to resolve object names. When we refer to a table using just its name, PostgreSQL searches for the table by using something called a schema search path, which is a list of schemas to look in. The default is "$user", public:
Rebeca SaraiFebruary 19, 2020
medium.com
Lessons learned scaling PostgreSQL database to 1.2bn records/month:
Flávio JuvenalFebruary 3, 2020
postgresql.org
How to combine multiple indexes:
11.5. Combining Multiple Indexes A single index scan can only use query clauses that use the index's columns with operators of …
Lais VarejãoDecember 18, 2019
erthalion.info
PostgreSQL at low level: stay curious! >
Rebeca SaraiDecember 6, 2019
Today I learned
"DataError: integer out of range" in psycopg may be caused when you have a table with an integer auto-incrementing id and you...
"DataError: integer out of range" in psycopg may be caused when you have a table with an integer auto-incrementing id and you insert more than 2^31-1 records. Note that if you TRUNCATE the table, the id sequence DOES NOT reset. Use ALTER SEQUENCE sequence-name RESTART WITH 1; for that. Find out the sequence name with \d+ table_name.
Flávio JuvenalNovember 12, 2019
stackoverflow.com
Best way to select random rows PostgreSQL:
Flávio JuvenalNovember 7, 2019
speakerdeck.com
PostgreSQL Configuration for Humans:
Video: https://www.youtube.com/watch?v=IFIXpm73qtk PostgreSQL is the world’s most advanced open source database. Indeed! With around 270 configuratio…
Flávio JuvenalOctober 23, 2019
depesz.com
Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default:
Flávio JuvenalSeptember 30, 2019
Today I learned
If you have 4 parallel workers and need to process all rows from a SQL table, probably it's best to split the work with LIMIT...
If you have 4 parallel workers and need to process all rows from a SQL table, probably it's best to split the work with LIMIT/OFFSET into 4 parts and consume them with 4 tasks than to chose some smaller LIMIT/OFFSET and generate more than 4 tasks. OFFSET can get really slow and it doesn't build up speed when you make consecutive ones like OFFSET 1000, OFFSET 1100, OFFSET 1200, etc. In Python: limit = math.ceil(n_rows / n_workers) offsets = range(0, n_rows, limit)
Flávio JuvenalJuly 25, 2019
hakibenita.com
Fastest Way to Load Data Into PostgreSQL Using Python
Gustavo CarvalhoJuly 10, 2019
devcenter.heroku.com
PostgreSQL Concurrency with MVCC:
Understand how Multi Version Concurrency Control is implemented in Postgres and how it affects the design of highly concurrent applications.
Flávio JuvenalJuly 5, 2019
pgdash.io
Getting The Most Out Of Your PostgreSQL Indexes:
Flávio JuvenalMay 27, 2019
pythonspeed.com
Farewell to fsync(): 10× faster database tests with Docker
Realistic tests require a real database—but that can be difficult and slow. But Docker makes it simple, and some tweaks can make faster.
Gustavo CarvalhoMarch 28, 2019