Intro PostgreSQL is an excellent open-source database. It has an advanced planner that can usually generate good execution plans. But there are cases where it may produce suboptimal results. In order to estimate how many rows a query may return, Postgres relies on different statistics. They are available in the pg_stats view for each column of a table and the pg_class table and are updated when the VACUUM or ANALYZE commands are ran....
Making a GGPO-style rollback networking multiplayer game
Intro We will be making a simplified multiplayer clone of the arcade game, Mario Bros. It will have no deaths and a single infinite level. It will have rollback networking and should provide a smooth experience for people even with higher latencies. The source code is here. The game is playable here. Rollback Networking Rollback networking is an elegant form of netcode that can be simple and effective to implement. It works for games of all types from twitchy fighting games to slow-paced turn based ones....
Suboptimal PostgreSQL Queries
Intro Listed here are some potentially suboptimal PostgreSQL queries I’ve noticed in a professional setting or just looking at different queries over the years. Note not all are suboptimal under all conditions, and as always, you want to benchmark any queries yourself to confirm any issues. All queries and query problems heavily depend on your data, data distribution, statistics collected, Postgres configuration, and hardware. All queries were ran multiple times to make sure there were as few cache misses as possible and the median times were taken....
PostgreSQL Lock Contention For Frequently Updated Rows
Intro A transaction that updates a row in Postgres acquires a RowExclusiveLock on the table it updates. The transaction will hold this lock until the end of the transaction where it will either commit or abort. No other transaction can update this row while this lock is held. This can potentially lead to lock contention for transactions that are trying to concurrently update the same row. How much impact does this have and what can you do to alleviate this if there is an issue?...