Posts in Postgresql (8 found)
Andrew Atkinson 3 weeks ago

PostgreSQL 18: 23x Faster Inserts With UUID v7

We recently switched to version 7 (v7) uuid primary keys and saw significantly faster inserts for some tables. The databases were running Postgres 18.4 and mostly used v1 with some v4 uuid values for primary keys. Changing the column default involved running a single alter table command, but did require an exclusive lock on the table, blocking everything including selects. To solve that, we used a short lock timeout and lots of retries. The biggest speedup was 23x faster average execution time for a multi-row insert query called 12,000 times per minute on a table with billions of rows. This September and October I'll be in Austin, TX and NYC, check my Book page for upcoming appearances. The system uses UUID primary keys throughout. I typically recommend starting with bigint and sequences over UUID v4 primary keys , although here uuid v1 was used. Insert performance is not as bad for v1 compared with v4. Still though, v7 brings better performance than both for inserts and can also result in smaller indexes with fewer page splits meaning less CPU and IO. What drives bad performance for v4 and to a lesser extent v1? Let’s do a quick refresher. As new table rows are inserted and a primary key is defined, primary key values are maintained in sorted order in a b-tree index. Just like table rows, index entries in Postgres are stored in fixed size 8kb pages. Postgres needs to know in which page to place the new index entry. For sorted order, the first bytes of new uuid values are compared. For v4 given new values are very random and not monotonically increasing (they lack “monotonicity”), values can be earlier or later, meaning they’re unlikely to be placed into the same recently accessed page. This is bad for caching! When new values are monotonically increasing, the recently accessed page is “hot” in the Postgres buffer cache (in memory copy of the on-disk page). When Postgres is not able to use the hot index page for the newly inserted value, that page could be outside the buffer cache, not in the OS cache, and ultimately result in a much slower disk read which increases latency. Besides the worse insert performance, since v4 values are scattered to more pages, this means there are more “page splits” when new inserts are attempted in full pages. Page splits cause more latency from increase WAL and IO. We experimented and benchmarked with v1, v4, and v7 uuid formats and we leveraged the research and write-ups from external sources like the ones below. Benchmarks are great, but what kind of real world results did we see? We decided to make this the new default unless v4 was needed for more randomness. After all qualified tables were changed, I began going through insert queries for each changed table. For many of the tables, there wasn’t an obvious change. However, for a handful we saw an immediate and significant improvement. I picked 5 with speedups of 6x, 8x, 9x, 20x, and 23x. The PgAnalyze graphs for the 23x, 9x, and 6x queries are shown below. Showing PgAnalyze insert query graphs for tables A, B, C: Table A - 23x reduction. 0.7ms to 0.03ms, 12000 calls/min Table B - 9x reduction. 0.6ms to 0.07ms, 2000 calls/min Table C - 6x reduction. 0.50ms to 0.08ms, 9500 calls/min Now that we’ve seen the results, let’s talk about how this was done and the challenges. The UUID values came from various sources: We replaced most of these with the function in Postgres 18. To do that, we needed to run a single statement per table. The statement ran fast, so no problem, right? One wrinkle we found was that modifying the column default while fast, required an lock. This lock type conflicts with every read and write operation including regular statements. For our highest queried tables, they’re queried constantly, so this was a problem. There was almost never a “window” to perform this operation, and we didn’t want to take downtime for this switch. While heavily queried tables were a challenge, infrequently queried tables did not pose a problem for this alter table statement at all. For those, we could use our migrations framework (Active Record in Ruby on Rails) and perform the using a regular old migration. For those, we did add some safeguards, by creating an explicit transaction and using to control timeout values. We’d set short timeouts for the to give up quickly if it didn’t work or ran too long. For the higher activity tables, we’d need some retries. We’d use a manual psql session: The would commit if it grabbed the lock within 50ms, or we’d get an error that the was reached. The benefit of the manual approach was we could retry until successful and backfill a Rails migration to keep everything in sync. A more sophisticated solution might have automated retries within Ruby. However, for our most heavily queried tables, we wanted even more control over the retries. How did we do that? Sometimes one or two retries would do the job. Great, we’d move on. However, for our most heavily queried table that didn’t work. What ended up working was using the same strategy of retries, but just adding more sophistication with looping and backoffs. Claude helped me cook up the PL/pgSQL looping retry function below, I did some testing and was ready to try it. It has these features: By using the function above, we were able to find a small window to perform the after several dozen quick retries! In cases where even many retries won’t work, and we don’t want downtime, we may be left with needing to actively monitor lock holder queries and to cancel them (assuming that’s ok). Thanks to Ants Aasma from the community PostgreSQL Slack for this idea. We didn’t end up needing to do this, but here was my prep for this. It’s still useful to review lock holder queries. First we’d inspect live queries: And identify queries holding locks: If we find them, we could cancel them to create a window to run our . That could mean bad user experience so you’d need to figure that out for your database. We’d likely want to stack up our operation to occur immediately after. Fortunately we didn’t end up needing to do this, but I’d be interested to hear the stories from others with heavily queried databases. Since uuid v7 values use a timestamp in their first bits, this timestamp can be easily decoded. This can be viewed as “leaking” or exposing the creation time of the record via that timestamp, which could be a downside for your database. You’ll have to decide that. v4 UUIDs do not expose the creation time. We found some significant speedups for insert queries after switching to primary keys, for a relatively low effort change. A nice ROI. The only wrinkle was the exclusive lock required, but we solved that with short lock related timeouts and many retries. Although this didn’t benefit 100% of our tables, the gains for some were significant and uuid v7 has become our new default choice for uuid primary keys. Thanks to the Postgres core team for creating this new capability within Postgres. The availability in core made it possible to adopt on AWS RDS which supports a limited amount of extensions. Thanks for reading, and until next time. How Sequential UUIDv7 Boosts Ingestion Performance Simplicity and power of UUID v7 PostgreSQL UUID Performance: Benchmarking Random (v4) and Time-based (v7) UUIDs The function from the module The function added in Postgres 13 that generates v4 UUIDs natively UUID v4 values sent by a client application, which meant the column default function was not used Try up to 50 times (max attempts is configurable) Add a pause in between retries, with a jittered backoff of 50-250ms

ruby Ruby database Database PostgreSQL performance Performance
0 views
Andrew Atkinson 3 months ago

Beta Testing PostgreSQL With Docker

The Postgres community values feedback from testing of Beta releases, and with Docker it’s been easier to get pre-release versions up and running. With the recent announcement of PostgreSQL 19 Beta 1 , let’s get that running and test some of the new capabilities. First, you’ll need to install Docker for your OS! Grab the version needed for your OS and processor architecture, for example ARM or AMD/Intel/x86. On MacOS run or in your Terminal to learn more about your hardware details. For Windows check Install Docker Desktop on Windows Official Postgres images for Docker Postgres are limited to fully released versions. Fortunately @yosifkit created a PR to add 19 Beta 1 (merged by @ tianon ) with instructions for how to use to build pre-release versions. This command downloads and builds : With that built, I could invoke with . I named mine . I also passed the env vars below based on how I run other Docker Postgres containers (these options may not be necessary). The final command: To check if it’s running, I run . For logs I’d run: . The container is running and the logs have what we want: “database system is ready to accept connections”. Let’s connect to the database using psql on the container: We should see output like showing version 19: Great. Let’s try out some things in 19. 19 Added a new system view for checking out locks. Let’s try it out: We get a lot of new data like counts, and more. What about the new extension? First let’s load it and then create a table to experiment with: With that in place we can show the output via with a new parameter: I wonder why the rows estimate is 2550 by default? Let’s run . After doing that, it looks more sensible with a estimate of 1: The extension gained new capabilities in 19. Let’s try it out: Oops, we need to add it to first. We can see that’s currently not the case: One way to do that with is the parameter as follows: Now we see what we want in : We have not yet enabled the extension though given doesn’t list it. Let’s do that: Now shows it, and we’re ready to query it. One of the additions is tracking the use of prepared statements. Let’s create a basic table and prepared statement. Create table again if needed: Create a simple prepared statement and execute it. The goal here is for to increment the field. Now let’s execute it: Did it work? It worked! We see was incremented. This looks very useful to monitor the use of prepared statements. Please give this a shot and experiment with new features in Postgres 19! Add 19.x builds (currently beta 1)

PostgreSQL docker Docker
0 views
Andrew Atkinson 1 years ago

Short alphanumeric pseudo random identifiers in Postgres

In this post, we’ll cover a way to generate short, alphanumeric, pseudo random identifiers using native Postgres tactics. These identifiers can be used for things like transactions or reservations, where users need to read and share them easily. This approach is an alternative to using long, random generated values like UUID values, which have downsides for usability and performance. We’ll call the identifier a and store it in a column with that name. Here are some example values: In database design, we can use natural or surrogate keys to identify rows. We won’t cover the differences here as that’s out of scope. For our identifier, we’re going to generate it from a conventional surrogate primary key called . We aren’t using natural keys here. The is intended for use outside the database, while the primary key is used inside the database to be referenced by foreign key columns on other tables. Whle is short which minimizes space and speeds up access, the main reason for it is for usability. With that said, the target for total space consumption was to be fewer bytes than a 16-byte UUID. This was achieved with an primary key and this additional 5 character generated value, targeting a smaller database where this provides plenty of unique values now and into the future. Let’s get into the design details. Here were the desired design properties: Additional details: Here are the functions used: This function obfuscates the integer value using exclusive or (XOR) obfuscation. Main entrypoint function: This converts the obfuscated value into the alphanumeric value, used within . This is “base 62” with the 26 upper and lower case characters, and 10 numbers (0-9). Reverses the back into the original integer. Used within : For a length of 5 with this system, we can create up to around ~1 billion unique values. This was sufficiently large for the original use case. For use cases requiring more values, by storing 6 characters for then up to ~56 billion values could be generated, based on a primary key. Let’s create a sample table with an primary key with a generated identity column. Besides the use in the identity column, we’ll again use the keyword to create a column for the . The column uses the column as input, obfuscates it, encodes it to base 62, producing a 5 character value. How do we guarantee conforms to our expected data properties? Constraints! For an existing system, we could add a unique index first as follows: Then we can add the unique constraint using the unique index, along with the constraint: Let’s insert data into the table: Let’s query the data, and also make sure it’s reversed (using the function) properly: Let’s compare the time spent inserting 1 million rows into an equivalent table without the column or value generation. That took an average of 2037.906 milliseconds, or around 2 seconds on my machine. Inserting 1 million rows with the took an average of 6954.070 or around 7 seconds, or about 3.41x slower. Note that these times were with the indexes and constraints in place on the table in the second example, but not the first, meaning their presence contributed to the total time. Summary: Creating this identifier made the write operations 3.4x slower for me locally, which was an acceptable amount of overhead for the intended use case. Compared with random values, the pseudo random remains orderable, which means that lookups for individual rows or ranges of rows can use indexes, running fast and reliably even as row counts grow. We can add a unique index on the column like this: We can very that individual lookups or range scans use this index, by inspecting query execution plans for this table. https://github.com/andyatkinson/pg_scripts/pull/15 Feedback on this approach is welcomed! Please use my contact form to provide feedback or leave comments on the PR. Future enhancements to this could include unit tests using pgTAP for the functions, packaging them into an extension, or supporting more features like case insensitivity or a modified input alphabet. Thanks for reading! A fixed size, 5 characters in length, regardless of the size of the input integer (and within the range of the data type) Fewer bytes of space than a data type An obfuscated value, pseudo random, not easily guessable. While not easily guessable, this is not meant to be “secure” Reversibility back into the original integer Only native Postgres capabilities, no extensions, client web app language can be anything as it’s within Postgres Non math-heavy implementation is stored using not not , following recommendations for best practices PL/PgSQL functions, native Postgres data types and constraints are used, like UNIQUE, NOT NULL, and CHECK, and a stored generated column. Converts integers to bits, uses exclusive-or (XOR) bitwise operation and modulo operations. Did not set out to support case insensitivity now, possible future enhancement Did not try to exclude similar-looking characters (see: Base32 Crockford below), possible future enhancement Uses a Hexadecimal key (make this any key you want) Sets a max value for the data type range , which is just under 1 billion possible values. This was enough for this system and into the future, but a bigger system would want to use Converts integer bytes into bits gets a constraint and , so we know we have a unique value A constraint is added to validate the length Base32 Crockford - An emphasis ease of use for humans: removing similar looking characters, case insensitivity. ULID - Also 128 bits/8 bytes like UUIDs, so I had ruled these out for space consumption, and they’re slightly less “usable” NanoIDs at PlanetScale - I like aspects of NanoID. This is random generation though like UUID vs. encoding a unique integer.

PostgreSQL sql Sql database Database
0 views
Shayon Mukherjee 1 years ago

Another look into PostgreSQL CTE materialization and non-idempotent subqueries

A few days ago, I wrote about a surprising planner behavior with CTEs, DELETE, and LIMIT in PostgreSQL, a piece I hastily put together on a bus ride. That post clearly only scratched the surface of a deeper issue that I’ve since spent way too many hours exploring. So here are some more formed thoughts and findings. The core issue revisited Let’s quickly recap: when using a query like the following, the planner might execute your query in ways you don’t expect.

database Database PostgreSQL
0 views
Shayon Mukherjee 2 years ago

Stop Relying on IF NOT EXISTS for Concurrent Index Creation in PostgreSQL

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments. Let’s understand how PostgreSQL handles concurrent index creation When we initiate CREATE INDEX CONCURRENTLY, PostgreSQL first creates an entry for the index in the system catalogs (specifically in pg_index) and marks it as invalid.

database Database PostgreSQL performance Performance
0 views
hyPiRion 2 years ago

Implementing System-Versioned Tables in Postgres

There's no official support for system-versioned tables in Postgres yet, and the temporal extensions aren't supported on Azure/AWS/GCP. Let's implement it ourselves with 3 triggers and an index.

tutorial Tutorial database Database PostgreSQL
0 views
Pinaraf's website 13 years ago

Review – “Instant PostgreSQL Starter”

Thanks to Shaun M. Thomas , I have been offered a numeric copy of the “ Instant PostgreSQL Backup ” book from Packt publishing, and was provided with the “ Instant PostgreSQL Starter ” book to review. Considering my current work-situation, doing a lot of PostgreSQL advertising and basic teaching, I was interested in reviewing this one… Like the Instant collection ditto says, it’s short and fast. I kind of disagree with the “focused” for this one, but it’s perfectly fine considering the aim of that book. Years ago, when I was a kid, I discovered databases with a tiny MySQL-oriented book. It teaches you the basis : how to install, basic SQL queries, some rudimentary PHP integration. This book looks a bit like its PostgreSQL-based counterpart. It’s a quick travel through installation, basic manipulation, and the (controversy) “Top 9 features you need to know about”. And that’s exactly the kind of book we need. So, what’s inside ? I’d say what you need to kick-start with PostgreSQL. The installation part is straight forward : download, click, done. Now you can launch pgadmin, create an user, a database, and you’re done. Next time someone tells you PostgreSQL ain’t easy to install, show him that book. The second part is a fast SQL discovery, covering a few PostgreSQL niceties. It’s damn simple : Create, Read, Update, Delete. You won’t learn about indexes, functions, advanced queries here. For someone discovering SQL, it’s what needs to be known to just start… The last part, “Top 9 features you need to know about”, is a bit more hard to describe. PostgreSQL is a RDBMS with included batteries, choosing 9 features must have been a really hard time for the author, and I think nobody can be blamed for not choosing that or that feature you like : too much choice… The author spends some time on pg_crypto, the RETURNING clause with serial, hstore, XML, even recursive queries… This is, from my point of view, the troublesome part of the book : mentioning all these features means introducing complicated SQL queries. I would never teach someone how to do recursive queries before teaching him joins, it’s like going from elementary school to university in fourty pages. But the positive part is that an open-minded and curious reader will have a great teaser and nice tracks to follow to increase his knowledge of PostgreSQL. Mentioning hstore is really cool, that’s one of the PostgreSQL feature one have to know… To sum up my point of view about this book : it’s a nice book for beginners, especially considering the current NoSQL movement and people forgetting about SQL and databases. It’s a bit sad we don’t have more books like this one about PostgreSQL. I really hope Packt publishing will try to have a complete collection, from introduction (this book) to really advanced needs ( PostgreSQL High Performance comes to mind) through advanced SQL queries, administration tips and so on… They have a book about PostgreSQL Server Programming planned next month, I’m really looking forward to this one.

Xml sql Sql php Php database Database PostgreSQL
0 views