Partum

partum

I’ve named “Not a Vessel” to honor my wife and all the women who fight to preserve their identity through pregnancy. this Hashmask The following is our story. is a fine art collection of 16,384 unique pieces. It allows the owner of a piece to name it, with the name stored in the Ethereum blockchain. Some contain hidden traits like my wife’s Hashmask with her glyph of a pregnant woman on the top left. The Hashmasks We knew nothing when my wife was pregnant with our first child. We had no idea about pregnancy, birth, or being parents. We were both excited and terrified, same as all first-time parents-to-be. My wife, though, had been doing her research. She was doing everything to stay as healthy as possible and was looking forward to giving birth even though she knew it would be grueling. She’s a strong woman. Then, towards the end, and after being told twice that our son wasn’t growing, we got a scan. Our son was growing alright but his head was pointing up with the umbilical cord around his neck. Now, this sounds scary, but as long as he remained in the womb he’d be absolutely fine. The issue would be coming out as the baby could suffer brain damage if the head got stuck for too long. We discussed the possibility of still having a natural birth at length with the director of the maternity unit in one of the world’s best hospitals, to be certain of the risks. Although a natural birth was certainly possible the risk of harming our son was too high. The recommendation was to have a planned C-section instead. It was the best choice considering the odds. On the appointed day we went to the hospital and proceeded into an operating room. Then our son was extracted from my wife’s womb. The amazement of having a child hid the experience’s awfulness. We didn’t know any of the people in the operating room. My wife still is obsessed with the fact that she didn’t even take her shoes off. The post-partum was even worse. A C-section is major surgery. My wife couldn’t even stand to help her crying baby. She kept being told to “rest” while also getting people into her room every couple of hours for needless consultations, cleaning, food delivery, even to be offered photography services. The noise from the corridor was unbearable. She was supposed to stay three days. We left after two so that she could actually rest. Now, don’t get me wrong. It was an excellent medical facility. The staff members were impeccable professionals. The issue is that a hospital, as exceptional as it may be, is still a hospital. It will never be a comfortable place. When my wife got pregnant the second time she saw an opportunity to have a natural birth ( ). She wanted to have as little medical intervention as possible without incurring any unreasonable risks for her or our baby. with her shoes off! After a lot of careful research, she decided that she wanted to have our daughter at home. I was skeptical at first. Wasn’t that dangerous? What if something went wrong? What were the risks? As it turns out home births aren’t that risky. Medically trained midwives come to your home with enough diagnostic equipment to monitor the situation, knowledge to assess it and there’s a plan B of how and when to fall back to the hospital if there are complications. I can see the raising eyebrows; I raised mine. That cannot be zero-risk. There’s a risk to the baby and to the mother. This could end in disaster! Well, yes, like anything in life reducing any risk to the absolute zero usually involves huge sacrifices. I’m risk-averse when it’s reasonable to do so. My wife researched home births thoroughly and convinced me that the risks were comparable to giving birth in a hospital. Still not convinced? Please read about the and come back. cascade of intervention My wife came home crying after disclosing her home birth intention to the lead obstetrician. I started to fear for her mental health because I knew she was gearing up for a fight against the medical establishment and the chances of victory were slim. During her first pregnancy, she went into the operating room because it seemed the best thing to do. My concern this time was that she’d fight for months only to be forced somehow back into the operating room. She’s strong. She doesn’t give up a fight. She would be badly hurt if defeated again. I didn’t want her to take on this struggle. I begged her to concede but to no avail. I had to side with her. We’re married, you know. For better or worse, we’re a team. Through several months of consultations at the birth clinic, she surreptitiously assembled her own adept birth team. There would be two hardened midwives specialized in home births to take care of the baby on her flanks, one doula centered on her care plus me taking up the rear guard to speak in her name if she couldn’t. The medical establishment would not go gentle into that good night. There was a salvo of warnings: , , . More frequent consultations were suggested. The baby was growing slowly. We slid from the 25th percentile to the 17th percentile… “if it drops under the 10th percentile she should be induced,” they casually sniped. advanced age for childbearing her iron was low her amniotic fluid was low There were trust issues too. The birth clinic got paid per consultation. Was that the reason they wanted to engage more by stacking on added consultations? Measuring a baby in the womb has an enormous margin of error. Could we trust that; should we? Who could we trust? My wife would cry after each consultation. There was nothing wrong with the baby or was there? Was she doing the right thing? Did she have to capitulate and accept a hospital birth? Why, exactly? Eventually our trust in the birth clinic evaporated. Our confidence had coalesced on the midwives. My wife went AWOL on the last consultation. What was the point of being told that our baby was small and we should go to the hospital? We knew what the in utero test said and weren’t convinced that the medical establishment was right. My wife went into labor a couple of weeks later. The doula came to support us when the labor gathered pace. We called the midwives a couple of hours later. The birth team was assembled and at the ready. Our daughter’s birth wasn’t painless but it was flawless. My wife gave birth in our bedroom with the help of her birth team, myself, and even my five-year-old son. Everything went fine, better than the last time. My daughter went from my wife’s womb into our bedroom where she spent the first few days as if in a larger womb. Then she started discovering the world one room at a time followed by one face and one street at a time. We were relaxed and comfortable. My wife recovered easily. Our daughter came into this world healthy and beautiful. We were never close to danger at any point. My wife fought hard to preserve her identity and agency despite being pregnant. She shouldn’t have had to do it but she fought tooth and nail to be treated as her own person, her own person who was pregnant with a child that she cared about as only a mother can. She fought to not be a faceless vessel for a child. For that reason, I’m naming in her honor and in the honor of the women who fight to preserve their identity during pregnancy. this Hashmask Not a Vessel Obligatory Legal Disclaimer: Nothing in the personal story should be interpreted as medical advice nor should it be relied on as a substitute for a licensed medical professional’s opinion. The reader is advised to consult all medical resources available before making any decisions that could impact one’s well-being.

My Wife: A Strong Woman, Not a Vessel

What I Would Have Missed in July 2017

Babies at Work: It’s Weird that it’s Weird

Hello to all two of the people that read these (whenever I can find the time to write them). I’m here to continue my Freelancer series (I have a feeling this is going to be a LLOOOONNNGGGGG series of these, because I am pretty much figuring this stuff out as I go. Any major happenings that happen to occur in my life due to freelancing is usually what prompts these which is why this is only the second one that I’ve written. Anyway let’s get into this. Backstory Benjamin Grimm Duke About two months ago this little wonder fell out of my body after nine grueling months of growing him. The day he was born was a long and busy one, not only did I not get sleep the night before (I was working), I was already exhausted and burned out from the weeks before, not sleeping, being in and out of the hospital for what I thought was the big day, working on side projects, studying and wrangling his three sisters and older brother (that’s right folks I got 5 kiddos). And I know what you’re thinking, and I’m right there with you, I’m about five seconds from sleeping in a different house to keep this from happening again, but on the other hand I make cute babies……and I don’t mean to brag buuutttttt Dad is pretty easy on the eyes My partner makes some cute babies too, and he’s a wonderful father who does what he can to provide. We’ve never had an issue with improving our situation when I pop out another offspring. Things might be tight for a little bit, but we’ve always been able to stretch to make room for another bundle of joy if (and when) the need arises. I would however like to say that I am closed for business for AT LEAST the next year so I can properly recoperate and drop the baby weight…I’m going off cantor here, let me bring it back. When I got pregnant with Ben, I had a job, granted I was a contractor and if all went as planned, I would have been hired on permanently, unfortunately that didn’t happen (if you would like to know more please go dig through the old blog posts, I wrote about it about 6 months ago), and I was let go, no sooner than I was let go, I got picked up by not one, but two contracts. My mentor from my apprenticeship ( ) and snatched me up pretty much THE SAME WEEK I was let go, so I had no reason to think I should worry about money or anything like that. These two awesome entities saw an opportunity to help me out, and they did, and I’m grateful. HOWEVER, there were some things that I didn’t know before taking on both as a contractor about being an expectant parent (I put parent because this applies to guys too). Zee Spencer Operation Code You Need Employment Insurance To Go On Maternity Leave You pretty much need it for any medical leave, and as a contractor YOU FOOT THE BILL. This is because as a self-employed person you foot the bill for pretty much all of your expenses. Your taxes, your healthcare, and your equpiment and work area. Maternity leave is no exception, and you have to foot the bill to ensure that you can get the time off without starving to death. I was also told I could file for disability, but everywhere I tried to look for info on that, the wording was let’s say a bit vague as to how to go about it and what to do and how long it would last. Whether You’re Freelancer Or Employee You Should Take Maternity Leave Especially if you’re the one who the baby fell out of. I’m not saying take an entire year off, but it should probably be longer than a week, because I was only off for the three days I was in the hospital and the weekend after, and I completely burned out about two weeks ago (look for a future post) and it made it hard for me to focus on anything. I should have taken extra care to make time for leave because not only did I just have a baby, I still have 4 other kids to take care of on top of that. Sleeping…for now…… Take Whatever Help You Can Get Self explanatory, whether its folks offering to come help you clean some things or work some things out or even if they want to get you out of the house for a few minutes for some coffee. TAKE THE HELP. Don’t get caught up in the“I NEED MONEY I JUST HAD A BABY!” loop. Money isn’t more important than your well being or your sanity (I figured that out after experiencing that burn out a couple weeks ago, stay tuned folks). REST Take a break, get some air, walk the dog. Don’t keep your head in your keyboard, don’t go from baby to work to baby to work. Get sleep when the baby is sleeping, make sure you’re eating and do something for you every once in a while. Self care is important not only so that you can take of your new bundle, but so you can continue to do your job so it all doesn’t come crashing down and you got burned out and just do nothing. If You Feel Depressed/Sad/Angry Talk To Someone Don’t stuff it down. Some sadness and I’ll even say some depression is expected after having a baby. They call it baby blues and it’s normal, it’s your hormones rebalancing and you getting used to catering to a screaming blob of flesh pretty much all day every day. However if it’s prolonged (longer then about two weeks) you’re going to want to see a doctor because it’s probably post partum depression and that’s not something to ignore or mess around with. Take Care Of Yourself Make sure you’re eating, staying hydrated, still doing at least some of the things you liked to do before you took on being a parent and a contractor. Don’t get caught up in work and baby all the time, you’ll lose your mind if you do. Get baby snuggles, get some rest, pet the dog, play with your other kids if you have them, talk to your spouse/family/significant other. Don’t forget who you are or the things that you enjoyed doing before you had the baby. I bought an ergo wrap when I had my last kid, she didn’t like and I happened to have it when I had this one, I wrap him up to me and clean the house, work, draw and even play video games. I’ve done Google Hangouts with the kid sleeping in his wrap on camera. Pair Programming Remember, You Can Do This Whenever you start to feel like you’re failing as a parent or a developer or a contractor, just remember that you can do it. Hell you’re so good at it, you’re doing it and you don’t even realize it. You’re going to get down on yourself ALOT, and you’re going to think that you’re not doing your best because you can’t devote all of your attention to your new baby because you gotta eat, and so do they, but I’m here to tell you, the fact that you feel that way makes you a pretty awesome parent in my book. I’m Going To End It By Saying This He’s pooping Being a parent is challenging, and no one really knows what they’re doing. Coupling that with love/hate relationship that many of us develop with freelancing is not an easy task. There are no rule books that tell you what to do and how to do it, and it’s mostly because everyone handles it and works with it in their own way. There are some fundamental things that go without saying that you have to do (pay taxes, get health insurance and business insurance) and there are things that just sprout up out of NOWHERE and leave you guessing. You may not always come out on top when things like that occur (I have yet to, I think I can honestly say that) but it’s important to note that they way you handle things and knowledge and experience you gain is important, and it’ll help you through it if you encounter it again. I’m pullin’ for ya…… SHAMELESS PROMOTION AREA: is on medium! go follow them! Operation Code is AWESOME Free Code Camp Is the awesome founder of Operation Code! Follow him! David Molina Is Operation code’s Software Consultancy Go follow them! Deploy Like what I wrote? I have a patreon: https://www.patreon.com/ArtsyVet My loving sister (@busterbrimstone on twitter) has gone through the trouble of creating a GoFundMe because I have two vehicles that have fallen into HORRIFIC disrepair. My husband’s car is falling apart and mine just doesn’t run AT ALL. I’m not one for asking for help, and she sprung this on me and now I have to share it, but she’s giving away iHeart radio tickets to a random benefactor if the campaign reaches it’s goal in time for the concert. If you donate even a buck you’re in the running it can be found here: https://www.gofundme.com/2zue96a8

Things I wish Someone Would Have Told Me About Freelancing Part Two: Maternity Leave Edition

Location, Location, Location! PostGIS and Heroku Postgres

অবস্থান, অবস্থান, অবস্থান! পোস্টজিআইএস এবং হেরোকু পোস্টগ্রেস

Lage, Lage, Lage! PostGIS und Heroku Postgres

¡Ubicación, ubicación, ubicación! PostGIS y Heroku Postgres

Emplacement, emplacement, emplacement ! PostGIS et Heroku Postgres

स्थान, स्थान, स्थान! PostGIS और Heroku Postgres

場所、場所、場所！PostGIS と Heroku Postgres

위치, 위치, 위치! PostGIS와 Heroku Postgres

Localização, localização, localização! PostGIS e Heroku Postgres

Местоположение, Расположение, Местоположение! PostGIS и Heroku Postgres

Konum, Konum, Konum! PostGIS ve Heroku Postgres

Vị trí, vị trí, vị trí! PostGIS và Heroku Postgres

位置，位置，位置！PostGIS 和 Heroku Postgres

Scientific American, Volume XXIV., No. 12, March 18, 1871 by Various, is part of the HackerNoon Books Series. You can jump to any chapter in this book . THE SHERMAN PROCESS. here THE SHERMAN PROCESS. When people boast of extraordinary successes in processes the details of which are kept profoundly hidden from public scrutiny, and when the evidences of success are presented in the doubtful form of specimens which the public has no means of tracing directly to the process, the public is apt to be skeptical, and to express skepticism often in not very complimentary terms. For a considerable time, the public has been treated to highly-colored accounts of a wonderful metallurgic process whereby the best iron and steel were said to be made, from the very worst materials, almost in the twinkling of an eye. This process has been called after its assumed inventor, or discoverer, the "Sherman Process." The details of the process are still withheld, but we last week gave an extract from an English contemporary, which throws a little light upon the subject. The agent relied upon to effect the remarkable transformation claimed, is iodine, used preferably in the form of iodide of potassium, and very little of it is said to produce a most marvellous change in the character of the metal. A very feeble attempt at explaining the rationale of this effect has been made, in one or two English journals, which we opine will not prove very satisfactory to chemists and scientific metallurgists. The has published two three-column articles upon the subject, the first containing very little information, and the second a great number of unnecessary paragraphs, but which gives the proportion of the iodide used, in the extremely scientific and accurate formula expressed in the terms "a small quantity." Engineer Assertions of remarkable success have also been given. Nothing, however, was said of remarkable failures, of which there have doubtless been some. A series of continued successes would, we should think, by this time, have sufficed for the parturition of this metallurgic process, and the discovery would ere this have been introduced to the world, had there not been some drawbacks. We are not prepared to deny that the process is all that is claimed for it; but the way in which it has been managed is certainly one not likely to encourage faith in it. in toto The very name of "process" implies a system perfected, and if it be still so far back in the experimental stage that nothing definite in the way of results can be relied upon, it is not yet a process. If, in the use of iodine, in some instances, fine grades of iron or steel are produced, and in as many other experiments, with the same material, failures result, it is just as fair to attribute the failures to the iodine, as the successes. A process worthy the name is one that acts with approximate uniformity, and when, in its use, results vary widely from what is usual, the variation may be traced to important differences in the conditions of its application. On the whole, we are inclined to believe Mr. Sherman's experiments have not yet developed a definite process, and we shall receive with much allowance the glowing statements published in regard to it, until such time as it can face the world and defy unbelief. The patents obtained by Mr. Sherman seem to cover the use of iodine, rather than the manner of using it, and throw no light upon the rationale of the process. A patent was granted by the United States Patent Office, Sept. 13, 1870, to J. C. Atwood, in which the inventor claims the use of iodide of potassium in connection with the carbons and fluxes used in making and refining iron. In his specification he states that he uses about of this salt to eighty pounds of the metal. This is about 1⁄373 of one per cent. He uses in connection with this exceedingly small proportion of iodide of potassium, about two ounces of lampblack, or charcoal, and four ounces of manganese, and asserts that steel made with these materials will be superior in quality to that made by the old method. These claims we are inclined to discredit. Certainly, we see no chemical reason why this small amount of iodide should produce such an effect, and the specification itself throws no light upon our darkness. fifteen grains If the experiments in these so-called processes have no better basis than is apparent from such information as at present can be gathered respecting them, it is probable we shall wait some time before the promised revolution in iron and steel manufacture is accomplished through their use. About HackerNoon Book Series: We bring you the most important technical, scientific, and insightful public domain books. This book is part of the public domain. Various (2006). Scientific American, Volume XXIV., No. 12, March 18, 1871. Urbana, Illinois: Project Gutenberg. Retrieved https://www.gutenberg.org/cache/epub/19180/pg19180-images.html This eBook is for the use of anyone anywhere at no cost and with almost no restrictions whatsoever. You may copy it, give it away or re-use it under the terms of the Project Gutenberg License included with this eBook or online at , located at . www.gutenberg.org https://www.gutenberg.org/policy/license.html

THE SHERMAN PROCESS

If you’re working with growing PostgreSQL tables, you're likely no stranger to the challenges of managing large datasets efficiently: Your query performance is degrading. You’re dealing with maintenance overhead. You’re finding it hard to keep up with high ingestion rates. You’re having trouble managing your data lifecycle. . By partitioning your large PostgreSQL tables, you can keep them fast and efficient. But setting up and maintaining partitioned PostgreSQL tables in production can be difficult. Postgres partitioning is your most powerful ally in solving these problems “Yes,” your mind may go, “I might be able to improve my performance if I partition my tables, but this will be at the cost of countless hours spent on manual configurations, running maintenance jobs, testing, and not to mention the unforeseen issues that might pop up during scaling. It’s like having a potent car with an incredibly complicated gearbox.” If you’re using vanilla PostgreSQL in products like , there’s a lot of truth to this. You will undoubtedly spend much of your time managing your partitioned tables. Plus, you’ll have to deal with custom scripts, keep rigorous maintenance practices, and carefully monitor your performance to revisit and tweak your configuration whenever you see changes in your dataset or ingestion rate. Amazon RDS But guess what: there’s a better way of creating a Postgres partition, and it’s called hypertables. Meet Hypertables: Automatic PostgreSQL Partitioning for Your Large PostgreSQL Tables Hypertables (which are available via the and, in AWS, via ) are an innovation that makes the experience of creating a Postgres partition completely seamless. They automate the generation and management of data partitions without changing your user experience. TimescaleDB extension the Timescale platform Working with a hypertable feels exactly like working with a regular PostgreSQL table. But, under the covers, hypertables create all the partitioning magic, speeding up your queries and ingests. This performance boost will sustain as your tables' volume keeps growing, . making hypertables extremely scalable Hypertables are optimized for , so this is the type of partitioning that we’ll focus on in this article. However, hypertables also work for tables that aren’t time-based but have something similar, for example, a BIGINT primary key. time-based partitioning Let’s explain how hypertables work with an example. Imagine you have a PostgreSQL table called , where data from various IoT devices is stored with a timestamp. The table might look something like this: sensor_data CREATE TABLE sensor_data (
    device_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    temperature FLOAT NOT NULL,
    humidity FLOAT NOT NULL
); Now, as the volume of grows, you start facing performance issues and management complexities. Here’s where hypertables come to help. If you were using Timescale, the only thing you’d need to do is convert your table into a hypertable: sensor_data sensor_data SELECT create_hypertable('sensor_data', 'event_time'); This is how easy it is. With this simple command, is now a hypertable that automatically partitions your data by the column. sensor_data event_time Your PostgreSQL partitioning is all set. Native Partitioning vs. Hypertables: How Much Easier Does It Get? Let’s look at what’s happening under the hood. If you were using a traditional native method to create a Postgres partition, you would have to go through all these steps to set up partitioning in : sensor_data Create a parent table with the common schema and constraints but no data. Define child tables, each covering a specific time range. Add indexes to the parent and child tables. Set up a job for scheduling the creation of partitions. Set up a job for managing old partitions. Attaching it all together. Each one of these steps comes with its chunk of code; ; you’ll have to monitor potential issues on each one of these steps and set up adjustments manually along the way, etc. Overall, you’ll create significant maintenance overhead for yourself. they require you to run different extensions, like pg_partman and cron What hypertables do instead is encapsulate and automate all these steps, significantly reducing the complexity, manual effort, and potential for errors on your end: With hypertables, there’s no need to create a parent table manually and to define child tables for each time range. You would simply convert your existing table into a hypertable. Hypertables also simplify indexing. When you create an index on a hypertable, Timescale automatically creates the corresponding indexes on all current and future partitions, ensuring consistent query performance without manual adjustments. Hypertables automatically create new partitions on the fly based on the specified time interval. As new data is ingested, appropriate partitions are ready to store the data without manual intervention or scheduled jobs. , completely removing partition management from your to-do list. Using Timescale eliminates the risk of partitions not existing Timescale maintains its own partition catalogs and implements its own minimized locking strategy to ensure that your application’s read or write operations are never blocked by the underlying partitioning operations ( ). something that can be an issue in native PostgreSQL partitioning Once your PostgreSQL table becomes a hypertable, you can keep querying it as usual. You will instantly experience a performance boost. When you execute a query, Timescale’s query planner intelligently routes the query to the appropriate partition(s), ensuring that only relevant data is scanned. This process remains completely transparent; you don't need to think about it or worry about which partition contains which data. Something similarly straightforward happens when you ingest data. Timescale will take care of routing your new data to the appropriate partition under the hood, ensuring that each partition remains optimally sized. ( ) The default partition size is seven (7) days in Timescale, but you can easily modify this. Partitioning Is Only the Beginning: Features Unlocked With Hypertables Hypertables make partitioning seamless unlock a wealth of features that will help you improve your PostgreSQL performance even further and save you time when managing your data. and A few examples: By enabling , your hypertable will change from row to column-oriented. This can reduce storage usage by up to 95 % and unlock blazing-fast analytical queries while Columnar compression for faster queries and cheaper storage. Timescale compression allowing the data to be updated . Blazing-fast analytical views. By creating incrementally updated materialized views, known as continuous aggregates, you’ll improve the performance of aggregate queries tremendously. Continuous aggregates automatically refresh and store aggregated data, enabling you to build fast visualizations, including real-time insights and historical analytics that go back in time. Hypertables allow you to set up automatic data retention policies with one simple command: . You can just tell Timescale when you want your data dropped, and your hypertables will automatically drop outdated partitions when it’s time. Easy and configurable data retention. add_retention_policy Hypertables come with a full set of that give you a blazing-fast full set of mathematical analytical functions, procedures, and data types optimized for effectively querying, aggregating, and analyzing large volumes of data. SQL hyperfunctions to run analytics with fewer lines of code. hyperfunctions Queries that reference now( ) when pruning partitions , and your ordered DISTINCT queries will benefit from . Faster DISTINCT and now() queries. will perform better in Timescale SkipScan The lets you schedule any SQL or function-based job within PostgreSQL, meaning you don’t need an external scheduler or another extension like . Built-in job scheduler. Timescale job scheduler pg_cron When To Use Hypertables: Example Use Cases In sum, if you plan to partition your PostgreSQL tables by time, you’ll surely benefit from hypertables. But who doesn’t love some concrete use-case examples? Let’s paint a few scenarios where hypertables would be most useful. Needless to say, this is not a comprehensive list! . If you’re intrigued by hypertables and Timescale but are unsure if your use case is a fit, don’t hesitate to contact us Ingesting thousands of energy metrics per second An engineering team at a leading energy company is tasked with managing the data from a newly installed smart grid, a big investment for the energy company, which now has granular insights into energy consumption, distribution efficiency, and grid health metrics. Elements in the smart grid generate thousands of energy metrics per second that need to be properly collected, analyzed, and managed. These energy metrics are currently stored in PostgreSQL, but the engineering team has to figure out the best solution to ingest this high-velocity data efficiently without losing granularity or accuracy. They must also ensure they can query this data quickly for real-time monitoring and analysis. This would be an ideal use case for Timescale: Timescale’s hypertables can handle the high ingestion without imposing manual work on the team. Hypertables also optimized query performance, ensuring that real-time energy data will be readily accessible for queries. As the smart grid expands, Timescale's hypertables will seamlessly scale, accommodating increased data volumes without compromising performance. Given that Timescale is built on PostgreSQL, the engineering team can leverage their existing knowledge and tools, ensuring a smooth transition and minimal learning curve. -- Creating a table to store energy metrics from the smart grid
CREATE TABLE energy_metrics (
    element_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    voltage DECIMAL NOT NULL,
    current DECIMAL NOT NULL,
    frequency DECIMAL NOT NULL,
    PRIMARY KEY(grid_id, event_time)
); -- Converting the energy_metrics table into a hypertable
SELECT create_hypertable('energy_metrics', 'event_time'); -- Sample query to ingest new metrics data into the hypertable
INSERT INTO energy_metrics (element_id, event_time, voltage, current, frequency) 
VALUES  (1, NOW(), 210.5, 10.7, 50.01); -- Sample query to retrieve the latest energy metrics for real-time monitoring
SELECT * FROM energy_metrics 
WHERE element_id = 1 
ORDER BY event_time DESC 
LIMIT 10; Building dashboards for monitoring sensor data An industrial manufacturing company operates a range of heavy machinery and equipment in its facilities. Each piece of machinery is equipped with sensors that continuously monitor and log temperature data in a table to ensure optimal performance and safety. sensor_data The company needs its PostgreSQL database to achieve two distinct yet critical objectives: Provide engineers and maintenance staff with real-time temperature data to detect anomalies and ensure that machinery is operating within safe temperature ranges. Analyze historical temperature data to identify trends, predict maintenance needs, and improve operational efficiency. The team decides to turn into a hypertable. To facilitate real-time monitoring, they create a continuous aggregate to calculate the average temperature for every piece of machinery, which is updated every minute: sensor_table CREATE MATERIALIZED VIEW real_time_avg_temp
WITH (timescaledb.continuous, timescaledb.refresh_interval = '1m') AS
SELECT device_id,
       time_bucket('1 minute', event_time) AS one_min,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id, one_min; With , the maintenance team has immediate access to the average temperature of every machinery piece, enabling swift responses to temperature anomalies and preventing potential breakdowns. real_time_avg_temp For historical analysis, the team creates another continuous aggregate view, this time aggregating daily average temperatures: CREATE MATERIALIZED VIEW daily_avg_temp
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket('1 day', event_time) AS one_day,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id, one_day; Both views ( and ) feed into a monitoring dashboard. The maintenance team would get alerted of potential issues as they arise. At the same time, the team can review historical temperature trends, conduct analyses to predict when machinery might need maintenance, and optimize operational protocols to mitigate excessive temperature fluctuations. real_time_avg_temp daily_avg_temp Storing large volumes of weather data effectively An environmental research institute is collecting and analyzing many TBs of weather data to study climate change. The team already knows PostgreSQL, so they want to stick to it—but the storage cost is becoming a concern. The team decides to start using Timescale. After optimizing their database to reduce storage use and enabling compression, their storage costs become a fraction of what they were, with the data remaining fully accessible for analysis. CREATE TABLE weather_data (
    sensor_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    temperature DECIMAL NOT NULL,
    humidity DECIMAL NOT NULL,
    pressure DECIMAL NOT NULL,
    PRIMARY KEY(sensor_id, event_time)
); -- Conversion to hypertable 
SELECT create_hypertable('weather_data', 'event_time'); -- Enabling compression
ALTER TABLE weather_data 
SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
); -- Sample query
SELECT * FROM weather_data 
WHERE sensor_id = 1 
AND event_time > NOW() - INTERVAL '1 month'
ORDER BY event_time DESC 
LIMIT 10; Querying high volumes of crypto data in real-time A new crypto exchange is grappling with the challenge of providing real-time analytics to traders. As the data volume stored in the underlying PostgreSQL database increases, the engineering team struggles to keep the database fast enough. To them, it’s essential to deliver a better user experience than the competition, which has a more established but slower product. Keeping up the speed and responsiveness of their portal is paramount. The team knows that by partitioning their large pricing table, they’ll most likely improve query performance. Instead of attempting to manage partitioning themselves, since they’re already swamped, the engineers decide to implement Timescale. Once turned into a hypertable, their pricing table automatically partitions the data as it gets in. Real-time analytics, previously marred by delays, are now swift and accurate. P.S. Check out the story of how Messari improved its performance with hypertables. Get Started With Hypertables PostgreSQL partitioning is a powerful tool for managing large tables. On its own, Postgres partitioning can be complex to implement and maintain, but Timescale's hypertables make the whole process seamless and automatic. The best part is that by using hypertables, you’ll unlock a myriad of other awesome features (like columnar compression and automatic materialized views) that will make it even easier to scale your PostgreSQL database. If you're ready to explore Timescale's hypertables, , our fully managed PostgreSQL—but faster—cloud solution. It’s free for 30 days, and no credit card is required. If you are self-hosting your own PostgreSQL instance, start by signing up to Timescale you can get access to hypertables by adding the TimescaleDB extension . Written by . Carlota Soto Also published . here

How Hypertables Enable Automatic PostgreSQL Partitioning for Your Datasets

because it’s awesome: its rock-solid reliability, rich feature set, and open-source nature make it a popular choice for businesses of all sizes. But as your data grows, you may encounter certain challenges, especially when dealing with large tables. Anyone struggling with query performance, maintenance overhead, or managing the data lifecycle? 🙋 PostgreSQL is the most popular relational database on the planet One popular strategy to tackle these issues is . In this article, we'll explore when and why you should consider partitioning your large tables, sharing some best practices and common pitfalls in case you decide to move on with partitioning. table partitioning PostgreSQL What Is Table Partitioning? Table partitioning is a database design technique that divides a large table into smaller, more manageable pieces known as partitions. Each partition is a sub-table that holds a subset of the data, with each row existing in exactly one partition. Partitions can have their own indexes, but any unique indexes (including primary keys) must include the column that was used to partition the table. This technique can offer significant advantages when managing large tables. By breaking down a large table into smaller partitions, you can benefit from improved query performance, optimized index sizes, and more efficient data maintenance operations, keeping your large-scale databases more agile and responsive overall. But partitioning is not a one-size-fits-all solution: sometimes, partitioning won't benefit you. It could even harm your performance, as we’ll see later on. In terms of the types of partitioning you could implement, : PostgreSQL supports three partitioning strategies : this strategy is ideal for time-series data or incrementing sequences (maybe a BIGINT primary key), where you partition data based on a range of values (e.g.,g: by day or by a number of keys). Range partitioning : you would use list partitioning when you want to partition data based on specific values in a column (e.g., country or department). List partitioning : hash partitioning is suitable when there is no clear partitioning key, as it distributes data evenly across partitions based on a hash function. Hash partitioning One important characteristic of (independent of which strategy you follow) is that it does not support “global indexes”: there is no way to create an index across partitions. Instead, when querying across partitions, PostgreSQL will evaluate the clause, and if there are constraints on the partition column, it will use those to exclude partitions that don’t hold data relevant to the query. partitioning in PostgreSQL WHERE Consider the following example: Imagine we took a table storing sensor metrics and partitioned it by the column representing the ingest time. The table could now be broken down into multiple partitions, each of which could store the metrics ingested in a single day.  Once the table is partitioned by day (if we query the table for a range of days), PostgreSQL would exclude the partitions outside the range—meaning that only the relevant data would be scanned. Compare this to querying the original table, where we would have to scan all the data (or one large index). One can clearly see how partitioning could help you reduce CPU, disk activity, and query time. It’s probably clear to you at this point that if you were to implement partitioning in a production setup, you'd need automation in place to create and maintain partitions, especially if the partitioned table expects to receive data continuously. There are multiple ways to do this, the most common being the following: You can write SQL procedures/functions to check if the necessary partitions for upcoming days/weeks/months exist, using cron (on Unix-like systems) or Task Scheduler (on Windows) to run the script regularly. You can use a PostgreSQL extension dedicated to this, the most popular being (which will probably need to be paired with a scheduler). pg_partman Determining which methodology to follow for table partitioning —but this is something you can think about later. For now, let’s help you figure out if partitioning would be beneficial for you. deserves its own article When Should You Consider Partitioning? As we said above, partitioning can be very powerful, but it is certainly not a good idea for every single use case. Contrary to what people often think, the decision to partition a PostgreSQL table is not strictly based on an absolute table size but rather on various factors that interact with the table's size. It's essential to evaluate your database's characteristics and requirements before implementing partitioning. Generally speaking, you should start thinking about partitioning if you identify with one (or more) of the following: As we said above, the size of your tables is not the only thing that determines if you may see benefits from partitioning; this said, if you have large tables (this is from tens of millions of rows to billions of rows), you would probably benefit from partitioning. You have large tables. Even if the current table size isn't massive, a high data ingestion rate can indicate that the table will grow significantly in the near future. To implement a partitioning strategy, it might be beneficial to preemptively manage this growth before it starts affecting your performance and maintenance operations. Your ingestion rate is very high. Partitioning may also be beneficial if your queries are starting to slow down, especially those that should only touch a subset of your data. This could be true even when your tables are smaller due to the complexity of the data and queries. For example, partitioning can significantly enhance query performance when your daily queries include searches based on a specific range or criteria. Let's say you're dealing with time-series data: partitioning by date can help you quickly retrieve records within a particular time frame without scanning the entire table. You’re beginning to notice query performance degradation. As a table grows, maintenance operations like , , and indexing can take longer and might start impacting your operational efficiency. Partitioning can simplify these operations because you can focus on maintaining smaller partitions independently, reducing the impact on your database's overall performance. You’re dealing with maintenance overhead. VACUUM ANALYZE If your dataset has built-in obsolescence, where older data is periodically purged, partitioning can make these operations much more efficient. Dropping an old partition is much faster and less resource-intensive than deleting rows. You’re managing data retention policies. If you want to operate with limited memory, you might benefit from partitioning, as smaller indexes and data chunks fit better in memory and improve cache hit rates. In most cases, this will also improve performance. You want to use less memory. When Not to Use Partitioning On the contrary, there are situations where introducing partitioning may be counterproductive, and you may want to look at other optimizations instead, such as tuning your indexes or queries: If your tables are not large and are unlikely to grow significantly, partitioning would add unnecessary complexity without delivering noticeable benefits. Your tables are small, and you’re not ingesting at high rates. Implementing and maintaining partitioned tables introduces a certain level of operational and architectural complexity: managing such tables would become more challenging, so make sure you feel ready to go there— You’re just starting with PostgreSQL. or use a hosted service to manage this for you. If your queries usually access data uniformly across the table rather than focusing on specific subsets (like recent data, ranges of data, specific categories, and so on), then your query performance could actually become worse. You have uniform data access patterns. If most of your queries don’t use your partition key in the clause, you’ll scan every partition every time. This will be slow and will get slower the more partitions you have. Frequent full scans. WHERE Partitioning Best Practices If, after reading this article, , make sure to follow this advice—it will help you set up things right from the start: you’ve decided to give partitioning a go . This is one of the first design questions that will come to your mind when implementing partitioning: what’s your ideal partition size? The answer is it depends—you should aim for a balance between too large and too small. While PostgreSQL can handle a high number of partitions, having too many will increase planning time and could negatively affect query time. At the same time, if your partitions are too large, then you won’t be able to use ranges to exclude data, and the effectiveness of partition pruning will be minimized. Choose the right partition size Aim to keep partitions relatively uniform in size, ensuring that maintenance tasks and query performances are consistent across partitions. Keep your partition size consistent. . Opt for a key that aligns with your query patterns. For instance, if most of your queries filter by date, a timestamp or date column would be an ideal partitioning key. Choose the right partitioning key . Ensure that partitions for upcoming periods (e.g., future days or months) are created ahead of time so there's no interruption in data ingestion. While you can use a default partition to catch orphaned records, in practice, this introduces a maintenance burden and does not perform well. Create partitions in advance . For example, if you're partitioning by time and data has a limited useful life, schedule regular tasks to drop or archive old partitions. Take advantage of data retention policies to maintain old partitions If you’re especially interested in optimizing query performance, make sure to analyze and understand the query execution plan to validate that only necessary partitions are being scanned. Optimize your queries. If you're using tablespaces to place partitions on different storage engines (e.g., EBS or S3), ensure that frequently accessed partitions are on faster storage and older or less accessed partitions can be on slower, cheaper storage. Properly place partitions across different storage mediums. Pitfalls to Avoid Similarly, make sure to avoid these common mistakes: . It's tempting to create a high number of small partitions, but as we mentioned earlier, this won’t work well—you’ll get into query planning and management challenges. Over-partitioning . Avoid creating unnecessary indexes on your partitions. Only index the columns that are frequently filtered or joined on. Inefficient indexing Queries spanning multiple partitions or not using the partition key in the clause might suffer in performance. Ensure that the majority of your queries are optimized for the partitioning scheme. Unoptimized query pattern. WHERE . If you insert data with no partition to live in, it will either be rejected or stored in a partition. Ensure you either pre-create partitions at a quiet time (as this will lock your table) or use an extension that creates new partitions on the fly. Running out of partitions DEFAULT If you're creating many partitions, especially on different tablespaces or disks, monitor disk usage to avoid out-of-space issues. Monitor disk usage (partitions need extra space). Wrap Up Partitioning can be a powerful ally in your production database, especially if your tables are starting to get big, your ingestion is growing, or your query performance is starting to degrade. Still, partitioning is not a one-size-fits-all solution: consider if it’s the right fit for your use case before deciding to implement it, and if we decide to do so, make sure to follow best practices to get the most out of it. PostgreSQL tables Also published . here

PostgreSQL Table Partitioning: Boosting Performance and Management

THE UNCONSCIOUS ORIGIN OF THE HERO

LIST OF BUBBLES

Stories, News & Mentions about PARTUM

Partum’s Stories on HackerNoon